BPEL Automatic Recovery

A customer of mine had a requirement on automating message recovery in 10.1.3.4 BPEL. While researching I came accross these two sites which talk about 10.1.3.4 and 10.1.3.5 versions of SOA Suite.
Antony Renold's blog and Oracle Documentation.  In prior versions one had to write a small script and have a scheduler run the script.

10g Oracle ESB Deployment error

While re-deploying a 10g Oracle ESB project we ran into this issue

[script] <deploymentSummary>
   [script]    <message>Entity Deployment Failed</message>
   [script]    <errors>
   [script]       <error code="1072" severity="5">
   [script]          <description>A Service or ServiceGroup of name "SalesQuoteEBS" already exists under parent "SalesQuote".</description>
   [script]          <fix>Retry after providing different name or choosing another parent.  If error is not fixable, contact Oracle Support.</fix>
   [script]       </error>
   [script]    </errors>
   [script] </deploymentSummary> 

From the ESB DT logs at 

$ORACLE_HOME/j2ee/OC4J_ESBDT/log/OC4J_ESBDT_ESBDT_GROUP_1/oc4j/log.xml we saw

    <SUPPL_DETAIL><![CDATA[oracle.tip.esb.console.exception.ConsoleException: A Service or ServiceGroup of 

name "SalesQuoteEBS" already exists under parent "SalesQuote".
Retry after providing different name or choosing another parent.  If error is not fixable, contact Oracle Support.
        at oracle.tip.esb.console.util.ServiceUtil.checkServiceExists(ServiceUtil.java:1475)
        at oracle.tip.esb.console.XMLConsoleManagerImpl.createServiceEntity(XMLConsoleManagerImpl.java:1271)
        at oracle.tip.esb.console.XMLConsoleManagerImpl.createServiceFromElement(XMLConsoleManagerImpl.java:667)
        at oracle.tip.esb.configuration.deployment.JDevDeploymentManager.createEntity(JDevDeploymentManager.java:716)
        at oracle.tip.esb.configuration.deployment.JDevDeploymentManager.createServices(JDevDeploymentManager.java:700)
        at oracle.tip.esb.configuration.deployment.JDevDeploymentManager.deploy(JDevDeploymentManager.java:191)
        at oracle.tip.esb.configuration.deployment.DeploymentServlet.doPost(DeploymentServlet.java:120)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:763)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
        at com.evermind.server.http.ResourceFilterChain.doFilter(ResourceFilterChain.java:64)
        at oracle.security.jazn.oc4j.JAZNFilter$1.run(JAZNFilter.java:400)
        at java.security.AccessController.doPrivileged(Native Method)
        at javax.security.auth.Subject.doAsPrivileged(Subject.java:517)
        at oracle.security.jazn.oc4j.JAZNFilter.doFilter(JAZNFilter.java:414)
        at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:623)
        at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:370)
        at com.evermind.server.http.HttpRequestHandler.doProcessRequest(HttpRequestHandler.java:871)
        at com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:453)
        at com.evermind.server.http.AJPRequestHandler.run(AJPRequestHandler.java:313)
        at com.evermind.server.http.AJPRequestHandler.run(AJPRequestHandler.java:199)
        at oracle.oc4j.network.ServerSocketReadHandler$SafeRunnable.run(ServerSocketReadHandler.java:260)
        at oracle.oc4j.network.ServerSocketAcceptHandler.procClientSocket(ServerSocketAcceptHandler.java:234)
        at oracle.oc4j.network.ServerSocketAcceptHandler.access$700(ServerSocketAcceptHandler.java:29)
        at oracle.oc4j.network.ServerSocketAcceptHandler$AcceptHandlerHorse.run(ServerSocketAcceptHandler.java:879)
        at com.evermind.util.ReleasableResourcePooledExecutor$MyWorker.run(ReleasableResourcePooledExecutor.java:303)
        at java.lang.Thread.run(Thread.java:595)
]]></SUPPL_DETAIL>
 

Digging deeper we saw that it was an issue with the Deployer class. It was trying

to create a service and caught EntityAlreadyExistException and tried to update the

service.  In this case the service with the guid does not exist while there is a 

service with the same name under the same parent but with a different guid.

Workaround

This is a bug and till it gets fixed, the easiest way to avoid running into this 

error is to ensure the service has the same guid in all environments.  

This way the deployer catches EntityAlreadyExistException and does an update 

for re-deployment usecases.

Unable to login to SOA Dehydration store Database

Ran into a weird issue with our SOA Dehydration Store.  Saw the following the logs

Cannot fetch a datasource connection.

The process domain was unable to establish a connection with the datasource with the connection URL "jdbc/BPELServerDataSourceWorkflow". The exception reported is: ORA-00604: error occurred at recursive SQL level 1

ORA-01653: unable to extend table SYS.AUD$ by 1024 in tablespace SYSTEM

ORA-02002: error while writing to audit trail

ORA-00604: error occurred at recursive SQL level 1

ORA-01653: unable to extend table SYS.AUD$ by 1024 in tablespace SYSTEM

When we tried to login to the dehydration store database using the standard user orabpel we got the same error.  We then asked the DBA to clean the SYS.AUD$ table that fixed the issue for us. Verified the tablespace and found that it came down by 30% in this case.  So was wondering how to manage these tables so that we have uninterrupted running of our SOA Suite server when I tumbled on this blog post Why is my SYSTEM Tablespace so Big?

ESB Instances not showing up on the console

Noticed that in 10.1.3.4 MLR#10 Oracle SOA Suite ESB instances were not erroring out nor were they invoking the BPEL processes downstream. The logs (opmn and log.xml) had nothing worth mentioning.  Downstream BPEL process was not being invoked.  It looked like the ESB RTs were not working the way they should be.  Looking at the ESB Dehydration store, saw that the tablespace used by user oraesb was full.  Clearing the tablespace (as this was a test instance) got the ESB RTs going. 


Found the following SQL useful in analyzing the tablespace - found this long time back, unknown author.
select d.status,
  db.name dbname,
  d.tablespace_name tsname,
  d.extent_management,
  d.allocation_type,
  to_char(nvl(d.min_extlen / 1024, 0),
    '99G999G990D90', 'NLS_NUMERIC_CHARACTERS = '',.'' ')
    "ALLOC_SIZE (K)",
  d.contents "Type",
case
when(d.contents = 'TEMPORARY') then
  to_char(nvl(a.bytes / 1024 / 1024, 0),
  '99G999G990D90', 'NLS_NUMERIC_CHARACTERS = '',.'' ')
else
  to_char(nvl(t.bytes / 1024 / 1024, 0),
  '99G999G990D90', 'NLS_NUMERIC_CHARACTERS = '',.'' ')
end as "Size (M)",
  to_char(nvl((a.bytes - nvl(f.bytes, 0)) / 1024 / 1024,   0),
  '99G999G990D90', 'NLS_NUMERIC_CHARACTERS = '',.'' ')
  "Used (M)",
  to_char(nvl((a.bytes - nvl(f.bytes, 0)) / a.bytes * 100,   0),
  '990D90', 'NLS_NUMERIC_CHARACTERS = '',.'' ')
  "Used (%)"
from sys.dba_tablespaces d,
    (select tablespace_name,
     sum(bytes) bytes
   from dba_data_files
   group by tablespace_name)
a,
    (select tablespace_name,
     sum(bytes) bytes
   from dba_temp_files
   group by tablespace_name)
t,
    (select tablespace_name,
     sum(bytes) bytes
   from dba_free_space
   group by tablespace_name)
f,
  v$database db
where d.tablespace_name = a.tablespace_name(+)
 and d.tablespace_name = f.tablespace_name(+)
 and d.tablespace_name = t.tablespace_name(+)
order by 10 desc;

Used the following script to purge all instances from ESB Dehydrations store.

DECLARE
 po dbms_aqadm.aq$_purge_options_t;
BEGIN
--before starting clean up temp tables
EXECUTE IMMEDIATE 'TRUNCATE TABLE temp_ACTIVITY';
EXECUTE IMMEDIATE 'TRUNCATE TABLE ESB_TRACKING_FIELD_VALUE';
EXECUTE IMMEDIATE 'TRUNCATE TABLE ESB_FAULTED_INSTANCE';
EXECUTE IMMEDIATE 'TRUNCATE TABLE ESB_TRANSACTION_STATUS';
EXECUTE IMMEDIATE 'TRUNCATE TABLE ESB_INSTANCE_RELATION_XML';
EXECUTE IMMEDIATE 'TRUNCATE TABLE ESB_SERVICE_RELATION';
EXECUTE IMMEDIATE 'TRUNCATE TABLE ESB_RELATION_XML';
EXECUTE IMMEDIATE 'TRUNCATE TABLE ESB_ACTIVITY';

--before purging the aq topics
 po.block := FALSE;
 DBMS_AQADM.PURGE_QUEUE_TABLE(
   queue_table => 'ESB_ERROR_RETRY',
   purge_condition => NULL,
   purge_options => po);

 DBMS_AQADM.PURGE_QUEUE_TABLE(
   queue_table => 'ESB_ERROR',
   purge_condition => NULL,
   purge_options => po);

 DBMS_AQADM.PURGE_QUEUE_TABLE(
   queue_table => 'ESB_JAVA_DEFERRED',
   purge_condition => NULL,
   purge_options => po);

 DBMS_AQADM.PURGE_QUEUE_TABLE(
   queue_table => 'ESB_MONITOR',
   purge_condition => NULL,
   purge_options => po);

 DBMS_AQADM.PURGE_QUEUE_TABLE(
   queue_table => 'ESB_CONTROL',
   purge_condition => NULL,
   purge_options => po);
END;
/
exit;

/

 
 

Timezone Adjustment in Oracle SOA 10g

Using XPath 2.0  function

fn:adjust-dateTime-to-timezone()

it becomes easy to move the incoming dateTime from one timezone to another timezone.


Since Oracle SOA suite 10g does not support XPath 2.0 one of  the ways to achieve this is by string manipulation and formatting the result.  Another option is to write a custom function.


The inbound date is in PDT and we would like to convert this to UTC.  The XSL looks like