11g Mediator – Diagnosing Resequencer Issues

In a previous blog post, we saw a few useful tips to help us quickly monitor the health of resequencer components in a soa system at runtime. In this blog post, let us explore some tips to diagnose mediator resequencer issues. During the diagnosis we will also learn some key points to consider for Integration […]

Mediator Parallel Routing Rules

Introduction In 11g, mediator executes routing rules either sequentially or in parallel.  If you are planning to use parallel routing rules, you would need to understand how the mediator queues and evaluates routings in parallel in different threads. This article describes 2 different threads used in the parallel routing rules and the design consideration if […]

White Paper on Message Sequencing Patterns using Oracle Mediator Resequencer

One of the consequences of Asynchronous SOA-based integration patterns is that it does not guarantee that messages will reach their destination in the same sequence as initiated at the source. Ever faced an integration scenario where – an update order is processed in the integration layer before the create order? – the target system cannot […]

Resequencer Health Check

11g Resequencer Health Check In this Blog we will see a few useful queries to monitor and diagnose the health of resequencer components running in a typical SOA/AIA Environment. The first query is a snapshot of the current count of Resequencer messages in their various states and group_statuses. Query1: Check current health of resequencers select […]

Mediator Instance Tracking

 

Mediator supports three modes for instance tracking by changing the audit level in EM->SOA->SOA-INFRA->SOA Administration->Mediator Properties:

 

  1. Off – No instance tracking for successfully completed instance, however, instances and faulted instances are created even in this mode.  Audit trail will not be created with this flag.
  2. Production – Instance tracking is enabled for all.  All audit details are logged, except the details of assign activities, but the instances and payloads are not captured.
  3. Development – Instance tracking is enabled for all.  All audit details are logged, and the instances and payloads are also captured.

 

The following tables are used by Mediator to store the instance and audit trail data:

 

  1. MEDIATOR_INSTANCE – This table contains one row for each mediator instance. Each instance has a unique id. It stores ecid, composite instance id and parent component id from normalized message and overall state of an instance in the component_state column.  The component state depends on the combination of the mediator case instance states, the states are listed here.
  2. MEDIATOR_CASE_INSTANCE – This table contains one row for each mediator routing rule and fault information for a routing rule is also stored.  Each case instance has one unique id.  It stores mediator instance id and case name, related fault information and information pertaining to retries.  This is the base table for executing automatic retries using fault policies.
  3. MEDIATOR_CASE_DETAIL – This table contains multiple rows for each routing rule and stores mediator audit trail xml as a blob for each routing rule. Each case detail rows are bound together by case id.  It stores case detail state, audit trail for each case detail.  The state of the latest case detail is the current state of the case.
  4. MEDIATOR_AUDIT_DOCUMENT- This table stores payload at each stage of mediator message flow and payloads are stored only when instance tracking audit level is set to “Development”. Each row in this table stores the payload at a point in the message flow. e,g, transformed payload, payload being sent to the target service.

Below is a screenshot of a basic mediator project with 2 routing rules which polls an xml file from an input folder, transforms the content and writes the xml file to a folder. 

When the mediator receives a massage, it creates a mediator instance, and then depending on the number of routing rule, one or more case instance will be created in the MEDIATOR CASE INSTANCE table. The engine will then initializes the audit trail xml and stores it as an XML document. After each processing point (e.g. transformation, filter evaluation etc), it stores the trail messages to audit trail xml and persists to audit trail table (MEDIATOR_CASE_DETAIL.AUDIT_TRAIL  and/or MEDIATOR_AUDIT_DOCUMENT), then the mediator instance state will be updated.

1. When the mediator instance kicks off, a composite instance will be created in the COMPOSITE_INSTANCE table, and unique ECID will be assigned to the instance.

 

select * from composite_instance where ecid=’1b7e5955c26b51de:-56440391:13d41f410c6:-8000-000000000000144b’

2. Using ECID, you can retrieve the mediator instance data and the component state from the mediator instance table.  From this point onward, MEDIATOR_INSTANCE.ID will be used to retrieve the mediator case data.

select * from mediator_instance where ecid=’1b7e5955c26b51de:-56440391:13d41f410c6:-8000-000000000000144b’

 3. Depending on the number of routing rules, the mediator will store each routing rule separately in the MEDIATOR_CASE_INSTANCE table and the MEDIATOR_CASE_INSTANCE .ID will be used to retrieve the case detail for each routing rule.  In the above example, there are 2 routing rules.

select * from mediator_case_instance where instance_id = ‘C64B82E086BB11E2BFBE1B53FB1929E1’;

4. The audit trail of each routing rule is stored in the MEDIATOR_CASE_DETAIL table in compressed format.

select * from mediator_case_detail where instance_id = ‘C64B82E086BB11E2BFBE1B53FB1929E1’;

 

Below are the xml data that are stored in the MEDIATOR_CASE_DETAIL.AUDIT_TRAIL column.  In the example below, two routing rules were being executed. The first event routing rule’s result was equal to “false”, then the second routing rule was executed. The second event routing rule’s result was successful, subsequently the message was transformed and published to the destination. If you have the audit trail level set to “Development”, you can use the audit id in the case trail to retrieve the payload from the MEDIATOR_AUDIT_DOCUMENT table for further investigation.

CASE=ID= C64BA9F086BB11E2BFBE1B53FB1929E1

<case_trail>

  <event type=”inputPayloadReceived” status=”Completed”

         parentId=”C64B82E086BB11E2BFBE1B53FB1929E1″ date=”1362615182063″

         auditId=“C64BA9F086BB11E2BFBE1B53FB1929E1“>

    <message>MediatorAudit_29</message>

  </event>

</case_trail>

CASE_ID= C66EE96086BB11E2BFBE1B53FB1929E1

<case_trail>

  <event type=”case” id=”C66EE96086BB11E2BFBE1B53FB1929E1

         parentId=”C64B82E086BB11E2BFBE1B53FB1929E1″ caseName=”USCustomer.Write”

         date=”1362615182073″ auditId=”C64BA9F086BB11E2BFBE1B53FB1929E1″>

    <message>MediatorAudit_0#USCustomer.Write</message>

  </event>

  <event type=”condition” status=”Completed”

         parentId=”C66EE96086BB11E2BFBE1B53FB1929E1″ date=”1362615182074″

         auditId=”C64BA9F086BB11E2BFBE1B53FB1929E1″>

    <message>MediatorAudit_1#false#$in.CustomerData/imp1:CustomerData/Country=’US'</message>

  </event>

</case_trail>

 

CASE _ID= C670700086BB11E2BFBE1B53FB1929E1

<case_trail>

  <event type=”case” id=”C670700086BB11E2BFBE1B53FB1929E1

         parentId=”C64B82E086BB11E2BFBE1B53FB1929E1″

         caseName=”CanadaCustomer.Write” date=”1362615182083″

         auditId=”C64BA9F086BB11E2BFBE1B53FB1929E1″>

    <message>MediatorAudit_0#CanadaCustomer.Write</message>

  </event>

  <event type=”condition” status=”Completed”

         parentId=”C670700086BB11E2BFBE1B53FB1929E1″ date=”1362615182083″

         auditId=”C64BA9F086BB11E2BFBE1B53FB1929E1″>

    <message>MediatorAudit_1#true#$in.CustomerData/imp1:CustomerData/Country=’CA'</message>

  </event>

  <event type=”transform” status=”Completed”

         parentId=”C670700086BB11E2BFBE1B53FB1929E1″ date=”1362615182102″

         auditId=”C67292E086BB11E2BFBE1B53FB1929E1″>

    <message>MediatorAudit_3#Customer#xsl/CustomerData_To_Customer_2.xsl</message>

  </event>

  <event type=”publish” status=”Completed”

         parentId=”C670700086BB11E2BFBE1B53FB1929E1″ date=”1362615182124″

         auditId=”C67292E086BB11E2BFBE1B53FB1929E1″ parentRefId=”mediator:C64B82E086BB11E2BFBE1B53FB1929E1:C670700086BB11E2BFBE1B53FB1929E1:oneway”>

    <message>MediatorAudit_9#Write#CanadaCustomer</message>

  </event>

</case_trail>

 

Event Delivery Network (EDN) – A practical example

Details of how Oracle Event Delivery Network is used may be found here

A lower level description of EDN can be found here.