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

Applies to: Database Management Software Oracle std & Enterprise edition 11.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 successfully ended.                                  

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;

1 comment:

  1. Thanks so much for the steps.

    Regards,
    Stephen

    ReplyDelete