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 

	Oracle Database 11.2 Pre-Upgrade Information Tool 04-30-2014 20:00:37
	Script Version: 11.2.0.4.0 Build: 008
	.
	**********************************************************************
	Database:
	**********************************************************************
	--> name: NTORCL
	--> version: 11.2.0.1.0
	--> compatible: 11.2.0.0.0
	--> blocksize: 8192
	--> platform: Linux x86 64-bit
	--> timezone file: V11
	.
	**********************************************************************
	Tablespaces: [make adjustments in the current environment]
	**********************************************************************
	--> SYSTEM tablespace is adequate for the upgrade.
	.... minimum required size: 1105 MB
	--> SYSAUX tablespace is adequate for the upgrade.
	.... minimum required size: 896 MB
	--> UNDOTBS1 tablespace is adequate for the upgrade.
	.... minimum required size: 400 MB
	--> TEMP tablespace is adequate for the upgrade.
	.... minimum required size: 60 MB
	--> EXAMPLE tablespace is adequate for the upgrade.
	.... minimum required size: 78 MB
	--> NTSOADEV_SOAINFRA tablespace is adequate for the upgrade.
	.... minimum required size: 17736 MB
	--> NTSOADEV_IAS_ORASDPM tablespace is adequate for the upgrade.
	.... minimum required size: 6 MB
	--> SOADEV_SOAINFRA tablespace is adequate for the upgrade.
	.... minimum required size: 1031 MB
	--> SOADEV_IAS_ORASDPM tablespace is adequate for the upgrade.
	.... minimum required size: 6 MB
	.
	**********************************************************************
	Flashback: OFF
	**********************************************************************
	**********************************************************************
	Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]
	Note: Pre-upgrade tool was run on a lower version 64-bit database.
	**********************************************************************
	--> If Target Oracle is 32-Bit, refer here for Update Parameters:
	-- No update parameter changes are required.
	.
	--> If Target Oracle is 64-Bit, refer here for Update Parameters:
	-- No update parameter changes are required.
	.
	**********************************************************************
	Renamed Parameters: [Update Oracle Database 11.2 init.ora or spfile]
	**********************************************************************
	-- No renamed parameters found. No changes are required.
	.
	**********************************************************************
	Obsolete/Deprecated Parameters: [Update Oracle Database 11.2 init.ora or spfile]
	**********************************************************************
	-- No obsolete parameters found. No changes are required
	.
	**********************************************************************
	Components: [The following database components will be upgraded or installed]
	**********************************************************************
	--> Oracle Catalog Views [upgrade] VALID
	--> Oracle Packages and Types [upgrade] VALID
	--> JServer JAVA Virtual Machine [upgrade] VALID
	--> Oracle XDK for Java [upgrade] VALID
	--> Oracle Workspace Manager [upgrade] VALID
	--> OLAP Analytic Workspace [upgrade] VALID
	--> OLAP Catalog [upgrade] VALID
	--> EM Repository [upgrade] VALID
	--> Oracle Text [upgrade] VALID
	--> Oracle XML Database [upgrade] VALID
	--> Oracle Java Packages [upgrade] VALID
	--> Oracle interMedia [upgrade] VALID
	--> Spatial [upgrade] VALID
	--> Expression Filter [upgrade] VALID
	--> Rule Manager [upgrade] VALID
	--> Oracle Application Express [upgrade] VALID
	... APEX will only be upgraded if the version of APEX in
	... the target Oracle home is higher than the current one.
	--> Oracle OLAP API [upgrade] VALID
	.
	**********************************************************************
	Miscellaneous Warnings
	<Note: Section can be empty if no such warnings are found.>
	**********************************************************************
	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.
	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 NTSOADEV_SOAINFRA has 7 INVALID objects.
	.... USER SYS has 6 INVALID objects.
	WARNING: --> Your recycle bin contains 208 object(s).
	.... It is REQUIRED that the recycle bin is empty prior to upgrading
	.... your database. The command:
	PURGE DBA_RECYCLEBIN
	.... must be executed immediately prior to executing your upgrade.
	WARNING: --> Database contains schemas with objects dependent on DBMS_LDAP package.
	.... Refer to the 11g Upgrade Guide for instructions to configure Network ACLs.
	.... USER APEX_030200 has dependent objects.
	.
	**********************************************************************
	Recommendations
	**********************************************************************
	Oracle recommends gathering dictionary statistics prior to
	upgrading the database.
	To gather dictionary statistics execute the following command
	while connected as SYSDBA:
	EXECUTE dbms_stats.gather_dictionary_stats;
	**********************************************************************

view raw SOA_DB_Pre-Upgrade_Info hosted with ❤ by GitHub

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.