How to upgrade oracle database from version 9.2.0.8 to 11.2.0.2 By Sunjoy Jeergall ( 12/22/2011 ) I have recently upgraded some of the databases to Oracle 11g from 9i/10g . In this particular document, the database was upgraded from oracle 9.2.0.8 to oracle 11.2.0.2 using manual method. Before we start upgrade we need to get familiar with upgrade path, upgrade methods (manual, using dbua or export / import), common pitfalls and so on. Also, it is necessary to go through the documents provided by oracle. These are very detailed documents by oracle corporation. 11gR2_Upgrade_Companion, Upgrade_Guide_e23633, Complete Checklist for Manual Upgrades to 11gR2, Complete checklist to upgrade the database to 11gR2 using DBUA , Recommendations to Avoid Common Pitfalls TIP: Gathering stats before upgrade reduces the time to upgrade. Recompilation of synonyms increases upgrade time(9i upgrade). Assumptions: It is assumed that the Oracle 11g has been already installed in the new ORACLE_HOME 1. Prepare pfile Location: /opt/oracle/product/11.2.0/dbhome_1/dbs/initMYDB.ora O7_DICTIONARY_ACCESSIBILITY=FALSE audit_sys_operations=TRUE audit_trail=OS compatible=10.1.0 control_files=/data/oradata/mydb/ora01/control1.ctl, /data/oradata/mydb/ora02/control2.ctl db_cache_size=4110417920 db_file_multiblock_read_count=8 db_files=255 db_name="mydb" global_names=TRUE java_pool_size=201326592 log_archive_dest_1='LOCATION=/data/oradata/mydb/ora08/arch/' log_buffer=8388608 max_dump_file_size=10240 open_cursors=500 optimizer_index_caching=1 optimizer_index_cost_adj=80 os_authent_prefix="" os_roles=FALSE processes=255 query_rewrite_enabled=TRUE remote_login_passwordfile=EXCLUSIVE remote_os_roles=FALSE resource_limit=TRUE shared_pool_size=543509914 sort_area_retained_size=800000 sort_area_size=800000 sql92_security=TRUE undo_management=AUTO utl_file_dir=/usr/local/mydb/db/data/owcp/outbound event="" db_block_size=2048 pga_aggregate_target=25165824 diagnostic_dest=/opt/oracle job_queue_processes=10 2. Create SYSAUX tablespace SQL> !vi cr_sysaux.sql "cr_sysaux.sql" 5 lines, 155 characters CREATE TABLESPACE sysaux DATAFILE '/data/oradata/mydb/ora10/sysaux01.dbf' SIZE 500M REUSE EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ONLINE; SQL> @cr_sysaux.sql Tablespace created. 3. Shutdown database in 9i version SQL> connect sys as sysdba Enter password: Connected. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> exit 4. Start new session in oracle 11g Start vew session with .profile_11g and startup database in upgrade mode (This .profile create env for 11g like $ORACLE_HOME for 11.2.0.2) $ sqlplus /nolog SQL*Plus: Release 11.2.0.2.0 Production on Fri Oct 14 11:36:30 2011 Copyright (c) 1982, 2010, Oracle. All rights reserved. SQL> connect sys as sysdba Enter password: Connected to an idle instance. SQL> startup upgrade pfile=/opt/oracle/product/11.2.0/dbhome_1/dbs/initMYDB.ora ORACLE instance started. Total System Global Area 4878663680 bytes Fixed Size 2166528 bytes Variable Size 754979072 bytes Database Buffers 4110417920 bytes Redo Buffers 11100160 bytes Database mounted. Database opened. 5. Execute these two utilities (@utlu112i.sql and @catupgrd.sql) Execute these two utilities (@utlu112i.sql and @catupgrd.sql) provided by ORACLE available at location $ORACLE_HOME/rdbms/admin @utlu112i.sql and @catupgrd.sql SQL> connect sys as sysdba Enter password: Connected. SQL> !pwd /opt/oracle/product/11.2.0/dbhome_1/rdbms/admin SQL> SQL> SPOOL upgrade.log SQL> @utlu112i.sql -. Run the Pre-Upgrade Information Tool by executing the utlu112i.sql script SQL> @catupgrd.sql . Run the catupgrd.sql script ;;;;;;;;;;;;;;;;;;; ;;;;;;;;;;;;;;;;;;;; DOC> DOC>##################################################################### ## DOC>##################################################################### ## DOC># SQL> SQL> Rem Set errorlogging off SQL> SET ERRORLOGGING OFF; SQL> SQL> Rem ********************************************************************* SQL> Rem END catupgrd.sql SQL> Rem ********************************************************************* 6. Restart the database as the upgrade shuts down the database at the end SQL*Plus: Release 11.2.0.2.0 Production on Fri Oct 14 12:25:01 2011 Copyright (c) 1982, 2010, Oracle. All rights reserved. SQL> connect sys as sysdba Enter password: Connected to an idle instance. SQL> startup pfile=initMYDB.ora ORACLE instance started. Total System Global Area 4878663680 bytes Fixed Size 2166528 bytes Variable Size 754979072 bytes Database Buffers 4110417920 bytes Redo Buffers 11100160 bytes Database mounted. Database opened. SQL> select name,open_mode from v$database; NAME OPEN_MODE --------- -------------------MYDB READ WRITE SQL> select * from v$version; BANNER ------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production PL/SQL Release 11.2.0.2.0 - Production CORE 11.2.0.2.0 Production TNS for Solaris: Version 11.2.0.2.0 - Production NLSRTL Version 11.2.0.2.0 – Production 7. Run utlu112s.sql to display the results of the upgrade SQL> @utlu112s.sql SQL> connect sys as sysdba Enter password: Connected. SQL> @utlu112s.sql . Oracle Database 11.2 Post-Upgrade Status Tool 10-14-2011 12:34:46 . Component Status Version HH:MM:SS . Oracle Server . VALID 11.2.0.2.0 00:26:39 Oracle Text . VALID 11.2.0.2.0 00:01:32 Gathering Statistics . 00:03:30 Total Upgrade Time: 00:31:43 PL/SQL procedure successfully completed. 8. Execute the script catuppst.sql (to perform upgrade actions that do not require the database to be in UPGRADE mode) SQL> catuppst.sql SQL> @catuppst.sql TIMESTAMP ------------------------------------------------------------------------------COMP_TIMESTAMP POSTUP_BGN 2011-10-14 12:35:42 PL/SQL procedure successfully completed. This script will migrate the Baseline data on a pre-11g database to the 11g database. ;;;;;;;;;;;;;;;;;;;;;; ;;;;;;;;;;;;;;;;;;;;;;; SQL> PROMPT Updating registry... Updating registry... SQL> INSERT INTO registry$history 2 (action_time, action, 3 namespace, version, id, 4 bundle_series, comments) 5 VALUES 6 (SYSTIMESTAMP, 'APPLY', 7 SYS_CONTEXT('REGISTRY$CTX','NAMESPACE'), 8 '11.2.0.2', 9 0, 10 'PSU', 11 'Patchset 11.2.0.2.0'); 1 row created. SQL> COMMIT; Commit complete. SQL> SPOOL off SQL> SET echo off Check the following log file for errors: /opt/oracle/cfgtoollogs/catbundle/catbundle_PSU_MYDB_APPLY_2011Oct14_12_3 5_49.log $vi /opt/oracle/cfgtoollogs/catbundle/catbundle_PSU_MYDB_APPLY_2011Oct14_12_3 5_49.log SQL> exec dbms_registry.set_session_namespace('SERVER') PL/SQL procedure successfully completed. SQL> ALTER SESSION SET current_schema = SYS; Session altered. SQL> PROMPT Updating registry... Updating registry... SQL> INSERT INTO registry$history 2 (action_time, action, 3 namespace, version, id, 4 bundle_series, comments) 5 VALUES 6 (SYSTIMESTAMP, 'APPLY', 7 SYS_CONTEXT('REGISTRY$CTX','NAMESPACE'), 8 '11.2.0.2', 9 0, 10 'PSU', 11 'Patchset 11.2.0.2.0'); 1 row created. SQL> COMMIT; Commit complete. SQL> SPOOL off 9. Execute the script utlrp.sql (to recompile any remaining stored PL/SQL and Java code) SQL> @utlrp.sql DOC> The following query reports the number of errors caught during DOC> recompilation. If this number is non-zero, please query the error DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors DOC> are due to misconfiguration or resource constraints that must be DOC> fixed before objects can compile successfully. DOC># ERRORS DURING RECOMPILATION --------------------------0 Function created. PL/SQL procedure successfully completed. Function dropped. PL/SQL procedure successfully completed. 10. Verify that all expected packages and classes are valid SQL> SELECT count(*) FROM dba_invalid_objects; SQL> SELECT distinct object_name FROM dba_invalid_objects; SQL> SELECT distinct object_name FROM dba_invalid_objects; OBJECT_NAME ------------------------------------------------------------------------------------------------------------------------------QUEST_SL_PLAN_TABLES QUEST_SL_EXPLAIN_TREE QUEST_SL_REPOS_PICK_PLAN_TABLE QUEST_SL_USER_MANAGER OBJECT_NAME ------------------------------------------------------------------------------------------------------------------------------QUEST_SL_PICK_PLAN_TABLES QUEST_SL_SCHEDULED_COLLECTOR OBJECT_NAME ------------------------------------------------------------------------------------------------------------------------------QUEST_SL_LAB_TREE 7 rows selected. --------------------End of upgrade--------------------