Sunday, January 22, 2023

Upgrade 19c DST Timezone files from 32 to 40

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)