SQL> conn / as sysdba
SQL> -- this gives the current RDBMS DST version
SQL> SELECT version FROM v$timezone_file;
VERSION
----------
32
SQL> column PROPERTY_NAME format a30
SQL> column VALUE format A30
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
2 FROM DATABASE_PROPERTIES
3 WHERE PROPERTY_NAME LIKE 'DST_%'
4 ORDER BY PROPERTY_NAME;
PROPERTY_NAME VALUE
------------------------------ ------------------------------
DST_PRIMARY_TT_VERSION 32
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONE
-- Find out the latest DST version
-- Below output shows version 40 is the latest one
SQL> ! ls -ltr $ORACLE_HOME/oracore/zoneinfo/timezone_*.dat
-rw-r--r--. 1 oracle oinstall 344425 Dec 18 2012 /u01/db19c/oracore/zoneinfo/timezone_13.dat
-rw-r--r--. 1 oracle oinstall 286264 Dec 18 2012 /u01/db19c/oracore/zoneinfo/timezone_4.dat
-rw-r--r--. 1 oracle oinstall 274900 Dec 18 2012 /u01/db19c/oracore/zoneinfo/timezone_2.dat
-rw-r--r--. 1 oracle oinstall 302100 Dec 9 2015 /u01/db19c/oracore/zoneinfo/timezone_8.dat
-rw-r--r--. 1 oracle oinstall 341718 Dec 9 2015 /u01/db19c/oracore/zoneinfo/timezone_17.dat
-rw-r--r--. 1 oracle oinstall 344448 Dec 9 2015 /u01/db19c/oracore/zoneinfo/timezone_14.dat
-rw-r--r--. 1 oracle oinstall 345356 Dec 9 2015 /u01/db19c/oracore/zoneinfo/timezone_11.dat
-rw-r--r--. 1 oracle oinstall 343594 Jun 22 2016 /u01/db19c/oracore/zoneinfo/timezone_22.dat
-rw-r--r--. 1 oracle oinstall 345637 Jun 22 2016 /u01/db19c/oracore/zoneinfo/timezone_10.dat
-rw-r--r--. 1 oracle oinstall 342602 Jul 25 2016 /u01/db19c/oracore/zoneinfo/timezone_27.dat
-rw-r--r--. 1 oracle oinstall 286651 Aug 10 2016 /u01/db19c/oracore/zoneinfo/timezone_3.dat
-rw-r--r--. 1 oracle oinstall 342602 Aug 10 2016 /u01/db19c/oracore/zoneinfo/timezone_26.dat
-rw-r--r--. 1 oracle oinstall 340197 Aug 10 2016 /u01/db19c/oracore/zoneinfo/timezone_25.dat
-rw-r--r--. 1 oracle oinstall 340192 Aug 10 2016 /u01/db19c/oracore/zoneinfo/timezone_24.dat
-rw-r--r--. 1 oracle oinstall 343768 Aug 10 2016 /u01/db19c/oracore/zoneinfo/timezone_23.dat
-rw-r--r--. 1 oracle oinstall 343249 Aug 10 2016 /u01/db19c/oracore/zoneinfo/timezone_21.dat
-rw-r--r--. 1 oracle oinstall 343167 Aug 10 2016 /u01/db19c/oracore/zoneinfo/timezone_20.dat
-rw-r--r--. 1 oracle oinstall 274427 Aug 10 2016 /u01/db19c/oracore/zoneinfo/timezone_1.dat
-rw-r--r--. 1 oracle oinstall 343167 Aug 10 2016 /u01/db19c/oracore/zoneinfo/timezone_19.dat
-rw-r--r--. 1 oracle oinstall 341718 Aug 10 2016 /u01/db19c/oracore/zoneinfo/timezone_18.dat
-rw-r--r--. 1 oracle oinstall 343044 Aug 10 2016 /u01/db19c/oracore/zoneinfo/timezone_16.dat
-rw-r--r--. 1 oracle oinstall 344448 Aug 10 2016 /u01/db19c/oracore/zoneinfo/timezone_15.dat
-rw-r--r--. 1 oracle oinstall 345024 Aug 10 2016 /u01/db19c/oracore/zoneinfo/timezone_12.dat
-rw-r--r--. 1 oracle oinstall 351525 Aug 10 2016 /u01/db19c/oracore/zoneinfo/timezone_9.dat
-rw-r--r--. 1 oracle oinstall 286815 Aug 10 2016 /u01/db19c/oracore/zoneinfo/timezone_7.dat
-rw-r--r--. 1 oracle oinstall 286217 Aug 10 2016 /u01/db19c/oracore/zoneinfo/timezone_6.dat
-rw-r--r--. 1 oracle oinstall 286310 Aug 10 2016 /u01/db19c/oracore/zoneinfo/timezone_5.dat
-rw-r--r--. 1 oracle oinstall 341401 Sep 28 2016 /u01/db19c/oracore/zoneinfo/timezone_28.dat
-rw-r--r--. 1 oracle oinstall 341401 Dec 5 2016 /u01/db19c/oracore/zoneinfo/timezone_29.dat
-rw-r--r--. 1 oracle oinstall 340884 May 3 2017 /u01/db19c/oracore/zoneinfo/timezone_30.dat
-rw-r--r--. 1 oracle oinstall 340892 Nov 6 2017 /u01/db19c/oracore/zoneinfo/timezone_31.dat
-rw-r--r--. 1 oracle oinstall 340869 Jun 20 2018 /u01/db19c/oracore/zoneinfo/timezone_32.dat
-rw-r--r-- 1 oracle oinstall 416668 Jan 16 15:01 /u01/db19c/oracore/zoneinfo/timezone_40.dat
-rw-r--r-- 1 oracle oinstall 416668 Jan 16 15:01 /u01/db19c/oracore/zoneinfo/timezone_39.dat
-rw-r--r-- 1 oracle oinstall 420560 Jan 16 15:01 /u01/db19c/oracore/zoneinfo/timezone_38.dat
-rw-r--r-- 1 oracle oinstall 420560 Jan 16 15:01 /u01/db19c/oracore/zoneinfo/timezone_37.dat
-rw-r--r-- 1 oracle oinstall 420442 Jan 16 15:01 /u01/db19c/oracore/zoneinfo/timezone_36.dat
-rw-r--r-- 1 oracle oinstall 420256 Jan 16 15:01 /u01/db19c/oracore/zoneinfo/timezone_35.dat
-rw-r--r-- 1 oracle oinstall 423103 Jan 16 15:01 /u01/db19c/oracore/zoneinfo/timezone_34.dat
-rw-r--r-- 1 oracle oinstall 425763 Jan 16 15:01 /u01/db19c/oracore/zoneinfo/timezone_33.dat
SQL> -- If there are objects containing TSTZ data in recycle bin,
SQL> -- please purge the bin now.
SQL>
SQL> purge dba_recyclebin;
DBA Recyclebin purged.
SQL> -- Set client_info so one can use:
SQL> -- select .... from V$SESSION where CLIENT_INFO = 'upg_tzv';
SQL>
SQL> EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('upg_tzv')
PL/SQL procedure successfully completed.
SQL> -- this alter session might speed up DBMS_DST on some db's
SQL> -- see Bug 10209691 / Bug 12658443
SQL>
SQL> alter session set "_with_subquery"=materialize;
Session altered.
SQL> -- to avoid the issue in note 1407273.1
SQL>
SQL> alter session set "_simple_view_merging"=TRUE;
Session altered.
SQL> -- start prepare window
SQL> -- these steps will NOT update any data yet.
SQL>
SQL> exec DBMS_DST.BEGIN_PREPARE(40);
PL/SQL procedure successfully completed.
SQL> -- check for prepare status
SQL>
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
2 FROM DATABASE_PROPERTIES
3 WHERE PROPERTY_NAME LIKE 'DST_%'
4 ORDER BY PROPERTY_NAME;
PROPERTY_NAME VALUE
------------------------------ ------------------------------
DST_PRIMARY_TT_VERSION 32
DST_SECONDARY_TT_VERSION 40
DST_UPGRADE_STATE PREPARE
SQL> -- truncate logging tables if they exist.
SQL>
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.
SQL> -- log affected data
SQL>
SQL> set serveroutput on
SQL> BEGIN
2 DBMS_DST.FIND_AFFECTED_TABLES
3 (affected_tables => 'sys.dst$affected_tables',
4 log_errors => TRUE,
5 log_errors_table => 'sys.dst$error_table');
6 END;
7 /
PL/SQL procedure successfully completed.
SQL> -- Now check what tables have affected data in TSTZ columns.
SQL> -- If dst$affected_tables has no rows then there is no actual data to update by DBMS_DST
SQL> -- If dst$affected_tables has rows it simply means those rows need
SQL> -- to be updated by DBM_DST during the DST upgrade (= point 4)
SQL> -- because they contain timezones that are affected by the DST upgrade
SQL>
SQL> SELECT * FROM sys.dst$affected_tables;
no rows selected
SQL> -- If dst$affected_tables has rows then you can see in dst$error_table
SQL> -- if there are any rows with a "problem" and what kind of problem there are in those rows.
SQL> -- Note that if there are rows in dst$affected_tables
SQL> -- this does not mean there need to be rows in dst$error_table
SQL> --
SQL> SELECT * FROM sys.dst$error_table;
SQL> -- error_on_overlap_time is error number ORA-1883
SQL> -- error_on_nonexisting_time is error number ORA-1878
SQL> SELECT * FROM sys.dst$error_table;
no rows selected
SQL> -- all "error_on_overlap_time" rows
SQL>
SQL> SELECT * FROM sys.dst$error_table where ERROR_NUMBER= '1883';
no rows selected
SQL> -- all "error_on_nonexisting_time" rows
SQL> SELECT * FROM sys.dst$error_table where ERROR_NUMBER= '1878';
no rows selected
SQL> -- check for all other possible problems
SQL> SELECT * FROM sys.dst$error_table where ERROR_NUMBER not in ('1878','1883');
no rows selected
SQL> -- end prepare window, the rows above will stay in those tables.
SQL> EXEC DBMS_DST.END_PREPARE;
A prepare window has been successfully ended.
PL/SQL procedure successfully completed.
SQL> -- check if this is ended
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
2 FROM DATABASE_PROPERTIES
3 WHERE PROPERTY_NAME LIKE 'DST_%'
4 ORDER BY PROPERTY_NAME;
PROPERTY_NAME VALUE
------------------------------ ------------------------------
DST_PRIMARY_TT_VERSION 32
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONE
SQL> -- Do the actual RDBMS DST version update of the database using DBMS_DST
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup upgrade;
ORACLE instance started.
Total System Global Area 4194300344 bytes
Fixed Size 9171384 bytes
Variable Size 2097152000 bytes
Database Buffers 1023410176 bytes
Redo Buffers 7602176 bytes
In-Memory Area 1056964608 bytes
Database mounted.
Database opened.
SQL> alter pluggable database pdb1 open upgrade;
Pluggable database altered.
SQL> column name for a30
SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED MIGRATE
PDB1 MIGRATE
SQL> set serveroutput on
SQL> EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('upg_tzv')
PL/SQL procedure successfully completed.
SQL>
SQL> alter session set "_with_subquery"=materialize;
Session altered.
SQL> alter session set "_simple_view_merging"=TRUE;
Session altered.
SQL> -- start upgrade window
SQL>
SQL> EXEC DBMS_DST.BEGIN_UPGRADE(40);
An upgrade window has been successfully started.
PL/SQL procedure successfully completed.
SQL>
SQL> -- check if this select give no rows, if it does something went wrong
SQL>
SQL> SELECT * FROM sys.dst$error_table;
no rows selected
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
2 FROM DATABASE_PROPERTIES
3 WHERE PROPERTY_NAME LIKE 'DST_%'
4 ORDER BY PROPERTY_NAME;
PROPERTY_NAME VALUE
------------------------------ ------------------------------
DST_PRIMARY_TT_VERSION 40
DST_SECONDARY_TT_VERSION 32
DST_UPGRADE_STATE UPGRADE
SQL> -- Optionally you can check what user tables still need to be updated using DBMS_DST.UPGRADE_DATABASE
SQL> -- BEGIN_UPGRADE upgrades system tables that contain TSTZ data and marks user tables
SQL> -- (containing TSTZ data) with the UPGRADE_IN_PROGRESS property.
SQL> -- even if this select gives no rows you still need to do to the rest of the steps
SQL> -- it simply gives an indication of how many user objects need to processed in the later steps
SQL> -- some oracle provided users may be listed here, that is normal
SQL> column owner for a30
SQL> col table_name for a30
SQL> SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS FROM ALL_TSTZ_TABLES where UPGRADE_IN_PROGRESS='YES';
OWNER TABLE_NAME UPG
------------------------------ ------------------------------ ---
GSMADMIN_INTERNAL AQ$_CHANGE_LOG_QUEUE_TABLE_L YES
DVSYS AUDIT_TRAIL$ YES
MDSYS SDO_DIAG_MESSAGES_TABLE YES
DVSYS SIMULATION_LOG$ YES
GSMADMIN_INTERNAL AQ$_CHANGE_LOG_QUEUE_TABLE_S YES
SQL> shutdown immediate
Database closed.
Database dismounted.
SQL> startup
ORACLE instance started.
Total System Global Area 4194300344 bytes
Fixed Size 9171384 bytes
Variable Size 2097152000 bytes
Database Buffers 1023410176 bytes
Redo Buffers 7602176 bytes
In-Memory Area 1056964608 bytes
Database mounted.
Database opened.
SQL>
SQL> -- now upgrade the tables who need action
SQL> alter session set "_with_subquery"=materialize;
Session altered.
SQL> alter session set "_simple_view_merging"=TRUE;
Session altered.
SQL> set serveroutput on
SQL> VAR numfail number
SQL> BEGIN
2 DBMS_DST.UPGRADE_DATABASE(:numfail,
3 parallel => TRUE,
4 log_errors => TRUE,
5 log_errors_table => 'SYS.DST$ERROR_TABLE',
6 log_triggers_table => 'SYS.DST$TRIGGER_TABLE',
7 error_on_overlap_time => FALSE,
8 error_on_nonexisting_time => FALSE);
9 DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);
10 END;
11 /
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"
Number of failures: 0
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L"
Number of failures: 0
Table list: "MDSYS"."SDO_DIAG_MESSAGES_TABLE"
Number of failures: 0
Table list: "DVSYS"."AUDIT_TRAIL$"
Number of failures: 0
Table list: "DVSYS"."SIMULATION_LOG$"
Number of failures: 0
Failures:0
PL/SQL procedure successfully completed.
SQL> SELECT * FROM sys.dst$error_table;
no rows selected
SQL> -- if there where no failures then end the upgrade.
SQL> VAR fail number
SQL> BEGIN
2 DBMS_DST.END_UPGRADE(:fail);
3 DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);
4 END;
5 /
An upgrade window has been successfully ended.
Failures:0
PL/SQL procedure successfully completed.
SQL> -- last checks
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) valueSQL>
2 FROM DATABASE_PROPERTIES
3 WHERE PROPERTY_NAME LIKE 'DST_%'
4 ORDER BY PROPERTY_NAME;
PROPERTY_NAME VALUE
------------------------------ ------------------------------
DST_PRIMARY_TT_VERSION 40
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONE
SQL> SELECT * FROM v$timezone_file;
FILENAME VERSION CON_ID
-------------------- ---------- ----------
timezlrg_40.dat 40 0
SQL> select TZ_VERSION from registry$database;
TZ_VERSION
----------
32
SQL> update registry$database set TZ_VERSION = (select version FROM v$timezone_file);
1 row updated.
SQL> commit;
Commit complete.
SQL> select TZ_VERSION from registry$database;
TZ_VERSION
----------
40
Reference: Updating the RDBMS DST version in 12c Release 1 (12.1.0.1 ) and above using DBMS_DST (Doc ID 1509653.1)
