Updating the RDBMS DST version in 11gR2 (11.2.0.1 and up) using DBMS_DST

Applies to: Database Management Software Oracle std & Enterprise edition11.2.0.1 to 11.2

[oracle@netmindtwcint 11g_upgrade]$ sqlplus sys as sysdba

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

  2  FROM DATABASE_PROPERTIES

  3  WHERE PROPERTY_NAME LIKE 'DST_%'

  4  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

  2  DBMS_DST.FIND_AFFECTED_TABLES

  3  (affected_tables => 'sys.dst$affected_tables',

  4  log_errors => TRUE,

  5  log_errors_table => 'sys.dst$error_table');

  6  END;

  7  /

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 successfullyended.                                  

PL/SQL procedure successfully completed.

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value

  2  FROM DATABASE_PROPERTIES

  3  WHERE PROPERTY_NAME LIKE 'DST_%'

  4  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

  2  FROM DATABASE_PROPERTIES

  3  WHERE PROPERTY_NAME LIKE 'DST_%'

  4  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>   2    3    4    5    6    7    8    9   10   11

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>   2    3    4    5

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;