After completing a slew of 10.2 to 11.2 RDBMS upgrades recently, I went back and did as the pre-upgrade analyzer (utlu1112.sql) suggested and updated the Timezone Version from 10 to 11 (per doc 944122.1). The main take away from this is that Oracle has changed and to some degree simplified this process (at least for Version 11). It still requires taking the database down several times, which is more than annoying. I figured it may be easiest to just show my runtime log here, with commands I executed in bold. I performed all of these upgrades on RHEL5.4 x86_64.
As a (perhaps not-so) interesting side-note, 11.2.0.1 is still only available for download on OTN on Sun & Linux platforms.
$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue Dec 15 11:06:31 2009
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup upgrade
ORACLE instance started.
Total System Global Area 167014400 bytes
Fixed Size 2211528 bytes
Variable Size 113246520 bytes
Database Buffers 46137344 bytes
Redo Buffers 5419008 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> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 167014400 bytes
Fixed Size 2211528 bytes
Variable Size 113246520 bytes
Database Buffers 46137344 bytes
Redo Buffers 5419008 bytes
Database mounted.
Database opened.
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 11
DST_SECONDARY_TT_VERSION 10
DST_UPGRADE_STATE UPGRADE
SQL> select owner, table_name, upgrade_in_progress from all_tstz_tables;
OWNER TABLE_NAME UPG
------------------------------ ------------------------------ ---
SYS AQ$_AQ_PROP_TABLE_S NO
SYS TSM_DST$ NO
SYS SCHEDULER_FILEWATCHER_QT NO
SYS SCHEDULER$_STEP_STATE NO
SYS SCHEDULER$_JOB_DESTINATIONS NO
SYS SCHEDULER$_EVENT_LOG NO
SYS KET$_CLIENT_TASKS NO
SYS SCHEDULER$_JOB NO
SYS SCHEDULER$_FILEWATCHER_HISTORY NO
SYS WRI$_ALERT_OUTSTANDING NO
SYS AQ$_SUBSCRIBER_TABLE NO
OWNER TABLE_NAME UPG
------------------------------ ------------------------------ ---
SYS AQ$_SCHEDULER_FILEWATCHER_QT_L NO
SYS AQ$_KUPC$DATAPUMP_QUETAB_L NO
SYS ALERT_QT NO
SYS AQ$_AQ$_MEM_MC_L NO
SYS SCHEDULER$_WINDOW NO
SYS SCHEDULER$_EVENT_QTAB NO
SYS WRI$_OPTSTAT_HISTHEAD_HISTORY NO
SYS AQ$_SCHEDULER$_REMDB_JOBQTAB_S NO
SYS FGR$_FILE_GROUP_VERSIONS NO
SYS OPTSTAT_USER_PREFS$ NO
SYS KET$_CLIENT_CONFIG NO
OWNER TABLE_NAME UPG
------------------------------ ------------------------------ ---
SYS AQ$_AQ$_MEM_MC_S NO
SYS SCHEDULER$_WINDOW_DETAILS NO
SYS SCHEDULER$_REMDB_JOBQTAB NO
SYS SCHEDULER$_LIGHTWEIGHT_JOB NO
SYS SCHEDULER$_GLOBAL_ATTRIBUTE NO
SYS SCHEDULER$_FILE_WATCHER NO
SYS WRI$_OPTSTAT_IND_HISTORY NO
SYS AQ$_SCHEDULER_FILEWATCHER_QT_S NO
SYS SCHEDULER$_JOB_RUN_DETAILS NO
SYS WRI$_OPTSTAT_OPR NO
SYS AQ$_SYS$SERVICE_METRICS_TAB_L NO
OWNER TABLE_NAME UPG
------------------------------ ------------------------------ ---
SYS FGR$_FILE_GROUP_FILES NO
SYS TSM_SRC$ NO
SYS SCHEDULER$_REMOTE_JOB_STATE NO
SYS WRI$_OPTSTAT_HISTGRM_HISTORY NO
SYS WRI$_OPTSTAT_AUX_HISTORY NO
SYS REG$ NO
SYS AQ$_SYS$SERVICE_METRICS_TAB_S NO
SYS FGR$_FILE_GROUPS NO
SYS AQ$_ALERT_QT_S NO
SYS WRR$_REPLAY_DIVERGENCE NO
SYS SCHEDULER$_SCHEDULE NO
OWNER TABLE_NAME UPG
------------------------------ ------------------------------ ---
SYS WRI$_OPTSTAT_TAB_HISTORY NO
SYS WRI$_ALERT_HISTORY NO
SYS AQ$_SCHEDULER$_REMDB_JOBQTAB_L NO
SYS AQ$_SCHEDULER$_EVENT_QTAB_S NO
SYS AQ$_KUPC$DATAPUMP_QUETAB_S NO
SYS OPTSTAT_HIST_CONTROL$ NO
SYS KET$_AUTOTASK_STATUS NO
SYS AQ$_AQ_PROP_TABLE_L NO
SYS AQ$_ALERT_QT_L NO
SYS SCHEDULER$_FILEWATCHER_RESEND NO
SYS AQ$_SCHEDULER$_EVENT_QTAB_L NO
OWNER TABLE_NAME UPG
------------------------------ ------------------------------ ---
DBSNMP MGMT_DB_FEATURE_LOG NO
WMSYS WM$VERSIONED_TABLES NO
WMSYS AQ$_WM$EVENT_QUEUE_TABLE_S NO
WMSYS AQ$_WM$EVENT_QUEUE_TABLE_L NO
SYSMAN MGMT_PROV_NET_CONFIG YES
SYSMAN MGMT_PROV_IP_RANGE YES
SYSMAN AQ$_MGMT_NOTIFY_QTABLE_L YES
SYSMAN AQ$_MGMT_LOADER_QTABLE_S YES
SYSMAN AQ$_MGMT_LOADER_QTABLE_L YES
SYSMAN MGMT_PROV_SUITE_INST_MEMBERS YES
SYSMAN MGMT_PROV_BOOTSERVER YES
OWNER TABLE_NAME UPG
------------------------------ ------------------------------ ---
SYSMAN AQ$_MGMT_NOTIFY_QTABLE_S YES
SYSMAN MGMT_PROV_STAGING_DIRS YES
SYSMAN MGMT_PROV_OPERATION YES
SYSMAN MGMT_PROV_ASSIGNMENT YES
SYSMAN MGMT_CONFIG_ACTIVITIES YES
SYSMAN MGMT_PROV_CLUSTER_NODES YES
SYSMAN MGMT_PROV_RPM_REP YES
SYSMAN MGMT_PROV_DEFAULT_IMAGE YES
74 rows selected.
SQL> set serveroutput on;
SQL> declare
num_of_failures number;
begin
dbms_dst.upgrade_database(num_of_failures);
dbms_output.put_line(num_of_failures);
dbms_dst.end_upgrade(num_of_failures);
dbms_output.put_line(num_of_failures)
end
;
/
Table list: SYSMAN.AQ$_MGMT_NOTIFY_QTABLE_S
Number of failures: 0
Table list: SYSMAN.AQ$_MGMT_NOTIFY_QTABLE_L
Number of failures: 0
Table list: SYSMAN.AQ$_MGMT_LOADER_QTABLE_S
Number of failures: 0
Table list: SYSMAN.AQ$_MGMT_LOADER_QTABLE_L
Number of failures: 0
Table list: SYSMAN.MGMT_PROV_SUITE_INST_MEMBERS
Number of failures: 0
Table list: SYSMAN.MGMT_PROV_STAGING_DIRS
Number of failures: 0
Table list: SYSMAN.MGMT_PROV_RPM_REP
Number of failures: 0
Table list: SYSMAN.MGMT_PROV_OPERATION
Number of failures: 0
Table list: SYSMAN.MGMT_PROV_NET_CONFIG
Number of failures: 0
Table list: SYSMAN.MGMT_PROV_IP_RANGE
Number of failures: 0
Table list: SYSMAN.MGMT_PROV_DEFAULT_IMAGE
Number of failures: 0
Table list: SYSMAN.MGMT_PROV_CLUSTER_NODES
Number of failures: 0
Table list: SYSMAN.MGMT_PROV_BOOTSERVER
Number of failures: 0
Table list: SYSMAN.MGMT_PROV_ASSIGNMENT
Number of failures: 0
Table list: SYSMAN.MGMT_CONFIG_ACTIVITIES
Number of failures: 0
0
An upgrade window has been successfully ended.
0
PL/SQL procedure successfully completed.
SQL> select * 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 11
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONE
