AE - Error ORA-01555: snapshot too old
search cancel

AE - Error ORA-01555: snapshot too old

book

Article ID: 110900

calendar_today

Updated On:

Products

CA Automic Workload Automation - Automation Engine CA Automic One Automation

Issue/Introduction

I got the following error when running reorg or unload as below:

20180810/142218.599 - U00029108 UCUDB: SQL_ERROR Database handles DB-HENV: 2361050 DB-HDBC: 23abe68 
20180810/142218.599 - U00003591 UCUDB - DB error info: OPC: 'OCIStmtExecute' Return code: 'ERROR' 
20180810/142218.599 - U00003592 UCUDB - Status: '' Native error: '1555' Msg: 'ORA-01555: snapshot too old: rollback segment number 8 with name "_SYSSMU8_2210851416$" too small' 
20180810/142222.600 - U00000006 DEADLOCK 
20180810/142222.600 - U00003594 UCUDB Ret: '6' opcode: 'EXEC' SQL Stmnt: 'UPDATE AH SET AH_DeleteFlag = 1 WHERE AH_Idnr = ?'

The same can occur as well in case of issues with table RH, the RWP may show a high load  (100%), the associated table MQ*RWP a very important amount of records, and the RWP displays the following errors:

20210609/125638.672 - U00003525 UCUDB: ===> 'SELECT * FROM RH WHERE RH_Type    = ? AND RH_AH_Idnr = ?'
20210609/125638.673 - U00000006 DEADLOCK or Connection to database lost - Rollback handling initiated. See previous messages.
20210609/125638.673 - U00000006 DEADLOCK or Connection to database lost - Rollback handling initiated. See previous messages.
20210609/125638.696 - U00029108 UCUDB: SQL_ERROR    Database handles  DB-HENV: f09f00  DB-HDBC: 18e5438
20210609/125638.696 - U00003591 UCUDB - DB error info: OPC: 'OCIStmtExecute' Return code: 'ERROR'
20210609/125638.696 - U00003592 UCUDB - Status: '' Native error: '1555' Msg: 'ORA-01555: snapshot too old: rollback segment number 26 with name "_SYSSMU26_185541413$" too small'

 

Environment

Release: 12.x, 21.x
Component: Automation Engine

Database: Oracle

Resolution

According to the Oracle message, please contact a DBA and ask them to increase the size of rollback segment 8 with name "_SYSSMU8_2210851416$".

'ORA-01555: snapshot too old: rollback segment number 8 with name "_SYSSMU8_2210851416$" too small'


If that does not work, identify what is the query throwing the query and launch the same in sqlplus, you should hit the same error.
In the above case:
UPDATE AH SET AH_DeleteFlag = 1 WHERE AH_Idnr = ?

The error may be caused by a corrupted index of the table AH, so check with a query like
select count(*) from AH;

If it throws the same error then rebuild the indexes of that table, starting with the Primary Key, and run the query above, rebuilding indexes as needed, until the error is fixed.

In case of ILM, please ask the DB administrator to check the Index status in the different table partitions, and rebuild the indexes on those corrupted.

On this example, we can see that the current partition (215) indexes are ok 

SQL> select count(*) from automic.rh partition ("215");

  COUNT(*)
----------
    251305

But in the previous partion 214, they are corrupted:


SQL> select count(*) from automic.rh partition ("214");
select count(*) from automic.rh partition ("214")
                             *
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number 17 with name
"_SYSSMU17_408686081$" too small

 

In order to fix it, see the example of rebuild commands for ILM that had to be launched to fix the indexes for table RH

alter index AUTOMIC.PK_RH rebuild partition  "214";

alter index AUTOMIC.NK_RH_AH_IDNR rebuild partition  "214";

 

Additional Information

You can find more information here:
http://www.dba-oracle.com/t_ora_01555_snapshot_old.htm
https://docs.oracle.com/database/121/ADMQS/GUID-26945DEA-D1F9-42BA-8CB5-7BE4DB6EC14D.htm#ADMQS12051 
https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_6013.htm 

 

This can also be caused by a large amount of records being deleted at one time.  Updating the reorg_chunk_size to a lower number in the unload ini file may help.  More info can be found here: https://docs.automic.com/documentation/WEBHELP/English/all/components/DOCU/21.0/Automic%20Automation%20Guides/Content/AWA/Admin/inifiles/admin_ini_utility_db_unload.htm?tocpath=Reference%7CConfiguration%20(INI)%20Files%7CUtilities%7C_____8

Please check with Oracle Technical Support if a Bug Fix is available for your current database version or if an Upgrade to a supported version is needed.