[Oracle] Understanding undo Table Space

First, back paragraph introduction

In an Oracle database, when something modifies the data, Oracle first saves the original value of the data to a fallback segment. A thing can only save its fallback information to a fallback segment, while multiple parallel objects may use the same fallback segment.

(1) The role of the fallback section

There are 4 main functions in the fallback section: Rollback of things, database recovery, read consistency, and flashback query.

--Thing rollback: When a thing fails or the user performs a rollback operation (rollback), Oracle restores the data to its original value using the information saved in the fallback segment;

--Database recovery: When the database instance fails to run, Oracle recovers the database (both committed and uncommitted transactions) using the information from the Redo log file, and then uses the information in the rollback segment to roll back the uncommitted transactions;

--Read consistency: When a user modifies the data, the original value is saved to the fallback segment beforehand, and if there are other users accessing the data, the information in the fallback segment is accessed, so that the current user does not commit the modification other users can not see, to ensure the consistency of the data;

--Flashback query: By keeping the information in the fallback segment, the user can query the state of a data at some point in the past

(2) How the fallback section works

When a transaction begins, the system assigns a fallback segment to the thing, and the original value of the data is copied to the fallback segment throughout the transaction's life cycle, when the data changes. The fallback segment works in a circular write manner, and when the transaction is written back to a section of the fallback segment, it is then written to the next area of the fallback segment, and when all the extents are full, the transaction begins to loop to the first zone or allocate a new zone (DataFile is autoextend). The fallback segment is owned by user Sys, and each fallback segment contains at least 2 extents.

Second, the management of the fallback table space

(1) Create undo table Space

In Oracle 11g, there are 2 ways to create an undo tablespace, one to create an undo tablespace when creating a database, and one to create an undo tablespace using the Create undo tablespace. Undo Tablespace is used to hold the fallback information for a transaction in which the user cannot create the database object.

Here we describe how to use Craete undo Tablespace to create an undo table space.

-- using the Create undo Tablespace

CREATE [Bigfile | Smallfile]UNDO tablespace tbs_namedatafile'Path/filename'SIZEinteger [K | M] [Reuse][Autoextend] [OFF | on] NEXT integer [K | M]MAXSIZE[UNLIMITED | integer [K | M] ][EXTENT MANAGEMENT LOCAL] [autoallocate][RETENTION GUARANTEE | Noguarantee]

(2) Modify undo table Space

You can use ALTER TABLESPACE to modify the undo tablespace, allowing you to do the following with the undo table space:

--Add a data file for the undo table space;

--Rename the data file of the undo table space;

--Bring the data file of the Undo table space online or offline;

--Enable or disable the retention time of the protection fallback information in the fallback segment;

Example 1, when the undo table space capacity is insufficient, you can consider adding new data files or changing the size of the data file

-- add a new data file for undo Tablespace Alter Add ' /home/app/oracle/oradata/orcl/untbs02.dbf ' size 50M; -- expands the untbs02.dbf file inside the undo tablespace to 100M Alter Database ' /home/app/oracle/oradata/orcl/untbs02.dbf ' resize 100M;

(3) Delete undo table Space

As with normal table spaces, you can use drop tablespace to delete the undo tablespace, but you cannot delete the Undo table space that is currently in use. If you have fallback information for any uncommitted transactions in the Undo table space, you cannot use the drop tablespace to delete the table space. In addition, even if the undo tablespace has been deleted using drop tablespace, there may be non-expiring fallback information in that tablespace, which causes the fallback information required for some queries to be lost. Therefore, when deleting, you should be careful not to delete such undo table space.

(4) Toggle Undo Table Space

While the database is running, you can switch from one undo table space to another, because the initialization parameter undo_tablespace is a dynamic parameter, directly modified, no need to restart the instance, the switch mode is as follows:

ALTER SET = Undotbs_name

In the following situations, an error occurs when the Undo table space is toggled:

--The specified undo table space does not exist;

--The specified tablespace is not an undo table space;

--The specified undo table space is being used by another instance;

After the undo tablespace is switched, any new fallback information goes into the new undo tablespace, and if there are uncommitted transactions in the old undo Tablespace, the old undo table space goes into pending offline state, and the undo tablespace for pending offline cannot be used by new transactions It cannot be deleted, and the currently uncommitted transaction will continue to use the tablespace. When all transactions are committed, the old undo table space goes offline.

Example 2, toggle undo Table Space

-- Toggle Undo Table Space -Experimental purpose:

--   1. Learn to toggle Undo Table Space

--2. Learn to delete undo table space A: +: -Sql>Show Parameter Undo_tablespace--window 1 View the current undo table SpaceNAME TYPE VALUE------------------------------------ ----------- ------------------------------Undo_tablespace string UNDOTBS1 A: $: -Sql> Insert  intoDeptValues( -,'a','b');--"Window 1" executes an INSERT statement without committing1Row Insertedsql> AlterSystemSetUndo_tablespace=UNDOTNS2;--"Window 2" toggle Undo Table SpaceSystem Altered SQL>Show parameter undo_tablespace;--window 2 View new tablespaceNAME TYPE VALUE------------------------------------ ----------- ------------------------------Undo_tablespace string Undotns2sql> DropTablespace UNDOTBS1 including contents anddatafiles;--"Window 2" delete old tablespace, error, hint old tablespace is in useORA-30013: Undo Tablespace'UNDOTBS1'  isCurrentlyinch  Use A: -: GenevaSql> Commit;--"Window 1" Commit TransactionSQL> DropTablespace UNDOTBS1 including contents anddatafiles;--"Window 2" has been successfully deleted after a period of timeTablespace dropped

(5) Fallback information retention time

We've talked about the 4 effects of fallback information, and when we commit a transaction, the fallback information is no longer working for transaction rollback and database recovery. However, for a long transaction, it has the function of read consistency, which guarantees that the information queried is always the old information. In addition, the implementation of the various flashback operations of the database also requires information in the fallback segment. Oracle automatically adjusts the retention time of the fallback information based on the size of the undo table space and how much it is, and you can set the retention time of the fallback information in the fallback segment by adjusting the initialization parameter undo_retention:

-- set the fallback information retention time to 1800s SQL > ALTER SET = 1800 ;

It is important to note that undo_retention is a "soft setting" for Oracle, How does this "soft setting" work? For example, when the user sets the Undo_retention parameter to 1800s, Oracle will try to save the fallback information 1800s, but in this process, if the fallback table space is not enough, The new fallback information will still overwrite the fallback information that is not up to 1800s.

In order to guarantee the read consistency of long queries and various flashback operations, we can also specify that fallback information must be retained to undo_retention time, by enabling the retention guarantee feature of the undo tablespace to ensure that only expired (committed and reached undo _retention set value) will not overwrite, even if the undo tablespace capacity is insufficient, does not overwrite the non-expiring fallback information.

-- Enable Retention_guarantee  >ALTER tablespace UNDOTBS1 RETENTION GUARANTEE;

Iii. querying the Undo table space

The data dictionary associated with the undo table space is as follows:

Data dictionary	Explain
V$undostat	Contains statistics for all undo tablespace to monitor and adjust the undo table space. With this view, you can estimate the size of the current undo table space, which Oracle uses to automate the management of fallback information, which is made up of a statistical record every 10 minutes in the last 4 days.
V$rollstat	Contains performance statistics for fallback segments in the Undo table space
V$transaction	Contains the fallback segment information used by the transaction
Dba_undo_extents	Contains the size and status information for the undo table Space Central
Dba_hist_undostat	Contains snapshots of V$undostat, mainly 4 days ago statistics

Example 3, querying the current state of fallback information in the Undo table space

Sql> SelectTablespace_name,segment_name,extent_id,status fromdba_undo_extents; Tablespace_name segment_name extent_id STATUS------------------------------ ------------------------------ ---------- ---------UNDOTBS3 _syssmu10_968665341$0UNEXPIREDUNDOTBS3 _syssmu10_968665341$1EXPIREDUNDOTBS3 _syssmu9_3484649867$0UNEXPIREDUNDOTBS3 _syssmu9_3484649867$1EXPIRED .......

Undo table Space The state of Central has a total of 3 kinds: EXPIRED, unexpired, active.

--expired: indicates that the transaction corresponding to the fallback information has been committed and the save time exceeds the reserved area;

--unexpired: indicates that the transaction corresponding to the fallback information has been committed and the save time has not exceeded the reserved area;

--active: indicates that the transaction corresponding to the fallback information has not been submitted, the area is still in use;

[Oracle] Understanding undo Table Space