How to Upgrade Timezone ( DST ) Manually During Oracle 12c Upgrade

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/