Sunday, March 11, 2012

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.



2 comments:

  1. Bluehost is definitely one of the best web-hosting company for any hosting plans you require.

    ReplyDelete
  2. I would like to thank you for the efforts you have made in writing this article. I am hoping the same best work from you in the future as well. Your write up is a fine example of it.For more details about oracle fusion SCM training please check our website.

    Oracle Fusion SCM Online Training



    ReplyDelete