Hi,
I will explain How to Upgrade Timezone ( DST ) Manually During Oracle 12c Upgrade.
If you want to learn how to Upgrade database from Oracle 11g to Oracle 12.2, you can read the following article.
https://ittutorial.org/manual-upgrade-oracle-database-from-11-2-0-4-to-12-2-0-1-without-dbua/
Our Timezone version was 14 before Oracle 12.2 upgrade as follows.
SQL> select TZ_VERSION from registry$database; TZ_VERSION ---------- 14
Once Oracle 12.2 upgrade is completed, I have also upgraded Timezone to 28 with the following steps.
Step-1: Shutdown database and startup database in upgrade mode as follows.
SQL> shu immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> SQL> SQL> startup upgrade; ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance ORACLE instance started. Total System Global Area 2.7488E+11 bytes Fixed Size 29906208 bytes Variable Size 2.6307E+10 bytes Database Buffers 2.4803E+11 bytes Redo Buffers 506961920 bytes Database mounted. Database opened. SQL>
Step-2: Purge Recycle bin and set the following Oracle hidden parameters.
SQL> purge dba_recyclebin; DBA Recyclebin purged. SQL> SQL> alter session set "_simple_view_merging"=TRUE; Session altered. SQL> alter session set "_with_subquery"=materialize; Session altered. SQL>
Step-3: Run the prepare step.
exec DBMS_DST.BEGIN_PREPARE(26);
Step-4: Truncate the following tables.
SQL>TRUNCATE TABLE SYS.DST$TRIGGER_TABLE; Table truncated. SQL>TRUNCATE TABLE sys.dst$affected_tables; Table truncated. SQL>TRUNCATE TABLE sys.dst$error_table; Table truncated.
Step-5: Query the tables if they keep any data or not.
SQL> select * from sys.dst$affected_tables; no rows selected SQL> select * from sys.dst$error_table; no rows selected SQL> SQL>
Step-6: Run the timezone upgrade scripts as follows.
SQL> EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('upg_tzv');
PL/SQL procedure successfully completed.
SQL>
SQL> exec DBMS_DST.BEGIN_UPGRADE(26); PL/SQL procedure successfully completed. SQL> SELECT * FROM sys.dst$error_table; no rows selected SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME; 2 3 4 PROPERTY_NAME VALUE -------------------------------------------------------------------------------------------------------------------------------- ------------------------------ DST_PRIMARY_TT_VERSION 26 DST_SECONDARY_TT_VERSION 14 DST_UPGRADE_STATE UPGRADE SQL> SQL> alter session set "_with_subquery"=materialize; alter session set "_simple_view_merging"=TRUE; Session altered. SQL> Session altered. SQL>
Step-7: Now Upgrade database timezone as follows.
SQL> VAR numfail number
BEGIN
DBMS_DST.UPGRADE_DATABASE(:numfail,
parallel => TRUE,
log_errors => TRUE,
log_errors_table => 'SYS.DST$ERROR_TABLE',
log_triggers_table => 'SYS.DST$TRIGGER_TABLE',
error_on_overlap_time => FALSE,
error_on_nonexisting_time => FALSE);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);
SQL> END;
/ 2 3 4 5 6 7 8 9 10 11
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
SQL> SELECT * FROM sys.dst$error_table;
no rows selected
Step-8: Now end timezone upgrade as follows.
SQL> VAR fail number
BEGIN
DBMS_DST.END_UPGRADE(:fail);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);
END;SQL> 2 3 4
5 /
PL/SQL procedure successfully completed.
Step-9: Check timezone file and parameters as follows.
SQL> SELECT * FROM v$timezone_file; FILENAME VERSION CON_ID -------------------- ---------- ---------- timezlrg_26.dat 26 0 SQL> SQL> SQL> SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME; 2 3 4 PROPERTY_NAME VALUE -------------------------------------------------------------------------------------------------------------------------------- ------------------------------ DST_PRIMARY_TT_VERSION 26 DST_SECONDARY_TT_VERSION 0 DST_UPGRADE_STATE NONE SQL> SQL> SQL> SELECT VERSION FROM v$timezone_file; VERSION ---------- 26
Step-10: Check timezone version from database, it is not updated. You need to update it as follows.
SQL> select TZ_VERSION from registry$database; TZ_VERSION ---------- 14 SQL> update registry$database set TZ_VERSION = (select version FROM v$timezone_file); commit; 2 3 1 row updated. SQL> Commit complete. SQL>
Step-11: Now check it, Timezone version has been 26.
SQL> select TZ_VERSION from registry$database; TZ_VERSION ---------- 26
Do you want to learn Oracle Database for Beginners, then read the following articles.
https://ittutorial.org/oracle-database-19c-tutorials-for-beginners/