Functions of UNDO data in Oracle 10 GB and operations on UNDO tablespace

Oracle 10gMediumFunctions of UNDO dataAndUNDO tablespaceThe common operation is what we will introduce in this article. UNDO data is also called ROLLBACK data, which is used to ensure data consistency. when a DML operation is executed, the data before the transaction operation is called an UNDO record. the UNDO segment is used to save the old value of the data modified by the transaction, where the location of the modified data block and the data before modification are stored.

The role of UNDO data:

1. roll back the transaction

When you perform the DML operation to modify the 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, the old transaction needs to be rolled back, to cancel transaction changes. assume that user A executes the statement UPDATE emp SET sal = 1000 WHERE empno = 7788 and finds that the employee's salary should be changed to 7963 instead of 7788, you can cancel transaction changes by executing the ROLLBACK statement. when the ROLLBACK command is executed, oracle writes UNDO data 800 of the UNDO data segment back to the data segment.

2. read consistency

When a user retrieves database data, oracle always uses the data that the user can only see submitted data (read submitted) or specific time point (SELECT statement time point ). this ensures data consistency. for example, when user A executes the UPDATE emp SET sal = 1000 WHERE empno = 7788 statement, the UNDO record is stored in the rollback segment, the new data is stored in the EMP segment. Assume that the data has not been submitted yet, and user B executes SELECT sal FROM emp WHERE empno = 7788. In this case, user B obtains the UNDO data 800, the data is obtained in the UNDO record.

3. transaction recovery

Transaction recovery is a part of routine recovery, which is automatically completed by the oracle server. if a routine failure (such as power failure, memory failure, and background process failure) occurs during database operation, the background process SMON automatically executes routine recovery when the oracle server is restarted, when a routine is executed for restoration, the distinct l will re-create all unapplied records. roll back uncommitted transactions.

4. Reverse Query (FlashBack Query)

Flashback query is used to obtain database data at a specific time point. It is a newly added feature of 9i. It is assumed that the current time is a.m, A user executes the UPDATE emp SET sal = 3500 WHERE empno = 7788 statement at AM, modifies and submits the transaction (the employee's original salary is 3000). In order to get the employee's salary before, you can use flashback to query features.

Use UNDO Parameters

1. UNDO_MANAGEMENT

This initialization parameter is used to specify the UNDO data management mode. to use the automatic management mode, you must set this parameter to AUTO. If you use MANUAL management mode, you must set this parameter to MANUAL, oracle uses undo tablespace management undo management. In manual management mode, oracle uses rollback segments to manage undo data. Note that when using automatic management mode, if the initialization parameter UNDO_TABLESPACE is not configured, oracle automatically selects the first available UNDO tablespace to store UNDO data. If no available UNDO tablespace is available, oracle uses the SYSTEM rollback segment to store UNDO records, and the warning is recorded in the ALTER file.

2. UNDO_TABLESPACE

This initialization parameter is used to specify the UNDO tablespace to be used by the routine. When using the automatic UNDO management mode, you can configure this parameter to specify the UNDO tablespace to be used by the routine.

In the RAC (Real Application Cluster) structure, because an UNDO tablespace cannot be used by multiple routines at the same time, each routine must be configured with an independent UNDO tablespace.

3. UNDO_RETENTION

This initialization parameter is used to control the maximum retention time of UNDO data. Its default value is 900 seconds. From 9i, you can specify the retention time of undo data by configuring this initialization parameter, so as to determine the earliest time point that can be viewed by the Flashback Query feature (Flashback Query.

Create an UNDO tablespace

The UNDO tablespace is used to store UNDO data, and no Data Objects (tables, indexes, and clusters) can be created in the UNDO tablespace)

1. Use the create database Command to CREATE the UNDO tablespace.

When you use the create database Command to CREATE a DATABASE, you can CREATE an undo tablespace by specifying the undo tablespace option. The example is as follows:

 
 

  
  
CREATE DATABASE db01  
  
  
…  
  
  
UNDO TABLESPACE undotbs_01  
  
  
DATAFILE '/opt/oracle/oradata/ge01/UNDOTBS1.dbf' SIZE 30M; 
 
 

Note: The undo tablespace clause is not mandatory. If the automatic UNDO management mode is used and this clause is not specified, the undo tablespace named UNDOTBS1 is automatically generated when the database is created.

2. Use the create undo tablespace command to CREATE an undo tablespace.

 
 

  
  
create undo tablespace undotbs2 datafile '/opt/oracle/oradata/ge01/UNDOTBS2.dbf' size 100m reuse autoextend on next 50m maxsize 5000m; 
 
 

Modify UNDO tablespace

Use the alter tablespace command to modify the undo tablespace.

When the transaction uses up the undo tablespace, use alter tablespace... Add datafile to ADD data files.

When the disk of the undo tablespace is full, use alter tablespace... Rename datafiel command to move data files to other disks.

Use alter database... OFFLINE/ONLINE: OFFLINE/ONLINE tablespaces.

When the database is in ARCHIVELOG mode, use alter tablespace... The begin backup/end backup command backs up the UNDO tablespace.

Switch UNDO tablespace

After the routine is started and the database is opened, a specific routine can only use one UNDO tablespace at a time. Switching an UNDO tablespace refers to the UNDO tablespace currently used by the stop routine and starting other UNDO tablespaces, the following describes how to switch the undotbs2 tablespace.

Alter system set undo_tablespace = undotbs02;

In the RAC (Real Application Cluster) mechanism, different routines must use independent UNDO tablespace instead of the same UNDO tablespace.

Delete UNDO tablespace

The UNDO tablespace being used by the current routine cannot be deleted. If you are sure to delete the UNDO tablespace being used by the current routine, switch to the UNDO tablespace first, and then delete the corresponding UNDO tablespace.

Drop tablespace undotbs3;

1. Determine the UNDO tablespace used by the current routine: Show parameter undo_tablespace

2. display all UNDO tablespaces of the database: SELECT tablespace_name FROM dba_tablespaces WHERE contents = 'undo ';

3. display the UNDO tablespace statistics

When using the automatic UNDO management mode, you need to properly set the size of the UNDO tablespace. For example, to reasonably plan the size of the UNDO tablespace, you should collect the statistics of the UNDO tablespace during the peak hours of database operation. finally, the size of the UNDO tablespace is determined based on the statistics. you can collect UNDO statistics by querying the dynamic performance view V % UNDOSTAT.

 
 

  
  
SELECT TO_CHAR(BEGIN_TIME,'HH24:MI:SS') BEGIN_TIME,  
  
  
TO_CHAR(END_TIME,'HH24:MI:SS') END_TIME,UNDOBLKS FROM V$UNDOSTAT; 
 
 

BEGIN_TIME indicates the start statistical time, END_TIME indicates the end statistical time, And UNDOBLKS indicates the number of data blocks occupied by UNDO data. oracle generates a row of statistical information every 10 minutes.

4. display the UNDO segment statistics.

When the automatic UNDO management mode is used, oracle automatically creates 10 UNDO segments in the UNDO tablespace. by querying the dynamic information view V $ ROLLNAME, you can display the names of all online UNDO segments, you can query the dynamic performance view V $ ROLLLISTAT to display the statistics of the UNDO segment. by performing connection queries between V $ ROLLNAME and V $ ROLLLISTAT, You can monitor specific information of specific UNDO segments.

 
 

  
  
SELECT a.name, b.xacts, b.writes, b.extents   
  
  
FROM v$rollname a, v$rollstat b WHERE a.usn=b.usn; 
 
 

Name is used to identify the UNDO segment Name, xacts is used to identify the number of active transactions contained in the UNDO segment, Writes is used to identify the number of bytes written on the undo segment, extents identifies the number of UNDO segments.

5. display the active transaction information.

When a DML operation is performed, oracle places the old data of the operation into the UNDO segment. The dynamic performance view v $ session is used to display session details, dynamic Performance view v $ transaction is used to display transaction details. Dynamic Performance view v $ rollname is used to display the name of online UNDO segments. by performing a connection query between the three dynamic performance views, you can determine the sessions in which transaction operations are being performed, the UNDO segments used by the transaction, and the number of UNDO blocks occupied by the transaction.

 
 

  
  
Col username format a10  
  
  
Col name format a10  
  
  
SELECT a.username, b.name, c.used_ublk   
  
  
FROM v$session a, v$rollname b, v$transaction c   
  
  
WHERE a.saddr=c.ses_addr AND b.usn=c.xidusn AND a.username='gedb'; 
 
 

6. Display UNDO zone information

Data Dictionary view dba_undo_extents is used to display detailed information about all zones in the UNDO tablespace, including the size and status of the UNDO tablespace.

 
 

  
  
SELECT extent_id, bytes, status   
  
  
FROM dba_undo_extents WHERE segment_name’_SYSSMU5$’; 
 
 

Extent_id is used to identify the area number, bytes is used to identify the area size, and status is used to identify the area status (ACTIVE: indicates that the area is ACTIVE, EXPIRED: indicates that the area is not used ).

Common columns in V $ ROLLSTAT

USN: rollback segment ID

RSSIZE: Default size of rollback segments

XACTS: Number of active transactions

Columns used for incremental operations within a period of time

WRITES: Number of WRITES to the rollback segment (unit: bytes)

SHRINKS: Number of rollback segments contracted

EXTENDS: Number of extended rollback segments

WRAPS: Number of wrap

GETS: Get the number of rollback segment Headers

WAITS: Number of rollback segment headers waiting

Here is an introduction to the role of UNDO data in Oracle 10 GB and the operation of UNDO tablespace. I hope this introduction will help you gain some benefits!