Oracle UNDO monitoring and oracleundo monitoring

Oracle UNDO monitoring and oracleundo 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.
 
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.
 
The following is the script for monitoring UNDO.
V $ undostat
Select INST_ID, to_char (BEGIN_TIME, 'yyyy/MM/DD HH24: MI: ss') "begin time", END_TIME, UNDOBLKS, TXNCOUNT, UNXPBLKRELCNT, ACTIVEBLKS, keys, EXPIREDBLKS
From gv $ undostat order by 2;
 
 
View which undo segment is being used by a transaction
Select s. sid, s. serial #, s. SQL _id, v. usn, segment_name, r. status, v. rssize/1024/1024 mb
From dba_rollback_segs r, v $ rollstat v, v $ transaction t, v $ session s
Where r. segment_id = v. usn and v. usn = t. xidusn and t. addr = s. taddr
Order by segment_name;
 
 
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 ');
 
 
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 ');
 
 
Various extent conditions in UNDO
Select sum (bytes)/1024/1024 MB, status, tablespace_name
From dba_undo_extents
Group by status, tablespace_name order by 3, 2;
 
 
View the size of undo segemcnt dba_segments
Col SEGMENT_NAME for a30
Col OWNER for a8
Col TABLESPACE_NAME for a20
Select tablespace_name, owner, segment_name, bytes/1024/1024 mb
From dba_segments where tablespace_name like 'undotbs % ';
 
 
View undo segemcnt size v $ rollstat
Select segment_name, v. rssize/1024/1024 mb
From dba_rollback_segs r, v $ rollstat v
Where r. segment_id = v. usn (+)
Order by segment_name;
 
 
Undo segemnt extent info
Select segment_name, tablespace_name, r. status,
(Initial_extent/1024) InitialExtent, (next_extent/1024) NextExtent,
Max_extents, v. curext CurExtent
From dba_rollback_segs r, v $ rollstat v
Where r. segment_id = v. usn (+)
Order by segment_name;
 
 
View the UNDO status of a transaction
Select s. sid, s. serial #, t. XIDUSN, t. STATUS, t. USED_UBLK
From v $ transaction t, v $ session s
Where t. addr = s. taddr;
 
 
View the UNDO details of a transaction
Set lines 199
Col STATUS for a8
Col USERNAME for a6
Col name for a25
Col substr (s. program, 1, 78) for a30
SELECT r. name,
D. tablespace_name,
S. sid,
S. serial #,
S. username,
T. status,
T. cr_get,
T. phy_io,
T. used_ublk,
T. noundo,
Substr (s. program, 1, 78)
FROM sys. v _ $ session s, sys. v _ $ transaction t, sys. v _ $ rollname r, Dba_Rollback_Segs d
WHERE t. addr = s. taddr
And t. xidusn = r. usn
AND d. segment_name = r. name
Order by t. cr_get, t. phy_io;
 
 
 
 
 
 
 
What is the Oracle Undo mechanism?

1. The UNDO tablespace is used to store UNDO data. When you perform DML operations, Oracle writes the old data of these operations to the UNDO segment. You can use not only rollback segments but also UNDO tablespaces to manage UNDO data.
2. UNDO data: when the user executes the DML operation to modify data, the UNDO data is stored in the UNDO segment, and the new data is stored in the data segment. If the transaction operation has problems, you need to roll back the transaction to cancel transaction changes.
For example, after executing UPDATE emp SET sal = 1000 WHERE empno = 7788, the employee's salary should be changed to 7963 instead of 7788. At this time, the ROLLBACK statement should be executed.
3. read consistency
When a user retrieves data, ORACLE always enables the user to see only submitted data, which is automatically provided by Oracle. When the user modifies the data but does not submit the data, another user uses the select statement to find the value, which is obtained from the undo tablespace.
4. transaction recovery
Transaction recovery is a part of routine recovery, which is automatically completed by the Oracle Server. If the qualifying process fails during database operation, the background process SMON automatically executes routine recovery when Oracle Server is started. When the routine is restored, Oracle retries all records that are not applied. Open the database and roll back uncommitted transactions.
5. Flashback Query
Flashback query is used to obtain database data at a specific time point.
6. UNDO_MANAGEMENT
The initialization parameters are used to specify the UNDO data management mode. If you use the automatic management mode, you must set this parameter to AUTO, and then use the UNDO tablespace to manage UNDO data. If you use the MANUAl management mode, you must set this value to MANUAl, the rollback segment is used to manage UNDO data.
7. UNDO_TABLESPACE
Used to specify the UNDO tablespace used by the routine. When the automatic UNDO management mode is used, you can configure this parameter to specify the UNDO tablespace to be used by the routine.
When using the RAC structure, you must configure an independent UNDO tablespace for each routine.
8. UNDO_RETENTION
This parameter is used to control the maximum retention time of UNDO data. The default value is 900 seconds. If this parameter is set, the earliest time point can be viewed in reverse query.
9. No data objects can be created on the UNDO tablespace.

How can we estimate the oracle undo tablespace? How big is the design?

UndoSpace = UR * (UPS * DBS)
UR: undo_retention Value
UPS: Number of undo data blocks generated per second
DBS: db_block_size

Undo tablespace size:
SELECT (UR * (UPS * DBS) AS "Bytes"
FROM (SELECT value as ur from v $ parameter WHERE name = 'undo _ retention '),
(SELECT undoblks/(end_time-begin_time) * 86400) AS UPS
FROM v $ undostat
WHERE undoblks = (select max (undoblks) FROM v $ undostat )),
(SELECT block_size AS DBS
FROM dba_tablespaces
WHERE tablespace_name = (select upper (value) FROM v $ parameter WHERE name = 'undo _ tablespace '));

If you have any help, please adopt