Oracle UNDO monitoring

Oracle UNDO monitoring

Oracle 10 GB and later versions have a new feature, that is, automatic adjustment of the undo retention time, which greatly simplifies management. For the undo tablespace of autoextend on, the undo_retention parameter is set to the minimum threshold for Oracle to automatically adjust undo retention. For undo tablespaces of non-auto Scaling (autoextend off) and non-guarantee, oracle will retain undo information based on the undo tablespace size and the historical information of v $ undostat (whether to count undo information is determined by the implicit parameter _ collect_undo_stats, which is TRUE by default. To minimize errors like ORA-01555. In this case, undo retention is useless. By default, _ UNDO_AUTOTUNE = TRUE. Enable the automatic optimization function of UNDO. The optimized undo retention can be seen in the TUNED_UNDORETENTION of V $ UNDOSTAT. Generally, oracle writes an unod tablespace usage record to V $ UNDOSTAT every 10 minutes, including TUNED_UNDORETENTION.

Of course, this feature is controlled by the implicit parameter _ undo_autotune. By default, it is set to TRUE. In some special cases, it is set to FALSE, for example, startup upgrade.
If the parameter is set to false, oracle does not adjust the undo retention Size Based on the tablespace size and so on, and the undo retention setting hours are prone to ora-01555 errors. For example, the tablespace is large enough, but the ora-01555 still appears.

In Oracle 10 Gb, you can use the V $ UNDOSTAT view to monitor the UNDO tablespace used by the current transaction in the instance. Each row in the view lists the statistics collected from the instance every ten minutes. Each row indicates the usage of the UNDO tablespace, the transaction volume, the query length, and other statistical snapshots every 10 minutes in the past 7*24 hours.
The usage of the UNDO tablespace varies with the transaction volume. Generally, we will also refer to the average usage and peak usage of the UNDO tablespace During computation.

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

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

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

The following SQL statement is used to calculate the average usage of the UNDO tablespace in the past 7x24 hours:
Col UNDO_RETENTION for a15
Col DB_BLOCK_SIZE for a15
Select ur undo_retention, dbs db_block_size, (ur * (ups * dbs) + (dbs * 24)/1024/1024 as "M_bytes"
From (select value as ur from v $ parameter where name = 'undo _ retention '),
(Select (sum (undoblks)/sum (end_time-begin_time) * 86400) ups from v $ undostat ),
(Select value as dbs from v $ parameter where name = 'db _ block_size ');

The following SQL statement calculates the space required for the UNDO tablespace Based on the peak value:
Col UNDO_RETENTION for a15
Col DB_BLOCK_SIZE for a15
Select ur undo_retention, dbs db_block_size, (ur * (ups * dbs) + (dbs * 24)/1024/1024 as "M_bytes"
From (select value as ur from v $ parameter where name = 'undo _ retention '),
(Select (undoblks/(end_time-begin_time) * 86400) ups from v $ undostat where undoblks in (select max (undoblks) from v $ undostat )),
(Select value as dbs from v $ parameter where name = 'db _ block_size ');

Note that due to RAC, there are generally two UNDO tablespaces, depending on the actual situation of the two instances to execute the above query.
In general, to maintain the normal operation of daily business as much as possible, we recommend that you estimate and allocate the size of the UNDO tablespace Based on the peak value, although there is a waste of storage space, however, the problem caused by insufficient UNDO tablespaces can be avoided.

At the same time, we can also use the DBA_UNDO_EXTENTS view to monitor the usage of the UNDO tablespace in Real Time:
Select sum (bytes)/1024/1024 MB, status, tablespace_name
From dba_undo_extents
Group by status, tablespace_name order by 3, 2;

This query returns the amount of space used for STATUS rollback information in the STATUS group. There are generally three STATUS states: EXPIRED, UNEXPIRED, and ACTIVE. ACTIVE indicates that the transaction-related rollback information is still ACTIVE. UNEXPIRED indicates that the rollback information is not retained for more than the value set by the instance parameter UNDO_RETENTION, EXPIRED indicates that the rollback information retention time has exceeded the value set by UNDO_RETENTION.

When the guarantee option is not enabled for the UNDO tablespace (current usage), the rollback space allocation for new Transactions follows the following principles:
A) Find a rollback segment that does not have an ACTIVE interval. If no, create a new rollback segment. If the space cannot generate a new segment, an error is returned.
B) if a rollback segment is selected, but the idle space is not enough to store the rollback information of the transaction, it will try to create a range. If there is no space on the tablespace, next step.
C) if the Inter-region creation fails, it will search for the EXPIRED range in other rollback segments and reuse them.
D) if there is no EXPIRED range in other rollback segments, it will continue to search for the UNEXPIRED range in other rollback segments and reuse it, note that transactions do not reuse the UNEXPIRED range in This rollback segment. Therefore, the rollback space of UNEXPIRED can only be reused by Oracle. If you still cannot obtain the required space, an error is returned.

When we observe that the ACTIVE rollback information occupies a large amount of space, it indicates that the system is currently running a busy transaction. Because the guarantee option of the UNDO tablespace is not enabled currently, the full rollback space of EXPIRED and part of the rollback space of UNEXPIRED can be reused by Oracle, during real-time monitoring, you can mainly observe the space used by the ACTIVE state rollback information.

When the system-related services remain unchanged, We can optimize the UNDO tablespace configuration to the maximum extent by calculating the peak usage of the UNDO tablespace. When the system is in the business adjustment stage, when new business is added or the business period is adjusted, You need to monitor the usage of the UNDO tablespace in real time to meet the dynamic adjustment requirements.

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

1 2 Next Page