1.從DML更新事務開始

來看這個更新語句:

SQL> conn scott/scott

Connected.

SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM

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

DEPTNO

----------

7369 SMITH CLERK 7902 17-DEC-80 800

20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300

30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500

30

EMPNO ENAME JOB MGR HIREDATE SAL COMM

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

DEPTNO

----------

7566 JONES MANAGER 7839 02-APR-81 2975

20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400

30

7698 BLAKE MANAGER 7839 01-MAY-81 2850

30

EMPNO ENAME JOB MGR HIREDATE SAL COMM

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

DEPTNO

----------

7782 CLARK MANAGER 7839 09-JUN-81 2450

10

7788 SCOTT ANALYST 7566 19-APR-87 10

20

7839 KING PRESIDENT 17-NOV-81 5000

10

EMPNO ENAME JOB MGR HIREDATE SAL COMM

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

DEPTNO

----------

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0

30

7876 ADAMS CLERK 7788 23-MAY-87 1100

20

7900 JAMES CLERK 7698 03-DEC-81 950

30

EMPNO ENAME JOB MGR HIREDATE SAL COMM

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

DEPTNO

----------

7902 FORD ANALYST 7566 03-DEC-81 3000

20

7934 MILLER CLERK 7782 23-JAN-82 1300

10

14 rows selected.

SQL>

SQL> select sal from emp where empno=7788;

SAL

----------

10

SQL> update emp set sal=4000 where empno=7788;

1 row updated.

SQL> select sal from emp where empno=7788;

SAL

----------

4000

先不提交這個事務,在另外視窗新開session,使用sys使用者查詢相關資訊,進行進一步的分析

2.獲得事務資訊

從事務表中可以獲得關於這個事務的資訊,該事務位於9號回滾段(XIDUSN),在9號回滾段

上,該事務位於第29號事務槽(XIDSLOT):

SQL> select xidusn,xidslot,xidsqn,ubablk,ubafil,ubarec from v$transaction;

XIDUSN XIDSLOT XIDSQN UBABLK UBAFIL UBAREC

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

9 29 385 1350 2 22

從v$rollstat檢視中也可獲得事務資訊,xacts欄位代表的是活動事務的數量,同樣看到該事務

位於9號回滾段

SQL> select usn,writes,rssize,xacts,hwmsize,shrinks,wraps from v$rollstat;

USN WRITES RSSIZE XACTS HWMSIZE SHRINKS WRAPS

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

0 7620 385024 0 385024 0 0

1 21390 29351936 0 29351936 0 0

2 22108 3268608 0 3268608 0 0

3 29954 450560 0 450560 0 0

4 23700 843776 0 843776 0 0

5 23334 450560 0 450560 0 0

6 21082 450560 0 450560 0 0

7 23146 2285568 0 2285568 0 0

8 28742 843776 0 843776 0 1

9 22648 2088960 1 2088960 0 0

10 24326 2220032 0 2220032 0 0

11 rows selected.

獲得回滾段名稱並轉儲段頭資訊

查詢v$rollname檢視獲得回滾段名稱,並轉儲回滾段頭資訊:

SQL> select * from v$rollname a where a.usn=9;

USN NAME

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

9 _SYSSMU9$

SQL> alter system dump undo header '_SYSSMU9$';

System altered.

生成的跟蹤檔案如下:

SQL> select

2 d.value||'/'||lower(rtrim(i.instance,

3 chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name

4 from ( select p.spid

5 from sys.v$mystat m,

6 sys.v$session s,sys.v$process p

7 where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,

8 ( select t.instance from sys.v$thread t,sys.v$parameter v

9 where v.name = 'thread' and

10 (v.value = 0 or t.thread# = to_number(v.value))) i,

11 ( select value from sys.v$parameter

12 where name = 'user_dump_dest') d

13 /

TRACE_FILE_NAME

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

/u01/app/oracle/admin/jingyong/udump/jingyong_ora_2611.trc

SQL>

4.獲得跟蹤檔案資訊

注意這就是前邊多閃提到過的回滾段頭資訊,其中包括事務表資訊,從以下的跟蹤檔案中,

可以清晰地看到這些內容:

*** 2013-01-07 08:19:09.209

********************************************************************************

Undo Segment: _SYSSMU9$ (9)

********************************************************************************

Extent Control Header

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

Extent Header:: spare1: 0 spare2: 0 #extents: 17 #blocks: 255

last map 0x00000000 #maps: 0 offset: 4080

Highwater:: 0x00800546 ext#: 10 blk#: 61 ext size: 128

#blocks in seg. hdr's freelists: 0

#blocks below: 0

mapblk 0x00000000 offset: 10

Unlocked

Map Header:: next 0x00000000 #extents: 17 obj#: 0 flag: 0x40000000

Extent Map

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

0x0080008a length: 7

0x008000f1 length: 8

0x008001d1 length: 8

0x008001d9 length: 8

0x008001e1 length: 8

0x008001e9 length: 8

0x008001f1 length: 8

0x008001f9 length: 8

0x00800201 length: 8

0x00800289 length: 8

0x00800509 length: 128

0x00800011 length: 8

0x00800041 length: 8

0x00800061 length: 8

0x008000d9 length: 8

0x008000e9 length: 8

0x008000a9 length: 8

Retention Table

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

Extent Number:0 Commit Time: 1388711270

Extent Number:1 Commit Time: 1388711270

Extent Number:2 Commit Time: 1388711270

Extent Number:3 Commit Time: 1388711270

Extent Number:4 Commit Time: 1388711270

Extent Number:5 Commit Time: 1388711270

Extent Number:6 Commit Time: 1388711270

Extent Number:7 Commit Time: 1388711270

Extent Number:8 Commit Time: 1388711270

Extent Number:9 Commit Time: 1388712870

Extent Number:10 Commit Time: 0

Extent Number:11 Commit Time: 1388710789

Extent Number:12 Commit Time: 1388710854

Extent Number:13 Commit Time: 1388710854

Extent Number:14 Commit Time: 1388710854

Extent Number:15 Commit Time: 1388711270

Extent Number:16 Commit Time: 1388711270

TRN CTL:: seq: 0x0129 chd: 0x001e ctl: 0x0019 inc: 0x00000000 nfb: 0x0000

mgc: 0x8201 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)

uba: 0x00800546.0129.16 scn: 0x0000.000e4b58

Version: 0x01

FREE BLOCK POOL::

uba: 0x00000000.0129.15 ext: 0xa spc: 0x1440

uba: 0x00000000.0129.05 ext: 0xa spc: 0x1e08

uba: 0x00000000.0129.42 ext: 0xa spc: 0x73e

uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0

TRN TBL::

index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt

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

0x00 9 0x00 0x0181 0x0004 0x0000.000e5e2d 0x00800545 0x0000.000.00000000 0x00000001 0x00000000 1357510314

0x01 9 0x00 0x0181 0x0003 0x0000.000e570f 0x00800544 0x0000.000.00000000 0x00000001 0x00000000 1357505284

0x02 9 0x00 0x0181 0x0009 0x0000.000e5c13 0x00800545 0x0000.000.00000000 0x00000001 0x00000000 1357508885

0x03 9 0x00 0x0181 0x0007 0x0000.000e58ac 0x00800545 0x0000.000.00000000 0x00000002 0x00000000 1357506485

0x04 9 0x00 0x0181 0x0006 0x0000.000e5f7d 0x00800545 0x0000.000.00000000 0x00000001 0x00000000 1357511286

0x05 9 0x00 0x0181 0x0002 0x0000.000e5c07 0x00800545 0x0000.000.00000000 0x00000001 0x00000000 1357508885

0x06 9 0x00 0x0181 0x000a 0x0000.000e5f89 0x00800545 0x0000.000.00000000 0x00000001 0x00000000 1357511286

0x07 9 0x00 0x0181 0x0008 0x0000.000e58b6 0x00800545 0x0000.000.00000000 0x00000001 0x00000000 1357506485

0x08 9 0x00 0x0181 0x002e 0x0000.000e5a56 0x00800545 0x0000.000.00000000 0x00000001 0x00000000 1357507685

0x09 9 0x00 0x0181 0x000b 0x0000.000e5c1e 0x00800545 0x0000.000.00000000 0x00000001 0x00000000 1357508886

0x0a 9 0x00 0x0181 0x0011 0x0000.000e5f93 0x00800545 0x0000.000.00000000 0x00000001 0x00000000 1357511286

0x0b 9 0x00 0x0181 0x002a 0x0000.000e5dba 0x00800545 0x0000.000.00000000 0x00000001 0x00000000 1357510086

0x0c 9 0x00 0x0181 0x0000 0x0000.000e5dd4 0x00800545 0x0000.000.00000000 0x00000001 0x00000000 1357510086

0x0d 9 0x00 0x0181 0x0010 0x0000.000e5fa8 0x00800545 0x0000.000.00000000 0x00000001 0x00000000 1357511286

0x0e 9 0x00 0x0181 0x0016 0x0000.000e62f8 0x00800546 0x0000.000.00000000 0x00000001 0x00000000 1357513687

0x0f 9 0x00 0x0181 0x0014 0x0000.000e6662 0x00800546 0x0000.000.00000000 0x00000001 0x00000000 1357516088

0x10 9 0x00 0x0181 0x0012 0x0000.000e5fb2 0x00800545 0x0000.000.00000000 0x00000001 0x00000000 1357511286

0x11 9 0x00 0x0181 0x000d 0x0000.000e5f9d 0x00800545 0x0000.000.00000000 0x00000001 0x00000000 1357511286

0x12 9 0x00 0x0181 0x0017 0x0000.000e6150 0x00800545 0x0000.000.00000000 0x00000001 0x00000000 1357512487

0x13 9 0x00 0x0181 0x002d 0x0000.000e64a1 0x00800546 0x0000.000.00000000 0x00000001 0x00000000 1357514887

0x14 9 0x00 0x0180 0x001b 0x0000.000e67fe 0x00800546 0x0000.000.00000000 0x00000001 0x00000000 1357517287

0x15 9 0x00 0x0181 0x000f 0x0000.000e6658 0x00800546 0x0000.000.00000000 0x00000001 0x00000000 1357516088

0x16 9 0x00 0x0181 0x0013 0x0000.000e6301 0x00800546 0x0000.000.00000000 0x00000001 0x00000000 1357513687

0x17 9 0x00 0x0181 0x000e 0x0000.000e615b 0x00800545 0x0000.000.00000000 0x00000001 0x00000000 1357512487

0x18 9 0x00 0x0180 0x0029 0x0000.000e53b5 0x00800544 0x0000.000.00000000 0x00000001 0x00000000 1357502883

0x19 9 0x00 0x0181 0xffff 0x0000.000e6813 0x00800546 0x0000.000.00000000 0x00000001 0x00000000 1357517288

0x1a 9 0x00 0x0181 0x0015 0x0000.000e64ba 0x00800546 0x0000.000.00000000 0x00000001 0x00000000 1357514888

0x1b 9 0x00 0x0181 0x0019 0x0000.000e6809 0x00800546 0x0000.000.00000000 0x00000001 0x00000000 1357517288

0x1c 9 0x00 0x0180 0x001f 0x0000.000e4cff 0x00800544 0x0000.000.00000000 0x00000001 0x00000000 1357498082

0x1d 10 0x80 0x0181 0x000a 0x0000.000e6858 0x00800546 0x0000.000.00000000 0x00000001 0x00000000 0

0x1e 9 0x00 0x0180 0x001c 0x0000.000e4cf4 0x00800544 0x0000.000.00000000 0x00000001 0x00000000 1357498082

0x1f 9 0x00 0x0180 0x0024 0x0000.000e4e9c 0x00800544 0x0000.000.00000000 0x00000001 0x00000000 1357499280

0x20 9 0x00 0x0180 0x002f 0x0000.000e5059 0x00800544 0x0000.000.00000000 0x00000001 0x00000000 1357500481

0x21 9 0x00 0x0180 0x0005 0x0000.000e5a6a 0x00800545 0x0000.000.00000000 0x00000001 0x00000000 1357507685

0x22 9 0x00 0x0180 0x0023 0x0000.000e5044 0x00800544 0x0000.000.00000000 0x00000001 0x00000000 1357500481

0x23 9 0x00 0x0180 0x0020 0x0000.000e504f 0x00800544 0x0000.000.00000000 0x00000001 0x00000000 1357500481

0x24 9 0x00 0x0180 0x0022 0x0000.000e4ea6 0x00800544 0x0000.000.00000000 0x00000001 0x00000000 1357499282

0x25 9 0x00 0x0180 0x0026 0x0000.000e5568 0x00800544 0x0000.000.00000000 0x00000001 0x00000000 1357504084

0x26 9 0x00 0x0180 0x0001 0x0000.000e5705 0x00800544 0x0000.000.00000000 0x00000001 0x00000000 1357505284

0x27 9 0x00 0x0180 0x0028 0x0000.000e5201 0x00800544 0x0000.000.00000000 0x00000001 0x00000000 1357501683

0x28 9 0x00 0x0180 0x002c 0x0000.000e520c 0x00800544 0x0000.000.00000000 0x00000001 0x00000000 1357501683

0x29 9 0x00 0x0180 0x002b 0x0000.000e5553 0x00800544 0x0000.000.00000000 0x00000001 0x00000000 1357504083

0x2a 9 0x00 0x0180 0x000c 0x0000.000e5dc6 0x00800545 0x0000.000.00000000 0x00000001 0x00000000 1357510086

0x2b 9 0x00 0x0180 0x0025 0x0000.000e555e 0x00800544 0x0000.000.00000000 0x00000001 0x00000000 1357504084

0x2c 9 0x00 0x0180 0x0018 0x0000.000e53a9 0x00800544 0x0000.000.00000000 0x00000001 0x00000000 1357502883

0x2d 9 0x00 0x0180 0x001a 0x0000.000e64ac 0x00800546 0x0000.000.00000000 0x00000001 0x00000000 1357514888

0x2e 9 0x00 0x0180 0x0021 0x0000.000e5a60 0x00800545 0x0000.000.00000000 0x00000001 0x00000000 1357507685

0x2f 9 0x00 0x0180 0x0027 0x0000.000e5064 0x00800544 0x0000.000.00000000 0x00000001 0x00000000 1357500483

回顧前面的事務資訊,該事務正好佔用的是第29號事務槽(0x1d),狀態(state)為10代表是活動的事務

SQL> select to_number('1d','xx') from dual ;

TO_NUMBER('1D','XX')

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

29

5.轉儲前映象資訊

再來看DBA(Data Block Address),這個DBA指向的就是包含這個事務的前映象的資料塊地址

0x00800546

DBA代表資料塊的儲存地址,透過轉換DBA計算出檔案號和資料塊號

SQL> SELECT DBMS_UTILITY.data_block_address_file (

2 TO_NUMBER (LTRIM ('0x00800546', '0x'), 'xxxxxxxx'))

3 AS file_no,

4 DBMS_UTILITY.data_block_address_block (

5 TO_NUMBER (LTRIM ('0x00800546', '0x'), 'xxxxxxxx'))

6 AS block_no

7 FROM DUAL;

FILE_NO BLOCK_NO

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

2 1350

經過轉換後,該前映象資訊位於file 2, block 1350,這和從事務表中查詢得的資料完全一致:

SQL> select xidusn,xidslot,xidsqn,ubablk,ubafil,ubarec from v$transaction;

XIDUSN XIDSLOT XIDSQN UBABLK UBAFIL UBAREC

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

9 29 385 1350 2 22

為了說明一些其他內容,繼續先前scott使用者的事務,再更新2條記錄:

SQL> update emp set sal=4000 where empno=7782;

1 row updated.

SQL> update emp set sal=4000 where empno=7698;

1 row updated.

將回滾段中的這個block轉儲出來:

SQL> alter system dump datafile 2 block 1350;

System altered.

SQL> select

2 d.value||'/'||lower(rtrim(i.instance,

3 chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name

4 from ( select p.spid

5 from sys.v$mystat m,

6 sys.v$session s,sys.v$process p

7 where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,

8 ( select t.instance from sys.v$thread t,sys.v$parameter v

9 where v.name = 'thread' and

10 (v.value = 0 or t.thread# = to_number(v.value))) i,

11 ( select value from sys.v$parameter

12 where name = 'user_dump_dest') d

13 /

TRACE_FILE_NAME

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

/u01/app/oracle/admin/jingyong/udump/jingyong_ora_4778.trc

SQL>

這是跟蹤檔案開始部分的資訊:

*** 2013-01-07 08:46:13.536

*** SERVICE NAME:(SYS$USERS) 2013-01-07 08:46:13.536

*** SESSION ID:(142.51) 2013-01-07 08:46:13.536

Start dump data blocks tsn: 1 file#: 2 minblk 1350 maxblk 1350

buffer tsn: 1 rdba: 0x00800546 (2/1350)

scn: 0x0000.000e6ba3 seq: 0x01 flg: 0x00 tail: 0x6ba30201

frmt: 0x02 chkval: 0x0000 type: 0x02=KTU UNDO BLOCK

Hex dump of block: st=0, typ_found=1

********************************************************************************

UNDO BLK:

xid: 0x0009.01d.00000181 seq: 0x129 cnt: 0x18 irb: 0x18 icl: 0x0 flg: 0x0000

注意,這部分資訊中有一個引數irb:0x18,irb指的是回滾段中記錄的最近的沒有提交變更的開始

之處,如果開始回滾,這是起始的搜尋點.

接下來是回滾資訊的偏移量,最後一個偏移地址正是0x18的資訊

Rec Offset Rec Offset Rec Offset Rec Offset Rec Offset

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

0x01 0x1f3c 0x02 0x1ed0 0x03 0x1e54 0x04 0x1de8 0x05 0x1d3c

0x06 0x1c90 0x07 0x1c24 0x08 0x1b78 0x09 0x1acc 0x0a 0x1a20

0x0b 0x1974 0x0c 0x1908 0x0d 0x189c 0x0e 0x17f0 0x0f 0x1784

0x10 0x1718 0x11 0x166c 0x12 0x1600 0x13 0x1594 0x14 0x14e8

0x15 0x147c 0x16 0x13e4 0x17 0x1388 0x18 0x132c

那麼我們可以找到Rec 0x18的資訊:

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

* Rec #0x18 slt: 0x1d objn: 51148(0x0000c7cc) objd: 51148 tblspc: 4(0x00000004)

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

Undo type: Regular undo Last buffer split: No

Temp Object: No

Tablespace Undo: No

rdba: 0x00000000

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

KDO undo record:

KTB Redo

op: 0x02 ver: 0x01

op: C uba: 0x00800546.0129.17

KDO Op code: URP row dependencies Disabled

xtype: XA flags: 0x00000000 bdba: 0x01000020 hdba: 0x0100001b

itli: 1 ispac: 0 maxfr: 4858

tabn: 0 slot: 5(0x5) flag: 0x2c lock: 0 ckix: 191

ncol: 8 nnew: 1 size: 1

col 5: [ 3] c2 1d 33

從上面的資訊中可以看到objn: 51148(0x0000c7cc) objd: 51148 tblspc: 4(0x00000004)

惹objn: 51148(0x0000c7cc) objd: 51148 是指object_id

tblspc: 4(0x00000004)是指表空間號4

SQL> select a.owner,a.object_name,a.object_type from dba_objects a where a.object_id=51148;

OWNER OBJECT_NAME OBJECT_TYPE

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

SCOTT EMP TABLE

SQL> select * from v$tablespace;

TS# NAME INCLUDED_IN_DATABASE_BACKUP BIGFILE FLASHBACK_ON ENCRYPT_IN_BACKUP

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

0 SYSTEM YES NO YES

1 UNDOTBS1 YES NO YES

2 SYSAUX YES NO YES

4 USERS YES NO YES

6 EXAMPLE YES NO YES

3 TEMP NO NO YES

6 rows selected

SQL> select username,default_tablespace from dba_users where username='SCOTT';

USERNAME DEFAULT_TABLESPACE

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

SCOTT USERS

透過上面的查詢可以知道0x18的前映象記錄是記錄的對scott使用者的emp表修改的記錄的前映象資訊

col 5: [ 3] c2 1d 33

c2 id 33轉換為十進位制是2850

SQL> select utl_raw.cast_to_number('c21d33') from dual;

UTL_RAW.CAST_TO_NUMBER('C21D33

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

2850

這就是下面的更新語句的前映象資訊,oracle就是這樣透過回滾段保留前映象資訊的

SQL> update emp set sal=4000 where empno=7698;

SQL> select empno,sal from scott.emp where empno in(7788,7782,7698);

EMPNO SAL

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

7698 2850

7782 2450

7788 10

在這條undo記錄上,還記錄一個資料rci,該引數代表的就是undo chain(同一個事務中的多次修改,

根據undo chain連結關聯)的下一個偏移量,此處為0x17,找到0x17這條undo記錄:

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

* Rec #0x17 slt: 0x1d objn: 51148(0x0000c7cc) objd: 51148 tblspc: 4(0x00000004)

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

Undo type: Regular undo Last buffer split: No

Temp Object: No

Tablespace Undo: No

rdba: 0x00000000

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

KDO undo record:

KTB Redo

op: 0x02 ver: 0x01

op: C uba: 0x00800546.0129.16

KDO Op code: URP row dependencies Disabled

xtype: XA flags: 0x00000000 bdba: 0x01000020 hdba: 0x0100001b

itli: 1 ispac: 0 maxfr: 4858

tabn: 0 slot: 6(0x6) flag: 0x2c lock: 0 ckix: 41

ncol: 8 nnew: 1 size: 1

col 5: [ 3] c2 19 33

這裡記錄的c2 19 33轉換為十進位制就是2450,是第二條更新記錄的前映象資訊

SQL> update emp set sal=4000 where empno=7782;

SQL> select utl_raw.cast_to_number('c21933') from dual;

UTL_RAW.CAST_TO_NUMBER('C21933

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

2450

這條undo記錄中的rci指向下一條記錄是0x16,找到0x16:

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

* Rec #0x16 slt: 0x1d objn: 51148(0x0000c7cc) objd: 51148 tblspc: 4(0x00000004)

* 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: 0x00800546.0129.14 ctl max scn: 0x0000.000e4b4d prv tx scn: 0x0000.000e4b58

txn start scn: scn: 0x0000.000e684e logon user: 54

prev brb: 8389956 prev bcl: 0

KDO undo record:

KTB Redo

op: 0x04 ver: 0x01

op: L itl: xid: 0x0009.00e.0000017e uba: 0x0080053d.0129.2f

flg: C--- lkc: 0 scn: 0x0000.000d444e

KDO Op code: URP row dependencies Disabled

xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x01000020 hdba: 0x0100001b

itli: 1 ispac: 0 maxfr: 4858

tabn: 0 slot: 7(0x7) flag: 0x2c lock: 0 ckix: 191

ncol: 8 nnew: 1 size: 0

Vector content:

col 5: [ 2] c1 0b

這裡的c10b轉換十進位制是10,正是第一條更新的記錄:

SQL> update emp set sal=4000 where empno=7788;

SQL> select utl_raw.cast_to_number('c10b') from dual;

UTL_RAW.CAST_TO_NUMBER('C10B')

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

10

這是這個事務中最後一條更新資料,所以其實undo chain的指標rci為0x00,表示這是最後

一條記錄.也可以從x$bh中找到這些資料塊:

SQL> select b.segment_name,a.file#,a.dbarfil,a.dbablk,a.class,a.state

2 from x$bh a,dba_extents b

3 where b.relative_fno=a.dbarfil

4 and b.block_id<=a.dbablk and b.block_id+b.blocks>a.dbablk

5 and b.owner='SCOTT' and b.segment_name='EMP' order by a.dbablk asc;

SEGMENT_NAME FILE# DBARFIL DBABLK CLASS STATE

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

EMP 4 4 27 4 1

EMP 4 4 28 1 1

EMP 4 4 29 1 1

EMP 4 4 30 1 1

EMP 4 4 31 1 1

EMP 4 4 32 1 3

EMP 4 4 32 1 1

7 rows selected

注意class為4的是段頭,class為1,塊號為28到32的為資料塊,如果此時在其他程式中查詢

scott.emp表,oracle需要構造一致性讀,透過前映象把變化前的資料展現給使用者:

SQL> select * from scott.emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7369 SMITH CLERK 7902 1980-12-17 800.00 20

7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30

7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30

7566 JONES MANAGER 7839 1981-4-2 2975.00 20

7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30

7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30

7782 CLARK MANAGER 7839 1981-6-9 2450.00 10

7788 SCOTT ANALYST 7566 1987-4-19 10.00 20

7839 KING PRESIDENT 1981-11-17 5000.00 10

7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30

7876 ADAMS CLERK 7788 1987-5-23 1100.00 20

7900 JAMES CLERK 7698 1981-12-3 950.00 30

7902 FORD ANALYST 7566 1981-12-3 3000.00 20

7934 MILLER CLERK 7782 1982-1-23 1300.00 10

14 rows selected

再來查詢哪些資料是髒資料

SQL> select b.segment_name,a.file#,a.dbarfil,a.dbablk,a.class,a.state,

2 decode(bitand(flag,1),0,'N','Y') dirty

3 from x$bh a,dba_extents b

4 where b.relative_fno=a.dbarfil

5 and b.block_id<=a.dbablk and b.block_id+b.blocks>a.dbablk

6 and b.owner='SCOTT' and b.segment_name='EMP' order by a.dbablk asc;

SEGMENT_NAME FILE# DBARFIL DBABLK CLASS STATE DIRTY

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

EMP 4 4 27 4 1 N

EMP 4 4 28 1 1 N

EMP 4 4 29 1 1 N

EMP 4 4 30 1 1 N

EMP 4 4 31 1 1 N

EMP 4 4 32 1 3 N

EMP 4 4 32 1 1 Y

EMP 4 4 32 1 3 N

9 rows selected

注意此時,buffer cache中多出了兩個資料塊,也就是32存在4份,其中state為3的就是一致性讀

構造的前映象.dirty為Y的記錄就是進行更改後沒有提交的記錄

6.轉儲資料塊資訊

在前映象資訊中,oracle還記錄了前映象對應的資料塊的地址.可以從bdba記錄中獲得這部分資訊

以上面資料為例bdba: 0x01000020記錄了更改的資料塊塊地址.

SQL> SELECT DBMS_UTILITY.data_block_address_file (

2 TO_NUMBER (LTRIM ('0x01000020', '0x'), 'xxxxxxxx'))

3 AS file_no,

4 DBMS_UTILITY.data_block_address_block (

5 TO_NUMBER (LTRIM ('0x01000020', '0x'), 'xxxxxxxx'))

6 AS block_no

7 FROM DUAL;

FILE_NO BLOCK_NO

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

4 32

0x01000020記錄的正是file 4,block 32

現在將資料表中的block轉儲出來,看看其中的記錄是什麼樣的資訊:

SQL> alter system dump datafile 4 block 32;

System altered.

SQL> select

2 d.value||'/'||lower(rtrim(i.instance,

3 chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name

4 from ( select p.spid

5 from sys.v$mystat m,

6 sys.v$session s,sys.v$process p

7 where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,

8 ( select t.instance from sys.v$thread t,sys.v$parameter v

9 where v.name = 'thread' and

10 (v.value = 0 or t.thread# = to_number(v.value))) i,

11 ( select value from sys.v$parameter

12 where name = 'user_dump_dest') d

13 /

TRACE_FILE_NAME

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

/u01/app/oracle/admin/jingyong/udump/jingyong_ora_4859.trc

檢查跟蹤檔案資訊:

** 2013-01-07 09:27:18.032

*** SERVICE NAME:(SYS$USERS) 2013-01-07 09:27:18.030

*** SESSION ID:(142.53) 2013-01-07 09:27:18.029

Start dump data blocks tsn: 4 file#: 4 minblk 32 maxblk 32

buffer tsn: 4 rdba: 0x01000020 (4/32)

scn: 0x0000.000e6ebb seq: 0x01 flg: 0x04 tail: 0x6ebb0601

frmt: 0x02 chkval: 0xf364 type: 0x06=trans data

Hex dump of block: st=0, typ_found=1

Block header dump: 0x01000020

Object id on Block? Y

seg/obj: 0xc7cc csc: 0x00.e6ebb itc: 2 flg: E typ: 1 - DATA

brn: 0 bdba: 0x1000019 ver: 0x01 opc: 0

inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0x0009.01d.00000181 0x00800546.0129.18 ---- 3 fsc 0x0002.00000000

0x02 0x0002.010.00000158 0x00800598.013f.26 C--- 0 scn 0x0000.000d4495

其中scn表示提交commit scn,fsc表示快速提交scn

這裡存在ITL事務槽資訊,ITL事務槽指Interested Transaction List(ITL),事務必須

獲得一個ITL事務槽才能進行資料修改,ITL內容主要包括:

Xid---------Transaction ID;

Uba---------Undo Block Address

Lck---------Lock Status

注意:Xid=Undo.segment.number+transaction.table.slot.number+wrap

在上面的輸出,看到itl1(0x01)上存在活動事務，將xid=0x0009.01d.00000181分解一下

該事務指向的回滾段號是9

SQL> select xidusn,xidslot,xidsqn,ubablk,ubafil,ubarec from v$transaction;

XIDUSN XIDSLOT XIDSQN UBABLK UBAFIL UBAREC

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

9 29 385 1350 2 22

事務表的事務槽slot號為0x1d(轉換為十進位制是29)

SQL> select to_number('1d','xx') from dual;

TO_NUMBER('1D','XX')

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

29

wrap#為0181正是dump回滾段看到的那個事務

TRN TBL::

index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt

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

......

0x1d 10 0x80 0x0181 0x000a 0x0000.000e6858 0x00800546 0x0000.000.00000000 0x00000001 0x00000000 0

.....

可以看到,在資料塊上同樣存在指向回滾段的事務資訊.

uba代表的是undo block address,指向具體的回滾段.可以看到ITL上uba=0x00800546.0129.18

將這個UBA進行分解:

0x00800546正是前映象的資料塊地址

seq:0129是順序號

18是undo記錄的開始地址(irb資訊)

uba的內容和undo中的資訊完全相符:

********************************************************************************

UNDO BLK:

xid: 0x0009.01d.00000181 seq: 0x129 cnt: 0x18 irb: 0x18 icl: 0x0 flg: 0x0000

繼續向下可以找到這3條被修改的記錄,鎖定位資訊LB指向0x01號ITL事務槽:

tab 0, row 5, @0x1d11

tl: 40 fb: --H-FL-- lb: 0x1 cc: 8

col 0: [ 3] c2 4d 63

col 1: [ 5] 42 4c 41 4b 45

col 2: [ 7] 4d 41 4e 41 47 45 52

col 3: [ 3] c2 4f 28

col 4: [ 7] 77 b5 05 01 01 01 01

col 5: [ 2] c2 29

col 6: *NULL*

col 7: [ 2] c1 1f

tab 0, row 6, @0x1d39

tl: 40 fb: --H-FL-- lb: 0x1 cc: 8

col 0: [ 3] c2 4e 53

col 1: [ 5] 43 4c 41 52 4b

col 2: [ 7] 4d 41 4e 41 47 45 52

col 3: [ 3] c2 4f 28

col 4: [ 7] 77 b5 06 09 01 01 01

col 5: [ 2] c2 29

col 6: *NULL*

col 7: [ 2] c1 0b

tab 0, row 7, @0x1e4c

tl: 40 fb: --H-FL-- lb: 0x1 cc: 8

col 0: [ 3] c2 4e 59

col 1: [ 5] 53 43 4f 54 54

col 2: [ 7] 41 4e 41 4c 59 53 54

col 3: [ 3] c2 4c 43

col 4: [ 7] 77 bb 04 13 01 01 01

col 5: [ 2] c2 29

col 6: *NULL*

col 7: [ 2] c1 15

SQL> select utl_raw.cast_to_number('c229') from dual;

UTL_RAW.CAST_TO_NUMBER('C229')

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

4000

這個事務的過程如下:

TRN TBL::

index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt

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

......

0x1d 10 0x80 0x0181 0x000a 0x0000.000e6858 0x00800546 0x0000.000.00000000 0x00000001 0x00000000 0

..... |

|

TX table slot

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0x0009.01d.00000181 0x00800546.0129.18 ---- 3 fsc 0x0002.00000000

0x02 0x0002.010.00000158 0x00800598.013f.26 C--- 0 scn 0x0000.000d4495

ITL

data row1

data row2 回滾表空間的資料塊-------------------------------Undo block

data row3 (UNDO BLK: |

xid: 0x0009.01d.00000181 |

seq: 0x129 cnt: 0x18 irb: 0x18 |

icl: 0x0 flg: 0x0000) |

鎖定位0x01 Rec #0x18 slt: 0x1d objn: 51148(0x0000c7cc) objd: 51148 ...

資料段回滾段

(1)當一個事務開始時,需要在回滾段事務表上分配一個事務槽.

(2)在資料塊頭部獲得一個ITL事務槽,該事務槽指向回滾段頭的事務槽

(3)在修改資料之前,需要記錄前映象資訊,這個資訊以undo record的形式儲存在回滾段中,

回滾段頭事務槽指向該記錄.

(4)鎖定修改的行,修改行鎖定位(lb-lock byte)指向ITL事務槽

(5)資料修改可以進行

這是一個事務的基本過程

7.塊清除(Block Cleanouts)

當使用者發出提交(commit)之後,oracle怎樣來處理的.oracle是需要寫出redo來保證故障時資料可以被

恢復,我們知道oracle並不需要在提交時就寫出變更的資料塊.那麼在提交時,oracle會對資料塊進行什麼操作?

在事務需要修改資料時,必須分配ITL事務槽,必須鎖定行,必須分配回滾段事務槽和回滾表空間來記錄要修改

的資料的前映象.當事務提交時,oracle需要將回滾段上的事務表資訊標記為非活動,以便空間可以被重用

那麼還有ITL事務資訊和鎖定資訊需要清除,以記錄提交.

由於oracle在資料塊上儲存了ITL和鎖定等事務資訊,所以oracle必須在事務提交之後清除這些事務資料,

這就是塊清除.塊清除主要清除的資料有行級鎖,ITL資訊(包括提交標誌,scn等).

如果提交時修改過的資料塊仍然在buffer cache中,那麼oracle可以清除ITL資訊,這種清除叫做快速塊清除

(fast block cleanout),快速塊清除還有一個限制,當修改的塊數量超過buffer cache的10%,則對超出的部

分不再進行快速塊清除.

如果提交事務時,修改過的資料塊已經被寫回到資料檔案上(或大量修改超出10%的部分),再次讀出該資料塊

進行修改,顯然成本過於高昂,對於這種情況,oracle選擇延遲塊清除(delayed block cleanout),等到下一次

訪問該block時再來清除ITL鎖定資訊,這就是延遲塊清除.oracle透過延遲塊清除來提高資料庫的效能,加快

提交操作.快速提交是最普遍的情況.來看一下延遲塊清除的處理.

繼續進行測試:

SQL> update emp set sal=4000 where empno=7788;

1 row updated.

SQL> update emp set sal=4000 where empno=7782;

1 row updated.

SQL> update emp set sal=4000 where empno=7698;

1 row updated.

更新完成之後,強制重新整理buffer cache,將buffer cache中的資料都寫出到資料檔案:

SQL> alter session set events='immediate trace name flush_cache';

Session altered

此時再提交事務;

SQL> commit;

Commit complete.

由於此時更新過的資料已經寫出到資料檔案,oracle將執行延遲塊清除,將此時的資料塊和回滾段轉儲出來:

[oracle@jingyong ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jan 7 10:18:56 2013

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> alter system dump datafile 4 block 32;

System altered.

SQL> alter system dump undo header '_SYSSMU9$';

System altered.

SQL> alter system dump datafile 2 block 1350;

System altered.

SQL> select

2 d.value||'/'||lower(rtrim(i.instance,

3 chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name

4 from ( select p.spid

5 from sys.v$mystat m,

6 sys.v$session s,sys.v$process p

7 where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,

8 ( select t.instance from sys.v$thread t,sys.v$parameter v

9 where v.name = 'thread' and

10 (v.value = 0 or t.thread# = to_number(v.value))) i,

11 ( select value from sys.v$parameter

12 where name = 'user_dump_dest') d

13 /

TRACE_FILE_NAME

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

/u01/app/oracle/admin/jingyong/udump/jingyong_ora_5023.trc

SQL>

檢視跟蹤檔案資訊,看資料塊上的資訊,ITL事務資訊仍然存在:

*** 2013-01-07 10:19:33.032

*** SERVICE NAME:(SYS$USERS) 2013-01-07 10:19:33.031

*** SESSION ID:(140.421) 2013-01-07 10:19:33.031

Start dump data blocks tsn: 4 file#: 4 minblk 32 maxblk 32

buffer tsn: 4 rdba: 0x01000020 (4/32)

scn: 0x0000.000e6ebb seq: 0x01 flg: 0x04 tail: 0x6ebb0601

frmt: 0x02 chkval: 0xf364 type: 0x06=trans data

Block header dump: 0x01000020

Object id on Block? Y

seg/obj: 0xc7cc csc: 0x00.e6ebb itc: 2 flg: E typ: 1 - DATA

brn: 0 bdba: 0x1000019 ver: 0x01 opc: 0

inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0x0009.01d.00000181 0x00800546.0129.18 ---- 3 fsc 0x0002.00000000

0x02 0x0002.010.00000158 0x00800598.013f.26 C--- 0 scn 0x0000.000d4495

xid=0x0009.01d.00000181的事務lck=3

資料塊的鎖定資訊仍然存在:

tab 0, row 5, @0x1d11

tl: 40 fb: --H-FL-- lb: 0x1 cc: 8

col 0: [ 3] c2 4d 63

col 1: [ 5] 42 4c 41 4b 45

col 2: [ 7] 4d 41 4e 41 47 45 52

col 3: [ 3] c2 4f 28

col 4: [ 7] 77 b5 05 01 01 01 01

col 5: [ 2] c2 29

col 6: *NULL*

col 7: [ 2] c1 1f

tab 0, row 6, @0x1d39

tl: 40 fb: --H-FL-- lb: 0x1 cc: 8

col 0: [ 3] c2 4e 53

col 1: [ 5] 43 4c 41 52 4b

col 2: [ 7] 4d 41 4e 41 47 45 52

col 3: [ 3] c2 4f 28

col 4: [ 7] 77 b5 06 09 01 01 01

col 5: [ 2] c2 29

col 6: *NULL*

col 7: [ 2] c1 0b

tab 0, row 7, @0x1e4c

tl: 40 fb: --H-FL-- lb: 0x1 cc: 8

col 0: [ 3] c2 4e 59

col 1: [ 5] 53 43 4f 54 54

col 2: [ 7] 41 4e 41 4c 59 53 54

col 3: [ 3] c2 4c 43

col 4: [ 7] 77 bb 04 13 01 01 01

col 5: [ 2] c2 29

col 6: *NULL*

col 7: [ 2] c1 15

再來看回滾段的資訊:

*** 2013-01-07 10:20:01.417

********************************************************************************

Undo Segment: _SYSSMU9$ (9)

********************************************************************************

Version: 0x01

FREE BLOCK POOL::

uba: 0x00800546.0129.18 ext: 0xa spc: 0x12ea

uba: 0x00000000.0129.05 ext: 0xa spc: 0x1e08

uba: 0x00000000.0129.42 ext: 0xa spc: 0x73e

uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0

TRN TBL::

index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt

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

0x1d 9 0x00 0x0181 0xffff 0x0000.000e743c 0x00800546 0x0000.000.00000000 0x00000001 0x00000000 1357525082

事務提交,事務表已經釋放。如果此時查詢scott.emp表,資料庫將產生延遲塊清除:

SQL> set autotrace on

SQL> select * from scott.emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM

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

DEPTNO

----------

7369 SMITH CLERK 7902 17-DEC-80 800

20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300

30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500

30

EMPNO ENAME JOB MGR HIREDATE SAL COMM

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

DEPTNO

----------

7566 JONES MANAGER 7839 02-APR-81 2975

20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400

30

7698 BLAKE MANAGER 7839 01-MAY-81 4000

30

EMPNO ENAME JOB MGR HIREDATE SAL COMM

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

DEPTNO

----------

7782 CLARK MANAGER 7839 09-JUN-81 4000

10

7788 SCOTT ANALYST 7566 19-APR-87 4000

20

7839 KING PRESIDENT 17-NOV-81 5000

10

EMPNO ENAME JOB MGR HIREDATE SAL COMM

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

DEPTNO

----------

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0

30

7876 ADAMS CLERK 7788 23-MAY-87 1100

20

7900 JAMES CLERK 7698 03-DEC-81 950

30

EMPNO ENAME JOB MGR HIREDATE SAL COMM

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

DEPTNO

----------

7902 FORD ANALYST 7566 03-DEC-81 3000

20

7934 MILLER CLERK 7782 23-JAN-82 1300

10

14 rows selected.

Execution Plan

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

Plan hash value: 3956160932

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

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

| 0 | SELECT STATEMENT | | 14 | 518 | 3 (0)| 00:00:01 |

| 1 | TABLE ACCESS FULL| EMP | 14 | 518 | 3 (0)| 00:00:01 |

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

Statistics

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

1 recursive calls

0 db block gets

8 consistent gets

0 physical reads

0 redo size

1413 bytes sent via SQL*Net to client

385 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

14 rows processed

SQL>

注意,在此查詢是產生了物理讀取和redo,這個redo就是因為延遲塊清除導致的,再次查詢則不會

產生redo了:

SQL> select * from scott.emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM

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

DEPTNO

----------

7369 SMITH CLERK 7902 17-DEC-80 800

20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300

30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500

30

EMPNO ENAME JOB MGR HIREDATE SAL COMM

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

DEPTNO

----------

7566 JONES MANAGER 7839 02-APR-81 2975

20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400

30

7698 BLAKE MANAGER 7839 01-MAY-81 4000

30

EMPNO ENAME JOB MGR HIREDATE SAL COMM

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

DEPTNO

----------

7782 CLARK MANAGER 7839 09-JUN-81 4000

10

7788 SCOTT ANALYST 7566 19-APR-87 4000

20

7839 KING PRESIDENT 17-NOV-81 5000

10

EMPNO ENAME JOB MGR HIREDATE SAL COMM

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

DEPTNO

----------

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0

30

7876 ADAMS CLERK 7788 23-MAY-87 1100

20

7900 JAMES CLERK 7698 03-DEC-81 950

30

EMPNO ENAME JOB MGR HIREDATE SAL COMM

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

DEPTNO

----------

7902 FORD ANALYST 7566 03-DEC-81 3000

20

7934 MILLER CLERK 7782 23-JAN-82 1300

10

14 rows selected.

Execution Plan

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

Plan hash value: 3956160932

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

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

| 0 | SELECT STATEMENT | | 14 | 518 | 3 (0)| 00:00:01 |

| 1 | TABLE ACCESS FULL| EMP | 14 | 518 | 3 (0)| 00:00:01 |

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

Statistics

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

1 recursive calls

0 db block gets

8 consistent gets

0 physical reads

0 redo size

1413 bytes sent via SQL*Net to client

385 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

14 rows processed

SQL>

再次轉儲一下該資料塊和回滾段:

SQL> alter system dump datafile 4 block 32;

System altered.

SQL> alter system dump undo header '_SYSSMU9$';

System altered.

SQL> alter system dump datafile 2 block 1350;

System altered.

SQL> select

2 d.value||'/'||lower(rtrim(i.instance,

3 chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name

4 from ( select p.spid

5 from sys.v$mystat m,

6 sys.v$session s,sys.v$process p

7 where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,

8 ( select t.instance from sys.v$thread t,sys.v$parameter v

9 where v.name = 'thread' and

10 (v.value = 0 or t.thread# = to_number(v.value))) i,

11 ( select value from sys.v$parameter

12 where name = 'user_dump_dest') d

13 /

TRACE_FILE_NAME

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

/u01/app/oracle/admin/jingyong/udump/jingyong_ora_5043.trc

SQL>

檢視跟蹤檔案,看到此時ITL事務資訊已經清除,但是注意,這裡的xid和uba資訊仍然存在:

Start dump data blocks tsn: 4 file#: 4 minblk 32 maxblk 32

buffer tsn: 4 rdba: 0x01000020 (4/32)

scn: 0x0000.000e7560 seq: 0x01 flg: 0x00 tail: 0x75600601

frmt: 0x02 chkval: 0x0000 type: 0x06=trans data

Block header dump: 0x01000020

Object id on Block? Y

seg/obj: 0xc7cc csc: 0x00.e7560 itc: 2 flg: E typ: 1 - DATA

brn: 0 bdba: 0x1000019 ver: 0x01 opc: 0

inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0x0009.01d.00000181 0x00800546.0129.18 C--- 0 scn 0x0000.000e743c

0x02 0x0002.010.00000158 0x00800598.013f.26 C--- 0 scn 0x0000.000d4495

其中scn表示提交commit scn,fsc表示快速提交scn

資料塊塊的鎖定位也已經清除了:

tab 0, row 5, @0x1d11

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

col 0: [ 3] c2 4d 63

col 1: [ 5] 42 4c 41 4b 45

col 2: [ 7] 4d 41 4e 41 47 45 52

col 3: [ 3] c2 4f 28

col 4: [ 7] 77 b5 05 01 01 01 01

col 5: [ 2] c2 29

col 6: *NULL*

col 7: [ 2] c1 1f

tab 0, row 6, @0x1d39

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

col 0: [ 3] c2 4e 53

col 1: [ 5] 43 4c 41 52 4b

col 2: [ 7] 4d 41 4e 41 47 45 52

col 3: [ 3] c2 4f 28

col 4: [ 7] 77 b5 06 09 01 01 01

col 5: [ 2] c2 29

col 6: *NULL*

col 7: [ 2] c1 0b

tab 0, row 7, @0x1e4c

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

col 0: [ 3] c2 4e 59

col 1: [ 5] 53 43 4f 54 54

col 2: [ 7] 41 4e 41 4c 59 53 54

col 3: [ 3] c2 4c 43

col 4: [ 7] 77 bb 04 13 01 01 01

col 5: [ 2] c2 29

col 6: *NULL*

col 7: [ 2] c1 15

8.提交之後的undo資訊

當提交事務之後,回滾段事務表標記該事務為非活動,繼續再來看一下回滾段資料塊的資訊,

看到這裡的irb指向了0x28，此前的事務已經不可回滾

********************************************************************************

UNDO BLK:

xid: 0x0009.02f.00000181 seq: 0x129 cnt: 0x28 irb: 0x28 icl: 0x0 flg: 0x0000

看一下偏移量列表也已經新增了一條資訊0x28 0x0a4c

Rec Offset Rec Offset Rec Offset Rec Offset Rec Offset

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

0x01 0x1f3c 0x02 0x1ed0 0x03 0x1e54 0x04 0x1de8 0x05 0x1d3c

0x06 0x1c90 0x07 0x1c24 0x08 0x1b78 0x09 0x1acc 0x0a 0x1a20

0x0b 0x1974 0x0c 0x1908 0x0d 0x189c 0x0e 0x17f0 0x0f 0x1784

0x10 0x1718 0x11 0x166c 0x12 0x1600 0x13 0x1594 0x14 0x14e8

0x15 0x147c 0x16 0x13e4 0x17 0x1388 0x18 0x132c 0x19 0x1280

0x1a 0x11d4 0x1b 0x1128 0x1c 0x10ac 0x1d 0x1000 0x1e 0x0f54

0x1f 0x0ee8 0x20 0x0e7c 0x21 0x0e10 0x22 0x0da4 0x23 0x0cf8

0x24 0x0c4c 0x25 0x0ba0 0x26 0x0b24 0x27 0x0ab8 0x28 0x0a4c

再看前映象0x18 0x132c的資訊,仍然存在:

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

* Rec #0x18 slt: 0x1d objn: 51148(0x0000c7cc) objd: 51148 tblspc: 4(0x00000004)

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

Undo type: Regular undo Last buffer split: No

Temp Object: No

Tablespace Undo: No

rdba: 0x00000000

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

KDO undo record:

KTB Redo

op: 0x02 ver: 0x01

op: C uba: 0x00800546.0129.17

KDO Op code: URP row dependencies Disabled

xtype: XA flags: 0x00000000 bdba: 0x01000020 hdba: 0x0100001b

itli: 1 ispac: 0 maxfr: 4858

tabn: 0 slot: 5(0x5) flag: 0x2c lock: 0 ckix: 191

ncol: 8 nnew: 1 size: 1

col 5: [ 3] c2 1d 33

oracle的undo的工作過程

相關文章