Upgrade Time Zone File And Timestamp With Time Zone Data
Upgrade Time Zone File And Timestamp With Time Zone Data
Upgrading the time zone file and timestamp with time zone (TSTZ) data in an Oracle database is indeed necessary for certain scenarios, such as when you upgrade to a new version of the database or when there are changes to the rules for Daylight Saving Time (DST).
Here’s a general process to perform this upgrade:
1. Check the latest version of the time zone file available in the Oracle home:
[orauat@devdb oracore]$ cd $ORACLE_HOME/oracore/
[orauat@devdb oracore]$ ls -ltrh
total 6
drwxr-xr-x 4 orauat dba 34 May 7 2021 zoneinfo
drwxr-xr-x 2 orauat dba 24 May 7 2021 mesg
2. Backup current zone info directory
[orauat@devdb oracore]$ cp -pr zoneinfo zoneinfo_old
3. Check the status of the database:
[orauat@devdb oracore]$ more $ORACLE_HOME/oracore/zoneinfo/timezdif.csv
# File version 1.0
# Fields: VERSION#, TIMEZONE_NAME, FROM_YEAR, TO_YEAR
# Note: A NULL/blank for TO_YEAR means max year representable/allowed by db.
14, Africa/Casablanca, 2010,
14, Africa/Tunis, 2010,
14, America/Argentina/San_Luis, 2010,
14, America/Tijuana, 2010,
14, America/Santiago, 2010, 2010
14, America/Asuncion, 2010,
14, Antarctica/Casey, 2010,
14, Antarctica/Davis, 2010,
4. Check also from sqlplus:
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE ‘DST_%’
ORDER BY PROPERTY_NAME; 2 3
PROPERTY_NAME VALUE
—————————— ——————————
DST_PRIMARY_TT_VERSION 14
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONE
SQL> select * from v$timezone_file;
FILENAME VERSION
——————– ———-
timezlrg_14.dat 14
SQL> select TZ_VERSION from registry$database;
TZ_VERSION
———-
14
5. Set related parameter
Check The recycle bin, it should be purged, and some session parameters set as per the given MOS notes:
Time Zone upgrade performed through DBUA takes more time in databases that have large amount of data impacted by new TZ files (Doc ID 2259734.1)
Updating the RDBMS DST version in 12c Release 1 (12.1.0.1 and up) using DBMS_DST (Doc ID 1509653.1)
alter session set “_with_subquery”=materialize;
alter session set “_simple_view_merging”=TRUE;
purge dba_recyclebin;
6. Start the upgrade process
SQL> set serveroutput on
SQL> exec DBMS_DST.BEGIN_PREPARE(31);
A prepare window has been successfully started.
PL/SQL procedure successfully completed.
SQL> shutdown immediate;
SQL> startup upgrade;
ORACLE instance started.
SQL> exec DBMS_DST.BEGIN_UPGRADE(31);
PL/SQL procedure successfully completed.
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME; 2 3 PROPERTY_NAME VALUE ------------------------------ ------------------------------ DST_PRIMARY_TT_VERSION 31 DST_SECONDARY_TT_VERSION 14 DST_UPGRADE_STATE UPGRADE SQL> shu immediate; Database closed. Database dismounted. ORACLE instance shut down.
SQL> startup
ORACLE instance started.
SQL> truncate table sys.dst$error_table;
Table truncated.
SQL> truncate table sys.dst$trigger_table;
Table truncated.
07. Check the Possible errors with the following query given below:
SQL> SELECT * FROM sys.dst$error_table;
no rows selected
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 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 => TRUE, error_on_nonexisting_time => TRUE); DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail); END; / PL/SQL procedure successfully completed.
08. Now, Check error again
SQL> SELECT * FROM sys.dst$error_table;
no rows selected
09. Now Check Updated Timezone
SQL> select * from v$timezone_file; FILENAME VERSION -------------------- ---------- timezlrg_31.dat 31
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME; PROPERTY_NAME VALUE ------------------------------ ------------------------------ DST_PRIMARY_TT_VERSION 31 DST_SECONDARY_TT_VERSION 0 DST_UPGRADE_STATE NONE