ORA-01438:value larger than specified precision allowed error in SOA 11g logs

Issue:
SQLDataException: ORA-01438 Error Frequently in SOA Log Files (the SOA managed server out and diognostic log file)


Applies to:
Oracle SOA Suite 11.1.1.3.0 and later (11g R1)


What you see in logs:

Error while invoking bean "cube delivery": Exception not handled by the Collaxa Cube system.[[

an unhandled exception has been thrown in the Collaxa Cube systemr; exception reported is: "ORABPEL-00000

Exception not handled by the Collaxa Cube system.

an unhandled exception has been thrown in the Collaxa Cube systemr; exception reported is: "java.sql.SQLDataException: ORA-01438: value larger than specified precision allowed for this column

at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:83)

at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:135)

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:210)

Effects:

Degraded performance of SOA Composites. Server spends additional processing power on logging errors, so performance of server is impacted. Also lots of log files are generated which might fill your disk system fast.

Cause:

This is an issue which has been identified by Oracle. The bug 12621337: ORA-01438 ON LIVE_INSTANCES COLUMN

The problem is with the precision of the column COMPOSITE_INSTANCE.LIVE_INSTANCES currently defined to be NUMBER(3) which can at the most hold a value up to 999. As there is no explicit limitation on this column, the correct way to handle this is to increase the precision of this column to accommodate more instances.


Solution:
The workaround is to modify the COMPOSITE_INSTANCE.LIVE_INSTANCES column's precision to be NUMBER(38) in the SOAINFRA schema.
To apply this workaround, follow the steps below:

1. Stop the SOA domain

2. Log in to the SOA infra repository database as SYSDBA

3. Modify the COMPOSITE_INSTANCE table as shown below

ALTER TABLE 

soa11g_soainfra.composite_instance 

MODIFY (live_instances NUMBER(38))

;

In the above sql statement I have assumed the dehydration prefix is soa11g. Please modify the statement according to your prefix.Restart the SOA domain. Check the logs.

SOA 11g managed Server takes an hour to start on Oracle T Series machine

Recently I had some one asking me, that their SOA managed server took an hour to start. The instance in question had some 200 plus composites in it. On questioning about the platform they were on I came to know that they had the below configuration:

WLS=10.3.4
SOA Suite 11.1.1.4

Operating System: Solaris 10
(SunOS endenapp04 5.10 Generic_144488-07 sun4v sparc SUNW,SPARC-Enterprise-T5120)
Hardware: SUN SPARC T5120


Bingo !!! I got it right,

The machine they were on(T5120 or the T1 SUN Server) is a pathetic one and if your DB tier is on it it makes your situation even worse. I have worked on such machines and the experience are better forgotten. They are called The T Series machines belong to the CMT family of servers, which boast of supporting multi threaded applications well. Unfortunately single threaded applications like the DB run very poorly on them. For example on machines like the T5120 or T5240 running the RCU may take close to an hour, on M5000 it takes 10 minutes. With so many composites (remember the composites are loaded from Dehydrarion ie DB) your application will definitely take an hour or few minutes less than that to start

For tuning SOA 11g on T5140 check my post on my blog:
http://oraclefusionfacts.blogspot.in/2011/03/jvm-tuning-for-oracle-soa-suite-11g.html

Don't expect to fly like rocket after you follow my post since you cannot win a F1 race when you are driving a cart. You may get a better performance some how.

See below note from Oracle which will give you enough proof of what i am talking about:
Weblogic: Performance Problems After Migrating From WLS 8.1 To WLS 10.3 And Moving To SUN Sparc T5240 With CMT Chips [ID 860459.1]
Poor Performance of SOA/OIM/WL/EM on Solaris 10 - SPARC T3 Server [ID 1361701.1]

Solution:

Please propose a M5000 box for the Database Tier or upgrade the App and DB Tier to the newer version of T series ie T4. Oracle guarantees manifold performance improvements on T4 as compared to T1/T2/T3s.