How to Use the UNDO_RETENTION parameter in Oracle 10 Gb Database

Oracle 10gDatabaseUNDO_RETENTIONThe usage of parameters is the main content we will introduce in this article. Next we will start to introduce them. We know that each database requires a method for managing rollback or data revocation. After a DML change occurs, the user has not submitted a COMMIT change. If the user does not want this change to continue, the user needs to cancel the modification and roll back the data to a time before the change, in this case, you need to use a data called the Undo record.

To revoke a record, we can:

1. When a ROLLBACK statement is used, roll back the transaction and cancel the data changed by the DML operation.

2. Restore the database.

3. read consistency is provided.

4. Use Oracle Flashback Query to analyze data based on previous time points.

5. Use the Oracle Flashback feature to restore the database from a logical fault.

Automatic Revocation Management AUM in 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_TABLESPACE

UNDO_RETENTION

View the initialization parameter settings:

SQL> show parameter undo_tablespace; NAME TYPE VALUE ---------------------------------------------------------------------- Undo_tablespace string UNDOTBS1 SQL> show parameter undo_management; NAME TYPE VALUE ---------------------------------------------------------------------- Undo_management string AUTO SQL> show parameter undo_retention; NAME TYPE VALUE ---------------------------------------------------------------------- Undo_retention integer 900 SQL>

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 the 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 revocation retention time in the initialization parameter UNDO_RETENTION:

UNDO_RETENTION = 1800 set the retention time to 30 minutes and 1800 seconds ).

The UNDO_RETENTION parameter is set to 900 seconds by default.

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 maximum 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. Mixing: 1 hour

3. DSS System: 3 hours

4. Flashback query: 24 hours

The high value of the UNDO_RETENTION parameter does not guarantee the time specified by the UNDO_RETENTION parameter for UNDO_RETENTION. The retention grarantee clause must be used to ensure that the specified time is revoked.

For example:

 
 

  
  
CREATE UNDO TABLESPACE UNDOTBS01  
  
  
DATAFILE  
  
  
‘E:/oracle/product/10.2.0/oradata/keymen/UNDOTBS01.DBF’  
  
  
SIZE 500M AUTOEXTEND ON  
  
  
RETENTION GUARANTEE 
 
 

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

Alter database UNDOTBS01 retention guarantee.

Disable the guarantee of Information revocation.

Alter database UNDOTBS01 retention noguarantee.

Set the size of the Undo tablespace

Oracle recommends that you use Undo Advisor to set the size of the tablespace to be removed. You can create an undo tablespace with a small size of about 500 mb. The AUTOEXTEND data file attribute is set 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 issue an automatic tablespace warning.

2. Oracle will issue a severe warning about automatic tablespace when the Undo tablespace is used up by 97%.

3. All DML statements are not allowed, and an error in space exceeding is received.

4. DDL statements allow continued execution.

The above is all about the use of the UNDO_RETENTION parameter in Oracle 10g. We will introduce it here and hope this introduction will help you gain some benefits.