large Oracle SQL transactions creating large archive logs and filling space on the DB server.
Article ID: 4911
Updated On:
Products
Issue/Introduction
Although no deployments are running currently, there are some large DB transactions running.
These are creating large archive Logs on the DB server and memory usage on DB server reaching about 90%.
One of the SQLs running might be:
DELETE FROM step_events WHERE job_id = :1
Environment
Cause
At the same time the problem is seen, nolio_purging.log starts logging errors like:
2016-12-06 12:56:47,563 [JobToOffline-1] ERROR (com.nolio.platform.server.dataservices.services.purging.ActiveJobToOfflineMonitor:124) - Failed to verify old processes in database
java.lang.RuntimeException: org.hibernate.exception.GenericJDBCException: ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDO'
This points to the UNDO tablespace being full:
Oracle doc explains in http://ora-30036.ora-code.com/ the ORA-30036:
ORA-30036:
unable to extend segment by string in undo tablespace 'string'
Cause: the specified undo tablespace has no more space available.
Action: Add more space to the undo tablespace before retrying the operation. An alternative is to wait until active transactions to commit.
Resolution
add a datafile to the UNDO tablespace with autoextend or a fixed size if autoextend is not permitted.
Googling for ORA-30036 results in lots of hits on how to overcome, e.g. http://www.dba-oracle.com/sf_ora_30036_unable_to_extend_segment_by_string_in_undo_tablespace_string.htm
Additional Information
More information about how purge operations may cause the UNDO tablespace to fill up quickly can be found on https://www.ca.com/us/services-support/ca-support/ca-support-online/knowledge-base-articles.tec1605915.html
The article was written for MSSQL, but the transaction log in MSSQL is comparable with the UNDO tablespace in Oracle.
Feedback
