Home / Errors/Workarounds12c / Upgrade Time Zone File And Timestamp With Time Zone Data
OracleDBconfig

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




  • 5

About Syed Saad Ali

With 13 years of experience as a certified and skilled Oracle Database Administrator, I possess the expertise to handle various levels of database maintenance tasks and proficiently perform Oracle updates. Throughout my career, I have honed my analytical abilities, enabling me to swiftly diagnose and resolve issues as they arise. I excel in planning and executing special projects within time-sensitive environments, showcasing exceptional organizational and time management skills. My extensive knowledge encompasses directing, coordinating, and exercising authoritative control over all aspects of planning, organization, and successful project completions. Additionally, I have a strong aptitude for resolving customer relations matters by prioritizing understanding and effective communication. I am adept at interacting with customers, vendors, and management, ensuring seamless communication and fostering positive relationships.

Check Also

OracleEBSerror

ORA-20002: Version of statistics table APPLSYS.FND_STATTAB is too old

ERROR ORA-20002: Version of statistics table APPLSYS.FND_STATTAB is too old

Leave a Reply