Archiving/purging process fails on Oracle with error "Job Database Maintenance.Process Expired Data threw an unhandled Exception"

This article describes how to resolve an error that occurs on Oracle during the xMatters automatic archiving/purging process.

Issue Details

On an xMatters deployment using an Oracle database, the automatic archiving/purging process may fail and generate an entry in the node logs similar to the following:

ERROR - - Job Database Maintenance.Process Expired Data threw an unhandled Exception:
org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [DELETE FROM EVS WHERE STATUS <> 'ACTIVE' AND WHEN_CREATED BETWEEN ? AND ? AND ROWNUM <= 1000]; SQL state [99999]; error code [30036]; ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'; nested exception is java.sql.SQLException: ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'

This error occurs when the undo table space does not have enough free space to hold the deletion of 1000 events (and their associated notifications).

Solution

To resolve this issue, adjust the batchSize property of the databaseManagementDAO bean in the\node\assets\resources\spring\integration\daoApplicationContext.xml. Before making this change, read through the following considerations.

Considerations

  • The batchSize parameter is used to control how many events will be deleted within one transaction (default value is 1000).
  • The consequence of reducing this size is that the automatic archiving/purging process will take longer to finish (i.e., smaller batchSize values result in longer processing time).
  • You may want to try different values (e.g., 900, 800, 700, etc.) to find an appropriate value that balances processing speed with the need for an adequate amount of free space in the undo table space.
  • If no value can satisfy both needs, you must increase your undo space (typically, this means doubling the undo space). If this fixes the problem, ask your DBA to check the usage of the undo space, and if it is not fully utilized, reduce the UNDO space accordingly.

xMatters Reference

JDN-1215 Originally created by Don Clark

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk