Monday, September 3, 2012

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 :
  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











8 comments:

  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 cleanly


    After 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.....?

    regards
    mohsin

    ReplyDelete
    Replies
    1. Follow the above document it's easy :)

      Delete
  2. Oracle Databases from 11.2.0.1 to 11.2.0.4
    Basic 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 cleanly

    copy alll files from old home to new home
    After Installing 11.2.0.4 , 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.4 Upgrade


    Copy the following Files to New Home 11.2.0.4 :
    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.4

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

    Note : Replace the Path , LD_Library_Path to new Home .



    Upgrade 11.2.0.1 database to 11.2.0.4

    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.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....

    ReplyDelete
    Replies
    1. Hello Mohsin Ali,

      These steps will work out?, I need to upgrade from 11.2.0.3.0 to 11.2.0.4.0

      Thanks in advance

      Delete
  3. Hello Osama,
    Can i have the link to your steps for upgrading from 11.2.0.1 to 11.2.0.4
    Regards

    ReplyDelete
  4. Hello Osama,
    How about upgrading from 11.2.0.2 to 11.2.0.3...

    ReplyDelete
  5. Thanks
    Useful documents

    ReplyDelete
  6. Would you have step upgrade rac 11.2.0.3 to 11.2.0.4? please

    ReplyDelete