large Oracle SQL transactions creating large archive logs and filling space on the DB server.

book

Article ID: 4911

calendar_today

Updated On:

Products

CA Release Automation - Release Operations Center (Nolio) CA Release Automation - DataManagement Server (Nolio)

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

 

 

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. 

Environment

Release Automation 5.0 or higher running on Oracle database

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.