SOA Suite 12.2.1.1 FileNotFound When Executing RCU

I was installing Oracle SOA Suite 12.2.1.1, and when I ran RCU to seed the database, it threw an exception relating to SOA Infrastructure.

Repository Creation Utility: Create - Completion Summary

Database details:
-----------------------------

Host Name                                    : myhost
Port                                         : 1521
Service Name                                 : ORCL
Connected As                                 : sys
Prefix for (prefixable) Schema Owners        : DEV
RCU Logfile                                  : /tmp/RCU2016-09-26_10-37_1761337454/logs/rcu.log
RCU Checkpoint Object                        : /tmp/RCU2016-09-26_10-37_1761337454/logs/RCUCheckpointObj

Component schemas created:

-----------------------------

Component                                    Status         Logfile
Common Infrastructure Services               Success        /tmp/RCU2016-09-26_10-37_1761337454/logs/stb.log
SOA Infrastructure                           Failed         /tmp/RCU2016-09-26_10-37_1761337454/logs/soainfra.log 
User Messaging Service                       Success        /tmp/RCU2016-09-26_10-37_1761337454/logs/ucsums.log
Metadata Services                            Success        /tmp/RCU2016-09-26_10-37_1761337454/logs/mds.log
WebLogic Services                            Success        /tmp/RCU2016-09-26_10-37_1761337454/logs/wls.log
Oracle Platform Security Services            Success        /tmp/RCU2016-09-26_10-37_1761337454/logs/opss.log
Audit Services                               Success        /tmp/RCU2016-09-26_10-37_1761337454/logs/iau.log
Audit Services Append                        Success        /tmp/RCU2016-09-26_10-37_1761337454/logs/iau_append.log
Audit Services Viewer                        Success        /tmp/RCU2016-09-26_10-37_1761337454/logs/iau_viewer.log

Repository Creation Utility - Create : Operation Completed
Repository Creation Utility - Dropping and Cleanup of the failed components
Repository Dropping and Cleanup of the failed components in progress.

Percent Complete: 100

The referenced log file indicated that a file could not be found.

FileNotFound /u01/fmw/soa/common/sql/soainfra/sql/oracle/createschema_soainfra_oracle_small.sql in attempt 0
FileNotFound /u01/fmw/soa/common/sql/soainfra/sql/oracle/createschema_soainfra_oracle_small.sql in attempt 1
FileNotFound /u01/fmw/soa/common/sql/soainfra/sql/oracle/createschema_soainfra_oracle_small.sql in attempt 2
FileNotFound /u01/fmw/soa/common/sql/soainfra/sql/oracle/createschema_soainfra_oracle_small.sql in attempt 3
/u01/fmw/soa/common/sql/soainfra/sql/oracle/createschema_soainfra_oracle_small.sql (No such file or directory)
Mon Sep 26 10:48:49.828 EDT 2016 NOTIFICATION SOAINFRA: oracle.sysman.assistants.rcu.backend.action.JavaAction::perform: Executing Java Action: oracle.ias.version.SchemaVersionUtil:utilDropRegistryEntry
Mon Sep 26 10:48:48.550 EDT 2016 rcu:Executing SQL statement: DROP USER DEV_SOAINFRA CASCADE
Mon Sep 26 10:48:49.869 EDT 2016 rcu:Executing SQL statement: drop tablespace DEV_SOAINFRA including contents and datafiles

So I perused the directory, and the file is there; however, it is contains uppercase SMALL in the name.

-rw-r-----.  1 oracle oinstall 1821419 Jun  8 19:07 createschema_soainfra_oracle_SMALL.sql

I renamed the file with lowercase "small" in the name, as RCU expects, and ran RCU again. This time, the seeding of the database processed successfully. Afterwards, I renamed the file to its original name.

SOA AqAdapter Not Dequeuing Messages

Messages were not being dequeued by the SOA AqAdapter in a composite. The error in the logs indicated a misleading reason for the behavior - AQ_INVALID_QUEUE_TYPE.

The queue object and queue existed and could be queried in the database directly. Messages could be enqueue/dequeued using anonymous blocks with no problem. However, the AqAdapter would not dequeue messages as expected.

ERROR

Please correct the reported issue and redeploy the BPEL process.

: Endpoint Activation Error.

AdapterFrameworkImpl::endpointActivation - Endpoint Activation Error.

The Resource Adapter AQ Adapter was unable to activate the endpoint oracle.tip.adapter.aq.inbound.AQDequeueActivationSpec:{QueueName=myqueue, DatabaseSchema=myuser, SchemaValidation=false} due to the following reason: BINDING.JCA-11976

AQ_INVALID_QUEUE_TYPE.

Unable to obtain the correct queue type.
Queue does not exist or not defined correctly.
Drop and re-create queue.

Please correct the reported issue and redeploy the BPEL process. […]

ANALYSIS

Similar to the DbAdapter distributed polling, the AqAdapter queries the query queue table using SELECT FOR UPDATE SKIP LOCKED.

You can view the actual SQL query by finding the sql_id of the DML that was passed to the database.

select sql_id,prev_sql_id,a.*
from gv$session a
where machine = ‘hostname';

select *
from gv$sql
where sql_id = sql_id;

FIX

In addition to granting execute to the object used in the queue and using DBMS_AQADM to grant privileges to the queue itself,

grant execute on queue_object to role;
DBMS_AQADM.GRANT_QUEUE_PRIVILEGE (
   privilege      => 'DEQUEUE',
   queue_name     => 'myqueue',
   grantee        => 'myuser',
   grant_option   => FALSE);

the jdbc user that the AqAdapter employs to connect to the database also needs select and update to the queue table. This table is what the AqAdapter polls for new messages in the queue.

grant select on aq$myqueue_query_table to role;grant update on aq$myqueue_query_table to role;

Oracle SOA Database Adapter Polling

I had a requirement to poll a database table, pass some values from the table to a service endpoint, and update the original record with values from the response. This is a fairly simple use case; however, I encountered some odd behavior when the solution was deployed to a clustered environment. Namely,

1. Multiple SOA instances were being created for a single database record, one for each DbAdapter deployment on the SOA managed servers, and
2. I was updating the Logical Delete Field in a BPEL process, and once the database adapter thread was finished, it too was updating the Logical Delete Field after I updated it.

Multiple Instances

Oracle documentation references capability called Skip Locking used in Distributed Polling. That is to say that the polling SQL will fetch the resultSet into a cursor. Any record that is locked in the fetch will be skipped. Unfortunately, it doesn't lock the record from another database adapter instance polling the same table and updating that record. There is also documentation that indicates an alternative approach using a MarkReservedValue that says, if you use a reserved value, skip locking will not be used.

Well, using a combination of providing a MarkReservedValue and enabling Distributed Polling was the only way I could get the database adapter to stop creating multiple instances for the same record.

Logical Delete Field Updated When Thread Completes

You cannot update the Logical Delete Field inside of a BPEL process and expect that value to be persisted. The last thing that the database adapter thread does before it sleeps is update the value of the Logical Delete Field with whatever you specified as the Read Value in the configuration.

The better solution is to add a new field to the database table and use it as the Logical Delete Field. Then, you can invoke another database adapter to update the value of whatever field you choose, and it will be persisted.

WebLogic Scripting Tool (WLST) Variable Values

When using WebLogic Scripting Tool (WLST) there are two common mechanisms that can be employed to obtain variable values outside of a Jython (.py) script. You can obtain a value from:

1. An environment variable, or
2. A properties file.

Using Environment Variables

When you need to obtain the value of an environment variable, import the os package into your script. Then, use the getenv() method to get the value of a specific environment variable.

import os

javaHome = os.getenv("JAVA_HOME")

print("JAVA_HOME is " + str(javaHome))

Using a Properties File

When you need to obtain the value of a property, use the loadProperties() method. Using this method, there is no need to declare the variable and assign a value. Instead, the property.name becomes the name of the variable. The property.value is the assigned value. You will need to define a properties file, e.g. wlstExample.properties.

myProperty=myValue

In the WLST script, reference the loadProperties() method and provide a path to the properties file. If the properties file is in the same directory, and likely it is, do not use a relative path like ../wlstExample.properties. Instead, just use the file name.

loadProperties("wlstExample.properties")

print("The value of myProperty is " + str(myProperty))

When executed, this script produces the following output.

[oracle@myHost ~]$ java weblogic.WLST wlstExample.py

Initializing WebLogic Scripting Tool (WLST) ...

Welcome to WebLogic Server Administration Scripting Shell

Type help() for help on available commands

JAVA_HOME is /usr/java/jdk1.7.0_75

The value of myProperty is myValue

CA API Gateway Route via Assertion Not Evaluating False

I was composing a CA API Gateway policy that was not falling out of an "All assertions must evaluate to true" block when the invocation of a service resulted in a fault. Since a fault was being thrown, I expected the assertion to evaluate to false; however, it was evaluated as true.

CA API Gateway Policy

When the endpoint was invoked, the policy would execute the "Route via" assertion and continue processing to the following "Add Audit Details" assertion as depicted in the following log.


2016-01-06T14:25:30.134-0500 INFO 10769 com.l7tech.server.policy.assertion.ServerAuditDetailAssertion: -4: Invoking service
2016-01-06T14:25:30.134-0500 INFO 10769 com.l7tech.server.policy.assertion.ServerHttpRoutingAssertion: 4047: Request is a context variable; using POST
2016-01-06T14:25:30.134-0500 INFO 10769 com.l7tech.server.policy.assertion.ServerAuditDetailAssertion: -4: Invoked service

The service that was invoked was configured to return a fault, and one would expect that if a "Route via" assertion returns a fault, this would result in a false condition. Therefore, control would fall out of the first "All assertions must evaluate to true" assertion and move on to the next "All assertions must evaluate to true" block.

Upon investigation, I discovered a somewhat confusing checkbox that solved the issue.

Pass through SOAP faults with error status 500 checkbox marked

In the HTTP(S) Routing Properties (double click the Route via assertion) window, on the Other tab, in the Assertion Outcome frame, there is a checkbox entitled, "Pass through SOAP faults with error status 500."

At first glance, one might interpret this as whatever fault is thrown by the invoked service, pass that fault right on through to the consumer. In fact, what this means is that the assertion will completely ignore that fault by evaluating the assertion as true and continue processing the next assertion.

After unmarking this checkbox, the assertion then evaluated to false, as expected.

Pass through SOAP faults with error status 500 checkbox unmarked

2016-01-06T14:26:44.623-0500 INFO 10684 com.l7tech.server.policy.assertion.ServerAuditDetailAssertion: -4: Invoking service
2016-01-06T14:26:44.623-0500 INFO 10684 com.l7tech.server.policy.assertion.ServerHttpRoutingAssertion: 4038: Downstream service returned status (500). This is considered a failure case.