Upgrading Oracle SOA Suite Database to 11.2.0.3+

In this post, will share my experience Upgrading a development SOA Database version from 11.2.0.1  to 11.2.0.4. This will be a pre-requisite to be addressed first by many of the customers, who are planning to uptake the next major release of SOA Suite 12c (12.1.3.0.0 - GA mid Y2014).To clarify, here am referring to the minimal required version for the SOA Suite 12c infrastructure i.e. SOAINFRA database (ver 11.2.0.3), which is certified to function with no other database vendor than Oracle.

The high-level details of the tasks executed are described below. Please consult the relevant Oracle Database Upgrade Guides for more details. (Upgrading to the New Release of  Oracle Database)

1 - Run the Database Pre-Upgrade Information Tool 

- Connect to the SOA 11g database as a user with SYSDBA privileges to run the Pre-Upgrade tool:

        SQL> @$11g_ORACLE_HOME/rdbms/admin/utlu112i.sql

- The Pre-Upgrade Information Tool displays warnings about possible upgrade issues with the database.
- For invalid objects or invalid components, Oracle recommends running the the utlrp.sql before starting the upgrade as a means to minimize the number of invalid objects and components marked with WARNING.
- Take necessary measures based on the Pre-Upgrade tool output; in my case no corrective actions were required.

SOA Database 11.2.0.1 Pre-Upgrade Information Tool Sample Output 

2 - Backup SOA Schemas (in 11.2.0.1)

2.1 Take necessary SOA schema backups

- in my development environment, a schema-mode export of the SOA schema was performed using Oracle Data Pump Export utility
- Refer Database Utilities Guide Chapter 2: Data Pump Export

2.2  Create export dump directory on the DB filesystem, say /u02/app/oracle/soa_expdp

2.3  Execute below SQL commands as SYSDBA:

  SQL> CREATE DIRECTORY soadumpdir AS '/u02/app/oracle/soa_expdp';

  SQL> GRANT READ, WRITE ON DIRECTORY soadumpdir TO system;

2.4  Run Data Pump export utility

   # cd $11g_ORACLE_HOME  

   # ./bin/expdp schemas=SOADEV_SOAINFRA directory=soadumpdir 

 dumpfile=expschema_soainfra.dmp logfile=expschema_soainfra.log

(where 'SOADEV' is the SOAINFRA schema prefix)

-  on prompt for credentials specify sys/<sys_password> as sysdba

-  SOA schema export dump file will be copied over to location specified  /u02/app/oracle/soa_expdp/expschema_soainfra.dmp

3 - Install Database 11.2.0.4 Software

-  Install 11.2.0.4 Database software on a new Oracle home to perform an out-of-place upgrade

-  Select the option to Install Database software only

4 - Perform Sanity Operations (before upgrade)
4.1 Check Invalid Objects

- Check for Invalid Objects. There should be no invalid objects in SYS and SYSTEM user schema.

   SQL> select unique OBJECT_NAME, OBJECT_TYPE, OWNER from DBA_OBJECTS where STATUS='INVALID';

- Recompile invalid objects with utlrp.sql before the upgrade by running the utlrp.sql script, located in the $11204_ORACLE_HOME/rdbms/admin directory

   SQL> @rdbms/admin/utlrp.sql
4.2 Check Duplicate Objects

- Always check for DUPLICATE objects in SYS/SYSTEM

SQL> select OBJECT_NAME, OBJECT_TYPE from DBA_OBJECTS where OBJECT_NAME || OBJECT_TYPE in (select OBJECT_NAME || OBJECT_TYPE from DBA_OBJECTS where OWNER='SYS') and OWNER='SYSTEM' and OBJECT_NAME not in ('AQ$_SCHEDULES_PRIMARY', 'AQ$_SCHEDULES', 'DBMS_REPCAT_AUTH');

- Fix DUPLICATE objects in SYS/SYSTEM BEFORE upgrade

4.3 Check Invalid Components

- Always check for NON VALID components

SQL> select substr(COMP_ID, 1,10) compid, substr(COMP_NAME,1,24) compname, STATUS, VERSION from DBA_REGISTRY where STATUS<>'VALID';

- Fix all NON VALID components BEFORE upgrade

4.4 Purge Recylce Bin

- If upgrading from 10g or 11g, purge the recyclebin

SQL> purge DBA_RECYCLEBIN;

5 - Upgrade SOA Database Using 11.2.0.4 DB Upgrade Assistant (DBUA)
- Run DB Upgrade Assistant from the 11.2.0.4 Oracle Home

    # cd $11.2.0.4_ORACLE_HOME
    # ./bin/dbua

- Select the option to backup database using DBUA (optional step)
- Summary of the steps performed during the database upgrade and Log files will be available at "$11.2.0.4_ORACLE_BASE/cfgtoollogs/dbua/<SID>/upgrade2"

6 - Post Upgrade
- Perform sanity checks for any additional Invalid/Duplicate Objects and Components post upgrade
- Add datafiles to SOAINFRA tablespace as recommended in the SOA 12c Upgrade Guide.

As a good practice,  refer the official Oracle SOA 12c Upgrade documentation for the latest updates