Undo Table Space

The role of undo

Use the undo tablespace to hold the front image of a block of data read from a data file, providing the information required for the following 4 scenarios:

1. Rolling back things: Rollback

2. Read consistency: When a long query is issued, you may need to undo constructing the CR block if you need to save the pre-image in Undo

3. Instance recovery: Instance Recovery (undo->rollback)

4. Flash back technology: Flashback query, Flashback table, etc.

Management mode of Undo

1.manaual Manual: Roll Segment (OBSOLETE)

2.auto Auto: Undo tablespace (Init Parameter:undo_management=auto)

To see which tablespace is activated by the undo table space

Sql>show parameter Undo

Viewing the Cong under the Undo table space

Sql>select * from V$rollname;

Undo Table Space Management

1. Multiple undo table spaces can be created, but only one at a time is active;

2. Undo Tablespace in Active state cannot offline and drop;

Sql>select tablespace_name,status,contents from Dba_tablespace;

Create undo Tablespace: Starting with 100M, the space is not enough to automatically expand until the disk is blown up (generally to make the maximum space limit for auto-expanded tablespace)

Sql>create undo tablespace undotbs2 DataFile '/u01/oradata/timran11g/undotbs02.dbf ' size 100m autoextend on;

The original undotbs01.dbf is in the active state, and the newly added undotbs02.dbf is standby:

Sql>select tablespace_name,status,contents from Dba_tablespace;

To toggle the Undo table Space:

Sql>alter system set undo_tablespace= ' UNDOTBS2 ';

To delete the Undo table space:

The Undo table space in the active state cannot be deleted:

Sql>drop tablespace UNDOTBS2 including contents and datafiles;

The Undo tablespace in an inactive state can be deleted:

Sql>drop tablespace undotbs1 including contents and datafiles;

4 states of the undo table space

1.active: Indicates that transaction (things) have not been commit, not covered;

2.unexpired: Commit, but also in undo_retention (retention: unit s), can not overwrite (non-mandatory), add guarantee property after forcing Undo_retention not overwrite. (Things have been committed, but retained for some time: 1. Guaranteed consistency of reading; 2. Flashback)

Sql>show parameter Undo

Sql>select tablespace_name,status,contents,retention from Dba_tablespaces;

To set the Guarantee property for a table space:

Sql>alter tablespace UNDOTBS2 Retention guarantee;

3.expired: Commit, and time exceeds undo_retention, ready to overwrite

4.free: Assigned but not used

Undo_retention parameters and undo Autoextend on Properties

Undo_retention: The parameter specifies the retention period of the unexpired commit data, which is the key to ensuring consistent read and the success of most flashback technologies.

After 10r2, the Undo Auto Tuning (AUM) is enabled as long as the undo_management parameter is auto,oracle, which automatically adjusts undo_retention based on the longest query and revocation build rate in the collected database. The addition of a field tuned_undoretention (calculated every 10 minutes) in view V$undostat is an automatic tuning of the undo_retention's pre-estimate. If you want to turn off AUM, you can set the regret parameter _undo_autotune-false.

Set the Undo tablespace to Autoextend on, which is the default setting when DBCA creates the database, a feature that will prioritize new space when the undo space is insufficient, followed by overwriting unexpired commit. If Autoextend on is not successful with physical restrictions, undo will steal unexpired commit space in case of noguarantee, and if it fails, Oracle ORA-30036 error: "The specified undo Tablespace have no more space available "the solution is to increase the datafile extension undo tablespace. Another about the undo error is ORA-01555: "Rollback records needed by a reader for consistent read is overwritten by other writers" it is because the UN The error that occurs when you construct a consistent read in do.

Avoid the above error: undo_managment=auto; Open Table space Auto-expansion.

Sql>select begin_time,end_time,maxquerylen,tuned_undoretention from V$undostat;

Undo Guarantee Features

Sql>select tablespace_name,status,contents,retention from Dba_tablespaces;

The Guarantee property is built with the undo table space and can be modified:

Sql>alter tablespace UNDOTBS2 Retention guarantee; Guarantee is not allowed to be overwritten during retention, the default configuration, undo retention is Noguarantee

Query for undo Information

1.v$session View user established session, in general, a session can only host a transaction, set up a session may not have a transaction, only when the transaction is active, v$transaction to see this transaction. In other words, if you see a transaction (in v$transaction), there must be a session with it, with two views linked together, the information is more clearly seen.

Sql>select username,sid,serial# from V$session where username are not null; Sid and serial# combination can uniquely locate a session

2.v$transaction viewing things that are currently in active state

Sql>select * from V$transaction;

3.v$rollname the name of the undo segment

Sql>select * from V$rollname;

In general, a session can only host a transaction, set up a session may not have a transaction, only when the transaction is active, v$transaction to see this transaction. In other words, if you see a transaction (in v$transaction), there must be a session with it, with two views linked together, the information is more clearly seen.

Example:

1. Open a cmd session S1,scott user Login database

2. Open another session S2,sys the user logs in to see how many sessions are connected at this time

Sql>select username,sid,serial# from V$session where username are not null;

Create a thing under a 3.S1 session

Sql>update emp1 set sal=1000 where empno=7788;

4. View things: What sessions are generated

Sql>select a.sid,a.serial,a.username,b.xidusn,b.xidslot,b.ubablk,b.status from v$session a,v$transaction b where A.SADDR=B.SES_ADDR;

Two tables, Sid and Serial# in V$session, Xidusn in V$transaction is the segment of undo Id,xidslot is the id,ubablk of the thing slot is the undo block number. In contrast, the following statement shows that--syssmu18 is an active segment, which coincides with the xinusn=18, indicating that the segment was read into buffer.

Note: A transaction can only be extended on one undo segment, not across other segments.

Test: Undo data file corruption or repair in simulated database open

If the current undo is broken when the database is open, such as now that the media is corrupted UNDOTBS2, the database cannot continue DML operations. If there is still something in the active state on the UNDOTBS2 table space (uncommitted thing), Oracle will needs all the segments below it RECOVERY, if there is a backup available to restore the UNDOTBS2, but if there is no backup, You can only use the newly created undo to replace the corrupted undo, and the uncommitted things on undo will also be discarded. However, Oracle does not provide a mechanism to automatically resolve needs recovery, and if you want to remove this corrupt undo tablespace, you must do something else (requires a system reboot). The following example simulates this situation:

There is one thing:

Delete the undo tablespace data file, and then update operation will be error:

$RM-F UNDOTBS02.DBF

$sqlplus/as SYSDBA

Sql>shutdown Abort

Sql>startup Mount

Sql>select file#,checkpoint_change#,name from V$datafile;

7 968161/u01/oradata/timran11g/undotbs02.dbf

The above checkpoint_change# read from the control file, the underlying file is corrupt, the database does not know, so here can also see the undo data file;

To view the data file, the undo data file is deleted, so the SCN is 0, the same as the above error 7th file:

Sql>select file#,checkpoint_change#,name from V$datafile_header;

In the Mount stage, you can read the control file only and do not detect the SCN consistency, and you must offline the number 7th file to open the database:

Sql>alter database datafile 7 offline;

Database has changed

Sql>alter database open;

Database has changed

At this point only the undo segment of the system, the other segment is missing (needs recovery: Indicates that these segments need to be repaired), the DML operation cannot be performed:

Sql>select * from V$rollname;

USN NAME

0 SYSTEM

Sql>select segment_name,status from Dba_rollback_segs; This static view can be used to list all (online or offline) undo information

Create a new undo table space:

Sql>create undo tablespace undotbs1 DataFile '/u01/oradata/timran11g/undotbs01.dbf ' size 100m autoextend on;

Sql>show parameter Undo

Sql>alter system set undo_tablespace= ' UNDOTBS1 ';

This provides a view to see that undo has been restored:

Sql>select * from V$rollname;

Providing another view will reveal that the undo segment of the original uncommitted object is unresolved, causing the table to be locked out of DML operations on the table:

Sql>select * from SCOTT.EMP1;

Sql>select segment_name,status from Dba_rollback_segs; This static view can list (online and offline) Undo segment Information

Delete Tablespace UNDOTBS2 There are active segments that cannot be deleted:

Sql>drop tablespace UNDOTBS2 including contents and datafiles;

Workaround:

There are two solutions: 1. Restore using a backup. 2. Use the implicit parameter _corrupted_rollback_segments provided by Oracle.

Assuming there is no backup, use the method that modifies the system's implicit parameters (that is, telling the database that these are bad segments)

Sql>create pfile from SPFile;

Sql>shutdown Abort

#vi/u01/oracle/dbs/inittimran11g.ora//Insert the following in the first line of the static parameter file

Then start the database with a static parameter file

Sql>startup pfile= '/u01/oracle/dbs/inittimran11g.ora '

Sql>drop rollback segment ' _syssmu11_1357956213$ '; To delete a change segment

Sql>drop rollback segment ' _syssmu12_1357956213$ ';

.....

The table space segment is in a normal state:

At this point, the SCOTT.EMP1 can perform normal DML operations:

Note: If you still cannot delete the undo segment under special circumstances, test the modified dictionary:

Sql>select * from V$tablespace; Note the ts# number of the undo that you want to delete, such as ts#=2 to execute the following statement

Sql>update seg$ set type#=3 where ts#=2;

Undo Table Space