Oracle CAMM with 10.1.3.5 SOA

We recently upgraded to 10.1.3.5 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 10.1.3.5 SOA:
- apply patch 9534485 (this is the latest 10.2.0.5.1 release of CAMM)
- apply patch 11939423 (change in one of the jar to read bpel descriptor)

Once it is done, just use 10.1.3.4 resource configuration to configure your 10.1.3.5 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, a.name 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 '0.0.0.1 '||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
  WHERE XREF_TABLE_NAME like 'INSTALLEDPRODUCT_ID' group by row_number, XREF_TABLE_NAME
  
  

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: -Dcom.sun.management.jmxremote=true
  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: http://bugs.sun.com/bugdatabase/view_bug.do?bug_id=6332311
  
  
• 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
  -Dcom.sun.management.jmxremote=true -Dcom.sun.management.jmxremote.port=9999 -Dcom.sun.management.jmxremote.authenticate=false -Dcom.sun.management.jmxremote.ssl=false
  
  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.
  
  

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/xreftool.sh -shell
deleteTable SOURCE_TARGET_XREF
createTable SOURCE_TARGET_XREF
addColumns SOURCE_TARGET_XREF SOURCE
addColumns SOURCE_TARGET_XREF TARGET
addColumns SOURCE_TARGET_XREF COMMON
listColumns SOURCE_TARGET_XREF

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.

Reports

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.