Tomcat Analyze job and Delete Log Analysis job causing UNDO tablespace to fail on long running actions with error ORA-01555: snapshot too old

book

Article ID: 140913

calendar_today

Updated On:

Products

STARTER PACK-CLARITY PPM Clarity PPM SaaS

Issue/Introduction

Processes, jobs, actions in UI failing with error:

ORA-01555: snapshot too old: rollback segment number 133 with name "_SYSSMU133_3251802835$" too small

(can be on different segment and name)

Upon checking with DBA we see UNDO tablespace getting out of storage and the session that is stuck belongs to either Tomcat Analyze job or Delete Log Analysis job

Cause

This is caused by two defects:

DE50064 Tomcat Analyze Job is causing library cache lock

DE50296 Delete Log Analysis Data Performance Issue

Both are fixed in 15.7.1

Environment

Release : 15.x and higher, fixed in 15.7.1 

Resolution

1. Ensure that the table LOG_DETAILS in Clarity database only contains under 10 mln records. If you have more records than that, we recommend truncating the table:

truncate table LOG_DETAILS

Then run the Tomcat Analyze job

 

2. For the fixed procedure:

Upgrade to 15.7.1

Or apply the corrected SP and SQL below:

 

1. Update the stored procedure LOG_RETENTION_SP on the Clarity PPM schema with the attached SQL SP DDL

2. Alter the tables for no logging by running the below command against the Clarity PPM schema:

ALTER TABLE LOG_DETAILS NOLOGGING; 

ALTER TABLE LOG_SUMMARY NOLOGGING; 

ALTER TABLE LOG_FILES NOLOGGING; 

ALTER TABLE LOG_SESSIONS NOLOGGING;

 

This will apply the same changes as the fix on 15.7.1 and prevent the issue from occurring again

 

Attachments

1576163517513__LOG_RETENTION_SP.sql get_app