Monitor and manage the use of Oracle UNDO tablespace

Monitor and manage the use of Oracle UNDO tablespace

The monitoring and management of Oracle Database UNDO tablespaces is one of our most important tasks in daily life. The UNDO tablespaces are usually managed automatically by Oracle (determined by the undo_management initialization parameter ); the UNDO tablespace is the pre-image data used to store DML operations. It is an important component of the instance recovery, data rollback, and consistent query functions. We often ignore monitoring of it, this may cause the following problems in the UNDO tablespace:
1). the space usage is 100%, leading to the failure of DML operations.
2). There are a large number of ORA-01555 alarm errors in the alarm log.
3) The instance fails to be restored and the database cannot be opened normally.

--------------------------------------------------------------------------------

Undo tablespace loss caused by rman backup and recovery

About Oracle releasing over-used undo tablespace

Oracle undo

Oracle undo image data exploration

Oracle ROLLBACK and undo)

Installing Oracle 12C in Linux-6-64

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

--------------------------------------------------------------------------------

1. intervene in Oracle's automated UNDO management.

Since UNDO is automated management, there are very few interventions and more monitoring, some interventions can be performed on the UNDO tablespace in the following areas:

1). initialization parameters

Undo_management = AUTO indicates that the instance automatically manages the UNDO tablespace. Since Oracle 9i, Oracle has introduced AUM (Automatic Undo Management ).
Undo_retention = 900 retention time of the UNDO data after the transaction is committed, in seconds.
Undo_tablespace = UNDOTBS1 activity's UNDO tablespace.
_ Smu_debug_mode = 33554432
_ Undo_autotune = TRUE

2). Automatic UNDO Retention

Automatic UNDO Retention is a new feature of 10 Gb. This feature is enabled by default in databases of 10 GB and later versions.
When automatic undo management is enabled in Oracle Database 10 Gb, there is always a current undo retention. Oracle Database tries to retain the old undo information at least for this time. The database collects usage statistics and adjusts the UNDO retention time based on the statistics and the undo tablespace size.
Oracle Database automatically adjusts the undo retention based on the undo tablespace size and system activity. It specifies the minimum value of the undo retention by setting the UNDO_RETENTION initialization parameter.

You can view the Oracle Auto-adjusted undo retention value through the following query:
SELECT TO_CHAR (BEGIN_TIME, 'Mm/DD/YYYY HH24: MI: ss') BEGIN_TIME,
TUNED_UNDORETENTION from v $ UNDOSTAT;

For automatically expanded UNDO tablespaces, the system retains at least the time specified by the UNDO to the parameter, and automatically adjusts the undo retention to meet the query's UNDO requirements, which may cause the UNDO to expand rapidly, consider not setting the undo retention value.

For fixed UNDO tablespaces, the system automatically adjusts based on the maximum possible undo retention. For details, refer to adjust Based on the UNDO tablespace size and usage history. This will ignore UNDO_RETENTION unless the tablespace has retention guarantee enabled.

Automatic undo retention adjustment does not support LOB, because it cannot store any undo information about LOBs transactions in the UNDO tablespace.

You can disable the Automatic UNDO Retention function by setting _ undo_autotune = FALSE.

3) Does the value calculated by TUNED_UNDORETENTION cause the UNDO tablespace to grow fast?

When the UNDO tablespace used does not automatically increase, the usage of tuned_undoretention is calculated based on the UNDO tablespace size. In some cases, especially for large UNDO tablespaces, this will calculate a large value.

To solve this problem, set the following instance parameters:
_ Smu_debug_mode = 33554432

When this parameter is set, the usage of TUNED_UNDORETENTION is not calculated based on the undo tablespace size, instead of the maximum values of the settings (MAXQUERYLEN + 300) and UNDO_RETENTION.

4). Automatic expansion of UNDO tablespace data files

If the UNDO tablespace is an automatically scalable tablespace, it is very likely that the UNDO tablespace state to expired extent will not be used (to reduce the chance of reporting a ORA-01555 error ), this will cause the UNDO tablespace to become very large. If the UNDO tablespace is set to non-auto-scaling, the EXTENT in the EXPIRED state can be used to control the size of the UNDO tablespace to a certain EXTENT, but this increases the risk of ORA-01555 errors and UNDO space insufficiency errors. Reasonable UNDO tablespace sizes that are not automatically extended, and reasonable UNDO_RETENTION settings can ensure stable use of UNDO space.

5). UNDO tablespace guarantee attributes

If the UNDO tablespace is in the noguarantee state, Oracle does not ensure that the data in the UNDO tablespace corresponding to the committed transaction retains the specified duration of UNDO_RETENTION. If the UNDO tablespace is insufficient, other transactions may steal space that has not expired; setting the UNDO tablespace to guarantee ensures that the data in the committed transaction corresponds to the data in the UNDO tablespace retains the specified duration of UNDO_RETENTION under any circumstances.

SQL> SELECT tablespace_name, retention FROM dba_tablespaces where tablespace_name = 'undotbs1 ';

TABLESPACE_NAME RETENTION
----------------------------------------------------------------------------------
UNDOTBS1 NOGUARANTEE

SQL> alter tablespace undotbs1 retention guarantee;

The tablespace has been changed.

SQL> SELECT tablespace_name, retention FROM dba_tablespaces where tablespace_name = 'undotbs1 ';

TABLESPACE_NAME RETENTION
----------------------------------------------------------------------------------
UNDOTBS1 GUARANTEE

6). UNDO tablespace size

For different types of business systems, adequate UNDO tablespaces are required to ensure the normal operation of the system. The size of the UNDO space is related to the business system, as well as the UNDO_RETENTION and the GUARANTEE attribute of the UNDO tablespace. Generally, we can estimate the size of the required UNDO tablespace through the statistical information of V $ UNDOSTAT.

2. Monitor the usage of the UNDO tablespace.

As an administrator, the daily monitoring work is more important for the UNDO tablespace. The following views are commonly used for monitoring:
A). DBA_ROLLBACK_SEGS
DBA_ROLLBACK_SEGS describes rollback segments.

B). V $ ROLLSTAT
V $ ROLLSTAT contains rollback segment statistics.

C). V $ TRANSACTION
V $ TRANSACTION lists the active transactions in the system.

D). V $ UNDOSTAT
V $ UNDOSTAT displays a histogram of statistical data to show how well the system is working. the available statistics include undo space consumption, transaction concurrency, and length of queries executed in the instance. you can use this view to estimate the amount of undo space required for the current workload. oracle uses this view to tune undo usage in the system. the view returns NULL values if the system is in manual undo management mode.

Each row in the view keeps statistics collected in the instance for a 10-minute interval. the rows are in descending order by the BEGIN_TIME column value. each row belongs to the time interval marked by (BEGIN_TIME, END_TIME ). each column represents the data collected for the participating statistic in that time interval. the first row of the view contains statistics for the (partial) current time period. the view contains a total of 576 rows, spanning a 4 day cycle.

E). DBA_UNDO_EXTENTS
DBA_UNDO_EXTENTS describes the extents comprising the segments in all undo tablespaces in the database. This view shows the status and size of each extent in the undo tablespace.

DBA_UNDO_EXTENTS.STATUS has three values:
ACTIVE indicates the undo extent that is still in use for uncommitted transactions. The DBA_ROLL_SEGMENTS.STATUS of the undo segment corresponding to this value must be in the ONLINE or pending offline status. Once undo segment is used for unactive transactions, then the corresponding undo segment will change to the OFFLINE status.
EXPIRED indicates the undo extent that has been submitted and exceeds the specified time of UNDO_RETENTION.
UNEXPIRED indicates the undo extent that has been submitted but has not exceeded the specified time of UNDO_RETENTION.

The principle of repeated use of undo extent in Oracle is as follows:
1). The active extent will not be occupied under any circumstances.
2) If the UNDO tablespace is automatically extended, Oracle will ensure that EXTENT retains at least the time specified by UNDO_RETENTION.
3 ). if the automatic expansion space is insufficient or the UNDO tablespace is not automatically extended, Oracle will try to reuse the EXPIRED status EXTENT under the same segment. If this segment does not have such EXTENT, the EXTENT in the EXPIRED status under another segment will be stolen. If there is still no such EXTENT, the EXTENT of this segment of UNEXPIRED will be used. If there is still no EXTENT, in this case, the EXTENT of UNEXPIRED in other segments will be stolen. If no EXTENT exists, an error will be reported.

For more details, please continue to read the highlights on the next page:

1 2 Next Page