Please Follow the Below Steps For Upgrading 10g to 11gR2 , Before Doing Anything you need to take backup for the following Files :
1. BACKUP BINARIES, DB,TNSNAMES,LISTENER,INIT,SPFILE,PWFILE THAT ARE BEING UPGRADED.
2. COMPATIBLE PARAMETER MUST BE SET TO MINIMUM 10.0.0.0. THE RECOMMENDED IS 11.2.0 FOR 11gR2 PARAMETER FILE.
Step-1 :
Install New Oracle Database Software & Apply Any Patches Necessary.
Step-2 :
Run The Pre-Upgrade Information Tools : (Mandatory)
1. Copy the Pre-Upgrade Information Tool (utlul12i.sql) from the Oracle Database 11gR2 directory: $ORACLE_HOME/rdbms/admin to a temporary directory /tmp.
2. Set your environment to the one that is being upgraded. Assuming 10g.
3. Change directory to /tmp that you copied utlu112i.sql to in Step 1.
4. Start SQL*Plus and login as ‘/ as sysdba’
5. Spool the results to a log file:
o SQL> SPOOL upgrade_info.log
6. Run the Pre-Upgrade Information Tool:
o SQL> @utlul12i.sql
o SQL> SPOOL OFF
Open The Spool File for Checking Purpose :
Oracle recommends gathering stats before the upgrade: EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
■ Deprecated CONNECT Role
■ Access Control to Network Utility Packages
■ Database Links with passwords
■ TIMESTAMP WITH TIME ZONE Data Type
■ Optimizer Statistics
■ Invalid Objects
■ Save Oracle Enterprise Manager Database Control Data
■ Complete Materialized View Refreshes
■ Ensure No Files Need Media Recovery
■ Ensure No Files Are in Backup Mode
■ Resolve Outstanding Distributed Transactions
■ Sync Standby Database with the Primary Database
■ Purging the Database Recycle Bin
Step-3 :
Prepare 11gR2 Home :
1. Copy configuration files (init file, tnsnames, listener, pwfile) from old Oracle Home to new.
2. Update init files with new COMPATIBLE parameter, fix any deprecated ones, and adjust the values to at least the minimum values indicated by the Pre-Upgrade Tool.
3. Update any relative path names in parameter file to fully path names.
Step-4 :
After all the Above Steps , you will start the upgrade Database for 10g Home :
1. Shutdown the database:
o SQL> SHUTDOWN IMMEDIATE;
2. Make sure the following checks:
o The oratab file points to Oracle Database 11g Release 2 Oracle Home
o The following environment variables point to the Oracle 11g Release 2 directories:
§ ORACLE_HOME
§ PATH
3. Change to the $ORACLE_HOME/rdbms/admin directory and start SQL*Plus
o sqlplus ‘/ as sysdba’
4. Start the instance by issuing following command (you may get messages that parameters are obsolete, fix those and start up the db again):
o SQL> STARTUP UPGRADE;
o SQL> SPOOL upgrade.log
5. Run the catupgrd.sql script:
o SQL> @catupgrd.sql
6. Once completed, shutdown the database and restart it.
o SQL> SHUTDOWN IMMEDIATE;
o SQL> STARTUP;
7. Run the Post-Upgrade Status Tool to provide a summary of the upgrade. (If there are any INVALID components, then check upgrade manual for fixes.)
o SQL> @utlul12s.sql
8. Run catuppst.sql to perform upgrade actions that do not require db to be in upgrade mode:
o SQL> $ORACLE_HOME/rdbms/admin/catuppst.sql
9. Run utlrp to recompile any remaining stored PL/SQL and other objects.
o SQL> $ORACLE_HOME/rdbms/admin/utlrp.sql
10. Verify that all objects are valid:
o SQL> SELECT count(*) FROM dba_invalid_objects;
o SQL> SELECT distinct object_name FROM dba_invalid_objects;
Thank you
Osama mustafa
Technology Geek 