conn / as sysdba shutdown immediate; startup upgrade; set serveroutput on -- check if previous prepare window is ended SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME; -- output should be -- PROPERTY_NAME VALUE -- ---------------------------- ------------------------------ -- DST_PRIMARY_TT_VERSION -- DST_SECONDARY_TT_VERSION 0 -- DST_UPGRADE_STATE NONE -- If DST_UPGRADE_STATE is "PREPARE" then you did not ended -- the prepare window in step 3) -- If there are objects containing TSTZ data in recycle bin, -- please purge the bin now. -- Otherwise dbms_dst.begin_upgrade will report "ORA-38301: Can not perform DDL/DML over objects in Recycle Bin". purge dba_recyclebin; -- clean used tables TRUNCATE TABLE SYS.DST$TRIGGER_TABLE; TRUNCATE TABLE sys.dst$affected_tables; TRUNCATE TABLE sys.dst$error_table; -- this alter session might speed up DBMS_DST on some db's -- see Bug 10209691 alter session set "_with_subquery"=materialize; -- to avoid the issue in note 1407273.1 alter session set "_simple_view_merging"=TRUE; -- start upgrade window EXEC DBMS_DST.BEGIN_UPGRADE(); -- the message -- "An upgrade window has been successfully started." -- will be seen -- check if this select SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME; -- gives this output: -- PROPERTY_NAME VALUE -- --------------------------- ------------------------------ -- DST_PRIMARY_TT_VERSION -- DST_SECONDARY_TT_VERSION -- DST_UPGRADE_STATE UPGRADE -- Optionally you can check what user tables still need to be updated using DBMS_DST.UPGRADE_DATABASE -- BEGIN_UPGRADE upgrades system tables that contain TSTZ data and marks user tables (containing TSTZ data) with the UPGRADE_IN_PROGRESS property. -- even if this select gives no rows you still need to do to the rest of the steps -- it simply gives an indication of how many user objects need to processed in the later steps -- some oracle provided users may be listed here, that is normal SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS FROM ALL_TSTZ_TABLES where UPGRADE_IN_PROGRESS='YES'; -- restart the database shutdown immediate startup alter session set "_with_subquery"=materialize; alter session set "_simple_view_merging"=TRUE; -- now upgrade the tables who need action 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; / -- ouput of this will be a list of tables like: -- Table list: SYSMAN.AQ$_MGMT_NOTIFY_QTABLE_S -- Number of failures: 0 -- .... -- Table list: SYSMAN.MGMT_PROV_ASSIGNMENT -- Number of failures: 0 -- Table list: SYSMAN.MGMT_CONFIG_ACTIVITIES -- Number of failures: 0 -- Failures:0 -- if there where no failures then end the upgrade. VAR fail number BEGIN DBMS_DST.END_UPGRADE(:fail); DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail); END; / -- output that will be seen: -- An upgrade window has been successfully ended. -- Failures:0 -- last checks SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME; -- needed output: -- PROPERTY_NAME VALUE -- ---------------------------- ------------------------------ -- DST_PRIMARY_TT_VERSION -- DST_SECONDARY_TT_VERSION 0 -- DST_UPGRADE_STATE NONE SELECT * FROM v$timezone_file; -- needed output: -- FILENAME VERSION -- ------------------ ---------- -- timezlrg_.dat -- if registry$database exists then update this static table also with the new DST version -- this table is used during upgrade and contians the DST version from before the upgrade -- this update is mainly to avoid confusion when people notice this has a lower version select TZ_VERSION from registry$database; update registry$database set TZ_VERSION = (select version FROM v$timezone_file); commit;