Upgrading The Time Zone File And Timestamp With Time Zone Data In A 12.2 Oracle Database 

time zone upgarde ..

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)

on new home 

$ find /u01ora/app/oracle/product/12.2 -name readme.txt -print

or 

head -10 $ORACLE_HOME/oracore/zoneinfo/readme.txt

or

 head -10 /u01ora/app/oracle/product/12.2/db_1/oracore/zoneinfo/readme.txt

Current Structure version: 3

Current Content Version  :26

Content Version 26  <<<< our file version 

status of the database

set pagesize 100 linesize 132

col PROPERTY_NAME format a30

col value format a20

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   <<<<, current version is 18 

DST_SECONDARY_TT_VERSION       0

DST_UPGRADE_STATE              NONE   <<< check this 

******** Do not continue if the aove output is different* 

select * from v$timezone_file;

FILENAME                VERSION     CON_ID

-------------------- ---------- ----------

timezlrg_14.dat              18          0

Start a prepare window!!!!!!!!!!!!!!

alter session set "_with_subquery"=materialize;

alter session set "_simple_view_merging"=TRUE;

purge dba_recyclebin;

Session altered.

SQL>

Session altered.

SQL>

DBA Recyclebin purged.

SQL> set serveroutput on

SQL> exec DBMS_DST.BEGIN_PREPARE(26);

A prepare window has been successfully started.

PL/SQL procedure successfully completed.

set pagesize 100 linesize 132

col PROPERTY_NAME format a30

col value format a20

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       26

DST_UPGRADE_STATE              PREPARE

SQL>  exec DBMS_DST.FIND_AFFECTED_TABLES;

PL/SQL procedure successfully completed.

SQL> select count(*) from sys.dst$affected_tables;

  COUNT(*)

----------

         0

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

  COUNT(*)

----------

         0

if error fix first 

fixed by looking at the documentation:

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/nlspg/datetime-data-types-and-time-zone-support.html#GUID-6377A1C4-F76A-4C53-82CD-BF4AE6148D6D

end the prepare window

SQL> exec DBMS_DST.END_PREPARE;

A prepare window has been successfully ended.

PL/SQL procedure successfully completed.

set pagesize 100 linesize 132

col PROPERTY_NAME format a30

col value format a20

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

************************************************************

Start an upgrade window:

SQL> shutdown immediate;  !!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup upgrade;  !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area  838860800 bytes

Fixed Size                  8798312 bytes

Variable Size             620760984 bytes

Database Buffers          205520896 bytes

Redo Buffers                3780608 bytes

Database mounted.

Database opened.

SQL> set serveroutput on

SQL>  exec DBMS_DST.BEGIN_UPGRADE(26);

An upgrade window has been successfully started.

PL/SQL procedure successfully completed.

set pagesize 100 linesize 132

col PROPERTY_NAME format a30

col value format a20

SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value     FROM DATABASE_PROPERTIES

    WHERE PROPERTY_NAME LIKE 'DST_%'

    ORDER BY PROPERTY_NAME;SQL> SQL> SQL> SQL>   2    3

PROPERTY_NAME                  VALUE

------------------------------ --------------------

DST_PRIMARY_TT_VERSION         26

DST_SECONDARY_TT_VERSION       18

DST_UPGRADE_STATE              UPGRADE   <<<<<<

for info ... 

col OWNER format a30

col TABLE_NAME format a30

SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS FROM ALL_TSTZ_TABLES;

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup;

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area  838860800 bytes

Fixed Size                  8798312 bytes

Variable Size             620760984 bytes

Database Buffers          205520896 bytes

Redo Buffers                3780608 bytes

Database mounted.

Database opened.

SQL> truncate table sys.dst$error_table;

Table truncated.

SQL> truncate table sys.dst$trigger_table;

Table truncated.

upgrade the TSTZ data in all user tables by invoking DBMS_DST.UPGRADE_DATABASE!!!!!!!!!!!

alter session set "_with_subquery"=materialize;

alter session set "_simple_view_merging"=TRUE;

set serveroutput on

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('Failures:'|| :numfail);

   END;

   /

Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L"

Number of failures: 0

Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"

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.

SELECT * FROM sys.dst$error_table;

SELECT * FROM sys.dst$error_table;

no rows selected

Finally, end the prepare window:

SQL>

 BEGIN

       DBMS_DST.END_UPGRADE(:numfail);

       DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);

    END;

    /

 BEGIN

       DBMS_DST.END_UPGRADE(:numfail);

       DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);

    END;

    /SQL>   2    3    4    5

An upgrade window has been successfully ended.

Failures:0

PL/SQL procedure successfully completed.

SQL> select * from v$timezone_file;

FILENAME                VERSION     CON_ID

-------------------- ---------- ----------

timezlrg_26.dat              26          0

set pagesize 100 linesize 132

col PROPERTY_NAME format a30

col value format a20

SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value     FROM DATABASE_PROPERTIES

    WHERE PROPERTY_NAME LIKE 'DST_%'

    ORDER BY PROPERTY_NAME;SQL> SQL> SQL> SQL> SQL>   2    3

PROPERTY_NAME                  VALUE

------------------------------ --------------------

DST_PRIMARY_TT_VERSION         26

DST_SECONDARY_TT_VERSION       0

DST_UPGRADE_STATE              NONE

SQL> select TZ_VERSION from registry$database;

TZ_VERSION

----------

        18

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

----------

        26  <<<<<<<<<<<<Done !!!!!!!!!!!