Legacy

Upgrading Oracle 11.2.0.1 to 11.2.0.3

Overview

What follows are manual steps to upgrade an 11.2.0.1 database to 11.2.0.3. This guide assumes you have installed Oracle 11.2.0.1 and 11.2.0.3 each into its own home directory on the system to be upgraded. In Oracle-speak this is called an out-of-place upgrade. Funny name but this is the recommended path. Also, Oracle support feels the manual method is more reliable than using the Database Upgrade Assistant (DBUA).

Starting with the first patch set for Oracle Database 11g Release 2 (11.2.0.2), Oracle Database patch sets are full installations of the Oracle Database software. You are no longer required to install the base release, and then apply the patch set.

The database name used in some of the examples is DB42.

Preupgrade Prep

0) Install the new version into its own home directory.
   Example: C:\app\oracle\product\11.2.0.3\dbhome_1

1) Run the run the pre-upgrade utility.
   SQLPLUS> @C:\app\oracle\product\11.2.0.3\db_1\RDBMS\ADMIN\utlu112i.sql
   * Run it from the old database calling the script in new version path.
   * Make sure you don't get any critical warnings.

Run following SQL (2-9) from the original ORACLE_HOME (11.2.0.1) database:

2) Decrease downtime by gathering optimizer statistics just prior to upgrade.
   SQLPLUS> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

3) Identify any invalid objects.
   SQLPLUS> %ORACLE_HOME%\rdbms\admin\utluiobj.sql

4) Ensure that no files need media recovery.
   SQLPLUS> SELECT * FROM v$recover_file;

5) Resolve any outstanding distributed transactions.
   SQLPLUS> SELECT * FROM dba_2pc_pending;

   If any rows returned issue the following:
      SQLPLUS> SELECT local_tran_id FROM dba_2pc_pending;
      SQLPLUS> EXECUTE dbms_transaction.purge_lost_db_entry('');
      SQLPLUS> COMMIT;

6) Purge the recycle bin.
   SQLPLUS> PURGE DBA_RECYCLEBIN;

7) Create a backup parameter file from the SPFILE.
   SQLPLUS> CREATE
   pfile='C:\app\oracle\product\11.2.0.3\db_1\database\initDB42.ora' 
   FROM spfile;

8) If DB Console\DB Control is configured and used then also copy the following 
   directory and their contents from 11.2.0.1 to 11.2.0.3.

    %ORACLE_HOME%\oc4j\j2ee\OC4J_DBConsole_

9) Shutdown the database cleanly.
   For Windows you must delete the Windows services in old home path (11.2.0.1).
   a. Stop the 11.2.0.1 Oracle database services.
   b. Delete 11.2.0.1 Oracle service using %ORACLE_HOME%\bin\ORADIM binary.
      C:> ORADIM -DELETE -SID DB42
   c. Delete Listener (LISTENER) using the Net Configuration Assistant.

10) Set environment variables to match the path of the version to install.
    ORACLE_BASE, ORACLE_HOME and TNS_ADMIN (if used).

11) Start services.
    For Windows you must create a new service in new home path (11.2.0.3)
    using ORADIM.
    a. Create Windows Service
       C:> ORADIM -NEW -SID DB42 -SYSPWD MyPassword -STARTMODE MANUAL 
       -PFILE %ORACLE_HOME%\DATABASE\spfileDB42.ORA

       The file pwdDB42.ora will be created.

    b. Create Oracle Listener (LISTENER) using the Net Configuration Assistant.

Upgrade Process

1) Start sqlplus and run catupgrd.sql script from the 11.2.0.3
   OS> cd  %ORACLE_HOME%\rdbms\admin
   OS> sqlplus "sys/MyPassword as sysdba"
       Displayed: Connected to an idle instance.

   SQLPLUS> spool C:\temp\upgrade.log
   SQLPLUS> startup upgrade
            Displayed: 
               ORACLE instance started.
               ...
               Database mounted.
               Database opened.
   SQLPLUS> set echo on
   SQLPLUS> @catupgrd.sql;
            Takes about 30-45 minutes.

   Note: The above process performs a shutdown immediate.   

2) Check catupgrd.sql spool file for errors.
   If successful each component will show upgraded to the latest version 
   (toward the end of the file).

3) Restart the database in normal mode and run final scripts.
   a. Bounce the Oracle service.
      If Windows, also start the OracleMTSRecoveryService
   b. Start database normally.
      OS> cd  %ORACLE_HOME%\rdbms\admin
      OS> sqlplus "sys/MyPassword as sysdba"
          Displayed: Connected to an idle instance.

      SQLPLUS> startup

  c. Recompile any INVALID application objects.
      SQLPLUS> @admin\utlrp.sql;

  d. Run dbupgdiag.sql script (See note: 556610.1) and verify that all
     the components in dba_registry are valid and there are no invalid 
     objects in dba_objects.

Post Upgrade Actions

Make changes to your OraNet to allow connections using the new listener (listener.ora, tnsnames.ora, sqlnet.ora etc.).
You may need to grant SYSDBA for user accounts that had it prior to upgrade.
Check your jobs to ensure they are using the new ORACLE_HOME.
Upgrade Time Zone to latest version using DBMS_DST.
For Windows
- Ensure the ORA_<SID>_AUTOSTART is set to TRUE in the Windows registry.
- Remove the key ORA_<OracleSID>_PFILE if it exists.
- Confirm the service file has been created in %ORACLE_HOME%\database.
  If not run: SQL> CREATE SPFILE FROM PFILE='c:\pfileDir\init<SID>.ora';
- Ensure there are no pointers going to previous version file paths.
  To list all parameters run: SQL> show parameter
  
  A common one that may or may not need to be changed is audit_file_dest.
  ALTER SYSTEM SET audit_file_dest='C:\APP\ORACLE\ADMIN\SUMMITDB\ADUMP' SCOPE=both;
  For Windows see 1063941.6

Comments

You can run "opatch lsinventory -detail" against previous and new ORACLE_HOME to compare installed products.
This document derived from 1276368.1. I had to go back-and-forth with Oracle support to put this together as concisely as I did with respect to Windows environments as their documentation leave out some significant actions.

<- Legacy