谈谈Oracle undo表空间
Oracle比其他数据库牛逼的地方好几个,其中一个很重要的就是undo表空间的引入(当然,锁也是很牛逼的一个东西)
1.oracle段的类型:
www.2cto.com
SQL> select segment_type from dba_segments t group by t.segment_type;
SEGMENT_TYPE
------------------
LOBINDEX
INDEX PARTITION
TABLE PARTITION
NESTED TABLE
ROLLBACK
LOB PARTITION
LOBSEGMENT
INDEX
TABLE
CLUSTER
TYPE2 UNDO
在dba_tablespace中.表空间的类型分为:undo,temporary,permanent
www.2cto.com
2.查看undo表空间创建之后创建的段
select * from dba_segments where tablespace_name = 'UNDOTBS1';
每一个undo段至少要有2个extent
也可以查看
select * from dba_rollback_segs
在status一栏有显示从数据库启动用的有10个undo段,如果存在多个undo表空间,那么从status=online的可以查看当前在用的回滚段.
个人认为9i的最大贡献就是,开始undo可以自动管理
3.每个回滚段最多有几个事务数?
在9i以前通过参数transactions_per_rollback_segment(默认是5个),现在已经失效,从10g开始默认一个回滚段上只有一个事务,如果回滚段不够的话,那么就自己创建undo段,直到undo表空间用完.这个时候,回滚段上的事务才开始有多个.
4.最牛逼的一致性读
一致性读(Consistent Get)是Oracle一个非常优秀的特性.(当然它也是产生ora-1555错误的主要原因)
在标准SQL中,为了防止并发事务中产生脏读,就需要通过加锁来控制.这样就会带来死锁、阻塞的问题,即时是粒度最小的行级锁,也无法避免这些问题.
为了解决这一矛盾,Oracle充分利用的回归段,通过会滚段进行一致性读取,即避免了脏读,又大大减少了系统的阻塞、死锁问题.
www.2cto.com
Oracle是如何实现一致性读的:
当Oracle更新数据块(Data Block Oracle中最小的存储单位)时,会在两个地方记录下这一更新动作.一个是在Redo Segment,.一个是回滚段UNDO Segment.并在数据块头部标示出来是否有修改数据.一个语句在读取数据快时,如果发现这个数据块是在它读取的过程中被修改的(即开始执行读操作时并没有被修改),就不直接从数据块上读取数据,而是从相应的回滚段条目中读取数据.这就保证了最终结果应该是读操作开始时的那一时刻的快照(snapshot),而不会受到读期间其他事务的影响.这就是Oracle的一致性读,也可以叫做多版本(Multi-Versioning).
5.ORACLE的据库事务隔离级别
事务隔离级别:一个事务对数据库的修改与并行的另一个事务的隔离程度。
两个并发事务同时访问数据库表相同的行时,可能存在以下三个问题:
(1)幻想读:事务T1读取一条指定where条件的语句,返回结果集。此时事务T2插入一行新记录,恰好满足T1的where条件。然后T1使用相同的条件再次查询,结果集中可以看到T2插入的记录,这条新纪录就是幻想。 www.2cto.com
(2)不可重复读取:事务T1读取一行记录,紧接着事务T2修改了T1刚刚读取的记录,然后T1再次查询,发现与第一次读取的记录不同,这称为不可重复读。
(3)脏读:事务T1更新了一行记录,还未提交所做的修改,这个T2读取了更新后的数据,然后T1执行回滚操作,取消刚才的修改,所以T2所读取的行就无效,也就是脏数据。
为了处理这些问题,SQL标准定义了以下几种事务隔离级别
![](http://www.2cto.com/uploadfile/2012/0925/20120925030820904.jpg)
Oracle数据库支持READ COMMITTED 和 SERIALIZABLE这两种事务隔离级别。Oracle不支持脏读。
![](http://www.2cto.com/uploadfile/2012/0925/20120925030820319.jpg)
SET TRANSACTION ISOLATION LEVEL
[READ UNCOMMITTED|READ COMMITTED|REPEATABLE READ|SERIALIZABLE]
6.Oracle是怎样实现一致性读的,我们可以通过以下实验来查看
(1)建一个测试的表
www.2cto.com
SQL> create tablespace test datafile '/u01/app/oracle/oradata/pmisdb/test.dbf' size 20M;
Tablespace created.
SQL> create table tt (id int,name varchar2(10)) tablespace test;
Table created.
SQL>
SQL> insert into tt values(1,'a++');
1 row created.
SQL> insert into tt values(2,'b');
1 row created.
SQL> insert into tt values(3,'c');
1 row created.
SQL> commit;
Commit complete.
SQL>
SQL> select * from tt;
ID NAME
---------- ----------
1 a++
2 b
3 c
www.2cto.com
SQL>
(2).打开一个session A ,对改表进行update操作
session A >update tt set name='a' where id=1;
1 row updated.
session A >select * from tt;
ID NAME
---------- ----------
1 a
2 b
3 c
www.2cto.com
(3)打开一个session B ,进行查询..因为有一致性读的特性,所以在session B中,在A没有提交前,B是看不到A修改的数据的.
session B >select * from tt;
ID NAME
---------- ----------
1 a++
2 b
3 c
session B >
(4)可以根据rowid以及oracle提供的dbms_rowid包来查看该条记录所在的数据文件和数据块
session A >select id,name,rowid from tt;
ID NAME ROWID
---------- ---------- ------------------
1 a AAARFuAAIAAAAAQAAA
2 b AAARFuAAIAAAAAQAAB
3 c AAARFuAAIAAAAAQAAC
SQL> select dbms_rowid.rowid_relative_fno('AAARFuAAIAAAAAQAAA') as file#,
2 dbms_rowid.rowid_block_number('AAARFuAAIAAAAAQAAA') as block#
3 from dual; www.2cto.com
FILE# BLOCK#
---------- ----------
8 16
(5)根据查询到的文件号和块号进行dump,注意这个地方dump的其实是内存里面的数据,如果需要dump磁盘上的数据文件,那么把8改成具体的路径就可以了,因为oracle写是异步的,这个时候磁盘的数据文件并不一定已经有这个信息了.
www.2cto.com
session A >alter system dump datafile 8 block 16;
System altered.
session A >SELECT d.VALUE || '/' || LOWER(RTRIM(i.INSTANCE, CHR(0))) || '_ora_' ||
2 p.spid || '.trc' trace_file_name
3 FROM (SELECT p.spid
4 FROM v$mystat m, v$session s, v$process p
5 WHERE m.statisti
c# = 1
6 AND s.SID = m.SID
7 AND p.addr = s.paddr) p,
8 (SELECT t.INSTANCE
9 FROM v$thread t, v$parameter v
10 WHERE v.NAME = 'thread'
11 AND (v.VALUE = 0 OR t.thread# = TO_NUMBER(v.VALUE))) i,
12 (SELECT VALUE FROM v$parameter WHERE NAME = 'user_dump_dest') d;
www.2cto.com
TRACE_FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/admin/pmisdb/udump/pmisdb_ora_3827.trc
(6)打开trace文件,进行观察,分别截取开头和有关事务的内容:
[root@pmiscs ~]# more /u01/app/oracle/admin/pmisdb/udump/pmisdb_ora_3790.trc
Dump file /u01/app/oracle/admin/pmisdb/udump/pmisdb_ora_3790.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1
System name:
Linux www.2cto.com
Node name: pmiscs
Release: 2.6.18-92.el5
Version: #1 SMP Tue Apr 29 13:16:12 EDT 2008
Machine: i686
Instance name: pmisdb
Redo thread mounted by this instance: 1
Oracle process number: 13
Unix process pid: 3790, image: oracle@pmiscs (TNS V1-V3)
*** ACTION NAME:() 2012-09-25 13:19:01.211
*** MODULE NAME:(sqlplus@pmiscs (TNS V1-V3)) 2012-09-25 13:19:01.211
*** SERVICE NAME:() 2012-09-25 13:19:01.211
*** SESSION ID:(544.3) 2012-09-25 13:19:01.211
Successfully allocated 2 recovery slaves
Using 543 overflow buffers per recovery slave
Thread 1 checkpoint: logseq 613, block 2, scn 18284996
cache-low rba: logseq 613, block 449 www.2cto.com
on-disk rba: logseq 613, block 508, scn 18285712
change track rba: logseq 613, block 507, scn 18285711
start recovery at logseq 613, block 449, scn 0
----- Redo read statistics for thread 1 -----
Read rate (ASYNC): 29Kb in 0.19s => 0.15 Mb/sec
Total physical reads: 4096Kb
Longest record: 2Kb, moves: 0/11 (0%)
Longest LWN: 6Kb, moves: 0/47 (0%), moved: 0Mb
Last redo scn: 0x0000.0117048f (18285711)
----------------------------------------------
----- Recovery Hash Table Statistics ---------
Hash table buckets = 32768
Longest hash chain = 1
Average hash chain = 9/9 = 1.0
Max compares per lookup = 1
Avg compares per lookup = 12/21 = 0.6
----------------------------------------------
*** 2012-09-25 13:19:01.442 www.2cto.com
KCRA: start recovery claims for 9 data blocks
*** 2012-09-25 13:19:01.499
KCRA: blocks processed = 9/9, claimed = 9, eliminated = 0
*** 2012-09-25 13:19:01.499
Recovery of Online Redo Log: Thread 1 Group 6 Seq 613 Reading mem 0
----- Recovery Hash Table Statistics ---------
Hash table buckets = 32768
Longest hash chain = 1
Average hash chain = 9/9 = 1.0
Max compares per lookup = 1
Avg compares per lookup = 21/21 = 1.0
----------------------------------------------
kwqmnich: current time:: 5: 19: 6
kwqmnich: instance no 0 check_only flag 1
kwqmnich: initialized job cache structure
*** 2012-09-25 13:21:45.194
Start dump data blocks tsn: 10 file#: 8 minblk 16 maxblk 16
buffer tsn: 10 rdba: 0x02000010 (8/16)
scn: 0x0000.01175499 seq: 0x01 flg: 0x00 tail: 0x54990601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0DCC2400 to 0x0DCC4400
... www.2cto.com
Block header dump: 0x02000010
Object id on Block? Y
seg/obj: 0x1116e csc: 0x00.1175499 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x2000009 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0004.005.00000c41 0x00800c4a.0692.13 ---- 1 fsc 0x0001.00000000
0x02 0x0003.01b.00000cfe 0x008006d1.072a.04 C--- 0 scn 0x0000.01151109
data_block_dump,data header at 0xdcc2464
=============== www.2cto.com
tsiz: 0x1f98
hsiz: 0x18
pbl: 0x0dcc2464
bdba: 0x02000010
76543210
flag=--------
ntab=1
nrow=3
frre=-1
fsbo=0x18
fseo=0x1f5c
avsp=0x1f64
tosp=0x1f65
0xe:pti[0] nrow=3 offs=0
0x12:pri[0] offs=0x1f5c
0x14:pri[1] offs=0x1f88
0x16:pri[2] offs=0x1f80
block_row_dump: www.2cto.com
tab 0, row 0, @0x1f5c
tl: 8 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 02
col 1: [ 1] 61
tab 0, row 1, @0x1f88
tl: 8 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 03
col 1: [ 1] 62
tab 0, row 2, @0x1f80
tl: 8 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 04
col 1: [ 1] 63
end_of_block_dump
End dump data blocks tsn: 10 file#: 8 minblk 16 maxblk 16
这个dump文件开头对
数据库的环境做了一些描述,中间是一些16进制的内容,最后面是事务和行的一些信息,任何一个事务想修改数据块,都必需要获取一个Itl:
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0004.005.00000c41 0x00800c4a.0692.13 ---- 1 fsc 0x0001.00000000
0x02 0x0003.01b.00000cfe 0x008006d1.072a.04 C--- 0 scn 0x0000.01151109
看上面的事务的信息,查看Flag,4个'-'代表有一个事务正在修改数据块,Lck代表当前锁定了一条数据,Itl=0x01,其实对应的就是下面的:
tab 0, row 0, @0x1f5c
tl: 8 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 02
col 1: [ 1] 61 www.2cto.com
当为0x1状态时,表明该条数据已经被锁定,加了TX锁,其他事务想访问它的时候会被阻塞..
(7)从这个时候
Oracle数据块的强大开始体现出来,如果是其他数据块(如sqlserver)的话,那么就会等待,而Oracle的一致性读很牛逼的解决了这个问题,它不让阻塞,而是让其他session去undo段里读,具体的undo地址就是Uba(undo block address)所指示的地址:0x00800c4a.0692.13
我们对这个地址进行转换,查询它具体是哪个文件的哪个块,首先将16进制转换为10进制,再用相应的工具包进行转换查询:
SQL> select to_number('00800c4a','xxxxxxxx') from dual;
TO_NUMBER('0080B673','XXXXXXXX')
--------------------------------
8391754
SQL> select dbms_utility.data_block_address_file(8391754) as file#,
2 dbms_utility.data_block_address_block(8391754) as block#
3 from dual;
FILE# BLOCK#
---------- ----------
2 3146
www.2cto.com
这下很清晰的查看到了,是在第2个数据文件的,第3146块上,再查询下第2个数据文件是啥文件
SQL> select tablespace_name,file_id from dba_data_files where file_id=2;
TABLESPACE_NAME FILE_ID
------------------------------ ----------
UNDOTBS1 2
哈,这下更清晰了,那个地址指向的就是undo表空间里面的数据块!
(8)再根据数据文件号和数据块进行dump:
SQL> select tablespace_name,file_id from dba_data_files where file_id=2;
TABLESPACE_NAME FILE_ID
------------------------------ ----------
UNDOTBS1 2
SQL> alter system dump datafile 2 block 3146;
www.2cto.com
System altered.
SQL> SELECT d.VALUE || '/' || LOWER(RTRIM(i.INSTANCE, CHR(0))) || '_ora_' ||
2 p.spid || '.trc' trace_file_name
3 FROM (SELECT p.spid
4 FROM v$mystat m, v$session s, v$process p
5 WHERE m.statistic# = 1
6 AND s.SID = m.SID
7 AND p.addr = s.paddr) p,
8 (SELECT t.INSTANCE
9 FROM v$thread t, v$parameter v
10 WHERE v.NAME = 'thread'
11 AND (v.VALUE = 0 OR t.thread# = TO_NUMBER(v.VALUE))) i,
12 (SELECT VALUE FROM v$parameter WHERE NAME = 'user_dump_dest') d;
www.2cto.com
TRACE_FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/admin/pmisdb/udump/pmisdb_ora_4113.trc
(9)查看dump undo出来的文件:
Dump file /u01/app/oracle/admin/pmisdb/udump/pmisdb_ora_4113.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1
System name: Linux
Node name: pmiscs
Release: 2.6.18-92.el5
Version: #1 SMP Tue Apr 29 13:16:12 EDT 2008
Machine: i686 www.2cto.com
Instance name: pmisdb
Redo thread mounted by this instance: 1
Oracle process number: 24
Unix process pid: 4113, image: oracle@pmiscs (TNS V1-V3)
*** ACTION NAME:() 2012-09-25 13:31:36.874
*** MODULE NAME:(sqlplus@pmiscs (TNS V1-V3)) 2012-09-25 13:31:36.874
*** SERVICE NAME:(SYS$USERS) 2012-09-25 13:31:36.874
*** SESSION ID:(523.77) 2012-09-25 13:31:36.874
Start dump data blocks tsn: 1 file#: 2 minblk 3146 maxblk 3146
buffer tsn: 1 rdba: 0x00800c4a (2/3146)
scn: 0x0000.0117548c seq: 0x01 flg: 0x04 tail: 0x548c0201
frmt: 0x02 chkval: 0x3970 type: 0x02=KTU UNDO BLOCK
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0E101400 to 0x0E103400
... www.2cto.com
********************************************************************************
UNDO BLK:
xid: 0x0004.005.00000c41 seq: 0x692 cnt: 0x13 irb: 0x13 icl: 0x0 flg: 0x0000
Rec Offset Rec Offset Rec Offset Rec Offset Rec Offset
---------------------------------------------------------------------------
0x01 0x1f94 0x02 0x1eac 0x03 0x1e04 0x04 0x1d68 0x05 0x1d04
0x06 0x1c68 0x07 0x1c04 0x08 0x1ba8 0x09 0x1b54 0x0a 0x1af8
0x0b 0x1aa4 0x0c 0x1a48 0x0d 0x19f4 0x0e 0x1900 0x0f 0x18b4
0x10 0x17f0 0x11 0x178c 0x12 0x1738 0x13 0x1684
www.2cto.com
*-----------------------------
* Rec #0x1 slt: 0x01 objn: 519(0x00000207) objd: 519 tblspc: 0(0x00000000)
* Layer: 10 (Index) opc: 22 rci 0x00
Undo type: Regular undo Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00800c49
*-----------------------------
index undo for leaf key operations
KTB Redo
op: 0x02 ver: 0x01
op: C uba: 0x00800c49.0692.2f
Dump kdilk : itl=2, kdxlkflg=0x1 sdc=0 indexid=0x401029 block=0x0040f182
(kdxlpu): purge leaf row
key :(10): 06 c5 2b 5f 60 0d 0e 02 c1 1d
www.2cto.com
...
*-----------------------------
* Rec #0x12 slt: 0x28 objn: 5141(0x00001415) objd: 5141 tblspc: 0(0x00000000)
* Layer: 10 (Index) opc: 22 rci 0x11
Undo type: Regular undo Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
index undo for leaf key operations
KTB Redo
op: 0x02 ver: 0x01
op: C uba: 0x00800c4a.0692.11
Dump kdilk : itl=3, kdxlkflg=0x1 sdc=0 indexid=0x402b51 block=0x00402b52
(kdxlpu): purge leaf row
key :(10): 02 c1 04 06 00 40 2b 2a 00 08
www.2cto.com
*-----------------------------
* Rec #0x13 slt: 0x05 objn: 69998(0x0001116e) objd: 69998 tblspc: 10(0x0000000a)
* Layer: 11 (Row) opc: 1 rci 0x00
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
uba: 0x00800c4a.0692.10 ctl max scn: 0x0000.0116f8c0 prv tx scn: 0x0000.0116f8d8
txn start scn: scn: 0x0000.0117548c logon user: 0
prev brb: 8391750 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
op: L itl: xid: 0x0009.00f.00000a96 uba: 0x0080019d.0670.22
flg: C--- lkc: 0 scn: 0x0000.0114e6a3
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x02000010 hdba: 0x0200000b
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 12
ncol: 2 nnew: 1 size: 2 www.2cto.com
col 1: [ 3] 61 2b 2b
End dump data blocks tsn: 1 file#: 2 minblk 3146 maxblk 3146
(10)怎么去读这个dump文件,查找那条有事务的记录呢?其实在上面undo地址Uba(undo block address)所指示的地址:0x00800c4a.0692.13,已经告诉我们了,0x00800c4a是16进制的地址,而13就是那条update的记录!我们单独把那条记录拿出来:
*-----------------------------
* Rec #0x13 slt: 0x05 objn: 69998(0x0001116e) objd: 69998 tblspc: 10(0x0000000a)
* Layer: 11 (Row) opc: 1 rci 0x00
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000 www.2cto.com
*-----------------------------
uba: 0x00800c4a.0692.10 ctl max scn: 0x0000.0116f8c0 prv tx scn: 0x0000.0116f8d8
txn start scn: scn: 0x0000.0117548c logon user: 0
prev brb: 8391750 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
op: L itl: xid: 0x0009.00f.00000a96 uba: 0x0080019d.0670.22
flg: C--- lkc: 0 scn: 0x0000.0114e6a3
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x02000010 hdba: 0x0200000b
itli: 1 ispac: 0 maxfr: 4858
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 12
ncol: 2 nnew: 1 size: 2
col 1: [ 3] 61 2b 2b
(11)对比session A dump出来的信息和从undo dump出来的信息:
--session A
col 1: [ 1] 61
www.2cto.com
--undo
col 1: [ 3] 61 2b 2b
通过对比,可以发现session A的col1的值为61,undo里面的col1的值为61 2b 2b,把这2个值转换成ascii码:
SQL> select chr(to_number('61','xx')),chr(to_number('2b','xx')) from dual;
CH CH
-- --
a +
哈哈,这下清楚了.原来61代表的是'a',2b代表的'+' www.2cto.com
(12)这样,我们就把undo是怎么工作的实验做完了.把原理再完整的描述一遍:session A对某条记录做了dml操作,这个操作是在内存中完成的,这个时候在undo里面记录一条信息,如果满足了DBWn的条件那么就会写入到磁盘中,不满足的话就在内存中,在没有提交之前,undo的信息一直不会被清除.session B在查询该条记录时,因为A没有提交,所以在itl事务槽中对该条信息有一个记录,会告诉session B去undo相应的地址查找该条记录的内容,而不去使用内存中被改变的信息.这就是Oracle的一致性读.
7.回过头来,我们再看看相应的数据字典和动态性能视图:
(1).在之前查找undo的文件和块的地方,其实oracle已经给了我们一个视图,告诉了我们相关的信息了 www.2cto.com
SQL> select t.UBAFIL,t.UBABLK from v$transaction t;
UBAFIL UBABLK
---------- ----------
2 3146
(2).其他几个动态性能视图
SQL> select a.USN,a.XACTS from v$rollstat a where xacts <> 0;
USN XACTS
---------- ----------
4 1
SQL> select segment_name from dba_rollback_segs where segment_id = 4;
SEGMENT_NAME
------------------------------
_SYSSMU4$ www.2cto.com
SQL> select * from dba_extents where segment_name = '_SYSSMU4$';
1 SYS _SYSSMU4$ TYPE2 UNDO UNDOTBS1 0 2 57 65536 8 2
2 SYS _SYSSMU4$ TYPE2 UNDO UNDOTBS1 1 2 225 65536 8 2
3 SYS _SYSSMU4$ TYPE2 UNDO UNDOTBS1 2 2 3081 1048576 128 2
4 SYS _SYSSMU4$ TYPE2 UNDO UNDOTBS1 3 2 2057 1048576 128 2
从v$rollstat的xacts不为0d记录中可以得到当前能有事务的回滚段,根据回滚段号去dba_rollback_segs查找相应的名字,再根据名字去dba_extents去查找相应的信息,这样一来,就把所有的知识都联系起来了.