All About Oracle ESB

Oracle ESB is technically an 'enterprise service bus' designed and implemented in an Oracle Fusion Architecture's SOA environment; to simplify the interaction and communication between existing Oracle products, third-party applications, or any combination of these.

 

ESB Queues

Use these procedures with great care as corrupting the queues could cause the ESB to be irreparable and or loss of transactions!

select count(*) as CONTROL from aq$esb_control; (this queue is used to communicate between the design time and the run time for exampel to disable an adapter)

select count(*) as ERROR_COUNT from aq$esb_error; (this queue is used to signal errors)

select count(*) as ERROR_R from aq$esb_error_retry; (this queue is used to schedule retrial errors for retry attempts)

select count(*) as DEF from aq$esb_java_deferred; (this queue is used to provide guaranteed delivery for Asynchronous services as in yesterdays issue)

select count(*) as MON from aq$esb_monitor; (this queue is used to send audit/logging information from the run time to the design time which then writes it to the DB)

NOTES

It is important to understand that the deferred queue comes into play ONLY when an ESB service is defined as Asynchronous. For synchronous requests there is no queuing and failures are returned to the client. I believe the KT sessions cover which of our services are Asynchronous in depth.

We cannot check the transaction in process very easily as normally it would not be there long enough. Occasionally  there are exceptions where the transactions become dead-locked on the Application. This means the transactions stay in an open state indefinitely and it is possible to find the current ones by looking at the list of transactions in ESB console. Find the oldest that are in "gray" state and then look at the tracking field values for them..

ESB Control Topic

Due to a known issue with ESBRT forced shutdown ESB_CONTROL topic subscribers are not removed always cleanly. This leaves some zombie subscribers to the mutli-consumer ESB_CONTROL topic there by persisting messages for non-existent consumers. In such scenarios following procedure can be followed to clean-up ESB_CONTROL topic.

At any point there should be at most three subscribers to ESB_CONTROL topic. One for DT and one for each RT in the cluster. More than three subscribers means we are running into zombie subscriber scenario. Use the following SQL to verify number of subscribers;

SELECT COUNT(*) FROM AQ$ESB_CONTROL_S

If the above select returns more than 3 we can follow the below procedure to clean-up ESB_CONTROL topic;

1. Shutdown RT and DT instances on all nodes of ESB cluster

2. Login to ESB repository database as ORAESB user

3. Make sure ESB_CLEAR_TOPIC procedure exists. If not compile the below procedure to create one

create or replace procedure esb_clear_topic(pQueName varchar2, status varchar2) as

pOptions dbms_aqadm.aq$_purge_options_t;

begin

pOptions.block := true;

dbms_aqadm.purge_queue_table(

queue_table => pQueName,

purge_condition => 'MSG_STATE = ''' || status || '''',

purge_options => pOptions);

end;

4. Run the following script to remove zombie subscribers

declare cursor c1 is

select * from aq$esb_control_s

subscriber sys.aq$_agent;

begin

for rec in c1 loop

subscriber := sys.aq$_agent(rec.name, NULL, NULL);

DBMS_AQADM.remove_SUBSCRIBER(

queue_name => 'ESB_CONTROL',

subscriber => subscriber);

end loop;

esb_clear_topic('ESB_CONTROL', 'EXPIRED');

end; /

5. Restart DT and RT instances on all nodes in the cluster

ESB Monitor/Java Deferred/Error/Error Retry

Occasionally you may find messages in these topics in states other than 'READY' status like 'EXPIRED' or 'UNDELIVERABLE'. Use the following SQL to verify that;

SELECT MSG_STATE, COUNT(*) FROM AQ$ESB_MONITOR GROUP BY MSG_STATE

SELECT MSG_STATE, COUNT(*) FROM AQ$ESB_JAVA_DEFERRED GROUP BY MSG_STATE

SELECT MSG_STATE, COUNT(*) FROM AQ$ESB_ERROR GROUP BY MSG_STATE

SELECT MSG_STATE, COUNT(*) FROM AQ$ESB_ERROR_RETRY GROUP BY MSG_STATE

If there are messages in unwanted statuses use the following script to clean them (or enter into SQLDeveloper and do Run as script);

BEGIN

ESB_CLEAR('ESB_CONTROL', 'EXPIRED');

END;

This will do all;

BEGIN

ESB_CLEAR('ESB_JAVA_DEFERRED', 'EXPIRED');
ESB_CLEAR('ESB_JAVA_DEFERRED', 'PROCESSED');
ESB_CLEAR('ESB_JAVA_DEFERRED', 'READY');
ESB_CLEAR('ESB_CONTROL', 'EXPIRED');
ESB_CLEAR('ESB_CONTROL', 'PROCESSED');
ESB_CLEAR('ESB_CONTROL', 'READY');
ESB_CLEAR('ESB_MONITOR', 'EXPIRED');
ESB_CLEAR('ESB_MONITOR', 'PROCESSED');
ESB_CLEAR('ESB_MONITOR', 'READY');
ESB_CLEAR('ESB_ERROR', 'EXPIRED');
ESB_CLEAR('ESB_ERROR', 'PROCESSED');
ESB_CLEAR('ESB_ERROR', 'READY');
ESB_CLEAR('ESB_ERROR_RETRY', 'EXPIRED');
ESB_CLEAR('ESB_ERROR_RETRY', 'PROCESSED');
ESB_CLEAR('ESB_ERROR_RETRY', 'READY');

END;

Refresh ESB Topics

Over a period of time, ESB Topics may get corrupted for unknown reasons. After using all other options below is the procedure to recreate the ESB Topic underlying AQ queues. Note that this procedure is only applicable when ESB Topics are enabled for AQ persistence.

1. ready SOUP UI for test.

3. Stop the environment

opmnctl stopall

4. Access the environment database schema used for ESB (either oraesb or esb).

5. Make sure Environment stopped successfully and JAVA DEFERRED queue has no message in ready state.

1. opmnctl status
 

2. select consumer_name, msg_state, count(*) from aq$esb_java_deferred group by consumer_name, msg_state order by consumer_name, msg_state;

3. Carry out the following query:
select * from gv$active_services;

This should show a list of services as follows:

esb should appear at least twice with different values in the INST_ID column
DB1 should appear once with INST_ID = 1
DB2 should appear once with INST_ID = 2


If this is not the case please contact the DBA team to ensure the DTP services have been created correctly.

4. Access the script {RT_HOME}/integration/esb/sql/oracle/create_esb_topics.sql

Locate the following code and change to latest version :

dbms_aqadm.create_queue_table(Queue_table => qtablename,
Queue_payload_type => 'SYS.AQ$_JMS_TEXT_MESSAGE',
multiple_consumers => true,
primary_instance => 1,
secondary_instance=> 2,
compatible => '10.2');
dbms_aqadm.create_queue (Queue_name => qname,
Queue_table => qtablename);
dbms_aqadm.start_queue(qname);

5. From SQL prompt Run the modified script against the ESB schema for the environment.

6. When script completes run the below query against .

select queue_table, primary_instance, secondary_instance, compatible from all_queue_tables
where owner = '';

Confirm the values for primary_instance(1),secondary_instance(2) and compatible(10.0.0).


7. Bring up the environment, Run the regression test, it should went fine.

8. Regression test does not exercise AQ, run the provider part of Load Test and confirm Queues are working fine.

This procedure is destructive with respect to in-flight messages within ESB layer. So proper analysis is required before executing this option!


It is important to understand that the deferred queue comes into play ONLY when an ESB service is defined as Asynchronous. For synchronous requests there is no queuing and failures are returned to the client. I believe the KT sessions cover which of our services are Asynchronous in depth.

We cannot check the transaction in process very easily as normally it would not be there long enough. Occainsonnally there are exceptions where the transactions become dead-locked on the Application. This means the transactions stay in an open state indefinitely and it is possible to find the current ones by looking at the list of transactions in ESB console. Find the oldest that are in "gray" state and then look at the tracking field values for them. This is not normally possible and cannot be proceduralised.
Removing Messages From ESB Deferred Queue

Follow these steps to identify large message payload in ESB deferred queue and remove them if required.

1. Run this query to get the consumers, Message State and Count

select consumer_name, msg_state, count(*)
from aq$esb_java_deferred
group by consumer_name, msg_state;

2. Run this query to get list of messages by enqueue time for a specific consumer in a specific state

select a.queue, a.consumer_name, a.msg_state, a.enq_timestamp, a.retry_count, a.msg_id
from aq$esb_java_deferred a
where consumer_name = 'consumer from previous query' and msg_state = 'state from previous query'
order by enq_timestamp;

3. Run this query to get the size of a specific message in the queue

select a.queue, a.consumer_name, a.msg_state, a.enq_timestamp,
length(a.user_data.text_lob) msg_size, a.retry_count, a.msg_id
from aq$esb_java_deferred a
where msg_id = 'message id from previous query';

4. To remove the message in the queue for a specific subscriber first determine the message ID of the message in the queue from above mentioned queries and then call the function as in the following example:

DECLARE
po_t dbms_aqadm.aq$_purge_options_t;
BEGIN
po_t.block := false;
dbms_aqadm.purge_queue_table('ESB_JAVA_DEFERRED', 'msg_id=' ' message id from previous query ' ' ', po_t);
END;

Note:
  This action should NEVER be taken without full analysis of the issue and that this is the right course

Purging of WSM/ESB schemas
Applies to: Oracle Web Services Manager - Version: 10.1.3.1 to 10.1.3.4.0 Information in this document applies to any of platform.

The following options can be performed to shrink the database without altering the operation of WSM.
1. Message logs 2. Log objects. 3. Policy versions

we can truncate tables messagelogs and log_objects directly, there is zero impact.
we can purge the policy version from the WSM layer to shrink the database. for safe side you can take backup of owsm schema and oc4j_soa under oracle_home/j2ee/.

ESB DVM Update Procedure
Login to the specific ESB console.
Example: For UAT: https://uat02-test.com:7778/esb

Step 1. Enter oc4j username/password
Step 2. Click on Maps...
You will get all the domain value maps.
Step 3. Select required value on from center side panel.
Example: selected CountryCode.
Wait for a moment… You will get the all the CountryCode domain values on the right hand panel. Where you can search for specific code. like CN for China.
Step 4. For exporting the map click on Export
Click "OK" You will be asked to save the XML file, save it.
Step 5. Make the required corrections or get the updated file that Need be uploaded. Now you need to upload it back to ESB console. Be carful while uploading the file, file structure should not be modified.
Step 6. Click on Import 

Locate the XML file. Choose the "Import Options"
Step 7. Click on "OK"
Wait for a moment…
Click "Save".
Note that a restart of ESB is NOT required.
Crosscheck
Perform Step 3 Now you will be able to see the modified values or uploaded file’s contents on the right hand panel.
 

WSM/ESB Tables for purging.

TABLES:
ORAESB.ESB_TRACKING_FIELD_VALUE
ORAESB.ESB_ACTIVITY
ORAESB.ESB_FAULTED_INSTANCEORA

ORAWSM.MEASUREMENT_ALARM_STORE
ORAWSM.MESSAGELOGS
ORAWSM.LOG_OBJECTS

#JVM heap utilization check (works on solaris)
$JAVA_HOME/bin/jmap -heap $(/usr/ucb/ps -axwww | grep java |grep oc4j_wsm |awk '{print $1}')
 oc4j soa
$JAVA_HOME/bin/jmap -heap $(/usr/ucb/ps -axwww | grep java |grep oc4j_soa |awk '{print $1}')