关于oracle数据读一致性的理解

santaclaus

　　查询数据库中所有的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'>　　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>>
　　System>　　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>　　1 row updated.
　　再执行查询看到已经更改过来
　　SQL> select * from test;
　　ID NAME
　　---------- ----------
　　1 abc
　　2 b
　　开启另外一个session再查询表test
　　C:\>sqlplus sys/oracle@DGWH as sysdba

　　SQL*Plus:>　　Copyright (c) 1982, 2005, Oracle.  All rights reserved.
　　Connected to:

　　Oracle Database 11g Enterprise Edition>　　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>　　_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>>
　　System>　　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>　　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>>　　86626;

　　System>　　下面是磁盘中保存数据块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>　　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>>
　　System>　　将文件编号为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>　　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>　　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>