AE - Error ORA-01555: snapshot too old
Article ID: 110900
Updated On:
Products
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.
Feedback
