Wednesday, November 9, 2011

Oracle SOA Suite 10g to 11g Migration – Migrating XREF Runtime Data.

Oracle SOA Suite 10g to 11g Migration – Migrating XREF Runtime Data:

If you have Oracle SOA Suite 10g cross reference (XREF) runtime data in an Oracle database (oraesb.XREF_DATA) then the same needs to be migrated to Oracle SOA Suite 11g (SOAINFRA.XREF_DATA).

The utility is required, as the XREF database schema table (XREF_DATA) has changed for Oracle Fusion Middleware 11g.
We had around 12 million records in our 10g environment and used the utility 

Upgrade10gXrefTo11gXref.sql provided by oracle to migrate the data.
This utility is using the DBLINK to get the data from 10g database because of this the migration was taking more time and could not able to complete.

We have done some modification to the SQL utility based our requirement and we were able to migrate the data successfully within 9hrs of time.
Instead of using the DBLIINK, we have used the Oracle DataPump Utility to transfer the data from Oracle SOA 10g database to Oracle SOA 11g database schema(SOAINFRA –Where the 11g XREF data is available ) and used the local table in the utility script.

The steps to execute the script:

- Using Oracle DataPump Utility, transfer the data from the table oraesb.XREF_DATA of SOA 10g source database to SOA 11g destination database table SOAINFRA.XREF_DATA_MIGRATION (the schema where the 11g XREF data is available).
- Change the MDS location 'oramds: /apps/EAIMetaData/Xref/' in the script, location where the XREF meta data has stored, with your value.
- Log in to Oracle database that hosts the Oracle SOA 11g XREF data as the SOAINFRA user and execute the script.

All the corrupted/duplicated data will be inserted into the table XREF_DATA_CORRUPTED.
Refer the script attached with this blog



  1. We have a requirement to upgrade AIA 2.0 to AIA 11.3 latest version.
    Current Product design and synch from Siebel to BRM - Design and Synch process not automate –requires manual intervention e.g. “Manual XREF data population.
    Kindly let us know how can we make manual process to automate in 11g.
    Thanks in advance

    1. You can use the xref:populateXRefRow function to dynamically populate the XREF table.

      xref:populateXRefRow(xrefLocation as string, xrefReferenceColumnName as string,
      xrefReferenceValue as string, xrefColumnName as string, xrefValue as string, mode
      as string) as string

      This function can be used from BPEL Assign activity and XSLT.

      Refer the following url for more details on XREF

      Albin I