We often receive the ORA-01555 (snapshot too old) errors during our nightly runs of key production batch jobs. We want to eliminate these errors.
Solution:
While setting a high value for the UNDO_RETENTION parameter can potentially minimize the possibility of receiving “snapshot too old” errors, it doesn’t guarantee that the database won’t overwrite older undo data that may be needed by a running transaction. You can move long-running batch jobs to a separate time interval when other programs aren’t running in the database to avoid these errors.
Regardless, while you can minimize the occurrence of “snapshot too old” errors with these
approaches, you can’t completely eliminate such errors without specifying the guaranteed undo retention feature. When you configure guaranteed undo retention in a database, no transaction can fail because of the “snapshot too old” error. Oracle will keep new DML statements from executing when you set up guaranteed undo retention. Implementing the guaranteed undo feature is simple. Suppose you want to ensure that the database retains undo for at least an hour (3,600 seconds). First set the undo retention threshold with the alter system command and then set up guaranteed undo retention by specifying the retention guarantee clause to alter the undo tablespace.
SQL> alter system set undo_retention=3600;
System altered.
SQL> alter tablespace undotbs1 retention guarantee;
Tablespace altered.
You can switch off guaranteed undo retention by executing the alter tablespace command with the retention noguarantee clause.
How It Works
Oracle uses the undo records stored in the undo tablespace to help roll back transactions, provide readconsistency, and to help recover the database. In addition, the database also uses undo records to read data from a past point in time using Oracle Flashback Query. Undo data serves as the underpinning for several Oracle Flashback features that help you recover from logical errors.
Occurrence of the Error
The ORA-01555 error (snapshot too old) may occur in various situations. The following is a case where the error occurs during an export.
EXP-00008: ORACLE error 1555 encountered
ORA-01555: snapshot too old: rollback segment number 10 with name "_SYSSMU10$" too small EXP-00000: Export terminated unsuccessfully
And you can receive the same error when performing a flashback transaction:
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number with name "" too small
ORA-06512: at "SYS.DBMS_FLASHBACK", line 37
ORA-06512: at "SYS.DBMS_FLASHBACK", line 70
The “snapshot too old” error occurs when Oracle overwrites undo data that’s needed by another transaction. The error is a direct result of how Oracle’s read consistency mechanism works. The error occurs during the execution of a long-running query when Oracle tries to read the “before image” of any changed rows from the undo segments. For example, if a long-running query starts at 1 a.m. and runs until 6 a.m., it’s possible for the database to change the data that’s part of this query during the period in which the query executes. When Oracle tries to read the data as it appeared at 1 a.m., the query may fail if that data is no longer present in the undo segments.
If your database is experiencing a lot of updates, Oracle may not be able to fetch the changed rows, because the before changes recorded in the undo segments may have been overwritten. The transactions that changed the rows will have already committed, and the undo segments don’t have a record of the before change row values because the database overwrote the relevant undo data. Since Oracle fails to return consistent data for the current query, it issues the ORA-01555 error. The query that’s currently running requires the before image to construct read-consistent data, but the before image isn’t
available.
The ORA-01555 error may be the result of one or both of the following: too many updates to the database or too small an undo tablespace. You can increase the size of the undo tablespace, but that doesn’t ensure that the error won’t occur again.
Influence of Extents
The database stores undo data in undo extents, and there are three distinct types of undo extents:
Active: Transactions are currently using these extents.
Unexpired: These are extents that contain undo that’s required to satisfy the undo retention time specified by the UNDO_RETENTION initialization parameter.
Expired: These are extents with undo that’s been retained longer than the
duration specified by the UNDO_RETENTION parameter.
If the database doesn’t find enough expired extents in the undo tablespace or it can’t get new undoextents, it’ll re-use the unexpired (but never an active undo extent) extents, and this leaves the door open for an ORA-01555, “snapshot too old” error. By default, the database will essentially shrink the undo retention period you specify, if it encounters space pressure to accommodate the undo from new transactions. Since the unexpired undo extents contain undo records needed to satisfy the undo retention period, overwriting those extents in reality means that the database is lowering the undo retention period you’ve set. Enabling the undo retention guarantee helps assure the success of long running queries as well as Oracle Flashback operations. The “guarantee” part of the undo retention guarantee is real—Oracle will certainly retain undo at least for the time you specify and will never overwrite any of the unexpired undo extents that contain the undo required to satisfy the undo retention period. However, there’s a stiff price attached to this guarantee—Oracle will guarantee retention even if it means that DML transactions fail because the database can’t find space to record the undo for those transactions. Therefore, you must exercise great caution when enabling the guaranteed undo retention capability.
Solution:
While setting a high value for the UNDO_RETENTION parameter can potentially minimize the possibility of receiving “snapshot too old” errors, it doesn’t guarantee that the database won’t overwrite older undo data that may be needed by a running transaction. You can move long-running batch jobs to a separate time interval when other programs aren’t running in the database to avoid these errors.
Regardless, while you can minimize the occurrence of “snapshot too old” errors with these
approaches, you can’t completely eliminate such errors without specifying the guaranteed undo retention feature. When you configure guaranteed undo retention in a database, no transaction can fail because of the “snapshot too old” error. Oracle will keep new DML statements from executing when you set up guaranteed undo retention. Implementing the guaranteed undo feature is simple. Suppose you want to ensure that the database retains undo for at least an hour (3,600 seconds). First set the undo retention threshold with the alter system command and then set up guaranteed undo retention by specifying the retention guarantee clause to alter the undo tablespace.
SQL> alter system set undo_retention=3600;
System altered.
SQL> alter tablespace undotbs1 retention guarantee;
Tablespace altered.
You can switch off guaranteed undo retention by executing the alter tablespace command with the retention noguarantee clause.
How It Works
Oracle uses the undo records stored in the undo tablespace to help roll back transactions, provide readconsistency, and to help recover the database. In addition, the database also uses undo records to read data from a past point in time using Oracle Flashback Query. Undo data serves as the underpinning for several Oracle Flashback features that help you recover from logical errors.
Occurrence of the Error
The ORA-01555 error (snapshot too old) may occur in various situations. The following is a case where the error occurs during an export.
EXP-00008: ORACLE error 1555 encountered
ORA-01555: snapshot too old: rollback segment number 10 with name "_SYSSMU10$" too small EXP-00000: Export terminated unsuccessfully
And you can receive the same error when performing a flashback transaction:
ERROR at line 1:
ORA-01555: snapshot too old: rollback segment number with name "" too small
ORA-06512: at "SYS.DBMS_FLASHBACK", line 37
ORA-06512: at "SYS.DBMS_FLASHBACK", line 70
The “snapshot too old” error occurs when Oracle overwrites undo data that’s needed by another transaction. The error is a direct result of how Oracle’s read consistency mechanism works. The error occurs during the execution of a long-running query when Oracle tries to read the “before image” of any changed rows from the undo segments. For example, if a long-running query starts at 1 a.m. and runs until 6 a.m., it’s possible for the database to change the data that’s part of this query during the period in which the query executes. When Oracle tries to read the data as it appeared at 1 a.m., the query may fail if that data is no longer present in the undo segments.
If your database is experiencing a lot of updates, Oracle may not be able to fetch the changed rows, because the before changes recorded in the undo segments may have been overwritten. The transactions that changed the rows will have already committed, and the undo segments don’t have a record of the before change row values because the database overwrote the relevant undo data. Since Oracle fails to return consistent data for the current query, it issues the ORA-01555 error. The query that’s currently running requires the before image to construct read-consistent data, but the before image isn’t
available.
The ORA-01555 error may be the result of one or both of the following: too many updates to the database or too small an undo tablespace. You can increase the size of the undo tablespace, but that doesn’t ensure that the error won’t occur again.
Influence of Extents
The database stores undo data in undo extents, and there are three distinct types of undo extents:
Active: Transactions are currently using these extents.
Unexpired: These are extents that contain undo that’s required to satisfy the undo retention time specified by the UNDO_RETENTION initialization parameter.
Expired: These are extents with undo that’s been retained longer than the
duration specified by the UNDO_RETENTION parameter.
If the database doesn’t find enough expired extents in the undo tablespace or it can’t get new undoextents, it’ll re-use the unexpired (but never an active undo extent) extents, and this leaves the door open for an ORA-01555, “snapshot too old” error. By default, the database will essentially shrink the undo retention period you specify, if it encounters space pressure to accommodate the undo from new transactions. Since the unexpired undo extents contain undo records needed to satisfy the undo retention period, overwriting those extents in reality means that the database is lowering the undo retention period you’ve set. Enabling the undo retention guarantee helps assure the success of long running queries as well as Oracle Flashback operations. The “guarantee” part of the undo retention guarantee is real—Oracle will certainly retain undo at least for the time you specify and will never overwrite any of the unexpired undo extents that contain the undo required to satisfy the undo retention period. However, there’s a stiff price attached to this guarantee—Oracle will guarantee retention even if it means that DML transactions fail because the database can’t find space to record the undo for those transactions. Therefore, you must exercise great caution when enabling the guaranteed undo retention capability.
No comments:
Post a Comment