Database upgrade -11.2.0.1 to 11.2.0.4

Database upgrade -11.2.0.1 to 11.2.0.4

Complete Checklist for out-of-Place Manual Upgrade from Previous 11.2.0.N Version to the Latest 11.2.0.N Patchset (Doc ID 1276368.1)

STEP 1)
Download the 11.2.0.4 or RDBMS software.

STEP 2)
Install the latest 11.2.0.4 RDBMS Software into a new ORACLE_HOME.

STEP 3)
After installing the latest 11.2.0.4 RDBMS software, run Pre-upgrade tool (utlu112i.sql) on database (11.2.0.1). You need to review pre-upgrade output and fix if any issues before upgrading to the latest release of 11.2.0.4.

STEP 4)
Run dbupgdiag.sql script from the below My Oracle Support article to verify that all the components in dba_registry are valid and no invalid data dictionary objects exist in dba_objects.

Note 556610.1 Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql)

$ sqlplus / as sysdba
sql> alter session set nls_language=’American’;
sql> @dbupgdiag.sql
sql> exit

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.

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

STEP 5)
Configure the target 11.2.0.4 ORACLE_HOME.

5.1)  make sure environment variables ORACLE_BASE, ORACLE_HOME, PATH and LD_LIBRARY_PATH are set to point to 11.2.0.4.

Set ORACLE_SID to the 11.2.0.4 DB name to upgrade i.e.

the /etc/oratab file points to your Oracle Database 11g Release 2 (11.2.0.4) Oracle home.

5.2) Copy init.ora/spfile and password file (orapw<Sid>.ora) from 11.2.0.1 $ORACLE_HOME/dbs to 11.2.0.4 $ORACLE_HOME/dbs

5.3) Copy network configuration files (listener.ora, sqlnet.ora, tnsnames.ora, etc) from 11.2.0.1 $ORACLE_HOME/network/admin (or $TNS_ADMIN) location to 11.2.0.4 $ORACLE_HOME/network/admin (or $TNS_ADMIN) location

5.4) If DB Console / DB Control is configured and used then also copy the following two directories and their contents from 11.2.0.1 to 11.2.0.2. (If DB Console / DB Control is not configured these directories MAY not exist).
ORACLE_HOME/<hostname_dbname>ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole_<hostname_dbname>
Specify the actual name for <hostname_dbname>

5.5) Make sure the COMPATIBLE initialization parameter is properly set for Oracle Database 11g Release 2 (11.2). The Pre-Upgrade Information Tool displays a warning in the Database section if COMPATIBLE is not properly set.

Step 6)
Upgrade the database manually.

6.1) Start sqlplus and run catupgrd.sql script from the newly installed (Target) $ORACLE_HOME/rdbms/admin

Sqlplus ” / as sysdba ”
SQL> spool /tmp/upgrade.log
SQL> startup upgrade
SQL> set echo on
SQL> @?/rdbms/admin/catupgrd.sql;
SQL> spool off
SQL> Shutdown immediate

6.2) Check catupgrd.sql spool file for errors.

6.3) restart the database in normal mode.

6.4) SQL> @$ORACLE_HOME/rdbms/admin/catuppst.sql;

6.5) SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql;

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

6.7) Upgrade Time Zone to latest version using DBMS_DST.

11.2.0.1 Uses Time Zone version 11 but 11.2.0.4 Uses Time Zone version 14. So, need to upgrade time Zone after DB upgrade. In case of DBUA upgrade it will update automatically.

Note 1201253.1 Actions For DBMS_DST Updates When Upgrading to 11.2.0.4.