ORA-02089 – COMMIT is not allowed

When calling a stored procedure from SOA Suite 11g (Bpel/OSB/BPM ….) , you might get the following error:
….
java.sql.SQLSyntaxErrorException: ORA-02089: COMMIT is not allowed in a subordinate session
….

The reason for that error(you can use one of them):
You have defined a data source base on Global Transaction (XA)
In a Global Transaction, the ‘Two-Phase Commit’ protocol for global transaction processing is automatically being selected. It means that the Weblogic server is responsible to complete all of the transactions (or non of them).
In short – adding a COMMIT to your code (being invoked) is not allowed.
There are few ways to solve this error:

1. Change the data source to use Non-XA
   (and check  the “Supports Global Transactions” & “Emulate Two-Phase Commit” buttons)
2. Delete the COMMIT from your code.
3. Use the “PRAGMA AUTONOMOUS_TRANSACTION“. This will kind of create a separate transaction that will allow to use a commit.For example:CREATE PROCEDURE XXX AS
   PRAGMA AUTONOMOUS_TRANSACTION;
   BEGIN …

How to create a Large Profile SOA 12c Repository running RCU in Silent Mode

One of the really nice features of SOA 12c is the ability to automatically create partitioned instance tables in the SOA repository. To do this you need to specify a LARGE "Database Profile" in RCU when you install the system:



(see http://docs.oracle.com/middleware/1213/core/RCUUG/rcu_screens.htm#RCUUG381)

Unfortunately none of the documentation that I found (even now, a year after SOA 12c was released) tells you how to do this when running RCU in silent mode. This might have been the obvious place to mention 
it:
 https://docs.oracle.com/middleware/1213/core/INSOA/planning.htm#INSOA524

On the RCU command line what you need to do is specify the SOA_PROFILE_TYPE variable (which sounds obvious once you know!), e.g.:

$ORACLE_HOME/oracle_common/bin/rcu -silent -createRepository -connectString <db_connect_string> -dbUser <db_user> -dbRole <db_role> -useSamePasswordForAllSchemaUsers true -schemaPrefix <schema_prefix> -variables SOA_PROFILE_TYPE=LARGE <components> -f < <password_file>

The valid values, as the screen-shot above suggests, are LARGE, MED (not medium) and SMALL. LARGE is the only option to produce partitioned tables. Both MED and SMALL produce an identical SOAINFRA schema in the current release but I assume Oracle might be thinking of more optimisation options later.

Note: the O-box SOA appliance uses partitioned tables for the SOA repository by default - the Partitioning Option for the Oracle Database is relatively inexpensive and, even with the built-in repository purging scripts in 12c, setting up partitioning from the start is much easier than migrating later when a system is very busy or has grown dramatically in size.

What's new in SOA Suite 12c

If you're a production SOA infrastructure designer or administrator I suspect it is the last of these drivers that will be of most interest to you. In this post I'll focus on new features which I have either been playing with, or that have caught my eye, though I expect I will have missed a few!

Improved SOA Dashboard within Fusion Middleware Console

Whilst SOA 11g has an EM dashboard that allows you to look at instances and faults the issue many customers face is that at some point, usually after the system has been in production for a while, the performance of these pages deteriorates. There are a few reasons behind this but usually it is a combination of data volumes in the middleware repository and the dashboard doing queries you don't necessarily need. Oracle has provided a couple of options, such as caching data, but really these have just been stop-gaps.
With 12c Oracle has re-designed the dashboard much more around what the administrator needs to do - primarily with errors and faults. This "management by exception" approach, along with the new Error Hospital screens, seems a more administrator-centric approach and, being driven by focussed searches, so should have much better performance.

Fusion Middleware Console's Enhanced Authorization Model

Whilst on the topic of FMC, there are situations for SOA where you want some users to have more limited access to FMC. For example, you may have some BPEL processes that have faulted where you might want a junior administrator to work with a business analyst (perhaps a specialist who works with one of your partners who provides service APIs, say, for credit card payments) to recover those faults and retry those process instances. The new fine grained access model will allow such an administrator to have just the permissions they need to do their job. Whilst I haven't tried this feature yet it does look promising and is a sign of a maturing platform.

Managed File Transfer (MFT)

BFT is a newly developed software component used, as its name suggests, for providing a more robust mechanism for handling the distribution and processing of files (e.g. over secure FTP) especially for tracking and securing them, and for handling retries. Oracle has previously demonstrated this (relatively quietly) at OpenWorld, but now that SOA 12c has been released I'm sure it will get much wider publicity. This is another product which falls under the "industrial SOA" moniker - whilst files transfer mechanisms have been around for almost as long as networked computers, their usage shows no sign of declining - once because it was the lowest common denominator mechanism of getting data from one computer to another, now increasingly part of B2B interactions and especially with large payloads (for example if you need to send a medical image from one hospital to another).

Enterprise Scheduler (ESS)

ESS is a product that has been migrated from Fusion Applications and offers a wide range of scheduling targets, with the ability to call all sorts of web services and other jobs (e.g. did I remember seeing ODI ones?). I haven't looked at this yet but every customer I've worked for has had scheduling requirements and I can see the benefits for managing them in an application-centric way.

Profiles

Here's a feature I like, and think will be very useful for customers looking to optimise licence usage on their SOA infrastructure. With SOA 12c you now have the choice of a number of configuration profiles which control the available functionality, and consequently resource consumption. For example, the lightest is probably "BPEL-ONLY" which is BPEL, core SOA infra and a partial set of adapters, through "SOA FOUNDATION ENTERPRISE" which is pretty everything excluding B2B, Healthcare and BPM, and "SOA CLASSIC" which is everything, i.e. including BPM. Note that MFT (and possibly ESS) are separately installed products so I assume aren't currently managed under profiles.

Repository Creation Utility (RCU) Enhancements

Some readers may have seen me present, along with Lucas Jellema, Ronald van Luttikhuizen and Guido Schmutz, a few of our SOA 12c beta experiences at Jurgen Kress' Partner Community event in Malta earlier this year. The topic I focussed on then was RCU enhancements.
Firstly RCU now maintains a table (in <prefix>_STB), alongside the one in SYS, to keep track of what schemas (and with what prefixes) have been installed in a database. This STB table is then used by the SOA Configuration Wizard to save you having to re-key this information and is much more convenient.
A very nice new feature is that RCU gives you the choice of "SMALL", "MEDIUM" or "LARGE" repositories and then applies some of the tuning and maintenance options we used to have to set up manually in SOA 11g. For example for medium and large repositories it will set create some tables as partitioned (note that you will Database Enterprise Edition and Partitioning Option licences for this).