Troubleshoot Fix Resolve ORA-01555:snapshot too old: rollback segment number %s

advertisements

_____________________________________________________________________________________________________________________

ORA-01555 Snapshot Too Old

This error is related to rollback (Oracle 8i and below) / UNDO (9i and above) segments. Oracle uses UNDO segments for maintaining the read consistency of the database by recording the block level changes to the UNDO. UNDO is used for reconstruct the read-consistent snapshot of the data.
Whenever a change happens to the any of the record by a transaction, a snapshot of the record before the changes were made copied to the UNDO segments. These undo records serves the transactions which started before making changes to the original record. Oracle overwrites these undo records when the corresponding original record is committed and there is not enough room for new undo records. If this kind of UNDO wipe out happen the transactions which are using these old snapshots from UNDO segments will die with ORA-01555 error.

Scenario:

        10:00 AM: transaction 1 started a long running process (Say 1hour) with select on table x

        10:05 AM: transaction 2 updated and committed the table x’s records which are being used by transaction 1 and the old snapshots recoded into UNDO segments. From this point in time the transaction 2 is using the old snapshots from the undo segment.

        10:30 AM: transaction 3 started updating some other tables and it needs space in undo segments. In between UNDO segment got filled and wiped out the undo segments which were used by transaction 2 as it is committed and longer used by transaction 2. These overwritten undo records are being used by the transaction 1. So the session connected to the transaction 1 will get disconnected with ORA-1555 error because of the unavailability of read consistent snapshot data.

Resolution

1. For oracle 8i and below increase the number of rollback segments and use bigger rollback segments.
2. Increase the undo tablesapce size
3. UNDO_RETENTION=[seconds] parameter: Oracle 10g provides this parameter and it keeps or assures the undo records in undo segment at least the duration mentioned in the parameter. Increase the value of the undo_retention parameter as required for the process.
4. Retention Guarantee: You can enable the undo retention guarantee by creating a tablespace with “Retention Guarantee” clause. You can specify this clause with ALTER TABLESPACE statement. If retention guarantee is enabled the specified minimum undo retention is guaranteed. The database never overwrites the unexpired undo data even if other transactions fail due to lack of space in the UNDO tablespace.
5. Automatic Undo Management: if the database is running on Oracle 9i and above implement Automatic Undo Management.

Recommendations

a.       Try to reschedule the long running jobs during less DML traffic

b.      Try to implement optimized commit in the long running jobs. (The frequent commit may cause the expiry of the undo segments. Also reduces the jobs performance due to the DBWR and LGWR overhead)

c.       Try to tune the long running jobs if possible.

_____________________________________________________________________________________________________________________

Website Stats