Oracle 11g. Upgrading the Time Zone File and Timestamp with Time Zone Data

Note1:
There are two modes
1) Prepare mode. To be used before actual upgrade.
2) Upgrade mode. The database timezone can be upgraded only in this mode.
To check the mode, use the query
exec DBMS_DST.BEGIN_UPGRADE(11);
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 11
DST_SECONDARY_TT_VERSION 4
DST_UPGRADE_STATE UPGRADE

If you execute DBMS_DST.UPGRADE_DATABASE in Prepare mode, you will get the error

ERROR at line 1:
ORA-56928: upgrade window does not exist
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DST", line 930
ORA-06512: at line 2

Note2:
Note that you have to restart the database in normal mode after DBMS_DST.BEGIN_UPGRADE(11);
Other wise you receive error
ERROR at line 1:
ORA-56930: database must be restarted before performing upgrade of user tables
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DST", line 933
ORA-06512: at line 2

Pre Timezone Upgrade
----------------------
1) In Oracle RAC, you must shut down all instances and only one instance should be started for upgrade
2) Verify that $ORACLE_HOME/oracore/zoneinfo/timezlrg_11.dat the file exists
eg: ls -ltr $ORACLE_HOME/oracore/zoneinfo/timezlrg_11.dat


Here is the spool file of actual timezone upgrade:

---- Start of spool file ----
SQL> select property_name, substr(property_value, 1, 30) value from database_properties
where property_name like 'DST_%' order by property_name;
2
PROPERTY_NAME VALUE
------------------------------ ----------
DST_PRIMARY_TT_VERSION 4
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONE

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup upgrade
ORACLE instance started.

Total System Global Area 730714112 bytes
Fixed Size 2210376 bytes
Variable Size 205522360 bytes
Database Buffers 515899392 bytes
Redo Buffers 7081984 bytes
Database mounted.
Database opened.
SQL> set serveroutput on
SQL> exec DBMS_DST.BEGIN_UPGRADE(11);
An upgrade window has been successfully started.

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;
2
PROPERTY_NAME VALUE
------------------------------ ----------
DST_PRIMARY_TT_VERSION 11
DST_SECONDARY_TT_VERSION 4
DST_UPGRADE_STATE UPGRADE

SQL> SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS FROM ALL_TSTZ_TABLES;

OWNER TABLE_NAME UPG
------------------------------ ------------------------------ ---
SYS ALERT_QT NO
SYS KET$_AUTOTASK_STATUS NO
SYS KET$_CLIENT_CONFIG NO
SYS KET$_CLIENT_TASKS NO
SYS OPTSTAT_HIST_CONTROL$ NO
SYS OPTSTAT_USER_PREFS$ NO
SYS FGR$_FILE_GROUPS NO
SYS FGR$_FILE_GROUP_FILES NO
SYS FGR$_FILE_GROUP_VERSIONS NO
SYS AQ$_ALERT_QT_L NO
SYS AQ$_ALERT_QT_S NO
SYS AQ$_AQ$_MEM_MC_L NO
SYS AQ$_AQ$_MEM_MC_S NO
SYS AQ$_AQ_PROP_TABLE_L NO
SYS AQ$_AQ_PROP_TABLE_S NO
SYS REG$ NO
SYS AQ$_KUPC$DATAPUMP_QUETAB_L NO
SYS AQ$_KUPC$DATAPUMP_QUETAB_S NO
SYS AQ$_SCHEDULER$_EVENT_QTAB_L NO
SYS AQ$_SCHEDULER$_EVENT_QTAB_S NO
SYS AQ$_SCHEDULER$_REMDB_JOBQTAB_L NO
SYS AQ$_SCHEDULER$_REMDB_JOBQTAB_S NO
SYS AQ$_SCHEDULER_FILEWATCHER_QT_L NO
SYS AQ$_SCHEDULER_FILEWATCHER_QT_S NO
SYS WRI$_ALERT_HISTORY NO
SYS WRI$_ALERT_OUTSTANDING NO
SYS WRI$_OPTSTAT_AUX_HISTORY NO
SYS WRI$_OPTSTAT_HISTGRM_HISTORY NO
SYS WRI$_OPTSTAT_HISTHEAD_HISTORY NO
SYS WRI$_OPTSTAT_IND_HISTORY NO
SYS WRI$_OPTSTAT_OPR NO
SYS WRI$_OPTSTAT_TAB_HISTORY NO
DBSNMP MGMT_DB_FEATURE_LOG NO
SYS SCHEDULER$_EVENT_LOG NO
SYS SCHEDULER$_EVENT_QTAB NO
SYS SCHEDULER$_FILEWATCHER_HISTORY NO
SYS SCHEDULER$_FILEWATCHER_RESEND NO
SYS SCHEDULER$_FILE_WATCHER NO
SYS SCHEDULER$_GLOBAL_ATTRIBUTE NO
SYS SCHEDULER$_JOB NO
SYS SCHEDULER$_JOB_DESTINATIONS NO
SYS SCHEDULER$_JOB_RUN_DETAILS NO
SYS SCHEDULER$_LIGHTWEIGHT_JOB NO
SYS SCHEDULER$_REMDB_JOBQTAB NO
SYS SCHEDULER$_REMOTE_JOB_STATE NO
SYS SCHEDULER$_SCHEDULE NO
SYS SCHEDULER$_STEP_STATE NO
SYS SCHEDULER$_WINDOW NO
SYS SCHEDULER$_WINDOW_DETAILS NO
SYS SCHEDULER_FILEWATCHER_QT NO
SYS WRR$_REPLAY_DIVERGENCE NO
SYS TSM_DST$ NO
SYS TSM_SRC$ NO
SYS AQ$_SUBSCRIBER_TABLE NO
SYS AQ$_SYS$SERVICE_METRICS_TAB_L NO
SYS AQ$_SYS$SERVICE_METRICS_TAB_S NO

56 rows selected.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 730714112 bytes
Fixed Size 2210376 bytes
Variable Size 209716664 bytes
Database Buffers 511705088 bytes
Redo Buffers 7081984 bytes
Database mounted.
Database opened.
SQL> set serveroutput on
SQL> VAR numfail number
SQL> BEGIN
2 DBMS_DST.UPGRADE_DATABASE(:numfail,
3 parallel => TRUE,
4 log_errors => TRUE,
5 log_errors_table => 'SYS.DST$ERROR_TABLE',
6 log_triggers_table => 'SYS.DST$TRIGGER_TABLE',
7 error_on_overlap_time => TRUE,
8 error_on_nonexisting_time => TRUE);
9 DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);
10 END;
11 /
Failures:0

PL/SQL procedure successfully completed.

SQL> DBMS_DST.END_UPGRADE(:numfail);
SP2-0734: unknown command beginning "DBMS_DST.E..." - rest of line ignored.
SQL> exec DBMS_DST.END_UPGRADE(:numfail);
An upgrade window has been successfully ended.

PL/SQL procedure successfully completed.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 730714112 bytes
Fixed Size 2210376 bytes
Variable Size 209716664 bytes
Database Buffers 511705088 bytes
Redo Buffers 7081984 bytes
Database mounted.
Database opened.
SQL>

---- End of Spool file ----