000031181 - Unable to purge email log with ORA-01878 error after changing log retention limit using Via L&G

Document created by RSA Customer Support Employee on Jun 14, 2016Last modified by RSA Customer Support Employee on Apr 22, 2017
Version 3Show Document
  • View in full screen mode

Article Content

Article Number000031181
Applies ToRSA Product Set: Identity Management and Governance
RSA Version/Condition: All
 
IssueAfter changing setting for "Days to retain email logs" under Admin -> Email->Settings tab->Edit button -> "Email Log Options" from 0 (0 = forever) to any value, in aveksaServer.log file will contain entries similar to the following messages.
ERROR (ApprovalInboxProcessorServiceProvider) [com.aveksa.server.email.EmailLogObjectStore] Unable to purge Email Log records older than `30` days from the database. SQL=`DELETE FROM T_EMAIL_LOG WHERE COMPLETED_TIME < (select current_timestamp - INTERVAL '30' day(9) FROM DUAL)
`.
com.aveksa.server.db.PersistenceException: Unable to purge Email Log records older than `30` days from the database. SQL=`DELETE FROM T_EMAIL_LOG WHERE COMPLETED_TIME < (select current_timestamp - INTERVAL '30' day(9) FROM DUAL)
`.
    at com.aveksa.server.email.EmailLogObjectStore.purgeOldEmailLogEntries(EmailLogObjectStore.java:250)
    at com.aveksa.server.email.mailboxmonitor.ApprovalInboxProcessorServiceProvider.purgeDatabase(ApprovalInboxProcessorServiceProvider.java:213)
    at com.aveksa.server.email.mailboxmonitor.MailboxMonitorThread.run(MailboxMonitorThread.java:49)
Caused by: com.aveksa.server.db.PersistenceException: Executing JDBC update failed
    at com.aveksa.server.db.PersistenceManager.executeJDBCUpdate(PersistenceManager.java:3046)
    at com.aveksa.server.db.PersistenceManager.executeJDBCUpdate(PersistenceManager.java:2904)
    at com.aveksa.server.email.EmailLogObjectStore.purgeOldEmailLogEntries(EmailLogObjectStore.java:247)
    ... 2 more
Caused by: java.sql.SQLException: ORA-01878: specified field not found in datetime or interval
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523)
    at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:193)
    at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:999)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1315)
    at oracle.jdbc.driver.OracleStatement.executeUpdateInternal(OracleStatement.java:1822)
    at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:1787)
    at oracle.jdbc.driver.OracleStatementWrapper.executeUpdate(OracleStatementWrapper.java:280)
    at org.jboss.resource.adapter.jdbc.WrappedStatement.executeUpdate(WrappedStatement.java:186)
    at com.aveksa.server.db.PersistenceManager.executeJDBCUpdate(PersistenceManager.java:3024)
    ... 4 more
ERROR (ApprovalInboxProcessorServiceProvider) [com.aveksa.server.email.mailboxmonitor.ApprovalInboxProcessorServiceProvider] FAILED method=purgeDatabase 
com.aveksa.server.db.PersistenceException: Unable to purge Email Log records older than `30` days from the database. SQL=`DELETE FROM T_EMAIL_LOG WHERE COMPLETED_TIME < (select current_timestamp - INTERVAL '30' day(9) FROM DUAL)
`.

    at com.aveksa.server.email.EmailLogObjectStore.purgeOldEmailLogEntries(EmailLogObjectStore.java:250)
    at com.aveksa.server.email.mailboxmonitor.ApprovalInboxProcessorServiceProvider.purgeDatabase(ApprovalInboxProcessorServiceProvider.java:213)
    at com.aveksa.server.email.mailboxmonitor.MailboxMonitorThread.run(MailboxMonitorThread.java:49)
Caused by: com.aveksa.server.db.PersistenceException: Executing JDBC update failed
    at com.aveksa.server.db.PersistenceManager.executeJDBCUpdate(PersistenceManager.java:3046)
    at com.aveksa.server.db.PersistenceManager.executeJDBCUpdate(PersistenceManager.java:2904)
    at com.aveksa.server.email.EmailLogObjectStore.purgeOldEmailLogEntries(EmailLogObjectStore.java:247)
    ... 2 more
Caused by: java.sql.SQLException: ORA-01878: specified field not found in datetime or interval
 
CauseOracle bug "11900845: AFTER DST CHANGE LOT OF DBMS_JOBS STARTED FAILING WITH ORA-1878".
 
ResolutionUpgrade Oracle to 11.2.0.2.11.
Note that even after upgrading, this Oracle bug may also result in bad data and requiring a delete of them.
Use the following DELETE statement connected as avuser. Make sure to change the date as appropriate.
DELETE FROM T_EMAIL_LOG WHERE COMPLETED_TIME < to_timestamp('11-MAR-2015','DD-MON-YYYY');
Alternatively use the attached clean-up-email-log.sql to archive the current email log into another table and keep only <n> days worth of logs. (Make sure to change the date as appropriate in this script).

Attachments

Outcomes