我們可以透過以下命令檢視系統回滾段,主要用於資料庫的內部事務或SYS的事務資訊記錄，UNDO$的資訊記錄.
``` sql
--oracle11.2.0.4
--segment 資訊
select segment_name,segment_type,blocks,extents,header_file,header_block from dba_segments where segment_name='SYSTEM';

SEGMENT_NAME SEGMENT_TYPE     BLOCKS	EXTENTS HEADER_FILE HEADER_BLOCK
------------ ------------ ---------- ---------- ----------- ------------
SYSTEM	     ROLLBACK		  48	      6 	  1	     128
--區資訊
col owner for a12
col segment_name for a12
col segment_type for a12
select owner,segment_name,file_id,EXTENT_ID,block_id,BLOCKS 
from dba_extents where segment_name='SYSTEM';

OWNER	     SEGMENT_NAME    FILE_ID  EXTENT_ID   BLOCK_ID     BLOCKS
------------ ------------ ---------- ---------- ---------- ----------
SYS	     SYSTEM		   1	      0        128	    8
SYS	     SYSTEM		   1	      1        136	    8
SYS	     SYSTEM		   1	      2        528	    8
SYS	     SYSTEM		   1	      3        536	    8
SYS	     SYSTEM		   1	      4        544	    8
SYS	     SYSTEM		   1	      5        552	    8
```

透過bbed檢查驗證,塊128為Undo段頭，其他存放undo資料。

``` shell
#128
BBED> map /v dba 1,128
 File: /u01/app/oracle/oradata/mydb11/system01.dbf (1)
 Block: 128                                   Dba:0x00400080
------------------------------------------------------------
 Unlimited Undo Segment Header

#136
BBED>  map /v dba 1,136
 File: /u01/app/oracle/oradata/mydb11/system01.dbf (1)
 Block: 136                                   Dba:0x00400088
------------------------------------------------------------
 Undo Data

#dump 128
alter system dump datafile 1 block 128;
#trace
Dump of buffer cache at level 4 for tsn=0 rdba=4194432
BH (0x91ff8b18) file#: 1 rdba: 0x00400080 (1/128) class: 15 ba: 0x91f42000
  set: 12 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 0,28
  dbwrid: 0 obj: -1 objn: 0 tsn: 0 afn: 1 hint: f
  hash: [0xa75ba9b0,0xa75ba9b0] lru: [0x91ff8d40,0x91ff8ad0]
  obj-flags: object_ckpt_list
  ckptq: [0x91ff7b40,0xa7c603e8] fileq: [0x8fff14f0,0xa7c60408] objq: [0xa3fe9a88,0xa3fe9a88] objaq: [0xa3fe9a68,0x91fdaad8]
  st: XCURRENT md: NULL fpin: 'ktuwh72: ktugus:ktuswr1' tch: 1
  flags: buffer_dirty redo_since_read
  LRBA: [0x6.6626.0] LSCN: [0x0.f2a66] HSCN: [0x0.f2a87] HSUB: [1]
Block dump from disk:
buffer tsn: 0 rdba: 0x00400080 (1/128)
scn: 0x0000.000f2a5d seq: 0x01 flg: 0x04 tail: 0x2a5d0e01
frmt: 0x02 chkval: 0xa4a2 type: 0x0e=KTU UNDO HEADER W/UNLIMITED EXTENTS
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007FF6ADAA3A00 to 0x00007FF6ADAA5A00
.....
  Extent Control Header
  -----------------------------------------------------------------
  Extent Header:: spare1: 0      spare2: 0      #extents: 6      #blocks: 47
                  last map  0x00000000  #maps: 0      offset: 4128
      Highwater::  0x00400220  ext#: 4      blk#: 0      ext size: 8
  #blocks in seg. hdr's freelists: 0
  #blocks below: 0
  mapblk  0x00000000  offset: 4
                   Unlocked
     Map Header:: next  0x00000000  #extents: 6    obj#: 0      flag: 0x40000000
  Extent Map
  -----------------------------------------------------------------
   0x00400081  length: 7
   0x00400088  length: 8
   0x00400210  length: 8
   0x00400218  length: 8
   0x00400220  length: 8
   0x00400228  length: 8

TRN CTL:: seq: 0x001c chd: 0x0002 ctl: 0x0018 inc: 0x00000000 nfb: 0x0001
            mgc: 0x8002 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
            uba: 0x00400220.001c.01 scn: 0x0000.000e184c
Version: 0x01
  FREE BLOCK POOL::
    uba: 0x00400220.001c.0a ext: 0x4  spc: 0x17d2
    uba: 0x00000000.001a.02 ext: 0x2  spc: 0x1f16
    uba: 0x00000000.0018.3a ext: 0x0  spc: 0x80e
    uba: 0x00000000.0000.00 ext: 0x0  spc: 0x0
    uba: 0x00000000.0000.00 ext: 0x0  spc: 0x0
  TRN TBL::
```

回滾段塊結構說明

- a. (ktubhseq): undo record seq# - this is the seq# of the block that 
this undo record WILL BE APPLIED TO. 
This is from the Undo Block. It is NOT the seq# of the undo block itself.

- b. (ktudbseq): redo RECORD seq# - this is the seq# number in the block 
that this redo WILL BE APPLIED TO. This is from the Redo Record.

``` shell
struct ktubh
{
kxid ktubhxid; /* txid of tx currently using or last used this block */
ub2 ktubhseq; /* undo block sequence number */
ub1 ktubhcnt; /* high water mark record index, number of undo entries */
ub1 ktubhirb; /* rollback record index, rec index to start the rollback */
ub1 ktubhicl; /* collecting record index, rec index to start retrieving col info */
ub1 ktubhflg; /* dummy */
ub2 ktubhidx[1]; /* byte offset of record in block, grows at runtime */
};

struct ktudb Kernel Transaction Undo Data operation Block (redo)
{
ub2 ktudbsiz; /* size of entry */
ub2 ktudbspc; /* verification: space left in undo block */
ub2 ktudbflg; /* flag to indicate the kind of redo operation */
kxid ktudbxid; /* current tx id */
ub2 ktudbseq; /* block sequence number */
ub1 ktudbrec; /* new record index for this change */
};
```

【UNDO】Oracle系統回滾段說明

相關文章