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

Converting OVA file for use with Oracle VM Server Xen Commands

Recently, my team received a OVA file (VBX_Image_from_customer.ova) - a customer instance image to validate SOA Suite environment upgrade in-house. It would have been much easier to import the appliance into the Virtual Box on our local env (laptop/desktop) and get going. However, we had to host the appliance internally for cross-development and QA teams, and the only dedicated hardware at our disposal was a Oracle VM Server 3.2 physical machine.

Basically, the problem was two-fold:
a) OVA file cannot be specified as a disk parameter in the Oracle VM template configuration vm.cfg
b) Virtual Box cannot be installed on a Oracle VM Server machine (because Virtual Box can't operate under another hypervisor)

In this write-up, I will share the main tasks executed to use the ova file and create the guest virtual machine on the OVM Server host.   The OVA (Open Virtual Appliance) file is nothing more than a TAR archive, containing the .OVF and .VMDK files. For those interested, here is a good post by Mike on the different file formats and tools for virtualization.

-- Install disk image conversion utility
# yum install kvm-qemu-img.x86_64

-- Extract the OVA file contents
# tar -xvf VBX_Image_from_customer.ova
# ls
    - VBX_Image_from_customer-disk1.vmdk
    - VBX_Image_from_customer.ovf

-- Determine if your version of QEMU supports VMDK Sparse by executing the following command
# qemu-img info VBX_Image_from_customer-disk1.vmdk

-- if you get a message like below then VMDK Sparse is not supported
<<qemu-img: Could not open 'VBX_Image_from_customer-disk1.vmdk'>>

-- Use the VBoxManage command-line tool that ships with VirtualBox (if qemu-img option does not work)
# VBoxManage clonehd VBX_Image_from_customer-disk1.vmdk --format RAW developmentSOA.img

-- Convert from vmdk to raw img if your version of QEMU supports VMDK Sparse
# qemu-img convert -f vmdk -O raw VBX_Image_from_customer-disk1.vmdk developmentSOA.img

-- Use the RAW image file in the vm.cfg 'disk' parameter; sample file below

bootloader = '/usr/bin/pygrub'
device_model = '/usr/lib/xen/bin/qemu-dm'
disk = ['file:<path-to-OVS-repository>/developmentSOA.img,hda,w']
memory = '8192'
maxmem = '8192'
OVM_simple_name = 'MyCompany SOA V2'
name = 'SOA_V2_MYCOMP'
OVM_os_type = 'Oracle Linux 5'
vcpus = 4
uuid = 'e405f7ea-80bb-4a14-97b2-cf969077e25a'
on_crash = 'restart'
on_reboot = 'restart'
keymap = 'en-us'
vnc = 1
vncconsole = 1
vnclisten = '127.0.0.1'
vncpasswd = ''
vncunused = 1
vif = ['bridge=xenbr0']
timer_mode = 2
expose_host_uuid = 1

-- Create VM guest instance using Xen command
# xm create vm.cfg -c

You should be all set to start up the customer guest virtual machine.