本文详细介绍了Oracle数据库中的撤销段管理，包括撤销的用途、撤销段头信息、撤销段类型以及自动撤销管理的配置、撤销表空间的创建、切换、删除等操作。此外，还涉及到撤销保留时间、撤销空间大小的确定以及如何获取撤销段信息，并通过实验展示了撤销段在并发事务中的作用和管理。
Implement Automatic Undo Management(rather than Manual)
Undo = rollback
Undo segment ->save old value(undo data)&relevant location
Undo segment header ->contains a transaction table->stores the information about the current transactions using the undo segement
A serial transaction(concurrent transactions included) uses only one undo segment to store all of its undo data.
purpose ->transaction rollback,transaction recovery, read consistency
transaction rollback: restores the original values back to the modifiled row.
transaction recovery: instance fails while transactions are in progress--> undo any uncommitted changes(when DB is opened again) <-- changes made to the undo segment are also protected by the online redo log files.
read consistency:a.transactions in progress, other users should not see any uncommitted changes made by these transactions.
b. a statement should not see any changes committed in execution
Read Consistency
Order: executing a SELECT statement
determines current SCN& ensures any changes not committed before the SCN are not processed by the statement.
whether a data block is old or not depends one the SCN(compare)
The Oracle server automatically manages the creation, allocation, and tuning of undo segments.
Types of Undo Segments
SYSTEM Undo Segment -> created in the SYSTEM tablespace when a DB is created.
-> used only for changes made to objects in the SYSTEMTBS
Non-SYSTEM Undo Segments -> A DB that has multiple tablespaces needs at least one non-SYSTEM segment for manual mode or one UNDO tablespace for auto mode.
Deferred: Used when tablespaces are taken offline immediate, temporary, or for recovery
Automatic Undo Management: Concepts
* Undo data is managed using an UNDO tablespace.
* You allocate one UNDO tablespace per instance with enough space for the workload of the instance.
*Undo segments are created with the naming convention: _SYSSMUn$
Automatic Undo Management: Configuration
* Configure two parameters in the initialization file(must&only):
– UNDO_MANAGEMENT：AUTO/MANUAL(not recommended), can not be changed after DB starts.
– UNDO_TABLESPACE：specifies a particular UNDO TBS to be used.
can be dynamically altered.
--SQL> ALTER SYSTEM SET undo_tablespace = UNDOTBS;
create at least one UNDO tablespace(auto,one active).
>= 1 UNDO tablespace may exist, only one can be active.
spfilewade.ora ---refer to manually create a DB
Automatic Undo Management: UNDO Tablespace creation
1.created with a DB by adding a clause in the CREATE DATABASE command
CREATE DATABASE db01
. . .
UNDO TABLESPACE undo1
DATAFILE '/u01/oradata/undoldb01.dbf' SIZE 20M
AUTOEXTEND ON ---refer to manually create a DB
2.created it later using CREATE UNDO TABLESPACE command:
CREATE UNDO TABLESPACE undo1
DATAFILE '/u01/oradata/undo1db01.dbf'
SIZE 20M;
NOTE:
During database creation, if the UNDO_MANAGEMENT parameter is set to AUTO and you omit the UNDO tablespace clause from the CREATE DATABASE statement then the Oracle server creates an UNDO tablespace with the name SYS_UNDOTBS. The default data file, for data file tablespace SYS_UNDOTS, will have the name 'dbu1<oracle_sid>.dbf‘. It will be located in $ORACLE_HOME/dbs. The size is operating system dependent. AUTOEXTEND is set to ON.
Automatic Undo Management: Altering an UNDO Tablespace
1.adds another data file to the UNDO tablespace:
ALTER TABLESPACE undotbs
ADD DATAFILE '/u01/oradata/undotbs2.dbf'
SIZE 30M
AUTOEXTEND ON;
---ADD DATAFILE/RENAME/DATAFILE [ONLINE|OFFLINE]/BEGIN BACKUP/END BACKUP
Automatic Undo Management: Switching UNDO Tablespaces
dynamic switching -> ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2;
Automatic Undo Management: Dropping an UNDO Tablespace
DROP TABLESPACE UNDOTBS2;
* can only be dropped if currently not in use by any instance.
* To drop an active UNDO tablespace:
– Switch to a new UNDO tablespace.
– Drop the tablespace after all current transactions are complete.
To determine whether any active transactions exists use the following query:
SQL> SELECT a.name,b.status FROM v$rollname a, v$rollstat b WHERE a.name IN ( SELECT segment_name FROM dba_segments) AND a.usn = b.usn;
Automatic Undo Management: Other Parameters
* UNDO_RETENTION parameter:controls how long to retain undo data to provide (defined in seconds) for consistent reads, allowing for longer queries & also requiring larger data files for UNDO TBS.
#undo_retention=0, when commit, rollback useless.
can be set in the initialization file or modified dynamically with an ALTER SYSTEM command -> alter system set undo_retention=900 scope=both;
Undo Data Statistics
V$UNDOSTAT view:displays a histogram of statistical data to show how well the database is working
Each row in the view keeps statistics collected in the instance for a 10-minute interval
Automatic Undo Management: Sizing an UNDO Tablespace **important**
Determining a size for the UNDO tablespace requires 3 pieces of information:
* (UR) UNDO_RETENTION in seconds -- from init file
* (UPS) Number of undo data blocks generated per second -- from v$undostat
* (DBS) Overhead varies based on extent and file size (db_block_size)
SQL> select max(undoblks /((end_time - begin_time)*24*3600)) from v$undostat
--- the peak undo blocks in a second! ---recommended
select sum(undoblks) / sum((end_time - begin_time)*24*3600) from v$undostat(可以存放七天的信息)
Automatic Undo Management: Undo Quota
* Long transactions and improperly written transactions can consume valuable resources.
* With undo quota, users can be grouped and a maximum undo space limit can be assigned to the group.
* UNDO_POOL, a Resource Manager directive, defines the amount of space allowed for a resource group. 了解就行，undo表空间事关全局。
Obtaining Undo Segment Information
DBA_ROLLBACK_SEGS
Dynamic Performance Views
– V$ROLLNAME
– V$ROLLSTAT
– V$UNDOSTAT
– V$SESSION
– V$TRANSACTION
related experiment:
RESULTS in 1st secureCRT:
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- -----------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL> select tablespace_name, contents from dba_tablespaces;
TABLESPACE_NAME CONTENTS
------------------------------ ---------
SYSTEM PERMANENT
UNDOTBS1 UNDO
SYSAUX PERMANENT
TEMP TEMPORARY
USERS PERMANENT
MYTBS PERMANENT
PAUL PERMANENT
7 rows selected.
SQL> create undo tablespace myundotbs
2 datafile '/u01/oradata/wade/myundotbs1.dbf' size 10M;
Tablespace created.
SQL> select tablespace_name, contents from dba_tablespaces;
TABLESPACE_NAME CONTENTS
------------------------------ ---------
SYSTEM PERMANENT
UNDOTBS1 UNDO
SYSAUX PERMANENT
TEMP TEMPORARY
USERS PERMANENT
MYTBS PERMANENT
PAUL PERMANENT
MYUNDOTBS UNDO
8 rows selected.
LOGIN IN as a common user hr/hr in a 2nd SecureCRT!
SQL> select * from t;
ID NAME
---------- ----------
0 gwan
SQL> insert into t values(1,'wade');
1 row created.
In 1st SecureCRT:
SQL> alter system set undo_tablespace=myundotbs;
System altered.
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- -------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string MYUNDOTBS
SQL> drop tablespace myundotbs;
drop tablespace myundotbs
*
ERROR at line 1:
ORA-30013: undo tablespace 'MYUNDOTBS' is currently in use
In 2nd SecureCRT
SQL> commit;
Commit complete.
In 1st SecureCRT
SQL> drop tablespace myundotbs;
drop tablespace myundotbs
*
ERROR at line 1:
ORA-30013: undo tablespace 'MYUNDOTBS' is currently in use
SQL> alter system set undo_tablespace = undotbs1;
System altered.
SQL> drop tablespace myundotbs;
Tablespace dropped.
SQL> !
[oracle@Oracle9iDemo ~]$ cd /u01/oradata/wade/
[oracle@Oracle9iDemo wade]$ ll
total 1132016
-rw-r----- 1 oracle oinstall 7061504 Dec 8 05:52 control01.ctl
-rw-r----- 1 oracle oinstall 7061504 Dec 8 05:52 control02.ctl
-rw-r----- 1 oracle oinstall 7061504 Dec 8 05:52 control03.ctl
-rw-r----- 1 oracle oinstall 7061504 Dec 8 05:52 control04.ctl
-rw-r----- 1 oracle oinstall 7061504 Dec 5 15:37 control05.ctl
-rw-r----- 1 oracle oinstall 10493952 Dec 8 05:49 myundotbs1.dbf
-rw-r----- 1 oracle oinstall 20979712 Dec 8 04:15 paul01.dbf
-rw-r----- 1 oracle oinstall 52429312 Dec 8 05:52 redo01.log
-rw-r----- 1 oracle oinstall 52429312 Dec 8 04:15 redo02.log
-rw-r----- 1 oracle oinstall 52429312 Dec 6 14:53 redo03.log
-rw-r----- 1 oracle oinstall 241180672 Dec 8 05:50 sysaux01.dbf
-rw-r----- 1 oracle oinstall 503324672 Dec 8 05:47 system01.dbf
-rw-r----- 1 oracle oinstall 20979712 Dec 8 04:16 temp01.dbf
-rw-r----- 1 oracle oinstall 178266112 Dec 8 05:51 undotbs01.dbf
-rw-r----- 1 oracle oinstall 5251072 Dec 8 05:47 users01.dbf
[oracle@Oracle9iDemo wade]$ rm -f myundotbs1.dbf
#如果加操作时加上句子including contents and datafiles就不用这么麻烦了。
[oracle@Oracle9iDemo dbs]$ exit
exit
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- -----------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL> alter system set undo_retention = 800 scope = both;
System altered.
SQL> desc v$transaction; -- check it in reference, documentation.
SQL> select addr, used_ublk from v$transaction;
no rows selected
In 2nd SecureCRT
SQL> insert into t values(1, 'wayne');
1 row created. --we have not committed it yet.
In 1st SecureCRT
SQL> /
ADDR USED_UBLK
-------- ----------
2A166220 1
In 2nd SecureCRT
SQL> insert into t values(1, 'wayne');
1 row created. --insert a row again.
In 1st SecureCRT
SQL> /
ADDR USED_UBLK
-------- ---------- --No change!
2A166220 1
In 2nd SecureCRT
SQL> commit;
Commit complete.
In 1st SecureCRT
SQL> /
no rows selected --recovered in Undo segment.
In 2nd SecureCRT
SQL> begin
2 for i in 1 .. 10000
3 loop
4 insert into t values(i, 'gwan');
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
In 1st SecureCRT
SQL> /
ADDR USED_UBLK
-------- ---------- --a huge change!
2A166220 87
SQL> select end_time, begin_time, undoblks from
2 v$undostat;
END_TIME BEGIN_TIM UNDOBLKS
--------- --------- ----------
08-DEC-09 08-DEC-09 1
08-DEC-09 08-DEC-09 4
08-DEC-09 08-DEC-09 90
08-DEC-09 08-DEC-09 43
08-DEC-09 08-DEC-09 4
08-DEC-09 08-DEC-09 11
08-DEC-09 08-DEC-09 2
08-DEC-09 08-DEC-09 7
08-DEC-09 08-DEC-09 6
08-DEC-09 08-DEC-09 52
08-DEC-09 08-DEC-09 5
END_TIME BEGIN_TIM UNDOBLKS
--------- --------- ----------
08-DEC-09 08-DEC-09 0
08-DEC-09 08-DEC-09 53
08-DEC-09 08-DEC-09 304
14 rows selected.
In 2nd SecureCRT
SQL> commit;
Commit complete.
In 1st SecureCRT
SQL> /
no rows selected
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- -------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL> create undo tablespace smallundo
2 datafile '/u01/oradata/wade/smallundo1.dbf' size 2M
3 autoextend off;
Tablespace created.
SQL> alter system set undo_tablespace=smallundo;
System altered.
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ---------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string SMALLUNDO
SQL> select addr, used_ublk from v$transaction;
no rows selected
In 2nd SecureCRT
SQL> begin
2 for i in 1 .. 1000000
3 loop
4 insert into t values(i, 'jennny');
5 end loop;
6 end;
7 /
begin
*
ERROR at line 1:
ORA-30036: unable to extend segment by 8 in undo tablespace 'SMALLUNDO'
ORA-06512: at line 4
In 1st SecureCRT
SQL> /
no rows selected
SQL> alter system set undo_tablespace = undotbs1;
System altered.
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
In 2nd SecureCRT
SQL> begin
2 for i in 1 .. 1000000
3 loop
4 insert into t values(i, 'jennny');
5 end loop;
6 end;
7 /
In 1st SecureCRT: switch immediately!
SQL> select addr, used_ublk from v$transaction;
SQL> select addr, used_ublk from v$transaction;
ADDR USED_UBLK
-------- ----------
2A14ECA0 2390
SQL> select addr, used_ublk from v$transaction;
ADDR USED_UBLK
-------- ----------
2A14ECA0 2390
SQL> select addr, used_ublk from v$transaction;
ADDR USED_UBLK
-------- ----------
2A14ECA0 2394
SQL> select addr, used_ublk from v$transaction;
ADDR USED_UBLK
-------- ----------
2A14ECA0 2394
SQL> select addr, used_ublk from v$transaction;
ADDR USED_UBLK
-------- ----------
2A14ECA0 2398
SQL> /
ADDR USED_UBLK
-------- ----------
2A14ECA0 2400
SQL> /
ADDR USED_UBLK
-------- ----------
2A14ECA0 3112
SQL> select addr, used_ublk from v$transaction;
ADDR USED_UBLK
-------- ----------
2A14ECA0 3273
SQL> select addr, used_ublk from v$transaction;
ADDR USED_UBLK
-------- ----------
2A14ECA0 4280
In 2nd secureCRT
PL/SQL procedure successfully completed.
In 1st secureCRT
SQL> select max(undoblks /((end_time - begin_time)*24*3600)) from v$undostat;
MAX(UNDOBLKS/((END_TIME-BEGIN_TIME)*24*3600))
---------------------------------------------
14.4716667 #一秒钟产生15个数据块
计算：15 * 8k * 900(秒) 大概就100多兆
SQL> select segment_name, tablespace_name from dba_rollback_segs;
SEGMENT_NAME TABLESPACE_NAME
------------------------------ ------------------------------
SYSTEM SYSTEM
_SYSSMU10$ UNDOTBS1
_SYSSMU9$ UNDOTBS1
_SYSSMU8$ UNDOTBS1
_SYSSMU7$ UNDOTBS1
_SYSSMU6$ UNDOTBS1
_SYSSMU5$ UNDOTBS1
_SYSSMU4$ UNDOTBS1
_SYSSMU3$ UNDOTBS1
_SYSSMU2$ UNDOTBS1
_SYSSMU1$ UNDOTBS1
SEGMENT_NAME TABLESPACE_NAME
------------------------------ ------------------------------
_SYSSMU20$ SMALLUNDO
_SYSSMU19$ SMALLUNDO
_SYSSMU18$ SMALLUNDO
_SYSSMU17$ SMALLUNDO
_SYSSMU16$ SMALLUNDO
_SYSSMU15$ SMALLUNDO
_SYSSMU14$ SMALLUNDO
_SYSSMU13$ SMALLUNDO
_SYSSMU12$ SMALLUNDO
_SYSSMU11$ SMALLUNDO
21 rows selected.
#两个undo表空间，undotbs1系统缺省，smallundo自己演示用的。
#segement的命名为_syssmu10_n$ 为多个transaction共享，由oracle自动分配
#system表空间里面也有一个名为system的undo segement，主要是用于修改系统表空间里面的DD。
#这个查询的是所有的segment的信息
SQL> select * from v$rollname; #查询的是所有在线的segment的信息
USN NAME
---------- ------------------------------
0 SYSTEM
1 _SYSSMU1$
2 _SYSSMU2$
3 _SYSSMU3$
4 _SYSSMU4$
5 _SYSSMU5$
6 _SYSSMU6$
7 _SYSSMU7$
8 _SYSSMU8$
9 _SYSSMU9$
10 _SYSSMU10$
11 rows selected. #用select usn, status from v$rollstat;效果一样。
#查segment的状态信息用v$rollstat。
练习题:
1.list the undo segmenbts in tablespace UNDOTBS.
desc dba_segments;
show parameter undo
col segment_name a30
select segment_name, tablespace_name from dba_segments where tablespace_name = 'UNDOTBS1';
2.List the undo segments in TBS UNDOTBS and their status.
扫一扫
2442
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
