OEG - how history repeats

As we know, Oracle recently released product called OEG (Oracle Enterprise Gateway) which is meant to replace OWSM gatweay from 10g stack. I was just comparing how it with my 10g eyes.

1. So entire 11g stack is deployed on weblogic and installation time you choose user, which is pretty much weblogic and your preferred password. OEG is separate installation, it doesn't ask for username and password during install. (It is actually admin/changeme, which you can find buried in some documents).

Ah, remind me about the same in 10g, whole stack will use oc4jadmin, but OWSM would have separate user "admin" and oracle chosen password "oracle".

2. OEG password is changeme, but when I login to OEG using http://localhost:8090/runtime/management/ManagementAgent, can not find a way change it. It was not very hard and later figured out it is in the Policy studio, as below:

I don't have to tell, how hard is to change "admin/oracle" password in 10g, or almost impossible to add new user in OWSM.

3. BPEL compatibility :
I remember (~2005ish during first OWSM release), when you register BPEL service to OWSM it won't work. You have to copy schema manually. Well, I could deal with it for a while and later patches fixed those problems.
With latest release of OEG (11.1.1.5.0), Java webservice registration went fine, but when I try to register BPEL composite (deployed on SOA 11.1.1.5.0), it didn't register at all. Of course no logs in any of the log file. Later realized that it only like WS which are compatible with WS-I standard (http://www.ws-i.org/). Upon downloading their tools, I figured out that 11g composites (at least exposed BPEL WS) are not WS-I standard composite.
I probably have to make it work using policy studio somehow, but it would be nice if gateway can accept my BPEL wsdl URL.

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.

AQ - queue is stopped

We started getting this error message in the log file all of sudden, and not sure how the dequeue for the queue got disabled.

[Linked-exception]
java.sql.SQLException: ORA-25226: dequeue failed, queue JMSUSER.AIA_CUSTOMERJMSQUEUE is not enabled for dequeue
ORA-06512: at "SYS.DBMS_AQIN", line 571
ORA-06512: at line 1

        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:138)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:316)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:282)
        at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:639)
        at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:184)
        at oracle.jdbc.driver.T4CCallableStatement.execute_for_rows(T4CCallableStatement.java:873)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1161)
        at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3001)
        at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3043)
        at oracle.jms.AQjmsConsumer.dequeue(AQjmsConsumer.java:1601)
        at oracle.jms.AQjmsConsumer.receiveFromAQ(AQjmsConsumer.java:916)
        at oracle.jms.AQjmsConsumer.receiveFromAQ(AQjmsConsumer.java:835)
        at oracle.jms.AQjmsConsumer.receive(AQjmsConsumer.java:776)
        at oracle.tip.adapter.jms.JMS.JMSMessageConsumer.consumeBlockingWithTimeout(JMSMessageConsumer.java:405)
        at oracle.tip.adapter.jms.inbound.JmsConsumer.run(JmsConsumer.java:330)
        at oracle.j2ee.connector.work.WorkWrapper.runTargetWork(WorkWrapper.java:242)
        at oracle.j2ee.connector.work.WorkWrapper.doWork(WorkWrapper.java:215)
        at oracle.j2ee.connector.work.WorkWrapper.run(WorkWrapper.java:190)
        at EDU.oswego.cs.dl.util.concurrent.PooledExecutor$Worker.run(PooledExecutor.java:830)
        at java.lang.Thread.run(Thread.java:595)

It was quite easy to fix it. Following query shows the queues which has dequeue or enqueue disabled:

select * from all_queues where trim(enqueue_enabled) = 'NO'

Once idetified which queue has issue, we can run following query to enabled the dequeue and enqueue for particular queue:

execute DBMS_AQADM.START_QUEUE(queue_name=> 'JMSUSER.AIA_CUSTOMERJMSQUEUE');

ESB 10g version agnostic routing rules - [default version]

In BPEL, it is quite easy that if we don't specify the version during partnerlink invocation, it will use the default one. But in ESB, especially when ESB is calling BPEL via native binding the version of the BPEL process is hard coded inside the routing rule. This was quite annoying because this pattern is heavily used in AIA and redeploying newer version of BPEL requires change in routing rule in ESB.

In 10.1.3.5, Oracle released a new feature which solves this problem. After 10.1.3.5 upgrade, if you deploy any BPEL process, it creates a separate version in BPELSystem called "default" which is visible in ESB Console as well as JDeveloper.

If we select default during routing rule, then the default version of the BPEL process would be called from ESB during native invocation. Therefore if we change the BPEL default version or redeploy BPEL process with higher version, it won't require change in ESB routing rules.