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


$ 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;

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: