Identity Manager application server log shows repetitive 'Exception while writing to the DB' error
search cancel

Identity Manager application server log shows repetitive 'Exception while writing to the DB' error

book

Article ID: 216877

calendar_today

Updated On:

Products

CA Identity Manager

Issue/Introduction

Application Server log shows the following repetitive "Exception while writing to the DB" error

12:07:10,326 ERROR [ims.RuntimeStatusDetail] Exception while writing to the DB
java.sql.SQLException: Numeric Overflow
 at oracle.jdbc.driver.NumberCommonAccessor.throwOverflow(NumberCommonAccessor.java:4139)
 at oracle.jdbc.driver.NumberCommonAccessor.getInt(NumberCommonAccessor.java:125)
 at oracle.jdbc.driver.OracleCallableStatement.getInt(OracleCallableStatement.java:1346)
 at oracle.jdbc.driver.OracleCallableStatementWrapper.getInt(OracleCallableStatementWrapper.java:617)
 at weblogic.jdbc.wrapper.CallableStatement_oracle_jdbc_driver_OracleCallableStatementWrapper.getInt(Unknown Source)
 at com.netegrity.ims.persistence.PersistenceProvider.saveRuntimeStatusDetail(PersistenceProvider.java:2955)
 at com.netegrity.ims.statusDetail.RuntimeStatusDetailService.writeToPersistence(RuntimeStatusDetailService.java:534)
 at com.netegrity.ims.statusDetail.RuntimeStatusDetailService.writeRuntimeStatusDetail(RuntimeStatusDetailService.java:509)
 at com.netegrity.ims.statusDetail.RuntimeStatusDetailMessageBean.onMessage(RuntimeStatusDetailMessageBean.java:78)
 at weblogic.ejb.container.internal.MDListener.execute(MDListener.java:438)
 at weblogic.ejb.container.internal.MDListener.transactionalOnMessage(MDListener.java:361)
 at weblogic.ejb.container.internal.MDListener.onMessage(MDListener.java:297)
 at weblogic.jms.client.JMSSession.onMessage(JMSSession.java:5107)
 at weblogic.jms.client.JMSSession.execute(JMSSession.java:4778)
 at weblogic.jms.client.JMSSession.executeMessage(JMSSession.java:4173)
 at weblogic.jms.client.JMSSession.access$000(JMSSession.java:127)
 at weblogic.jms.client.JMSSession$UseForRunnable.run(JMSSession.java:5632)
 at weblogic.work.SelfTuningWorkManagerImpl$WorkAdapterImpl.run(SelfTuningWorkManagerImpl.java:681)
 at weblogic.invocation.ComponentInvocationContextManager._runAs(ComponentInvocationContextManager.java:352)
 at weblogic.invocation.ComponentInvocationContextManager.runAs(ComponentInvocationContextManager.java:337)
 at weblogic.work.LivePartitionUtility.doRunWorkUnderContext(LivePartitionUtility.java:57)
 at weblogic.work.PartitionUtility.runWorkUnderContext(PartitionUtility.java:41)
 at weblogic.work.SelfTuningWorkManagerImpl.runWorkUnderContext(SelfTuningWorkManagerImpl.java:655)
 at weblogic.work.ExecuteThread.execute(ExecuteThread.java:420)
 at weblogic.work.ExecuteThread.run(ExecuteThread.java:360)

Due to this problem, task execution performance is affected, and Last Activity column in View Submitted Task is blank.

Environment

Release : 14.3 CP2
Application Server : WebLogic 12.2.1.4
External Database: Oracle 11g

Cause

The Numeric Overflow error is due to Sequence being used has reach max value.

Resolution

To remedy this issue we need rebuild Task Persistence (TP) database.

The issue here is due to TP database sequence which has overflowed and we can remedy this problem by rebuilding (creating new) TP database.

We can create a new schema in the Oracle database and modify the JDBC data source in WebLogic to point correctly to use the new schema. Upon Identity Manager restart, Identity Manager will recreate TP tables within the new schema.

We have the following JDBC datasources in Identity Manager

We just need to modify the TP data source to point to new schema/database. The remaining data sources should *not* be touched. 

The steps to rebuild TP database for this purpose are

1. Shutdown IM
2. Create a new schema (user with same permissions as the old TP database schema)
3. Use WebLogic Server Admin Console to modify TP data source so it points to new schema
4. Restart IM

Please note that when IM uses a brand new TP database the following happened
    - View Submitted Task page will not shows previous tasks data contained in old TP database
    - Tasks that were running in progress before using the new TP database will need to be recovered. So please note down those task before switching to new TP database and consider recovery plan
    - Task Schedules will be removed. So please note down any task schedules and organize to configure the schedules again after switching to new TP database