SOA 11g - Useful SOA Infra SQL queries for Oracle SOA Production Support - Part III ( Get SOA Payload from Back end tables ( SOA Infra) )

This post will be helpful when there is a need to reprocess high number of instance due to any server outage or data source outage.

Query to retrieve the payload from back end tables:

select Document from (select Document From Xml_Document Where 1 = 1 and Document_Id in (select document_id from document_ci_ref where cikey in (Select B.Cikey From Composite_Instance A, Cube_Instance B Where A.Ecid = B.Ecid And Nvl(A.Conversation_Id,'A')  = Nvl(B.Conversation_Id,'A') And Id = #Composite Instance ID)) order by doc_partition_date) a where rownum <=1

Write a Java program that automates the reprocessing job :- 

• Connect to SOA infra database
• Retrive the payload using above query
• Convert the blob to string
• Add SOA envelope to the retrived payload
• Generate the end point url using the values from cube instance table for this particular composite
• use HTTP post to invoke the composite

Above steps is just a way to automate the reprocessing, you can use this query and automate in any other possible way as well. 

Code snippet to convert the blob to string...

                                    Blob blob = payloadDetail.getBLOB("DOCUMENT");

                                    BinXMLStream inpbin = proc.createBinXMLStream(blob);

                                    BinXMLDecoder dec = inpbin.getDecoder();

                                    InfosetReader xmlreader = dec.getReader();

                                    XMLDocument doc = (XMLDocument)domimpl.createDocument(xmlreader);

                                    //doc.print(System.out);

                                    TransformerFactory tf = TransformerFactory.newInstance();

                                    Transformer transformer;

                                    transformer = tf.newTransformer();

                                    

                                    //Convert Xml to String                       

                                    StringWriter writer = new StringWriter();

                                    transformer.transform(new DOMSource(doc), new StreamResult(writer));

                                    String output = writer.getBuffer().toString();

Use this and write your process accordingly...

SOA 11g - Useful SOA Infra SQL queries for Oracle SOA Production Support - Part II ( Queries and its usage)

Query to find the count of instances for individual composites :

SELECT count(*),SUBSTR(COMPOSITE_DN,INSTR(COMPOSITE_DN,'/')+1,INSTR(SUBSTR(COMPOSITE_DN,INSTR(COMPOSITE_DN,'/')+1),'!')-1)
FROM COMPOSITE_INSTANCE WHERE
CREATED_TIME > TRUNC(SYSDATE)-7 AND CREATED_TIME < TRUNC(SYSDATE)
GROUP BY SUBSTR(COMPOSITE_DN,INSTR(COMPOSITE_DN,'/')+1,INSTR(SUBSTR(COMPOSITE_DN,INSTR(COMPOSITE_DN,'/')+1),'!')-1);

Normally composite_dn will be like XX_Test/HelloWorld!1.0*soa_36243828-42da-4952-9579-f58c0e913705, so to overcome that, we can use below code to get the composite name alone.

"SUBSTR(COMPOSITE_DN,INSTR(COMPOSITE_DN,'/')+1,INSTR(SUBSTR(COMPOSITE_DN,INSTR(COMPOSITE_DN,'/')+1),'!')-1)"

This query is used to get the instance count of all composites, all states between timestamps. 
Filters can be added and modified to get the results as required. 

How to find the instance ID if we have composite sensors in our composites:

SELECT Ci.*,
  number_value Delivery_ID
FROM composite_sensor_value csv,
  (SELECT *
  FROM COMPOSITE_INSTANCE
  WHERE CREATED_TIME > TRUNC(SYSDATE)-7
  AND CREATED_TIME  < TRUNC(SYSDATE)
  AND SUBSTR(COMPOSITE_DN,INSTR(COMPOSITE_DN,'/')+1,INSTR(SUBSTR(COMPOSITE_DN,INSTR(COMPOSITE_DN,'/')+1),'!')-1) IN ('%Composite_Name%')
    --AND STATE <> '1'
  )CI
WHERE CSV.COMPOSITE_INSTANCE_ID = CI.ID
AND SENSOR_NAME                 = '%Sensor_Name%'
AND NUMBER_VALUE                = '%Sensor_Values%'
ORDER BY CREATED_TIME;
Add or modify filters to get the results as expected. 

If you are not sure about the type of sensor, use this substr(clob_value, instr(clob_value,'>',1,1)+1, instr(clob_value,'</',1,1)-1) to filter or select the values. 

if you want to get the details for particular state, then read this post to understand the state and description.

Query to get the count of instance on hourly basis:

SELECT TO_CHAR(TRUNC(ci.CREATED_TIME, 'HH'), 'DD-MON-YYYY HH24:MI:SS'), COUNT (*) FROM COMPOSITE_INSTANCE CI
WHERE CREATED_TIME > TO_DATE('19-JUN-2014 08:35:10', 'DD-MON-YYYY HH24:MI:SS') AND
CREATED_TIME < sysdate
GROUP BY TO_CHAR (TRUNC (CI.CREATED_TIME, 'HH'), 'DD-MON-YYYY HH24:MI:SS')
ORDER BY TO_CHAR (TRUNC (CI.CREATED_TIME, 'HH'), 'DD-MON-YYYY HH24:MI:SS');

Modify the filter created_time accordingly to get the expected results. 
You can add composite_dn filter to get the hourly count of instances for one particular composite. 

Query to get the count of instances on different state:

SELECT COUNT(*),
  DECODE(cube_instance.STATE, 0, 'Initiated', 1, 'Running', 2, 'Suspended', 3, 'Faulted', 4, 'Closed Pending', 5, 'Closed Completed', 6, 'Closed Faulted', 7, 'Closed Cancelled', 8, 'Closed Aborted', 9, 'Closed Stale', 10,'Closed Rolled Back','unknown') state
FROM CUBE_INSTANCE
WHERE creation_date > TRUNC(sysdate)-1
AND creation_date   < sysdate
GROUP BY STATE;

If you want to get the same details on composite wise,

SELECT COUNT(*),
  DECODE(CUBE_INSTANCE.STATE, 0, 'Initiated', 1, 'Running', 2, 'Suspended', 3, 'Faulted', 4, 'Closed Pending', 5, 'Closed Completed', 6, 'Closed Faulted', 7, 'Closed Cancelled', 8, 'Closed Aborted', 9, 'Closed Stale', 10,'Closed Rolled Back','unknown') STATE ,
  COMPOSITE_NAME
FROM CUBE_INSTANCE
WHERE CREATION_DATE > TRUNC(sysdate)-1
AND CREATION_DATE   < SYSDATE
GROUP BY STATE,
  COMPOSITE_NAME
ORDER BY COMPOSITE_NAME;

Above are the major tables and possible joins, so we can rewrite as per our requirement and get the expected results quickly. 

Caution - From my experience, while running queries in Production SOA infra schema, make sure you restrict the number of records to search by adding date filter else it may thorow temp table space error. 

SOA 11g - Useful SOA Infra SQL queries for Oracle SOA Production Support - Part I ( List of tables and it states)

Those who handle Oracle SOA support will know how difficult to locate an instance using composite sensors in Enterprise Manager Console. This will eat our time and patience :)

But this can be avoided by using SOA infra tables. 

We all know that the transactions are available in SOA Infra tables across multiple tables and if we leverage it with proper joins, then life of support person will be easy. 

Below mentioned tables are the important ones that will be used to locate the instances.

CUBE_INSTANCE
COMPOSITE_INSTANCE
COMPOSITE_SENSOR_VALUE

Composite Instance :

Below are the important columns in composite instance tables. 

ECID: Unique values for one end to end transactions across several tables.
ID: Instance ID that is visible in Enterprise Manager Console
PARENT_ID: This will be null for initiator composite and child composite will have value of parent composite 
COMPOSITE_DN: Composite name along with partition name
STATE: State of instance like completed, running etc which is explained in details                           
CREATED_TIME: Creation time of an instance

List of states and its description:            

State	Description
0	Running
1	Completed
2	Running with faults
3	Completed with faults
4	Running with recovery required
5	Completed with recovery required
6	Running with faults and recovery required
7	Completed with faults and recovery required
8	Running with suspended
9	Completed with suspended
10	Running with faults and suspended
11	Completed with faults and suspended
12	Running with recovery required and suspended
13	Completed with recovery required and suspended
14	Running with faults, recovery required, and suspended
15	Completed with faults, recovery required, and suspended
16	Running with terminated
17	Completed with terminated
18	Running with faults and terminated
19	Completed with faults and terminated
20	Running with recovery required and terminated
21	Completed with recovery required and terminated
22	Running with faults, recovery required, and terminated
23	Completed with faults, recovery required, and terminated
24	Running with suspended and terminated
25	Completed with suspended and terminated
26	Running with faulted, suspended, and terminated
27	Completed with faulted, suspended, and terminated
28	Running with recovery required, suspended, and terminated
29	Completed with recovery required, suspended, and terminated
30	Running with faulted, recovery required, suspended, and terminated
31	Completed with faulted, recovery required, suspended, and terminated
32	Unknown
64	-

Any value in the range of 32 to 63 indicates that the composite instance state has not been enabled, but the instance state is updated for faults, aborts, etc.

Cube Instance:

CIKEY: Unique value for a instance  
CREATION_DATE: Creation date           
STATE: State of Instance                   
STATUS: Status of Instance        
ECID: Unique hexa decimal id for end to end transaction  
CMPST_ID: Instance ID that is visible in Enterprise Manager console or Value of ID column in Composite Instance table  
COMPOSITE_NAME: Name of composite  
DOMAIN_NAME: Partition Name  
COMPONENT_NAME: Name of component       
COMPOSITE_REVISION: Revision Number of composites   

CUBE_INSTANCE States

State	Description
0	STATE_INITIATED
1	STATE_OPEN_RUNNING
2	STATE_OPEN_SUSPENDED
3	STATE_OPEN_FAULTED
4	STATE_CLOSED_PENDING_CANCEL
5	STATE_CLOSED_COMPLETED
6	STATE_CLOSED_FAULTED
7	STATE_CLOSED_CANCELLED
8	STATE_CLOSED_ABORTED
9	STATE_CLOSED_STALE
10	STATE_CLOSED_ROLLED_BACK

Composite Sensor Values:

COMPOSITE_INSTANCE_ID: Instance ID that is visible in EM console or Value of ID in composite_instance table

COMPONENT_NAME: Name of the component          

SENSOR_NAME: Name of the sensors                    

CPST_PARTITION_DATE: Creation date 

STRING_VALUE: Sensor value in String datatype        

NUMBER_VALUE: Sensor value in Number datatype         

DATE_VALUE: Sensor value in Date datatype           

CLOB_VALUE: Sensor value in Clob datatype           

BLOB_VALUE: Sensor value in Blob datatype           

In following post, we shall see different queries that we use day by day for support purpose. 

Logstash for Weblogic - Part IV - Working across clustered environments

This is where logstash act as a real Champ..!!!

Logstash can be installed in any servers where it act as shipper and all these logs are accumulated in single place and can be used for real time monitoring. 

For this, additional components like redis, elsatic search are used. 

Logstash acts as a log collector, processor, shippers and indexer. 

It needs to be installed and running in servers where it will read the logs as events and as per the conf file configurations, here logstash acts as log collector and shipper. 

And in main server, it will act as indexer and processor. 

Refer previous post for more details. Post1, Post2, Post3;

Logstash has Kibana which is a dashboard for data visualization. 

Redis acts as a broker and receives the captured events from other servers ( Logstash that is acting as shipper) to main server. 

Elastic search acts as a persistent storage & search medium for all captured events

Below flow chart is self-explanatory. 

Now I shall explain steps to configure it across the servers.

For easy understanding, we have SOA server1 and SOA server2 and SOA Server1 will act as main server here and logs will be captured as event and shipped to SOA Server1 from SOA server2. So we can have as many as servers. 

Redis and Elastic search needs to be downloaded and running before any shipper starts shipping the events.  So as per our work case, we need to install it in SOA Server1.

Redis: 

•       Download redis and extract the source.
•       Execute the redis : src/redis-server --loglevel verbose 

You will get output as mentioned in below picture.

Elastic Search:

• Download the latest version of elastic search.
• Execute the command : bin/elasticsearch –f

Logstash as Shipper:

Run the logstash as shipper now in SOA Server2.

In config, make sure that output is directed to redis that is running in Server 1.

Like this,

Save this as say, shipper_server2.conf and run the logstash using below command.

java -jar logstash-1.2.0-flatjar.jar agent -f shipper_server2.conf

Now check your redis output, clients connected value will be 2 which indicated that shipper is successfully connected to Broker.

Now all the events captured will be stored in elastic search.

Logstash as Indexer:

To run logstash as indexer, make sure that input is read from redis.

List details mentioned in shipper output will be used here in input. 

Conf file will look like below,

  

And save this as indexer.conf and run using below command,

java -jar logstash-1.2.0-flatjar.jar agent -f indexer.conf

Kibana:

Use individual terminal to run kibana using below command,

java -jar logstash-1.2.0-flatjar.jar – web

Open kibana using this URL – http://localhost:9292

Logstash is so modular that all the above components can be installed on dedicated servers to distribute the load and specify the host accordingly.