One of the task dbas have to do is to quickly upgrade Oracle database and binaries, this post will list you the short path to upgrade an Oracle database from 10g to 11.2.
- Do a normal install of Oracle 11.2.0.4 with latest PSU applied. ( don’t touch the 10g version as you need it to upgrade the databases )
- On the 10g database run the script utlu112i.sql this script is shipped with the 11.2.0.4 you just have installed. Fix all problems the script showed up!
- On the 10g, connected as sysdba do the following cleaning tasks:
-
purge dba_recyclebin; exec dbms_stats.gather_dictionary_stats; shutdown immediate
- On the 11.2 binaries, start the DB and upgrade it:
cd $ORACLE_HOME/rdbms/admin $ sqlplus / AS sysdba SQL> startup UPGRADE SQL> SET echo ON SQL> SPOOL upgrade.log SQL> @catupgrd.sql $ sqlplus / AS sysdba SQL> STARTUP SQL> @utlu112s.sql SQL> @utlrp.sql SQL> @catuppst.sql SQL> @utlrp.sql SQL> shutdown SQL> startup SQL> ALTER system SET compatible='11.2.0.4' scope=spfile; SQL> shutdown immediate SQL> startup SQL> exit
- Your DB is now upgraded to 11.2.0.4, congrats!
- If utlu112i.sql let you know that you should upgrade the timezone definition, then follow the next few steps:
cd $ORACLE_HOME/rdbms/admin $ sqlplus / 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 <the old DST version number> -- DST_SECONDARY_TT_VERSION 0 -- DST_UPGRADE_STATE NONE purge dba_recyclebin; TRUNCATE TABLE SYS.DST$TRIGGER_TABLE; TRUNCATE TABLE sys.dst$affected_tables; TRUNCATE TABLE sys.dst$error_table; ALTER SESSION SET "_with_subquery"=materialize; ALTER SESSION SET "_simple_view_merging"=TRUE; EXEC DBMS_DST.BEGIN_UPGRADE(14); -- 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 <the new DST version number> -- DST_SECONDARY_TT_VERSION <the old DST version number> -- DST_UPGRADE_STATE UPGRADE shutdown immediate startup ALTER SESSION SET "_with_subquery"=materialize; ALTER SESSION SET "_simple_view_merging"=TRUE; 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 -- 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 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 <the new DST version number> -- DST_SECONDARY_TT_VERSION 0 -- DST_UPGRADE_STATE NONE SELECT * FROM v$timezone_file; -- needed output: -- FILENAME VERSION -- - -- timezlrg_14.dat 14 SELECT TZ_VERSION FROM registry$database; UPDATE registry$database SET TZ_VERSION = (SELECT version FROM v$timezone_file); commit; |