DST time zone upgrade steps

spool /opt/oracle/admin/O2S10896/upgrade/upgrd.log

SQL> shutdown immediate


SQL> startup upgrade
ORACLE instance started.


Total System Global Area  627732480 bytes
Fixed Size                  1345992 bytes
Variable Size             184550968 bytes
Database Buffers          436207616 bytes
Redo Buffers                5627904 bytes
Database mounted.
Database opened.


SQL> SELECT version FROM v$timezone_file;


   VERSION
----------
         4


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         4
DST_SECONDARY_TT_VERSION       0
DST_UPGRADE_STATE              NONE


SQL> alter session set "_with_subquery"=materialize;


Session altered.


SQL> alter session set "_simple_view_merging"=TRUE;


Session altered.


SQL> exec DBMS_DST.BEGIN_PREPARE(14)


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;


PROPERTY_NAME                  VALUE                                           
------------------------------ ------------------------------                 
DST_PRIMARY_TT_VERSION         4                                               
DST_SECONDARY_TT_VERSION       14                                             
DST_UPGRADE_STATE              PREPARE                                         


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> set serveroutput on
SQL> BEGIN
    DBMS_DST.FIND_AFFECTED_TABLES
    (affected_tables => 'sys.dst$affected_tables',
    log_errors => TRUE,
    log_errors_table => 'sys.dst$error_table');
    END;
    /


PL/SQL procedure successfully completed.


SQL> SELECT * FROM sys.dst$affected_tables;


no rows selected


SQL> SELECT * FROM sys.dst$error_table;


no rows selected


SQL> EXEC DBMS_DST.END_PREPARE;
A prepare window has been successfully ended.                                 


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;


PROPERTY_NAME                  VALUE                                           
------------------------------ ------------------------------                 
DST_PRIMARY_TT_VERSION         4                                               
DST_SECONDARY_TT_VERSION       0                                               
DST_UPGRADE_STATE              NONE                                           


SQL> purge dba_recyclebin;


DBA Recyclebin purged.


SQL> alter session set "_with_subquery"=materialize;


Session altered.


SQL> alter session set "_simple_view_merging"=TRUE;


Session altered.


SQL> EXEC DBMS_DST.BEGIN_UPGRADE(14);


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;


PROPERTY_NAME                  VALUE                                           
------------------------------ ------------------------------                 
DST_PRIMARY_TT_VERSION         14                                             
DST_SECONDARY_TT_VERSION       4                                               
DST_UPGRADE_STATE              UPGRADE                                         


SQL> SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS FROM ALL_TSTZ_TABLES where UPGRADE_IN_PROGRESS='YES';


no rows selected




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         14
DST_SECONDARY_TT_VERSION       4
DST_UPGRADE_STATE              UPGRADE


SQL> SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS FROM ALL_TSTZ_TABLES where UPGRADE_IN_PROGRESS='YES';


no rows selected


SQL> spool off;
SQL> shutdown immediate



SQL> startup


SQL> alter session set "_with_subquery"=materialize;
alter session set "_simple_view_merging"=TRUE;
Session altered.


SQL>


Session altered.


SQL> 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 => FALSE,
error_on_nonexisting_time => FALSE);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);
END;
/SQL> SQL> 
Failures:0


PL/SQL procedure successfully completed.


SQL> VAR fail number
BEGIN
DBMS_DST.END_UPGRADE(:fail);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);
END;
/SQL> 
An upgrade window has been successfully ended.
Failures:0


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; 


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
----------
         4


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
----------
        14


SQL> exit;