Upgrading Oracle Databases from 11.2.0.1 to 11.2.0.2

This discussion is about performing an upgrade from 11.2.0.1 databases to 11.2.0.2.
This is an out-of-place manual upgrade from 11.2.0.1 to 11.2.0.2. 11.2.0.2 Patch set is a full release.
It cannot be installed on top of existing 11.2.0.1 ORACLE_HOME.

Note the following 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

Running the Pre-upgrade script utlu112i.sql. This upgrade is focusing on Unix environments.

After installing the 11.2.0.2 software on new oracle /opt/app/oracle/product/11.2.0.2, Run the following script from new oracle home on the 11.2.0.1 database.

Connect as sys
SQL> spool pre_upgrade.log
SQL> @/opt/app/oracle/product/11.2.0.2/rdbms/admin/utlu112i.sql
SQL> spool off

Verify the pre_upgrade.log file for any issue.
If following warning is issued:
**********************************************************************
Recommendations
**********************************************************************
Oracle recommends gathering dictionary statistics prior to upgrading the database.
To gather dictionary statistics execute the following command while connected as SYSDBA:

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

If following warning is issued:

WARNING: –> Database contains INVALID objects prior to upgrade.
…. The list of invalid SYS/SYSTEM objects was written to
…. registry$sys_inv_objs.
…. The list of non-SYS/SYSTEM objects was written to
…. registry$nonsys_inv_objs.
…. Use utluiobj.sql after the upgrade to identify any new invalid
…. objects due to the upgrade.
…. USER PUBLIC has 1 INVALID objects.

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

Note:Run the utlu112i.sql again after running above two statements.

Following are the known issue

  • 11.2.0.2 uses Time Zone version 14. 11.2.0.1 use Time Zone version 11

    WARNING: –> Database is using a timezone file older than version 14.
    …. After the release migration, it is recommended that DBMS_DST package
    …. be used to upgrade the 11.2.0.1.0 database timezone version
    …. to the latest version which comes with the new release.

    Note: 11.2.0.2 uses Time Zone version 14. 11.2.0.1 use Time Zone version 11.
    11.2.0.2 DBUA would automatically upgrade Time Zone to version 14 if you checked “Upgrade Timezone Version and TIMESTAMP WITH TIME ZONE data” box

    You can either upgrade time zone version after upgrading to 11.2.0.2 using DBMS_DST package
    or upgrade 11.2.0.1 time zone to version 14

    Run pre-upgrade diagnostic utility dbupgradiag.sql

    Run dbupgdiag.sql script from below My Oracle Support article to verify that all the components in dba_registry are valid and no invalid data dictionary objects in dba_objects
    Note 556610.1 Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.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 (multiple times) to validate the invalid objects in the database, until there is no change in the number of invalid objects.

    $ cd $ORACLE_HOME/rdbms/admin
    $ sqlplus “/ as sysdba”
    SQL>@utlrp.sql

    After validating the invalid objects, re-run dbupgdiag.sql in the database once again and make sure that everything is fine.

    Configure the 11.2.0.1 database environment for 11.2.0.2 Upgrade

    Copy pfile/spfile and orapw.ora file to the 11.2.0.2 Home
    Connect as sys
    Go to /opt/app/oracle/product/11.2.0/dbs and copy the spfile.ora or init.ora and password file orapw.ora to /opt/app/oracle/product/11.2.0.2/dbs.

    Set the environment for 11.2.0.2 Upgrade

    Following environment variables needs to be set before proceeding the upgrade.

    Export ORACL_HOME=/opt/app/oracle/product/11.2.0.2
    Export ORACLE_SID=
    Echo $PATH

    Replace all occurrences of /opt/app/oracle/product/11.2.0 with /opt/app/oracle/product/11.2.0.2 and set the PATH to new values.

    Export PATH=
    Export LD_LIBRARY_PATH=/opt/app/oracle/product/11.2.0.2/lib
    Export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

    Copy network/admin files to new 11.2.0.2 oracle Home
    Copy network configuration files (listener.ora, sqlnet.ora, tnsnames.ora ..etc) from 11.2.0.1 $ORACLE_HOME/admin/network ( or $TNS_ADMIN) location to 11.2.0.2 $ORACLE_HOME/admin/network ( or $TNS_ADMIN) location

    Copy OC4J directories from 11.2.0.1 home to new 11.2.0.2 oracle Home (This is required only if there is DB Console configured)

    ORACLE_HOME/
    ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole_

    Specify the actual name for

    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

    Check the /tmp/upgrade.log for any errors.
    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;

    Make sure all the components are valid.
    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;

    Check LISTENER started from 11.2.0.2 Home

    Make sure Listener started from new 11.2.0.2 Home and listening to Oracle database.

  • This entry was posted in Oracle Upgrades. Bookmark the permalink.