Oracle 12c - AQ Adapter Enqueue and Dequeue

Advantages of using queuing:

• Oracle’s AQ (Advanced Queuing) is one of the extremely powerful Oracle Database message queuing based on Oracle Streams. This is scalable and reliable as weblogic’s JMS.
• This allows us to achieve de(or loose)-coupling and agility in real world. De-coupling is the measurement of dependency between the actual service provider and the service consumer(s). For a better function in SOA, this level of dependency should be low as possible. By Queing, the service consumer can publish the request & make them available for the provider which may or may not be available at when the request is published.
• AQ in-build Packages : There are two in-build packages which we need to know before we jump in

    1. Oracle AQ administration package (DBMS_AQADM) which helps us by providing AQ’s         administrative task like managing (create/delete/alter) queue table, privileged programs (grand/revoke), state of queue (start/stop), etc.
   2. Oracle AQ package (DBMS_AQ) which provides interface for enqueue and de-queue messages into the queue.

Lets start:

Step1: Create the needed db-user and provide the required grants.

    create user shop_keeper identified by shop_keeper;

    grant create session, resource, AQ_ADMINISTRATOR_ROLE, AQ_USER_ROLE to shop_keeper;

    grant execute on DBMS_AQADM to shop_keeper;

    grant execute on DBMS_AQ to shop_keeper;

    

Step2: Define the structure of message which is passing through the queue. 

Type is defined as Object (obviously it will have unique name) having multiple fields (defined using varchar, date, timeStamp,etc).

CREATE OR REPLACE TYPE ITEM_TYPE AS OBJECT (

ITEM_ID NUMBER(4),

ITEM_NAME VARCHAR2(30),

ITEM_PUBLISHER VARCHAR2(30)

);

desc ITEM_TYPE

Step3: Create a queue table for persisting the messages and its associated Queue

exec DBMS_AQADM.CREATE_QUEUE_TABLE(queue_table => 'SHOP_KEEPER.news_magazine_t', queue_payload_type => 'SHOP_KEEPER.ITEM_TYPE');

exec DBMS_AQADM.CREATE_QUEUE(queue_name => 'SHOP_KEEPER.news_magazine_q', queue_table => 'SHOP_KEEPER.news_magazine_t');

Step4: Start the queue using DBMS_AQADM package

exec SYS.DBMS_AQADM.START_QUEUE('SHOP_KEEPER.news_magazine_q');

Query to see the table and queue in DB:

select * from all_queues where name like '%NEWS_MAGAZINE_Q%'

Stumbled issue 1:

While executing below command, get the error

exec DBMS_AQADM.CREATE_QUEUE_TABLE(queue_table => 'SHOP_KEEPER.news_magazine_t', queue_payload_type => 'SHOP_KEEPER.ITEM_TYPE');

Error :

Error starting at line : 9 in command -

exec DBMS_AQADM.CREATE_QUEUE_TABLE(queue_table => 'SHOP_KEEPER.news_magazine_t', queue_payload_type => 'SHOP_KEEPER.ITEM_TYPE')

Error report -

ORA-01950: no privileges on tablespace 'USERS'

ORA-06512: at "SYS.DBMS_AQADM", line 115

ORA-06512: at line 1

01950. 00000 -  "no privileges on tablespace '%s'"

*Cause:    User does not have privileges to allocate an extent in the

           specified tablespace.

*Action:   Grant the user the appropriate system privileges or grant the user

           space resource on the tablespace.

Solution:

Just executed this command.

GRANT UNLIMITED TABLESPACE TO <user name>;

Step5: Create a Datasource in admin console.

Step6: Create an AQ  Connection Factory

Stumbled issue  2:

An error occurred during activation of changes, please see the log for details.

Message icon - Error java.lang.IllegalArgumentException: /app/Oracle/Middleware/Oracle_Home/soa/soa/Plan.xml (A file or directory in the path name does not exist.)

Message icon - Error /app/Oracle/Middleware/Oracle_Home/soa/soa/Plan.xml (A file or directory in the path name does not exist.)

Solution:

Move that plan.xml file from node 1 to node 2 and then activate the changes.

Step7: Create Enqueue adapter

XSD used:

<schema targetNamespace="http://xmlns.oracle.com/xdb/SHOP_KEEPER" xmlns="http://www.w3.org/2001/XMLSchema" xmlns:SHOP_KEEPER="http://xmlns.oracle.com/xdb/SHOP_KEEPER" elementFormDefault="unqualified" attributeFormDefault="qualified">

   <complexType name="ITEM_TYPE">

      <sequence>

         <element name="ITEM_ID" type="double" nillable="true" minOccurs="0"/>

         <element name="ITEM_NAME" nillable="true" minOccurs="0">

            <simpleType>

               <restriction base="string">

                  <maxLength value="30"/>

               </restriction>

            </simpleType>

         </element>

         <element name="ITEM_PUBLISHER" nillable="true" minOccurs="0">

            <simpleType>

               <restriction base="string">

                  <maxLength value="30"/>

               </restriction>

            </simpleType>

         </element>

      </sequence>

   </complexType>

   <element name="ITEM_TYPE" type="SHOP_KEEPER:ITEM_TYPE" />

</schema>

Step8: oneway BPEL invoke and transform to send data to AQ

Now Deploy and test.

During deploy we get below Metadata error for connection factory,

Stumbled issue 3:

There was an error deploying the composite on soa_server2: Operation failed - Member(Id=6 Timestamp=2018-12-04 23:44:26.071 Address=10.48.10.134:39640 MachineId=32196 Location=machine:testMachine process:13238290 member:soa_server1 Role=soa_cluster):Error while validating JCA Reference Binding meta data during composite deployment. : JCA deployment validation errors for 'Adapters/aqPublish_aq.jca' 

Solution:

Restart both the SOA servers.

Test From Em console:

Query to manually test/enqueuer the message in the Queue table:

DECLARE

  queue_option dbms_aq.enqueue_options_t;

  msg_properties dbms_aq.message_properties_t;

  payload item_type;

  msg_id VARCHAR(50);

BEGIN

  payload := item_type(1, 'The Times of India', 'The Times Group');

dbms_aq.ENQUEUE(queue_name => 'NEWS_MAGAZINE_Q',

enqueue_options => queue_option,

message_properties => msg_properties,

payload => payload

, msgid => msg_id);

--dbms_output.put_line('Message successfully Enqueued WITH messageId:'|| msg_id);

COMMIT;

END;

/

Step9: Dequeue from the AQ

Test and deploy:

12c SOA weblogic - General SSLEngine problem

Sometimes we stumbled with this General SSLEngine problem in the server while we are testing/transacting the interface data.

Error:
The invocation resulted in an error: General SSLEngine problem.

Solution: 
This is because one of the certificate of the external secure application has expired. Just import the latest certificate of the expired application into the weblogic server library and restart the affected manage servers one after one. It will work.

Click here ImportCert to get the steps to import the certficate in weblogic server.

12c SOA Weblogic - Certificate import steps

Certificate import steps:
1. Download the latest certificate of the invoking application URL from the browser and saved it. It will have a .cer DER/Binary file.

Steps to get the certificate opening the URL in Chrome:

Click on Lock symbol→Certificate(Valid)→go to Details Tab→Copy to file→Next→select DER encoded binary x.509(.CER)→Next→browse and select the folder where to save it with a apt name→Finish

Showing with the google's certificate:

2. Now convert it to .crt (PEM file). Click here CertificateConvert to convert the .cer to .crt file.

DER/Binary⇾Standard PEM

3. Get the cert in .crt format and place the file in weblogic server home/server/lib path

for e.g→ /app/Oracle/Middleware/Oracle_Home/wlserver/server/lib

4. Grant all the permissions

chmod 777 certficateOne.crt

5. Take the keystore backup as below:

cp CustomSOAKeysTrust.jks CustomSOAKeysTrust.jks_BK05092019

 6. Import certtificate in custom trust. Highlighted name need to be changed.

/usr/java8_64/bin/keytool -import -alias AliasCertName -trustcacerts -file yourCert.crt -keystore CustomSOAKeysTrust.jks -storepass CUSTOMPASSWORD

Note: /usr/java8_64/bin/ This is java bin path. provide the java bin path if it is not set.

7. List the certs to check if your cert is imported correctly.

/usr/java8_64/bin/keytool -list -keystore CustomSOAKeysTrust.jks -storepass CUSTOMPASSWORD

8. Do the same steps for server 2 (if it is clustered).

9. Restart the affected servers only one by one.

12c SOA - inMemoryOptimization

inMemoryOptimization

• This property indicates to Oracle BPEL Server that this process is a transient process and dehydration of the instance is not required. 
• When set to True, the completionPersistPolicy is used to determine persistence behavior. 
• This property can only be set to True for transient processes or processes that do not contain any dehydration points such as receive, wait, onMessage and onAlarm activities. 
• The inMemoryOptimization property is set at the BPEL component level. When set to True, dehydration is disabled which can improve performance in some use cases.

This property has the following values:
False (default): instances are persisted completely and recorded in the dehydration store database.
True: The completionPersist policy is used to determine persistence behavior.

completionPersistPolicy
This property configures how the instance data is saved. It can only be set at the BPEL component level. The completionPersistPolicy property can only be used when inMemoryOptimization is set to be True (transient processes).

Values:

• on (default): Completed instances are saved normally.
• deferred: All flow, audit, and state data is initially persisted to the Coherence cache. A separate write-behind thread performs a deferred write of the cache to the database. The write-behind thread wakes up at periodic intervals, the default being 5 minutes.
• Faulted: The flow trace, BPEL audit trace, and flow instance state data is not persisted for successful executions. If the flow encounters a fault, then all data is persisted to the database. Once the flow has been recovered, all flow data is purged. If a component reaches dehydration point, then the state data is persisted to the Coherence cache.
• Off: No instances (and their data) are saved.
• Immediate : The flow trace, BPEL audit trace, and flow instance state data is always persisted to the database.

Impact:

• This property can greatly impact the database growth.
• It can also impact the throughput (due to reduced I/O)

Syntax to add in composite.xml
<component name="BPELProcess">
 <implementation.bpel src="BPELProcess.bpel" />
 <property name="bpel.config.completionPersistPolicy">faulted</property>
 <property name="bpel.config.inMemoryOptimization">true</property>
   ...
</component>

Steps to Enable In-Memory SOA: 
 Set the inMemoryEnvironment property to true from em console. Navigate to SOA-Infra->SOA Administration->Common Properties->More SOA Infra advanced property.

Designing Your Business Process to Run In-Memory:

Creating a deferred Hello World process.

Deploy and test.

You can see the Audit information are not available.

Now change the completionPersistPolicy to immediate in composite.xml and test again and observed its showing the audit information fine now.

11g to 12c OSB projects migration points

1. Export 11g OSB code and import in 12c Jdeveloper.
Steps to import OSB project in Jdeveloper:
  File⇾Import⇾Service Bus Resources⇾ Select configuration Jar⇾ Browse and select the location where the 11g code saved⇾ select the OSB code and open⇾ check Include dependencies(if needed)⇾ finish.

2. Environment specific changes: Change the Business service endpoints, Host, Load balance URL etc. from Jdeveloper and you can also change them using the configuration file form ServiceBus console.

Following is the link to customize using configuration file:
https://soalicious.blogspot.com/2019/08/how-to-use-configuration-file-to.html

3. To display Xquery Mapper/Graphical design view properly, Right click on project⇾Service Bus⇾Convert To XQuery 1.0.

4. Proper service bus design view: In the JPR-file, there is a part called technologyScope. Remove the part “<string v="SOA"/>” from the technologyScope and restart the Jdeveloper tool and it starts showing the flows/diagrams in design view.

For more details:

https://soalicious.blogspot.com/2019/08/soa-12c-jedeveloper-flow-designs-in-osb.html

5. Use MDS reference: As we know, 12c OSB supports MDS, utilize this reuse-ability feature. Either create a resource service bus project and keep all the reusable artifacts in it or keep them in the MDS itself.

6. Once all the changes done, export the project from Jdeveloper and import in 12c service bus server.

Export steps:

  Right click on the project and click export⇾ Service bus resources⇾ Configuration Jar⇾ check the project to export⇾ provide a export destination⇾ Finish

7. Deploy in the service bus console: 

Click create session⇾ click import config jar⇾ Click Choose file and select the jar file you want to deploy⇾ click next⇾ click import⇾ Now you can see the code has been migrated to Server successfully and close it⇾ Activate it and provide appropriate comments.

SOA composites migration from 11g to 12c. (11.1.1.5.0) to (12.2.1.0.0).

Following are the points required for the migration:

1. Export SOA composites from 11g production (11.1.1.5.0) and import in 11g Jdeveloper (11.1.1.7.0) and compile/make the project and open in 12c Jdeveloper (12.2.1.0) using jpr.

Note: As per the Oracle SOA documentation, if you want to migrate from 11g version lower than 11.1.1.7.0, then you have to migrate that 11g old version first to 11.1.1.7.0 and then to 12C version.

2. Change Load balancer URL/Host from the composite.xml reference section.

3. Use the MDS references if the feature is not used in 11g version. Create MDS references for all reusable artifacts (xsd, wsdl, dvm etc.) in a composite and delete them locally.
Note: Abstract WSDL definition of an external service can be stored in MDS

4. Use new XPath functions in the BPEL/Mediator components.

For instances:

• Earlier releases of Oracle SOA Suite 11g encouraged the use of getCompositeInstanceID() as a way to identify and track the running instances. With the advent of the Flow ID concept, it is advisable to use the getFlowID() function where the FlowID of the instance needs to be tracked. getCompositeInstanceID() →  getFlowID()

• Also, be observant for the change in the namespace prefix for some of the functions.  For example, bpelx:copyList() should be used instead of ora:copyList(), although both the functions are available.

5. However, since you will be working on migrating projects from previous versions to 12c, you might face an error message displayed as follows for any composites which were initially developed using JDeveloper 11g:
Unable to read the diagram details.
Solution:
In the composite.xml file, find the following two properties:
<property name=”originalProductVersion” type=”xs:string” many=”false”>11.0.0.0.0</property>
<property name=”productVersion” type=”xs:string” many=”false”>12.2.1.0.0.151013</property>

Remove the originalProductVersion property, then compile and deploy again to your 12c environment.  You should now be able to see the composite definition.

6. Compare files with 11g and 12c and do the environment specific changes like (Endpoint URI, File path, User and password etc.)

7. Create partition (if not exist)

8. Create SAR file and deploy in EM console.

Durable vs Non-Durable

There is no concept of a topic being durable or a non durable. It is the subscription for a topic that can be durable or non durable.

Non durable subscription:
A non durable subscription means that publication will be delivered to the subscriber application as long as it is up and running. Once the application terminates, the broker will remove the subscription and no more publications will be delivered to that subscriber.

Durable Subscription:
On the other hand, for a durable subscription, publications will be delivered to the subscription even if the subscriber application is not running. Broker will hold such publications (in a queue) when the application is down. Once the application comes up, those publications will be delivered.

Durable subscriptions provide increased reliability at the cost of slower throughput for the following reasons:
• The Message Queue message broker must persistently store the list of messages assigned to each durable subscription so that should the broker fail, the list is available after recovery.
• Persistent messages for durable subscriptions are stored persistently, so that should a broker fail, the messages can still be delivered after recovery, when the corresponding consumer becomes active.
 By contrast, persistent messages for non-durable subscriptions are not stored persistently (should a broker fail, the corresponding consumer connection is lost and the message would never be delivered).