查询数据库中所有的undo表空间。

SQL> select tablespace_name from dba_tablespaces where contents='UNDO' ;


TABLESPACE_NAME

------------------------------

UNDOTBS1


创建一个undo表空间

SQL> create undo tablespace undotbs2 datafile '/u01/app/oracle/oradata/WH/undot

s2.dbf' size 20m;


Tablespace created.


SQL> select tablespace_name from dba_tablespaces where contents='UNDO' ;


TABLESPACE_NAME

------------------------------

UNDOTBS1

UNDOTBS2


查询数据库字典dba_sgement,undo段的信息

SQL> select * from dba_segments where tablespace_name in ('UNDOTBS1','UNDOTBS2')

一共有20个undo段,undotbs1和undotbs2各10个,每个段默认最小有2个extended


查询数据库字典dba_rollback_segs,可以看出udotbs1的回滚段都是处于online状态,undotbs2的回滚段处于offline状态


SQL> col segment_name format a30;

SQL> col tablespace_name format a15;

SQL> select segment_name,owner,tablespace_name,status from dba_rollback_segs;


SEGMENT_NAME                   OWNER  TABLESPACE_NAME STATUS

------------------------------ ------ --------------- ----------

SYSTEM                         SYS    SYSTEM          ONLINE

_SYSSMU10_1186132793$          PUBLIC UNDOTBS1        ONLINE

_SYSSMU9_1186132793$           PUBLIC UNDOTBS1        ONLINE

_SYSSMU8_1186132793$           PUBLIC UNDOTBS1        ONLINE

_SYSSMU7_1186132793$           PUBLIC UNDOTBS1        ONLINE

_SYSSMU6_1186132793$           PUBLIC UNDOTBS1        ONLINE

_SYSSMU5_1186132793$           PUBLIC UNDOTBS1        ONLINE

_SYSSMU4_1186132793$           PUBLIC UNDOTBS1        ONLINE

_SYSSMU3_1186132793$           PUBLIC UNDOTBS1        ONLINE

_SYSSMU2_1186132793$           PUBLIC UNDOTBS1        ONLINE

_SYSSMU1_1186132793$           PUBLIC UNDOTBS1        ONLINE


SEGMENT_NAME                   OWNER  TABLESPACE_NAME STATUS

------------------------------ ------ --------------- ----------

_SYSSMU20_1367581872$          PUBLIC UNDOTBS2        OFFLINE

_SYSSMU19_1367581872$          PUBLIC UNDOTBS2        OFFLINE

_SYSSMU18_1367581872$          PUBLIC UNDOTBS2        OFFLINE

_SYSSMU17_1367581872$          PUBLIC UNDOTBS2        OFFLINE

_SYSSMU16_1367581872$          PUBLIC UNDOTBS2        OFFLINE

_SYSSMU15_1367581872$          PUBLIC UNDOTBS2        OFFLINE

_SYSSMU14_1367581872$          PUBLIC UNDOTBS2        OFFLINE

_SYSSMU13_1367581872$          PUBLIC UNDOTBS2        OFFLINE

_SYSSMU12_1367581872$          PUBLIC UNDOTBS2        OFFLINE

_SYSSMU11_1367581872$          PUBLIC UNDOTBS2        OFFLINE


21 rows selected.


从参数undo_tablespace中可以看出当前数据库使用的undo表空间是undotbs1。

SQL> show parameter undo_tablespace;


NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

undo_tablespace                      string      UNDOTBS1

SQL>


改变uodo表空间为undotbs2,

SQL> alter system set undo_tablespace=undotbs2;


System altered.


SQL> show parameter undo_tablespace;


NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

undo_tablespace                      string      UNDOTBS2


SQL> select segment_name,owner,tablespace_name,status from dba_rollback_segs;


SEGMENT_NAME                   OWNER  TABLESPACE_NAME STATUS

------------------------------ ------ --------------- ----------------

SYSTEM                         SYS    SYSTEM          ONLINE

_SYSSMU10_1186132793$          PUBLIC UNDOTBS1        OFFLINE

_SYSSMU9_1186132793$           PUBLIC UNDOTBS1        OFFLINE

_SYSSMU8_1186132793$           PUBLIC UNDOTBS1        OFFLINE

_SYSSMU7_1186132793$           PUBLIC UNDOTBS1        OFFLINE

_SYSSMU6_1186132793$           PUBLIC UNDOTBS1        OFFLINE

_SYSSMU5_1186132793$           PUBLIC UNDOTBS1        OFFLINE

_SYSSMU4_1186132793$           PUBLIC UNDOTBS1        OFFLINE

_SYSSMU3_1186132793$           PUBLIC UNDOTBS1        OFFLINE

_SYSSMU2_1186132793$           PUBLIC UNDOTBS1        OFFLINE

_SYSSMU1_1186132793$           PUBLIC UNDOTBS1        OFFLINE


SEGMENT_NAME                   OWNER  TABLESPACE_NAME STATUS

------------------------------ ------ --------------- ----------------

_SYSSMU20_1367581872$          PUBLIC UNDOTBS2        ONLINE

_SYSSMU19_1367581872$          PUBLIC UNDOTBS2        ONLINE

_SYSSMU18_1367581872$          PUBLIC UNDOTBS2        ONLINE

_SYSSMU17_1367581872$          PUBLIC UNDOTBS2        ONLINE

_SYSSMU16_1367581872$          PUBLIC UNDOTBS2        ONLINE

_SYSSMU15_1367581872$          PUBLIC UNDOTBS2        ONLINE

_SYSSMU14_1367581872$          PUBLIC UNDOTBS2        ONLINE

_SYSSMU13_1367581872$          PUBLIC UNDOTBS2        ONLINE

_SYSSMU12_1367581872$          PUBLIC UNDOTBS2        ONLINE

_SYSSMU11_1367581872$          PUBLIC UNDOTBS2        ONLINE


21 rows selected.

然后再查询dba_rollback_segs,发现uodotbs的回滚段全部online了,而undotb1的回滚段变为了offline状态。


从前面实验可知一个实例可以创建多个undo表空间,但是一个实例只能使用一个undo表空间,不能同时让所有undo表空间的回滚段同时online。每个实例都会有个段名为system,所有者sys,并且属于system表空间的回滚段,其它回滚段的owner都为public,表名system回滚段是私有的,其它的回滚段可以让多个实例(RAC)共用


通过下面实验理解oracle是如何通过undo保证数据库读一致性


创建表test,并且插入2行数据提交

SQL> create table test (id int, name varchar2(10));


Table created.


SQL> insert into test values (1,'a');


1 row created.


SQL> insert into test values (2,'b');


1 row created.


SQL> commit;


Commit complete.


现在更新表test的一个字段值,不commit


SQL> select * from test;


       ID NAME

---------- ----------

        1 a

        2 b


SQL> update test set name='abc' where id=1;


1 row updated.


再执行查询看到已经更改过来

SQL> select * from test;


       ID NAME

---------- ----------

        1 abc

        2 b


开启另外一个session再查询表test

C:\>sqlplus sys/oracle@DGWH as sysdba


SQL*Plus: Release 10.2.0.1.0 - Production on Fri May 3 12:57:21 2013


Copyright (c) 1982, 2005, Oracle.  All rights reserved.



Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> select * from test;


       ID NAME

---------- ----------

        1 a

        2 b


然后查询v$transaction视图,发现有一个活动的事物,可知在为commit之前数据还是以前的状态,那么这时候的数据就是从undo里面读出来的


根据rowid可知这两行数据库都位于文件编号为1,块编号为86626的数据块中

SQL> select id, name, dbms_rowid.rowid_relative_fno(rowid) fno, dbms_rowid.rowid

_block_number(rowid) bno from test;


       ID NAME              FNO        BNO

---------- ---------- ---------- ----------

        1 abc                 1      86626

        2 b                   1      86626


将块编号为86626从内存中dump出来分析,dump出来的文件存放在background_dump_dest指定的目录中,以时间降序排列即可找到这个文件,若不确定是哪个该文件,由于文件名中包含该session的进程id,可以根据进程id找到。


SQL> alter system dump datafile 1 block 86626;


System altered.


SQL> show parameter background_dump_dest;


NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

background_dump_dest                 string      /u01/app/oracle/diag/rdbms/wh/

                                                WH/trace

SQL>


下面列出了dump文件中的关键信息

Block header dump:  0x00415262

Object id on Block? Y

seg/obj: 0x11195  csc: 0x00.c93cf  itc: 2  flg: O  typ: 1 - DATA

    fsl: 0  fnx: 0x0 ver: 0x01


Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0x01   0x000b.002.00000006  0x0140000b.0005.04  C---    0  scn 0x0000.000c9359

0x02   0x0011.003.00000006  0x0140006b.0005.04  ----    1  fsc 0x0000.00000000

bdba: 0x00415262

data_block_dump,data header at 0xc10c5c

===============

tsiz: 0x1fa0

hsiz: 0x16

pbl: 0x00c10c5c

    76543210

flag=--------

ntab=1

nrow=2

frre=-1

fsbo=0x16

fseo=0x1f86

avsp=0x1f77

tosp=0x1f77

0xe:pti[0]      nrow=2  offs=0

0x12:pri[0]     offs=0x1f86

0x14:pri[1]     offs=0x1f90

block_row_dump:

tab 0, row 0, @0x1f86

tl: 10 fb: --H-FL-- lb: 0x2  cc: 2

col  0: [ 2]  c1 02

col  1: [ 3]  61 62 63

tab 0, row 1, @0x1f90

tl: 8 fb: --H-FL-- lb: 0x0  cc: 2

col  0: [ 2]  c1 03

col  1: [ 1]  62

end_of_block_dump

End dump data blocks tsn: 0 file#: 1 minblk 86626 maxblk 86626


nrow=2 列出了改块包含了2行数据 第一行为row 0 第二行为row 1 ,每行有2个字段col 0 , col 1刚才修改的是第一行的第二个字段name=abc,这里列出了字段的值为61 62 63代表了abc,转换过程首先将abc转换为ascii码,然后将ascii码转换成16进制

SQL> select ascii('a'),ascii('b'),ascii('c') from dual;


ASCII('A') ASCII('B') ASCII('C')

---------- ---------- ----------

       97         98         99


将 97 98 99 转换成16进制数正好等于dump文件中的61 62 63,可知dump出来的数据库是准确的没错


再从磁盘中将该块存放的数据dump出来和内存中dump出来的信息做比较

SQL> select file_name from dba_data_files where file_id=1;


FILE_NAME

------------------------------------------------------------------------------


/u01/app/oracle/oradata/WH/system01.dbf


SQL> alter system dump datafile '/u01/app/oracle/oradata/WH/system01.dbf' block

86626;


System altered.


下面是磁盘中保存数据块86626的关键信息

flag=--------

ntab=1

nrow=2

frre=-1

fsbo=0x16

fseo=0x1f86

avsp=0x1f77

tosp=0x1f77

0xe:pti[0]      nrow=2  offs=0

0x12:pri[0]     offs=0x1f86

0x14:pri[1]     offs=0x1f90

block_row_dump:

tab 0, row 0, @0x1f86

tl: 10 fb: --H-FL-- lb: 0x2  cc: 2

col  0: [ 2]  c1 02

col  1: [ 3]  61 62 63

tab 0, row 1, @0x1f90

tl: 8 fb: --H-FL-- lb: 0x0  cc: 2

col  0: [ 2]  c1 03

col  1: [ 1]  62

end_of_block_dump

End dump data block from file /u01/app/oracle/oradata/WH/system01.dbf minblk 86626 maxblk 86626

SQL> select file_name from dba_data_files where file_id=1;


这里主要看下面这段,竟然发现磁盘中的数据未提交已经被修改已经被写改

tl: 10 fb: --H-FL-- lb: 0x2  cc: 2

col  0: [ 2]  c1 02

col  1: [ 3]  61 62 63


从这里可以得出结论当一个事物产生没有commit提交前,内存中的数据已经被修改过来,磁盘中的数据是否修改不一定。


下面的列出了在改块上存在锁,从lck中看出锁住了一行数据

Object id on Block? Y

seg/obj: 0x11195  csc: 0x00.c93cf  itc: 2  flg: O  typ: 1 - DATA

    fsl: 0  fnx: 0x0 ver: 0x01


Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0x01   0x000b.002.00000006  0x0140000b.0005.04  C---    0  scn 0x0000.000c9359

0x02   0x0011.003.00000006  0x0140006b.0005.04  ----    1  fsc 0x0000.00000000


tl: 10 fb: --H-FL-- lb: 0x2  cc: 2  0x2 表名改行存在锁标记,正好是我们修改的行

tl: 8 fb: --H-FL-- lb: 0x0  cc: 2   0x0  表明改行没有锁标记


那么当查询这个表的时候,oracle会检查到在该行上有一个锁标记,那么它就会读取uba(undo block address)的地址:0x0140006b.0005.04 同样也是一个16进制数,0x为16进制数标示符


SQL> select to_number('0140006b','xxxxxxxx') from dual;


TO_NUMBER('0140006B','XXXXXXXX')

--------------------------------

                       20971627


根据oracle提供的包可以求出20971627对应的文件编号和数据块编号


SQL> select dbms_utility.data_block_address_file(20971627) fno, dbms_utility.da

a_block_address_block(20971627) bno from dual;


      FNO        BNO

---------- ----------

        5        107


根据文件编号查出file_id=5的正好为前面修改的UNDOTBS2

SQL> select tablespace_name from dba_data_files where file_id=5;


TABLESPACE_NAME

------------------------------

UNDOTBS2


v$rollstat记录了对回滚段处于online的跟踪信息,这里查询给出查询关键信息如下


SQL> desc v$rollstat;

Name                                      Null?    Type

----------------------------------------- -------- -----------------------


USN                                                NUMBER

LATCH                                              NUMBER

EXTENTS                                            NUMBER

RSSIZE                                             NUMBER

WRITES                                             NUMBER

XACTS                                              NUMBER

GETS                                               NUMBER

WAITS                                              NUMBER

OPTSIZE                                            NUMBER

HWMSIZE                                            NUMBER

SHRINKS                                            NUMBER

WRAPS                                              NUMBER

EXTENDS                                            NUMBER

AVESHRINK                                          NUMBER

AVEACTIVE                                          NUMBER

STATUS                                             VARCHAR2(15)

CUREXT                                             NUMBER

CURBLK                                             NUMBER


SQL> select usn,xacts from v$rollstat;


      USN      XACTS

---------- ----------

        0          0

       11          0

       12          0

       13          0

       14          0

       15          0

       16          0

       17          1

       18          0

       19          0

       20          0


11 rows selected.


这里表明这在回滚段编号为17的上面有个活动的事物,根据编号查询dba_rollback_segs, 找到回滚段名


SQL> select segment_name from dba_rollback_segs where segment_id=17;


SEGMENT_NAME

------------------------------

_SYSSMU17_1367581872$


根据回滚段名查询dba_extents 数据库字典,可以看出该回滚段正如前面所说包含了2个extended,每个extended包含了8个块,第一个extended包含从105往后的连续8个块,

第二个从113往后的连续8个块,前面求出的ubs地址的快编号107属于第一个extended。

SQL> select segment_name,tablespace_name,file_id,block_id,blocks from dba_extent

s where segment_name='_SYSSMU17_1367581872$';


SEGMENT_NAME                   TABLESPACE_NAME    FILE_ID   BLOCK_ID     BLOCKS

------------------------------ --------------- ---------- ---------- ----------

_SYSSMU17_1367581872$          UNDOTBS2                 5        105          8

_SYSSMU17_1367581872$          UNDOTBS2                 5        113          8




SQL> alter system dump datafile 5 block 107;


System altered.


将文件编号为5,数据块编号为107的数据库块dump出来分析,在uba地址0x0140006b.0005.04中 04表示了在undo中的记录地址

下面列出了改地址记录的信息

*-----------------------------

* Rec #0x4  slt: 0x03  objn: 70037(0x00011195)  objd: 70037  tblspc: 0(0x00000000)

*       Layer:  11 (Row)   opc: 1   rci 0x00

Undo type:  Regular undo    Begin trans    Last buffer split:  No

Temp Object:  No

Tablespace Undo:  No

rdba: 0x00000000Ext idx: 0

flg2: 0

*-----------------------------

uba: 0x0140006b.0005.03 ctl max scn: 0x0000.000c8af8 prv tx scn: 0x0000.000c8af8

txn start scn: scn: 0x0000.000c93a5 logon user: 0

prev brb: 0 prev bcl: 0

KDO undo record:

KTB Redo

op: 0x03  ver: 0x01

compat bit: 4 (post-11) padding: 1

op: Z

KDO Op code: URP row dependencies Disabled

 xtype: XA flags: 0x00000000  bdba: 0x00415262  hdba: 0x00415261

itli: 2  ispac: 0  maxfr: 4863

tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 15

ncol: 2 nnew: 1 size: -2

col  1: [ 1]  61


End dump data blocks tsn: 5 file#: 5 minblk 107 maxblk 107


可以看到 col  1: [ 1]  61 转换后正好是修改之前的a,那么第二个session就从file_id=5 block_id=107的数据块中读出了修改前的信息


通过包 dbms_utility算出的uba(0x0140006b.0005.04)对应的地址在v$trasaction试图中可以查询得到,这四个字段的值正好对应了uba的地址,可以看出前面的所述是有依据可言的。

SQL> select ubafil,ubablk,ubasqn,ubarec from v$transaction;


   UBAFIL     UBABLK     UBASQN     UBAREC

---------- ---------- ---------- ----------

        5        107          5          4


SQL>