Oracle Databases from 11.2.0.1 to 11.2.0.2

Basic Steps : 

1. Install 11.2.0.2 into a separate ORACLE_HOME.
2. Take a full backup of the database.
3. Always shutdown the database cleanly


After Installing 11.2.0.2 , Go to 11.2.0.1 database and run :

Connect as sys

SQL> spool pre_upgrade.log

SQL> @ORACLE_HOME/rdbms/admin/utlu112i.sql

SQL> spool off

 To check Data Dictionary
Connect as sys

SQL> EXECUTE dbms_stats.gather_dictionary_stats;
Note: Ignore if dbms_stats.gather_dictionary_stats was run once.


To Fix invalid Object in the database :


connect as sys
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

Run pre-upgrade diagnostic utility dbupgradiag.sql

$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus “/ as sysdba”

SQL> spool pre_dbupgdiag.log

SQL>@dbupgdiag.sql

SQL>spool off
 if the dbupgdiag.sql script reports any invalid objects, run $ORACLE_HOME/rdbms/admin/utlrp.sql

Configure the 11.2.0.1 database environment for 11.2.0.2 Upgrade 


Copy the following Files to New Home 11.2.0.2 :
Spfile/Pfile/init.ora
orapwd
tnsname.ora
Listener.ora
OC4J Home (ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole_) Only if you have enterprise manager

Set The environment to 11.2.0.2

export ORACLE_HOME=/u01/app/oracle/prouct/11.2.0.2
export ORACLE_SID=ORCL

Note : Replace the Path , LD_Library_Path to new Home .



Upgrade 11.2.0.1 database to 11.2.0.2

sqlplus  / as sysdba
 spool upgrade.log
startup upgrade
set echo on
@?/rdbms/admin/catupgrd.sql;
spool off
shutdown immediate

Restart the database in normal mode


sqlplus  / as sysdba
 @/opt/app/oracle/product/11.2.0.2/rdbms/admin/catuppst.sql;
@/opt/app/oracle/product/11.2.0.2/rdbms/admin/utlrp.sql;
select comp_name,version,status from dba_registry;
select owner,count(*) from dba_objects where status != ‘VALID’ group by owner;

Change the compatibility parameter

Change the compatible parameter and restart the database.

SQL> alter system set compatible=’11.2.0.2.0' scope=spfile;
SQL> shutdown immediate;
SQL> startup;