Oracle Databases from 11.2.0.1 to 11.2.0.2

Posted on by Osama Mustafa in Database
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 :

  1. Spfile/Pfile/init.ora
  2. orapwd
  3. tnsname.ora
  4. Listener.ora
  5. 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;

And Please Make Sure the listener has been started from the new home not the old one .

I will Post Upgrade to 11.2.0.3 “Enshalla”

Thank you
Osama Mustafa

Published by Osama Mustafa

Osama considered as one of the leaders in Cloud technology, DevOps and database in the Middle-East. I have more than ten years of experience within the industry. moreover, certfied 4x AWS , 4x Azure and 6x OCI, have also obtained database certifications for multiple providers. In addition to having experience with Oracle database and Oracle products, such as middle-ware, OID, OAM and OIM, I have gained substantial knowledge with different databases. Currently, I am architecting and implementing Cloud and DevOps. On top of that, I'm providing solutions for companies that allow them to implement the solutions and to follow the best practices.

8 thoughts on “Oracle Databases from 11.2.0.1 to 11.2.0.2”

  1. Dear MR. Osama,I want to upgrade our DB to 11patch1 to patch 4, Can i use this document for said task, also kindly elaborate after your this step ( 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 cleanlyAfter Installing 11.2.0.2 , Go to 11.2.0.1 database and run ) We also can move all the DBFILEs/redologfile/controlfile etc… to new ORACLE_HOME…..?regardsmohsin

    Like

    Reply

  3. Oracle Databases from 11.2.0.1 to 11.2.0.4Basic Steps : 1. Install 11.2.0.4 into a separate ORACLE_HOME.2. Take a full backup of the database.3. Always shutdown the database cleanlycopy alll files from old home to new homeAfter Installing 11.2.0.4 , Go to 11.2.0.1 database and run :Connect as sysSQL> spool pre_upgrade.logSQL> @ORACLE_HOME/rdbms/admin/utlu112i.sqlSQL> spool off To check Data Dictionary Connect as sysSQL> 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 sysSQL> @$ORACLE_HOME/rdbms/admin/utlrp.sqlRun pre-upgrade diagnostic utility dbupgradiag.sql$ cd $ORACLE_HOME/rdbms/admin$ sqlplus “/ as sysdba”SQL> spool pre_dbupgdiag.logSQL>@dbupgdiag.sqlSQL>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.4 Upgrade Copy the following Files to New Home 11.2.0.4 :Spfile/Pfile/init.oraorapwdtnsname.oraListener.oraOC4J Home (ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole_) Only if you have enterprise managerSet The environment to 11.2.0.4export ORACLE_HOME=/u01/app/oracle/prouct/11.2.0.4export ORACLE_SID=ORCLNote : Replace the Path , LD_Library_Path to new Home .Upgrade 11.2.0.1 database to 11.2.0.4sqlplus / as sysdba spool upgrade.logstartup upgradeset echo on@?/rdbms/admin/catupgrd.sql;spool offshutdown immediateRestart the database in normal modesqlplus / as sysdba @/opt/app/oracle/product/11.2.0.4/rdbms/admin/catuppst.sql;@/opt/app/oracle/product/11.2.0.4/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.4.0' scope=spfile;SQL> shutdown immediate;SQL> startup;please review and comment all above steps….

    Like

    Reply

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.