Oracle Database ORA-01555 snapshot old

Cause:

User user1 updates the table. User user2 reads data in the table before user1 is submitted, and reads data in large batches (for example, it takes 3 minutes) in the past three minutes, user1 committed the transaction. After the transaction is committed, the rollback segment transactions occupied by the transaction will be marked as non-active, the rollback segment space can be overwritten and reused .. Then a problem arises, if a query needs to use the overwritten rollback segment to construct the image before consistent read, then there will be a famous Oracle ORA-01555 error.

The ora-01555 snapshot is old because the undo space is not large enough, some of the undo data is overwritten, the user cannot get the data before the modification.

Undo data can be divided into three types:
Active undo: undo data of uncommitted transactions, which cannot be overwritten and used to roll back rollback transactions.
Expired undo: undo data of the committed transaction, which can be overwritten.
Undo that has not expired: the transaction has been committed, but before the transaction is committed, some queries are in progress, it needs to read the data before the submission, this part of the data is not expired data. If this part of undo data is overwritten, A ora-01555 error occurs.

 

Solution:

(1) Increase the UNDO tablespace size

(2) increase the undo_retention time. The default value is 15 minutes.

(3) Optimize the SQL statements with errors to reduce the query time. The preferred method is

(4) Avoid frequent submission

The ORA-01555 error is described as follows in the Oracle 9i documentation:

01555,000 00, "snapshot too old: rollback segment number % s with name \" % s \ "too small"
// * Cause: rollback records needed by a reader for consistent read are
// Overwritten by other writers
// * Action: If in Automatic Undo Management mode, increase undo_retention
// Setting. Otherwise, use larger rollback segments

As you can see, in Oracle 9i Automatic Management of UNDO tablespace mode, the introduction of the UNDO_RETENTION parameter is just to reduce the occurrence of ORA-01555 errors. This parameter sets the retention time of the image data before the rollback segment is overwritten after the transaction is committed (the rollback segment becomes inactive). The Unit is seconds, the initial value of 9iR1 is 900 seconds, and the increase in Oracle 9iR2 is 10800 seconds.

Obviously, the higher this parameter is set, the more you can reduce the appearance of ORA-01555 errors, but the retention time and storage space are closely related, if the UNDO tablespace storage space is limited, then Oracle will choose to reclaim the space occupied by committed transactions, regardless of the UNDO_RETENTION parameter.

In the AUM mode of Oracle 9i, UNDO_RETENTION is actually a non-Guaranteed (NO Guaranteed) restriction. That is to say, if other transactions need to roll back the space, and the space is insufficient, the information will still be overwritten; from Oracle 10 Gb, Oracle has added Guarantee control for UNDO, that is, you can specify the UNDO tablespace to meet the UNDO_RETENTION limit. When the UNDO tablespace is set to Guarantee, the rollback space of the committed transaction must be retained for sufficient time. If the space of the UNDO tablespace is insufficient, the new transaction will fail due to insufficient space, instead of overwrite.

From the management changes of rollback segments of different versions, we can see that Oracle has been improving.

Oracle provides an internal event (10203 event) that can be used to track the block clearing operation of the database. 10203 event can be set using the following command. After the setting, you need to restart the database to take effect:

Alter system set event = "10203 trace name context forever" scope = spfile;

Note that the rollback segment or the original tablespace has been deleted when the delayed block is cleared, in this case, Oracle can still obtain the SCN information through the dictionary table UNDO $ and clear blocks.

Oracle ORA-01555 snapshot old description

Troubleshooting for ORA-01078 and LRM-00109

ORA-01555 ultra-long Query Duration time

Notes on ORA-00471 Processing Methods

ORA-00314, redolog corruption, or missing Handling Methods

Solution to ORA-00257 archive logs being too large to store