Monday, May 23, 2011

Oracle CAMM with SOA

We recently upgraded to and saw that CAMM was not able to discover any of the BPEL targets. We had to get one off patch to fix this issue:

Upgrading CAMM for SOA:
- apply patch 9534485 (this is the latest release of CAMM)
- apply patch 11939423 (change in one of the jar to read bpel descriptor)

Once it is done, just use resource configuration to configure your server.

- Restart SOA
- Restart CAMM

You can see BPEL process discoveries as below:

XRefUtility 10g - Round 2

We were using XrefUtility and realized that Xref utility allows us to run select clause on source and target database, so that we can manipulate any data before comparing it with Xref table values. In our case, we had to manipulate the XRef values as well before comparing it with source and target database.

For example, it is pretty common to add revision number in BRM values in Xref table. Those revision number changes in BRM without getting updated in AIA database. To solve that problem, revision number should be truncated from AIA XREF and target query. I had to modify XRefUtility to achieve this functionality. I enhanced UI as below:

Another thing we found, is that going through 8 or so report is so cumbersome. If you look at my blog, you can see each report is combination of different cases. It would be nice to have all discrepancy in one or two reports, and have more control over queries. I ended up just writing my own utility in SQL as below:

  • create source view
    drop materialized view sv;
    create materialized view sv as
    select a.row_id as sv_id, as sv_name from SIEBEL.S_ASSET@sbltst a, SIEBEL.S_PROD_INT@sbltst b, SIEBEL.S_ORG_EXT@sbltst c, SIEBEL.S_ASSET@sbltst a2
    where a.PROD_ID = b.ROW_ID
    AND a.BILL_ACCNT_ID = c.row_id
    AND a.root_asset_id = a2.row_id
    AND a2.status_cd = 'Active'

  • create target view
    drop materialized view tv;
    create materialized view tv as
    select * from (
    select ' '||PP.poid_type||' '||PP.poid_id0 AS tv_id, pp.descr as tv_name
    from PIN.group_t@BRMMIG g, .....

  • create xref view (make it tabluer from row based view so that we can do joins and easier query).
    drop materialized view xv;
    create materialized view xv as
    select row_number, XREF_TABLE_NAME as xref_table_name, max(decode(XREF_COLUMN_NAME, 'SEBL_01', VALUE,null)) sv_id ,max(decode(XREF_COLUMN_NAME, 'COMMON', VALUE,null)) common_id
    ,max(decode(XREF_COLUMN_NAME, 'BRM_01', rtrim(substr(value,0,length(VALUE)-2)),null))
    tv_id from XREF_DATA

Note we can create materialized view so that it doesn't affect the performance and refresh the view only when required. Now it is pretty simple to generate all reports and report numbers and data in one report:
  • count for sv_id null in xv - shows all descrepancy where source id is missing in AIA Xref Database
    select count(*) from xv where sv_id is null
    select * from xv where sv_id is null

  • count for tv_id null in xv - shows all descrepancy where target id is missing in AIA Xref Database
    select count(*) from xv where tv_id is null
    select * from xv where tv_id is null

  • count for rows in sv not in xv - shows all descrepancy where data is in source database but missing AIA xref
    select count(*) from sv where not exists ( select * from xv where xv.sv_id = sv.sv_id )
    select * from sv where not exists ( select * from xv where xv.sv_id = sv.sv_id )

  • count for rows in tv not in xv - shows all descrepancy where data is in target database but missing AIA xref
    select count(*) from tv where not exists ( select * from xv where xv.tv_id = tv.tv_id )
    select * from tv where not exists ( select * from xv where xv.tv_id = tv.tv_id )

OC4J JConsole 10g

For JDK 1.5

  • Changes in opmn.xml
    Add JVM parameter:
    Change tmp directory to c:\temp

  • (For Windows) Change the owner user for service same as logged in user
    Note make sure user name doesn't have (_) or (-) in it. bug:

  • Start the SOASuite in following sequence
    set TMP=c:\temp
    set TEMP=c:\temp
    opmnctl shutdown
    opmnctl startall
    jps (this should show all the pid which are used by Oracle App server

  • Start jconsole and use pid for oc4j_soa

For JDK 1.6

  • Changes in opmn.xml:
    Add following for oc4j_soa

    Change tmp directory to c:\temp

  • Start jconsole and use remote pid, with value localhost:9999
    Please note that JPS will still show "process information unavailable", but jconsole would work.

Tuesday, May 3, 2011

XRefUtility 10g

Recently had chance to work on XrefUtility. It is just designed for COMM PIPS to check on xref data anomaly, but it can be used for any XREF (as long as common column is named as COMMON). It generates multiple reports to show the different anomaly in Xref database. So basically it provides two distinct and valuable features:
- Generates multiple reports for Xref anomaly.
- Provides an interface to fix them

Installation: You can download the XRefUtility from Oracle Support 9326510. The installation note is included but I faced multiple jar files issues during installation. Nothing major, after adding those missing jars or changing path installation goes smooth.

It basically installs FixXref Bpel process and web application called XrefUtility. The FixXref should be installed in default domain. I tried to install in different domain which worked fine during install, but during run time it expected to have it in default domain.

Usage: usage is well documented in the guide but for my own purpose, I created my own XREF table and added my own data to create all possible scenario where Xref can have issues.

I created source table, target table and Xref called Source_Target_Xref. I also created meta data Xref which is not required for the XrefUtility but definitely required if you planning to use FixXRef.

integration/esb/bin/ -shell

User screen to configure Xref is quite simple as show below:

Configure AIA Database and Xref

Configure Source

Configure Target

It can generate multiple reports. As mentioned earlier, I created all possible combination for Xref anomaly, and as mentioned below, reports were able to catch pretty much all of them. Following diagram shows which report causes which type of anomaly.


Along with report it also generates text file which can be used for input to FixXref interface. I did found some issue with Fix Xref but mostly tried to avoid as it is better to fix the code or process which causes such anomaly. Probably, I will blog about FixXref in future.