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.
Release : 14.3 CP2
Application Server : WebLogic 12.2.1.4
External Database: Oracle 11g
The Numeric Overflow error is due to Sequence being used has reach max value.
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