ORA-01555 Fault Analysis

Today, the production database encountered a problem when performing a flashback query flash-back query,ORA-01555: Snapshot
Too old... The data cannot be flashed back... learn the lesson. In the installation documentation, you must adjust this parameter immediately after the database is installed.

My environment is that the database version is Oracle10g Release 2. Check the database parameter undo_retention settings and find that this parameter is set to 900 seconds (15 minutes) by default in 10 Gb ), this time is too short, and I forgot to modify it when installing it again. I will immediately change this parameter to for 3 hours:
Alter system set undo_retention = 10800 scope = both;

 

Why 3 hours?

Previously, the default value of this parameter was set to 10800. However, the Undo tablespace was extended and difficult to recycle. Oracle versions were constantly weighted and compromised.

Oracle may think like this:If flashback query is rarely used, and a large undo_retention may cause trouble, set it to a smaller value.

 

How can we understand the role of this parameter?

Link: http://blog.csdn.net/seagal890/article/details/3044226

Each database must have a method for managing rollback or data revocation. When a DML change has not been committed by the user, the user does not want the change to continue. The user needs to cancel the modification and roll back the data to a time before the change has occurred, in this case, you need to use Withdrawal record. With the Undo record, we can: 1. roll back the transaction when the rollback statement is used, undo the data changed by the DML operation 2. Restore the database 3. Provide read consistency 4. Use Oracle flashback query to analyze data from previous time points 5. Use Oracle flashback to recover data from logical faults automatic Revocation Management (AUM) in the database Oracle10g) you can configure parameters to automatically manage rollback segments in Oracle10g. To enable automatic space cancellation, you must specify the automatic cancellation mode in init. ora or the spfile file. Second, you need to create a dedicated tablespace to store the revocation information, which ensures that the user will not save the revocation information in the system tablespace. In addition, you need to select a retention period for revocation. To implement Aum, You need to configure the following three parameters: undo_mamagement UNDO_TABLESPACEUndo_retention:

SQL> show parameter undo_tablespace; Name type value =----------- interval undo_tablespace string undotbs1sql> show parameter undo_management; Name type value =----------- interval undo_management string autosql> show parameter undo_retention; name type value ------------------------------------ ----------- ------------------------- undo_retention integer 900sql>

Description of initialization parameters:

Initialization parameter	Description
Undo_management	If auto, use automatic undo management. The default is manual
Undo_tablespace	An optional dynamic parameter specifying the name of an undo tablespace. This parameter shocould be used only when the database has multiple Undo tablespaces and you want to direct the database instance to use a particle undo tablespace.
Undo_retention	The undo_retention parameter is ignored for a fixed size undo tablespace. the database may overwrite unexpired undo information when tablespace space becomes low. for an undo tablespace with the autoextend option enabled, the database attempts to honor the minimum retention period specified by undo_retention. when space is low, instead of overwriting unexpired undo information, the tablespace auto-extends. if Maxsize clause is specified for an auto-extending undo tablespace, when the maximum size is reached, the database may begin to overwrite unexpired undo information.

  If you set the initialization parameter undo_management to auto, Oracle10g will enable Aum.You can set the size of the undo_retention retention period in the initialization parameter undo_retention: undo_retention = 1800 set the retention period to 30 minutes (1800 seconds). The default value of the undo_retention parameter is 900 seconds. How many undo_retention values should be set? There is no ideal undo_retention interval. The retention interval depends on the estimated maximum length of time that a transaction may run. You can allocate an approximate time to undo_retention Based on the longest transaction length in the database. You can use the maxquerylen column in the V $ undostat view to query the longest query execution time in seconds in the past ). The time setting in the undo_retention parameter should be at least as long as the time given in the maxquerylen column. Oracle provides the following guidance for setting a time interval for revoking a new database: 1. OLTP system: 15 minutes 2. HYBRID: 1 hour 3. DSS System: 3 hours 4. Flashback query: the high value of the 24-hour undo_retention parameter does not guarantee the time specified by the undo_retention parameter. The retention grarantee clause must be used to ensure that the specified time is revoked. For example:

Create undo tablespace undotbs01datafile 'e:/Oracle/product/10.2.0/oradata/keymen/undotbs01.dbf' size 500 m autoextend onretention guarantee

You can also use the alter database command to ensure that the database is unretained.

Alter database undotbs01 retention guarantee

Disable guarantee of Information Revocation

Alter database undotbs01 retention noguarantee

Set the size of the Undo tablespace Oracle recommends that you set the size of the Undo tablespace with the help of Undo advisor. You can create an undo tablespace with a small size (about 500 mb) and set the autoextend data file attribute to on to allow automatic expansion of the tablespace. This tablespace will automatically grow to support the growth of the number of active transactions in the database and the growth of the transaction length. After the database runs properly for a period of time, you can use the Undo advisor to come up with suggestions on setting the tablespace size to be removed. Use the maximum time allowed in the analysis time period field. For this purpose, you can use the longest -- runing query length on the OEM undo Management page. You must also specify the value of the new undo retention field based on the flash back requirement. For example, if you want the table to flash back for 24 hours, you should use 24 hours as the value of this field. Assume that the retention guarantee clause is configured in the database to ensure that the retention is revoked. If the Undo tablespace is too small to meet all the active transactions that use it, the following situation occurs: 1. If the Undo tablespace is used up by 85%, oracle will release an automatic tablespace warning 2. When the Undo tablespace is used up by 97%, Oracle will release an automatic tablespace severe warning 3. All DML statements will not be allowed, and will receive a space exceeds error 4, DDL statements allow to continue to execute the undo_retention retention policy, the articles in the eygle more detailed description of the meaning of guarantee link: http://www.eygle.com/archives/2005/03/oracle10gaeundo.html no guaranteed:
That is to say, if other transactions need to roll back the space, and the space is insufficient, the information will still be overwritten.
In many cases, this is not expected to be seen.

Starting from Oracle10g, if you set undo_retention to 0, Oracle Enables automatic adjustment to meet the maximum query running requirements. Of course, if the space is insufficient, Oracle will meet the maximum allowed long-time queries. Instead of manual adjustments.

At the same time, Oracle has added guarantee control, that is, you can specify that the Undo tablespace must meet the undo_retention restrictions.

SQL> alter tablespace undotbs1 retention guarantee; Tablespace altered SQL> alter tablespace undotbs1 retention noguarantee; Tablespace altered The retention field is added to the dba_tablespaces view to describe this option: SQL> select tablespace_name,contents,retention from dba_tablespaces; TABLESPACE_NAME CONTENTS RETENTION ------------------------------ --------- ----------- SYSTEM PERMANENT NOT APPLY UNDOTBS1 UNDO NOGUARANTEE SYSAUX PERMANENT NOT APPLY TEMP TEMPORARY NOT APPLY USERS PERMANENT NOT APPLY 14 rows selected

This is explained in the Oracle official documentation:
RetentionUndo tablespace retention:
Guarantee-Tablespace is an undo tablespace with retention specified as guarantee

A Retention Value of guarantee indicates that unexpired undo in all undo segments in the Undo tablespace shocould be retained even if it means that forward going operations that need to generate undo in those segments fail.

Noguarantee-Tablespace is an undo tablespace with retention specified as noguarantee

Not apply-Tablespace is not an undo tablespace.