Upgrade the database time zone using DBMS_DST package in Oracle

Upgrade the database time zone file in Non-CDB database.

  1. Find the current time zone version of Oracle Database
SQL> select * from v$timezone_file;

FILENAME                VERSION     CON_ID
-------------------- ---------- ----------
timezlrg_18.dat              18          0

SQL> select TZ_VERSION from registry$database;
TZ_VERSION
----------
        18

col property_name for a26
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    18
DST_SECONDARY_TT_VERSION  0
DST_UPGRADE_STATE         NONE

Check the value of Timezone version need to upgrade 19c.

SQL> SELECT DBMS_DST.get_latest_timezone_version FROM dual;
GET_LATEST_TIMEZONE_VERSION
---------------------------
                         32

OR 
This max value file is 32 for 19c.
%ORACLE_HOME%\oracore\zoneinfo\timezlrg_32.dat

Upgrade the time zone in Oracle

  1. Prepare Window
  2. Upgrade Window

PREPARE WINDOW

  1. Verify the latest available version of the timezone file.
SQL> SELECT DBMS_DST.get_latest_timezone_version FROM dual;
GET_LATEST_TIMEZONE_VERSION
---------------------------
                         32

OR

%ORACLE_HOME%\oracore\zoneinfo
timezlrg_versionnumber.dat
timezone_versionumber.dat
Version number: latest version number eg 32

2. Execute the Procedure for prepare, get version in step 1.

EXEC DBMS_DST.BEGIN_PREPARE(new_version)
Eg:
EXEC DBMS_DST.BEGIN_PREPARE(32);

3. Verify the Prepare phase is started.

COLUMN property_name FORMAT A30
COLUMN property_value FORMAT A20
SELECT property_name, property_value FROM   database_properties
WHERE  property_name LIKE 'DST_%' ORDER BY property_name;

4. Check the following tables present otherwise create and truncate them.

SQL> select count(*) from sys.dst$error_table;
  COUNT(*)
----------
         0

SQL> select count(*) from sys.dst$affected_tables;
  COUNT(*)
----------
         0
-- If table is not present in database, then you can create with following commands:
--EXEC DBMS_DST.CREATE_ERROR_TABLE;
--EXEC DBMS_DST.CREATE_AFFECTED_TABLE;
--EXEC BMS_DST.CREATE_TRIGGER_TABLE;

--If table is not empty, having old data then you need to truncate both table:
--TRUNCATE TABLE sys.dst$affected_tables;
--TRUNCATE TABLE sys.dst$error_table;
--TRUCATE TABLE sys.dst$trigger_table;

5. Find the tables affected due to the upgrade process.

EXEC DBMS_DST.find_affected_tables;

6. Check if you find any affected columns or table.

SELECT * FROM sys.dst$affected_tables;
SELECT * FROM sys.dst$error_table;

SQL> select error_count from sys.dst$affected_Tables;
If error count is greater than 0 then check detail in sys.dst$error_table.

7. Execute the procedure for End prepare window.

EXEC DBMS_DST.END_PREPARE;

UPGRADE WINDOW:

  1. Start the Oracle Database in upgrade mode.
shutdown immediate;
startup upgrade;

2. Execute the Upgrade timezone procedure.

--Get latest value in Prepare Step 1
SELECT DBMS_DST.get_latest_timezone_version FROM dual;

-- Start the upgrade time zone version:
DBMS_DST.begin_upgrade(l_tz_version);
Example:
EXEC DBMS_DST.begin_upgrade(32);

Note: 
1. Other two parameter TRUE, if you donot want to ignore error during upgrade.
2. If you want to ignore then you can also truncate the error tables.

3. Check sys.dst$error_table to determine if the dictionary conversion was successful.

select * from sys.dst$error_table;
  1. Restart the database in normal mode.
Shutdown immediate;
startup;

5. Truncate the table if you find any error above and ignore during upgrade.

Truncate table sys.dst$error_table ;
Truncate table sys.dst$trigger_table;

6. Upgrade the TSTZ data in all tables by executing the procedure. We executed two procedure in one script as below:

SET SERVEROUTPUT ON
DECLARE
  v_failures   PLS_INTEGER;
BEGIN
  DBMS_DST.upgrade_database(v_failures);
  DBMS_OUTPUT.put_line('upgrade_database failures=' || v_failures);
  DBMS_DST.end_upgrade(v_failures);
  DBMS_OUTPUT.put_line('end upgrade failures=' || v_failures);
END;
/

7. Check the status of upgrade

COLUMN owner FORMAT A30
COLUMN table_name FORMAT A30
SELECT owner, table_name, upgrade_in_progress FROM dba_tstz_tables ORDER BY 1,2;

8. Check the time zone version.

Select * from v$timezone_file;

Select TZ_VERSION from registry$database;

col property_name for a26
SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES 
WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;

Issue: Different time zone in Registry$dataase and v$timezone_file.

update registry$database set TZ_VERSION = (select version FROM v$timezone_file);
--Verify:  
select * from v$timezone_file;
select TZ_VERSION from registry$database;

Refer
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/nlspg/datetime-data-types-and-time-zone-support.html#GUID-12830F09-735F-4E25-B141-42B4776A110A
–On Oracle Support: Document ID 412160.1

On link provided, we have option to upgrade time zone as separate procedure with all parameters:

-- Execute the UPGRADE_DATABASE Procedure:
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     => TRUE,
            error_on_nonexisting_time => TRUE);

DBMS_OUTPUT.PUT_LINE('Number of tables failed to upgrade:'|| :numfail);
END;
/

-- Execute the END_UPGRADE Procedure.
VAR numfail number;
BEGIN
  DBMS_DST.END_UPGRADE(:numfail);
  DBMS_OUTPUT.PUT_LINE('Number of tables failed to upgrade:'|| :numfail);
END;
/

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.