Kevin Kempf's Blog

December 16, 2009

Upgrading RDBMS 11.2 to Timezone Version 11

Filed under: 11g, Oracle — kkempf @ 10:15 am

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.

** EDIT ** there is a new doc now, 977512.1, which adds a few steps to prepare the upgrade, though the substance is still the same I recommend you review it thoroughly before proceeding!

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
Advertisements

1 Comment

  1. Brilliant instructions – clear, concise, correct. Well done fella

    Comment by Gary — December 22, 2011 @ 8:49 am


RSS feed for comments on this post.

Sorry, the comment form is closed at this time.

Blog at WordPress.com.