col operation format a50
col cost format 999999
col kbytes format 999999
col object format a20
select hash_value,child_number,
lpad('',2*depth)
||operation
||''
||options
||decode(id,0,substr(optimizer,1,6)||'Cost='||to_char(cost))operation,
object_name,object_type,cost,round(bytes/1024) kbytes
from v$sql_plan where hash_value in(
select a.sql_hash_value
from v$session a,v$session_wait b
where a.sid=b.sid
and b.event='db file sequential read')
order by hash_value,child_number,ID;
SQL> col operation format a55
SQL> col cost format 99999
SQL> col kbytes format 999999
SQL> col object format a25
SQL> select hash_value,child_number,
2 lpad('',2*depth)
3 ||operation
4 ||''
5 ||options
6 ||decode(id,0,substr(optimizer,1,6)||'Cost='||to_char(cost))operation,
7 object_name,object_type,cost,round(bytes/1024) kbytes
8 from v$sql_plan where hash_value in(
9 select a.sql_hash_value
10 from v$session a,v$session_wait b
11 where a.sid=b.sid
12 and b.event='db file sequential read')
13 order by hash_value,child_number,ID;
HASH_VALUE CHILD_NUMBER OPERATION OBJECT_NAME OBJECT_TYPE COST KBYTES
---------- ------------ ------------------------------------------------------- ------------------------------ ---------------------------------------- ----- ------
1722201563 0 SELECT STATEMENTFIRST_Cost=203255 20325
5
1722201563 0 SORTORDER BY 20325 5910
5
1722201563 0 HASHGROUP BY 20325 5910
5
1722201563 0 HASH JOIN 20193 5910
1
1722201563 0 TABLE ACCESSFULL BS_CORP TABLE 85 41
1722201563 0 NESTED LOOPSSEMI 20184 4561
5
1722201563 0 HASH JOIN 20184 4540
3
1722201563 0 HASH JOIN 19970 2515
4
1722201563 0 HASH JOIN 19529 1813
2
1722201563 0 HASH JOINRIGHT SEMI 19442 489
1
1722201563 0 VIEW VW_SQ_1 VIEW 19402 36
8
1722201563 0 FILTER
1722201563 0 NESTED LOOPS 19402 241
8
1722201563 0 NESTED LOOPS 19402 157
6
1722201563 0 MERGE JOINCARTESIAN 28436 264415
1722201563 0 INLIST ITERATOR
1722201563 0 INDEXRANGE SCAN PK_BS_CORP INDEX (UNIQUE) 4 0
1722201563 0 BUFFERSORT 28432 5509
1722201563 0 TABLE ACCESSFULL LV_BUSI_RECORD TABLE 862 5509
1722201563 0 TABLE ACCESSBY INDEX ROWID BS_FAMILY TABLE 19402 0
6
HASH_VALUE CHILD_NUMBER OPERATION OBJECT_NAME OBJECT_TYPE COST KBYTES
---------- ------------ ------------------------------------------------------- ------------------------------ ---------------------------------------- ----- ------
1722201563 0 BITMAP CONVERSIONTO ROWIDS
1722201563 0 BITMAP AND
1722201563 0 BITMAP CONVERSIONFROM ROWIDS
1722201563 0 INDEXRANGE SCAN PK_BS_FAMILY INDEX (UNIQUE) 1
1722201563 0 BITMAP CONVERSIONFROM ROWIDS
1722201563 0 INDEXRANGE SCAN IDX_BS_FAMILY_CORP_ID INDEX 1
1722201563 0 INDEXRANGE SCAN INDEX_FAMILY_ID INDEX 1 0
1722201563 0 TABLE ACCESSFULL LV_BUSI_BILL TABLE 393 847
1722201563 0 TABLE ACCESSFULL LV_BUSI_RECORD TABLE 867 15524
1722201563 0 TABLE ACCESSFULL BS_FAMILY TABLE 4407 23768
1722201563 0 TABLE ACCESSFULL LV_BUSI_ASSIGN TABLE 913 12421
1722201563 0 INDEXRANGE SCAN INDEX_FAMILY_ID INDEX 1 3236
1722201563 1 SELECT STATEMENTFIRST_Cost=197255 19725
5
1722201563 1 SORTORDER BY 19725 907
5
1722201563 1 HASHGROUP BY 19725 907
5
1722201563 1 HASH JOIN 19704 907
8
1722201563 1 HASH JOIN 19613 516
0
1722201563 1 TABLE ACCESSFULL BS_CORP TABLE 85 41
1722201563 1 NESTED LOOPSSEMI 19604 389
4
1722201563 1 NESTED LOOPS 19604 386
3
1722201563 1 NESTED LOOPS 19488 278
0
HASH_VALUE CHILD_NUMBER OPERATION OBJECT_NAME OBJECT_TYPE COST KBYTES
---------- ------------ ------------------------------------------------------- ------------------------------ ---------------------------------------- ----- ------
1722201563 1 HASH JOINSEMI 19442 75
1
1722201563 1 TABLE ACCESSFULL LV_BUSI_BILL TABLE 392 70
1722201563 1 VIEW VW_SQ_1 VIEW 19402 36
8
1722201563 1 FILTER
1722201563 1 NESTED LOOPS 19402 241
8
1722201563 1 NESTED LOOPS 19402 157
6
1722201563 1 MERGE JOINCARTESIAN 28436 264415
1722201563 1 INLIST ITERATOR
1722201563 1 INDEXRANGE SCAN PK_BS_CORP INDEX (UNIQUE) 4 0
1722201563 1 BUFFERSORT 28432 5509
1722201563 1 TABLE ACCESSFULL LV_BUSI_RECORD TABLE 862 5509
1722201563 1 TABLE ACCESSBY INDEX ROWID BS_FAMILY TABLE 19402 0
6
1722201563 1 BITMAP CONVERSIONTO ROWIDS
1722201563 1 BITMAP AND
1722201563 1 BITMAP CONVERSIONFROM ROWIDS
1722201563 1 INDEXRANGE SCAN PK_BS_FAMILY INDEX (UNIQUE) 1
1722201563 1 BITMAP CONVERSIONFROM ROWIDS
1722201563 1 INDEXRANGE SCAN IDX_BS_FAMILY_CORP_ID INDEX 1
1722201563 1 INDEXRANGE SCAN INDEX_FAMILY_ID INDEX 1 0
1722201563 1 TABLE ACCESSBY INDEX ROWID LV_BUSI_RECORD TABLE 1 0
1722201563 1 INDEXRANGE SCAN IDX_BUSI_BILL_BILLSN INDEX 1
HASH_VALUE CHILD_NUMBER OPERATION OBJECT_NAME OBJECT_TYPE COST KBYTES
---------- ------------ ------------------------------------------------------- ------------------------------ ---------------------------------------- ----- ------
1722201563 1 TABLE ACCESSBY INDEX ROWID BS_FAMILY TABLE 1 0
1722201563 1 INDEXUNIQUE SCAN PK_BS_FAMILY INDEX (UNIQUE) 1
1722201563 1 INDEXRANGE SCAN INDEX_FAMILY_ID INDEX 1 3236
1722201563 1 TABLE ACCESSFULL LV_BUSI_ASSIGN TABLE 913 12421
66 rows selected
oracle 块清除
块清除(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 Copyright (c) 1982, 2005, Oracle. All rights reserved. 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
查看跟踪文件信息,看数据块上的信息,ITL事务信息仍然存在:
其中scn表示提交commit scn,fsc表示快速提交scn
*** 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
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
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
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
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
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
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 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
345 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
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
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
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
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
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 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
查看跟踪文件,看到此时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
数据块块的锁定位也已经清除了:
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
提交之后的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 redo文件记录的内容
redo的内容
oracle通过redo来实同快速提交,一方面是因为redo log file可以连续,顺序地快速写出,另外一个方面
也和redo记录的精简内容有关.
为了了解redo的内容,先需要了解两个概念:改变向量和重做记录
改变向量(change vector)
改变向量表示对数据库内某一个数据块所做的一次变更.改变向量(change vector)中包含了变更的数据
块的版本号,事务操作代码,变更从属数据块的地址(DBA)以及更新后的数据.例如,一个update事务包含
一系列的改变向量,对于数据块的修改是一个向量,对于回滚段的修改又是一处向量.
重做记录(redo record)
重做记录通常由一组改变向量组成,是一个改变向量的集合,代表一个数据库的变更(insert,update,delete
等操作),构成数据库变更的最小恢复单位.例如,一个update的重做记录包括相应的回滚段的改变向量和相应
的数据块的改变向量等.
假定发出了一个更新语句;
update scott.emp set sal=4000 where empno=7788;
看一下这个语句是怎么执行的
1.检查empno=7788记录在buffer cache中是否存在,如果不存在则读取到buffer cache中.
2.在回滚表空间的相应回滚段事务表上分配事务槽,这个操作需要记录redo信息.
3.从回滚段读入或者在buffer cache中创建sal=3000的前镜像,这需要产生redo信息并记入redo log buffer
4.修改sal=4000,这是update的数据变更,需要记入redo log buffer
5.当用户提交时,会在redo log buffer记录提交信息,并在回滚段标记该事务为非激活(Inactive)
下面通过珍上具体的试验来再现这个过程.
1.先通过switch logfile切换日志,使用sys用户进行日志切换,使得接下来的更新可以使用新的日志.
SQL> alter system switch logfile;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------
1 1 14 52428800 1 NO ACTIVE 801067 03-JAN-14
2 1 15 52428800 1 NO CURRENT 813289 04-JAN-13
3 1 13 52428800 1 NO INACTIVE 764715 30-DEC-13
2.更新并提交事务
SQL> select * from scott.emp a where a.empno=7788;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
SQL> update scott.emp set sal=4000 where empno=7788;
1 row updated.
SQL> commit;
Commit complete.
3.使用sys用户在另外的session转储日志文件:
SQL> alter system dump logfile '/u01/app/oracle/product/10.2.0/oradata/jingyong/redo02.log'; 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_3969.trc
4.获取trace文件
从日志文件的转储信息中,可以找到这个事务信息(sid=149,serial#=930)
*** SERVICE NAME:(SYS$USERS) 2013-01-04 17:06:48.813 *** SESSION ID:(149.930) 2013-01-04 17:06:48.813
改变向量1
这是对于回滚段头的修改,分配事务表，从绝对文件号为2(AFN:2)可以知道这是UNDO
表空间,通过UBA的DBA换算能力找到相应的block
SQL> SELECT DBMS_UTILITY.data_block_address_file (
2 TO_NUMBER(LTRIM('0x00800099', '0x'), 'xxxxxxxx'))
3 AS file_no,
4 DBMS_UTILITY.data_block_address_block (
5 TO_NUMBER(LTRIM ('0x00800099', '0x'), 'xxxxxxxx'))
6 AS block_no
7 FROM DUAL;
FILE_NO BLOCK_NO
---------- ----------
2 153
REDO RECORD - Thread:1 RBA: 0x00000f.00000023.0010 LEN: 0x0200 VLD: 0x0d
SCN: 0x0000.000c68fd SUBSCN: 1 01/04/2013 17:04:58
CHANGE #2 TYP:0 CLS:35 AFN:2 DBA:0x00800099 OBJ:4294967295 SCN:0x0000.000c6859 SEQ: 1 OP:5.2
ktudh redo: slt: 0x0025 sqn: 0x00000137 flg: 0x0012 siz: 128 fbi: 0
uba: 0x0080106d.0102.29 pxid: 0x0000.000.00000000
CHANGE #4 TYP:0 CLS:36 AFN:2 DBA:0x0080106d OBJ:4294967295 SCN:0x0000.000c6858 SEQ: 1 OP:5.1
ktudb redo: siz: 128 spc: 2418 flg: 0x0012 seq: 0x0102 rec: 0x29
xid: 0x000a.025.00000137
ktubl redo: slt: 37 rci: 0 opc: 11.1 objn: 51148 objd: 51148 tsn: 4
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
0x00000000 prev ctl uba: 0x0080106d.0102.28
prev ctl max cmt scn: 0x0000.000c61f3 prev tx cmt scn: 0x0000.000c61ff
txn start scn: 0x0000.00000000 logon user: 0 prev brb: 8392808 prev bcl: 0 KDO undo record:
KTB Redo
op: 0x03 ver: 0x01
op: Z
KDO Op code: URP row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x01000020 hdba: 0x0100001b
itli: 2 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] c2 1f
改变向量2
这里记录的是前镜像信息,注意到”col 5: [ 2] c2 1f记录的就是对于col5的修改,修改前的数据值3000(c21f)
SQL> select utl_raw.cast_to_number('c21f') from dual;
UTL_RAW.CAST_TO_NUMBER('C21F')
------------------------------
3000
改变向量3
这里记录的是对于数据块的修改,”col 5: [ 2] c2 29″记录的是对于col5的修改,
修改后的值为4000(c229)
SQL> select utl_raw.cast_to_number('c229') from dual;
UTL_RAW.CAST_TO_NUMBER('C229')
------------------------------
4000
CHANGE #1 TYP:2 CLS: 1 AFN:4 DBA:0x01000020 OBJ:51148 SCN:0x0000.0006bfdb SEQ: 16 OP:11.5
KTB Redo
op: 0x11 ver: 0x01
op: F xid: 0x000a.025.00000137 uba: 0x0080106d.0102.29
Block cleanout record, scn: 0x0000.000c68f4 ver: 0x01 opt: 0x02, entries follow...
itli: 1 flg: 2 scn: 0x0000.0006bfdb
KDO Op code: URP row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x01000020 hdba: 0x0100001b
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 7(0x7) flag: 0x2c lock: 2 ckix: 191
ncol: 8 nnew: 1 size: 0
Vector content:
col 5: [ 2] c2 29
改变向量4
当事务提交之后,记录的scn信息,注意这里标记为”MEDIA RECOVERY MARKER SCN”,也就是说,这是一个
可以恢复的时间点,事务的恢复必须以redo record为最小单位
CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ: 0 OP:23.1
session信息
最前面的部分记录的是产生这些redo的session信息
*** 2013-01-04 17:06:48.813 *** SERVICE NAME:(SYS$USERS) 2013-01-04 17:06:48.813 *** SESSION ID:(149.930) 2013-01-04 17:06:48.813
对于redo日志来说,重做信息却相当精简,oracle只需要记录那些重构事务必须的信息(如事务号,文件号,块号,
行号,字段等)即可,这个数据量大大减少
oracle的回闪查询
oracle回闪查询的新特性
从oracle9i开始,oracle开始提供回闪查询特性(flashback query),允许将回滚段中的数据进行回闪,通过下面的例子来看一下这个从oracle9i开始提供的新特性.
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.
先不提交这个事务,在另外窗口新开session,使用sys用户查询相关信息,进行进一步的分析
获得事务信息
从事务表中可以获得关于这个事务的信息,该事务位于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.
这是执行alter system dump datafile 2 block 1350
转储的回滚表空间中的数据块的信息的一部分
*-----------------------------
* Rec #0x1d slt: 0x24 objn: 517(0x00000205) objd: 517 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: 0x00000000
*-----------------------------
uba: 0x00800546.0129.1b ctl max scn: 0x0000.000e4e9c prv tx scn: 0x0000.000e4ea6
txn start scn: scn: 0x0000.000e7526 logon user: 0
prev brb: 8389956 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
op: L itl: xid: 0x0006.016.0000015d uba: 0x00800419.00fe.11
flg: C--- lkc: 0 scn: 0x0000.000e7524
KDO Op code: URP row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x0040100f hdba: 0x00401001
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 116(0x74) flag: 0x2c lock: 0 ckix: 191
ncol: 9 nnew: 7 size: 0
Vector content:
col 2: [ 2] c1 0a
col 3: [ 2] c1 0a
col 4: [ 1] 80
col 5: [ 1] 80
col 6: [ 1] 80
col 7: [ 1] 80
col 8: [ 7] 78 71 01 07 0b 07 34
先注意到这里存在一个信息ctl max scn: 0x0000.000e4e9c,这个转换为scn值就是:
SQL> select (to_number('000','xxxx')*power(2,32)+to_number('e4e9c','xxxxxxxx')) scn
from dual;
SCN
----------
937628
查询一下当前数据的scn:
SQL> select dbms_flashback.get_system_change_number scn from dual;
SCN
----------
949630
SQL>
通过特定的语法,可以将scn 937628的历史状态数据查询出来:
SQL> select * from scott.emp as of scn 937628 where empno in(7788,7782,7698); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ---------- --------- ----- ----------- --------- --------- ------ 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
在查询结果中,注意到3名员工的薪水恢复到了之前的状态.而在当前的查询中,这个数据是变化
后的4000:
SQL> select * from scott.emp where empno in(7788,7782,7698); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ---------- --------- ----- ----------- --------- --------- ------ 7698 BLAKE MANAGER 7839 1981-5-1 4000.00 30 7782 CLARK MANAGER 7839 1981-6-9 4000.00 10 7788 SCOTT ANALYST 7566 1987-4-19 4000.00 20
由于这个查询需要从undo中获限前镜像信息,如果undo中的信息被覆盖,则以上查询将会失败.
测试一下,当新建undo表空间,切换undo表空间,再将原表空间offline之后:
SQL> create undo tablespace undotbs2 datafile 2 '/u01/app/oracle/product/10.2.0/oradata/jingyong/undotbs02.dbf' size 10M; Tablespace created SQL> alter system set undo_tablespace=undotbs2; System altered. SQL> alter tablespace undotbs1 offline; Tablespace altered. SQL> alter session set events='immediate trace name flush_cache'; Session altered.
再来查询,此时出现错误,记录该文件已经不可读取:
SQL> select * from scott.emp as of scn 937628 where empno in(7788,7782,7698); select * from scott.emp as of scn 937628 where empno in(7788,7782,7698) ORA-00376: file 2 cannot be read at this time ORA-01110: data file 2: '/u01/app/oracle/product/10.2.0/oradata/jingyong/undotbs01.dbf'
将undotbs1重新启用:
SQL> alter tablespace undotbs1 online; Tablespace altered. SQL> alter system set undo_tablespace=undotbs1; System altered.
此时前镜像信息再次可以查询,
SQL> select * from scott.emp as of scn 937628 where empno in(7788,7782,7698); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ---------- --------- ----- ----------- --------- --------- ------ 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
如果在其他session中执行大量事务,使用前镜像信息被覆盖:
在执行其它大量事务之前观察回滚段的使用情况:
SQL> select usn,xacts,rssize,hwmsize from v$rollstat where usn=9;
USN XACTS RSSIZE HWMSIZE
---------- ---------- ---------- ----------
9 0 2088960 2088960
SQL> begin
2 for i in 1..2000 loop
3 update scott.emp set sal=4000;
4 rollback;
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> select usn,xacts,rssize,hwmsize from v$rollstat where usn=9;
USN XACTS RSSIZE HWMSIZE
---------- ---------- ---------- ----------
9 1 4186112 4186112
那么再次查询就可能会收到如下错误:
SQL> select * from scott.emp as of scn 937628 where empno in(7788,7782,7698); select * from scott.emp as of scn 937628 where empno in(7788,7782,7698) ERROR at line 1: ORA-01555: snapshot too old:rollback segment number 9 with name "_SYSSMU9$" too small
ORA-01555错误出现说明要查询的前镜偈信息已经丢失了.
使用errorstack进行错误跟踪
errorstack是oracle提供的接口用于诊断oracle的错误信息.
诊断事件可以在session级设置,也可以在系统级设置,通常如果要诊断全局错误,最好在
系统级设置.设置了errorstack事件之后,oracle会将出错时的信息记入跟踪文件中.
用户就可以通过跟踪文件进行错误诊断和排查.
可以通过errorstack事件来跟踪ora-01555错误:
OS Pid: 2928 executed alter session set events '1555 trace name errorstack level 4' ORA-01555 caused by SQL statement below (SQL ID: bnaqc462nnjtb, Query Duration=0 sec, SCN: 0x0000.00068478): Mon Jan 7 20:23:35 2013 select * from scott.emp as of scn 427128 where empno in(7788,7782,7698) Mon Jan 7 20:23:35 2013 Errors in file /u01/app/oracle/admin/jingyong/udump/jingyong_ora_2928.trc: ORA-01555: snapshot too old: rollback segment number 3 with name "???" too small
这里注意到,触发ora-01555错误的语句被记录,出现错误的scn也被记录,这个scn:0x0000.00068478
找到jingyong_ora_2928.trc跟踪文件,就可以获得关于这次错误的相关信息用于诊断.
错误信息如下
ksedmp: internal or fatal error ORA-01555: snapshot too old: rollback segment number 3 with name "???" too small Current SQL statement for this session: select * from scott.emp as of scn 427128 where empno in(7788,7782,7698)
数据块信息,这里的块头就包含了ITL信息,根据这个ITL信息中的UBA,oracle可以定位回滚段.
查询前镜像信息,如果不存在,就可能出现ora-01555错误
Block header dump: 0x01000024
Object id on Block? Y
seg/obj: 0xc7cd csc: 0x00.6bfcb itc: 2 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x1000021 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x02 0x0003.011.000000f2 0x00805794.00c8.4a --U- 14 fsc 0x0000.0006bfdb
oracle 10g回闪查询特性的增强
oracle9i提供的闪回特性增强,为恢复带来了极大的方便,但是oracle9i的回闪查询只能提供某个
时间点的数据视图,并不能告诉用户这样的数据经过几个事务,怎样被修改的(update,insert,delete)
,而这些信息在回滚段中是存在的,在oracle10g中,oracle进一步加强了回闪查询的特性,提供以下
两种回闪查询:
回闪版本查询(flashback versions query)
回闪事务查询(flashback transaction query)
回闪版本查询允许使用一个新的versions子句查询两个时间点或者scn之间的数据版本.
这些版本可以按照事务进行区分,回闪版本查询只返回提交数据.没提交的数据不被显示
先创建一个测试表,执行一些DML操作
SQL> create table test as select username from dba_users; Table created. SQL> select * from test; USERNAME ------------------------------ MGMT_VIEW SYS SYSTEM DBSNMP SYSMAN SCOTT JYTEST1 JYTEST OUTLN MDSYS ORDSYS USERNAME ------------------------------ EXFSYS DMSYS WMSYS CTXSYS ANONYMOUS XDB ORDPLUGINS SI_INFORMTN_SCHEMA OLAPSYS TSMSYS BI USERNAME ------------------------------ PM MDDATA IX SH DIP OE HR 29 rows selected. SQL> delete from test where username='BI'; 1 row deleted. SQL> delete from test where username='JYTEST1'; 1 row deleted. SQL> commit; Commit complete. SQL> select * from test; USERNAME ------------------------------ MGMT_VIEW SYS SYSTEM DBSNMP SYSMAN SCOTT JYTEST OUTLN MDSYS ORDSYS EXFSYS USERNAME ------------------------------ DMSYS WMSYS CTXSYS ANONYMOUS XDB ORDPLUGINS SI_INFORMTN_SCHEMA OLAPSYS TSMSYS PM MDDATA USERNAME ------------------------------ IX SH DIP OE HR 27 rows selected.
再多执行一些DML操作
SQL> update test set username='JYTEST1' where username='JYTEST'; 1 row updated. SQL> delete from test where username='IX'; 1 row deleted. SQL> commit; Commit complete. SQL> select * from test; USERNAME ------------------------------ MGMT_VIEW SYS SYSTEM DBSNMP SYSMAN SCOTT JYTEST1 OUTLN MDSYS ORDSYS EXFSYS USERNAME ------------------------------ DMSYS WMSYS CTXSYS ANONYMOUS XDB ORDPLUGINS SI_INFORMTN_SCHEMA OLAPSYS TSMSYS PM MDDATA USERNAME ------------------------------ SH DIP OE HR JY 27 rows selected.
再来看看oracle10g的回闪版本查询,通过使用versions子句,和对数据表引入了一系列的伪列(
version_startime等),可以获得对数据表的所有事务操作,注意以下输出中的versions_operation
代表了不同类型的操作(D-Delete,I-Insert,U-Update),versions_xid是一个重要数据,代表了不
同版本的事务ID:
SQL> select versions_starttime,versions_endtime,versions_xid,versions_operation,
2 username from test versions between timestamp minvalue and maxvalue;
VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID VERSIONS_OPERATION USERNAME
--------------------------- ---------------------------- ---------------- ------------------ ---------------------- --------
07-1月 -13 11.07.58 下午 0300150096010000 I JY
07-1月 -13 11.06.46 下午 06002B0093010000 D IX
07-1月 -13 11.06.46 下午 06002B0093010000 U JYTEST1
07-1月 -13 11.05.22 下午 05001E00A7010000 D JYTEST1
07-1月 -13 11.05.22 下午 05001E00A7010000 D BI
MGMT_VIEW
SYS
SYSTEM
DBSNMP
SYSMAN
SCOTT
07-1月 -13 11.05.22 下午 JYTEST1
07-1月 -13 11.06.46 下午 JYTEST
OUTLN
MDSYS
ORDSYS
EXFSYS
DMSYS
WMSYS
CTXSYS
VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID VERSIONS_OPERATION USERNAME
--------------------------- ---------------------------- ---------------- ------------------ ---------------------- --------
ANONYMOUS
XDB
ORDPLUGINS
SI_INFORMTN_SCHEMA
OLAPSYS
TSMSYS
07-1月 -13 11.05.22 下午 BI
PM
MDDATA
07-1月 -13 11.06.46 下午 IX
SH
DIP
OE
HR
34 rows selected
通过以上输出,根据versions_xid可以清晰地区分不同事务在不同时间对数据所作的更改
具备了flashback version query查询的基础,就可以进行基于flashback version query的事务级恢复了.
这就是flashback transaction query, flashback transaction query可以从flashback transaction query
视图中获得指定事务的历史信息以及undo_sql,通过undo_sql,就可以撤消特定的提交事务.flashback
transaction query需要用到flashback_transaction_query视图,先看一下视图:
SQL> desc flashback_transaction_query Name Type Nullable Default Comments ---------------- -------------- -------- ------- ----------------------------------------- XID RAW(8) Y Transaction identifier START_SCN NUMBER Y Transaction start SCN START_TIMESTAMP DATE Y Transaction start timestamp COMMIT_SCN NUMBER Y Transaction commit SCN COMMIT_TIMESTAMP DATE Y Transaction commit timestamp LOGON_USER VARCHAR2(30) Y Logon user for transaction UNDO_CHANGE# NUMBER Y 1-based undo change number OPERATION VARCHAR2(32) Y forward operation for this undo TABLE_NAME VARCHAR2(256) Y table name to which this undo applies TABLE_OWNER VARCHAR2(32) Y owner of table to which this undo applies ROW_ID VARCHAR2(19) Y rowid to which this undo applies UNDO_SQL VARCHAR2(4000) Y SQL corresponding to this undo
该视图的定义为:
select xid, start_scn, start_timestamp,
decode(commit_scn, 0, commit_scn, 281474976710655, NULL, commit_scn)
commit_scn, commit_timestamp,
logon_user, undo_change#, operation, table_name, table_owner,
row_id, undo_sql
from sys.x$ktuqqry;
对于x$ktuqqry的查询非常耗时
测试一下对于x$ktuqqry表的查询
SQL> select count(addr) from x$ktuqqry;
COUNT(ADDR)
-----------
74786
如果需要撤消xid=0300150096010000的事务,可以通过如下步骤进行:
SQL> set autotrace on
SQL> select undo_sql from flashback_transaction_query where xid='0300150096010000';
UNDO_SQL
--------------------------------------------------------------------------------
delete from "SYS"."TEST" where ROWID = 'AAAM3VAABAAAO9KAAd';
Execution Plan
----------------------------------------------------------
Plan hash value: 1115820779
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2008 | 0 (0)| 00:00:01 |
|* 1 | FIXED TABLE FULL| X$KTUQQRY | 1 | 2008 | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(RAWTOHEX("XID")='0300150096010000')
Statistics
----------------------------------------------------------
90926 recursive calls
0 db block gets
238964 consistent gets
21783 physical reads
0 redo size
506 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4115 sorts (memory)
0 sorts (disk)
2 rows processed
通过执行相应的undo语句可以撤消该事务,通过这些新特性,oracle提供了一种”回滚”提交事务的
方法,极大地方便了用户应对不同情况的数据库恢复.
oracle的undo的工作过程
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
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
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
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
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
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
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
这是跟踪文件开始部分的信息:
*** 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 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 Copyright (c) 1982, 2005, Oracle. All rights reserved. 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
查看跟踪文件信息,看数据块上的信息,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
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
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
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
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
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
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 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
注意,在此查询是产生了物理读取和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
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
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
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
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
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 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> 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
查看跟踪文件,看到此时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丢失active或current日志文件的恢复操作过程
丢失活动或当前日志文件的恢复
oracle通过日志文件保证提交成功的数据不丢失,可是在故障中,用户可能损失了当前的
(current)日志文件.这又分为两种情况:此时数据是正常关闭的和此时数据库是异常关闭.
1.在损失当前日志时,数据库是正常关闭的.
由于关闭数据库前,oracle会执行全面检查点,当前日志在实例恢复中可以不再需要.
下面进行测试(数据库运行在非归档模式下).在oracle9i及以后版本中,是无法对当前
日志进行clear,需要通过until cancel恢复后再以resetlogs方式打开
[oracle@jingyong ~]$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jan 7 00:39:59 2013 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options SQL> select * from v$version where rownum<2; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod SQL> select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE ---------- ------- ------- ----------------------------------------------------------- --------------------- 3 ONLINE /u01/app/oracle/product/10.2.0/oradata/jingyong/redo03.log NO 2 ONLINE /u01/app/oracle/product/10.2.0/oradata/jingyong/redo02.log NO 1 ONLINE /u01/app/oracle/product/10.2.0/oradata/jingyong/redo01.log NO SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> !mv /u01/app/oracle/product/10.2.0/oradata/jingyong/redo* /u01/app/oracle SQL> startup ORACLE instance started. Total System Global Area 167772160 bytes Fixed Size 1218316 bytes Variable Size 62916852 bytes Database Buffers 100663296 bytes Redo Buffers 2973696 bytes Database mounted. ORA-00313: open failed for members of log group 1 of thread 1 ORA-00312: online log 1 thread 1: '/u01/app/oracle/product/10.2.0/oradata/jingyong/redo01.log' SQL> alter database clear logfile group 1; Database altered. SQL> alter database clear logfile group 2; Database altered. SQL> alter database clear logfile group 3; alter database clear logfile group 3 * ERROR at line 1: ORA-00313: open failed for members of log group 3 of thread 1 ORA-00312: online log 3 thread 1: '/u01/app/oracle/product/10.2.0/oradata/jingyong/redo03.log' ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3 SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------ 1 1 0 52428800 1 NO UNUSED 0 06-JAN-13 3 1 0 52428800 1 NO CLEARING_CURRENT 914164 06-JAN-13 2 1 0 52428800 1 NO UNUSED 914157 06-JAN-13 SQL> recover database until cancel; Media recovery complete. SQL> alter database open resetlogs; Database altered.
2.在损失当前日志,数据库是异常关闭的
如果在损失当前日志时,数据库是异常关闭的,那么oracle在进行实例恢复时必须要求当前日志,
否则oracle将无法保证提交成功的数据不丢失(也就是意味着oracle会丢失数据),在这种情况下,
oracle数据库将无法启动.
对于这种情况,通常需要从备份中恢复数据文件,通过应用归档日志文件向前推演,直到最后一个
完好的日志文件,然后可以通过resetlogs启动数据库完成恢复.丢失的数据就是损坏的日志文件
中的数据.
如果没有备份,oracle有一类具有特殊作用的隐含参数,其中一个参数是_allow_resetlogs_corruption,来看一下
这个参数的说明:
SQL> select x.ksppinm NAME,y.ksppstvl value,x.ksppdesc describ
2 from x$ksppi x, x$ksppcv y
3 where x.inst_id=USERENV('Instance')
4 and y.inst_id=USERENV('Instance')
5 and x.indx=y.indx
6 and x.ksppinm like '%_allow_resetlogs_%';
NAME VALUE DESCRIB
------------------------------ -------------- --------------------------------------------------
_allow_resetlogs_corruption FALSE allow resetlogs even if it will cause corruption
该参数的含义是,允许在破坏一致性的情况下强制重置日志,打开数据库._allow_resetlogs_corruption将
使用所有数据文件中最旧的scn打开数据库,所以通常需要保证system表空间拥有最旧的scn.
在强制打开数据库之后,可能因为各种原因伴随出现ora-00600错误,有些可以依据常规途径解决,看一下下面
的例子:
[oracle@jingyong ~]$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jan 7 02:04:02 2013 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options
查询当前的日志组
SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------ 1 1 2 52428800 1 NO CURRENT 936058 07-JAN-13 2 1 0 52428800 1 YES UNUSED 0 3 1 1 52428800 1 NO INACTIVE 914918 07-JAN-13 SQL> select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE ---------- ------- ------- ----------------------------------------------------------- --------------------- 3 STALE ONLINE /u01/app/oracle/product/10.2.0/oradata/jingyong/redo03.log NO 2 ONLINE /u01/app/oracle/product/10.2.0/oradata/jingyong/redo02.log NO 1 ONLINE /u01/app/oracle/product/10.2.0/oradata/jingyong/redo01.log NO
删除当前日志组的日志文件
SQL> !rm -rf /u01/app/oracle/product/10.2.0/oradata/jingyong/redo01.log SQL> shutdown abort; ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 167772160 bytes Fixed Size 1218316 bytes Variable Size 62916852 bytes Database Buffers 100663296 bytes Redo Buffers 2973696 bytes Database mounted. ORA-00313: open failed for members of log group 1 of thread 1 ORA-00312: online log 1 thread 1: '/u01/app/oracle/product/10.2.0/oradata/jingyong/redo01.log' ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3
启动失败,日志文件损坏,在mount状态,可以查询v$log视图,发现此处损坏的是current的日志文件
SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------ 1 1 2 52428800 1 NO CURRENT 936058 07-JAN-13 3 1 1 52428800 1 NO INACTIVE 914918 07-JAN-13 2 1 0 52428800 1 YES UNUSED 0
由于active和current日志没有完成检查点，在恢复中需要用到,丢失active和current日志情况类似.
如果没有备份,只好使用隐含参数_allow_resetlogs_corruption强制启动数据库,设置此参数之后,
在数据库open过程中,oracle会跳过某些一致性检查,从而使用权数据库可能跳过不一致状态,直接打开.
SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;
System altered.
SQL> shutdown
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 62916852 bytes
Database Buffers 100663296 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL> recover database until cancel;
ORA-00279: change 936059 generated at 01/07/2013 02:01:11 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/product/10.2.0/flash_recovery_area/JINGYONG/archivelog/2013_01_0
7/o1_mf_1_2_%u_.arc
ORA-00280: change 936059 for thread 1 is in sequence #2
Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1:
'/u01/app/oracle/product/10.2.0/oradata/jingyong/system01.dbf'
ORA-01112: media recovery not started
SQL> alter database open resetlogs;
Database altered.
如果运气好的话,数据库就可以成功打开了,如果不幸,则可能会遇到一些ora-00600的错误
那么就需要使用其它方法来进行恢复
通过这种方法恢复可以在alert日志中看见类似以下的日志信息:
Mon Jan 7 02:11:19 2013 alter database open resetlogs Mon Jan 7 02:11:19 2013 RESETLOGS is being done without consistancy checks. This may result in a corrupted database. The database should be recreated. RESETLOGS after incomplete recovery UNTIL CHANGE 936059 Resetting resetlogs activation ID 3141718551 (0xbb42d217) Mon Jan 7 02:11:19 2013
不一致恢复最后恢复到的change号是936059,信息中说明了强制resetlogs不进行一致性检查,
可能会导致数据库损坏,数据库应当重建.
oracle丢失inactive日志文件的恢复操作过程
丢失非活动日志组的故障恢复
如果数据库丢失的是非活动(inactive)日志组,由于非活动日志组已经完成检查点,
数据库不会发生数据损失,此时只需要通过clear重建该日志组即可恢复.
先删除一个非活动日志组,模拟一次故障损失:
SQL> !rm /u01/app/oracle/product/10.2.0/oradata/jingyong/redo03.log SQL> alter system switch logfile; System altered. SQL> alter system switch logfile; System altered.
此时启动数据库,数据库会提示日志丢失
SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 167772160 bytes Fixed Size 1218316 bytes Variable Size 67111156 bytes Database Buffers 96468992 bytes Redo Buffers 2973696 bytes Database mounted. ORA-00313: open failed for members of log group 3 of thread 1 ORA-00312: online log 3 thread 1: '/u01/app/oracle/product/10.2.0/oradata/jingyong/redo03.log'
此时在mount状态,可以查看各日志组及日志文件的状态:
SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------ 1 1 26 52428800 1 NO INACTIVE 913689 06-JAN-13 3 1 28 52428800 1 NO CURRENT 913701 06-JAN-13 2 1 27 52428800 1 NO INACTIVE 913696 06-JAN-13 SQL> select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE ---------- ------- ------- ----------------------------------------------------------- --------------------- 3 ONLINE /u01/app/oracle/product/10.2.0/oradata/jingyong/redo03.log NO 2 ONLINE /u01/app/oracle/product/10.2.0/oradata/jingyong/redo02.log NO 1 ONLINE /u01/app/oracle/product/10.2.0/oradata/jingyong/redo01.log NO
清除该日志组后即可启动数据库:
SQL>alter database clear logfile group 3; Database altered SQL>alter database open; Database altered
注意,如果数据库处于归档模式下,并且该日志组未完成归档则需要使用如下命令强制清除
alter database clear unarchived logfile group 3;
打开数据库之后,状态为stale的日志文件,在下次正常写入后,状态即可恢复正常:
SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------ 1 1 2 52428800 1 NO INACTIVE 914153 06-JAN-13 2 1 3 52428800 1 NO INACTIVE 914157 06-JAN-13 3 1 4 52428800 1 NO CURRENT 914164 06-JAN-13
oracle自定义过程来获得完整的sql语句
因为oracle将sql共享之后，截取出来的sql语句是带变量的
创建一个函数 jy_getsql来获得执行时的完整sql语句
CREATE OR REPLACE FUNCTION jy_getsql (my_sql_id in varchar2)
RETURN clob
IS
Result clob;
cursor jl(p_sql_id in varchar2) is
select decode(instr(b.BIND_NAME,'SYS'),0,':'||b.BIND_NAME||'',''||chr(58)||chr(34)||b.BIND_NAME||chr(34)||'') name,
decode(b.DATATYPE,2,dbms_sqltune.extract_bind(a.bind_data,b.POSITION).value_string ,''''||dbms_sqltune.extract_bind(a.bind_data,b.POSITION).value_string ||'''')
value_string
from v$sqlarea a ,v$sql_bind_metadata b
where a.LAST_ACTIVE_CHILD_ADDRESS = b.ADDRESS
and a.SQL_ID = p_sql_id
order by b.POSITION desc;
BEGIN
select a.SQL_FULLTEXT into Result
from v$sqlarea a where a.SQL_ID=my_sql_id;
for r in jl(my_sql_id) loop
Result := replace(Result,r.name,r.value_string);
end loop;
RETURN Result;
EXCEPTION
WHEN OTHERS
THEN
RETURN Result;
END;
下面的查询是查询数据库当前正处于等待状态的sql语句
select sw.sid,s.username,sw.event,sw.wait_time,sw.state,sw.seconds_in_wait,p.PROGRAM,s.MACHINE, (select c.SQL_FULLTEXT from v$sqlarea c where c.SQL_ID=s.SQL_ID) sql_fulltext, (select c.BIND_DATA from v$sqlarea c where c.SQL_ID=s.SQL_ID) BIND_DATA,s.SQL_ID, jy_getsql(s.SQL_ID) from v$session s,v$session_wait sw,v$process p where s.username is not null and s.PADDR=p.ADDR and sw.sid=s.sid and sw.event not like'%SQL*Net%' order by sw.wait_time desc
但是这还有一缺点就是
select 1 userid from dual
这个1没有在v$sqlarea.bind_data中
但where子句中的绑定变量还是有了
X$BH与Buffer Header
X$BH与Buffer Header
buffer header数据,可以从数据库的数据字典表中查询得到,这张字典表就是x$bh,x$bh中的bh就是
指buffer headers,每个buffer在x$bh中都存在一条记录
buffer header中存储每个buffer容纳的数据块的文件号,块地址,状态等重要信息,根据这些信息,
结合dba_extents视图,可以很容易地找到每个buffer对应的对象信息:
x$bh中还有一个重要的字段TCH,TCH为Touch的缩写,表示一个Buffer的访问次数,buffer被访问的次
数越多,说明该buffer就越抢手,也就可能存在热块竞争的问题
通过对下查询获得当前数据库最繁忙的buffer
SQL> select * from (select addr,ts#,file#,dbarfil,dbablk,tch from x$bh order by tch desc) where rownum<21; ADDR TS# FILE# DBARFIL DBABLK TCH ---------------- ---------- ---------- ---------- ---------- ---------- 9FFFFFFFBF5AACA8 7 12 12 638244 1722 9FFFFFFFBF5AACA8 7 13 13 598707 1636 9FFFFFFFBF5AAFF8 7 11 11 261996 1629 9FFFFFFFBF5AAFF8 6 8 8 135404 1614 9FFFFFFFBF5AAFF8 7 11 11 655501 1587 9FFFFFFFBF5AAFF8 7 11 11 269628 1568 9FFFFFFFBF5AAFF8 7 11 11 2742315 1568 9FFFFFFFBF5AAFF8 7 11 11 269612 1562 9FFFFFFFBF5AACA8 7 13 13 601755 1538 9FFFFFFFBF5AAFF8 7 13 13 599052 1514 9FFFFFFFBF5AACA8 7 13 13 254900 1506 9FFFFFFFBF5AACA8 7 12 12 261898 1504 9FFFFFFFBF5AAFF8 7 11 11 662797 1491 9FFFFFFFBF5AACA8 7 13 13 610957 1487 9FFFFFFFBF5AAFF8 6 8 8 715684 1465 9FFFFFFFBF5AAFF8 7 11 11 665204 1462 9FFFFFFFBF5AAFF8 7 11 11 132492 1461 9FFFFFFFBF5AACA8 6 8 8 1766500 1458 9FFFFFFFBF5AACA8 7 11 11 273549 1445 9FFFFFFFBF5AAFF8 7 11 11 266099 1441 20 rows selected
再结合dba_extents中的信息,可以查询得到这些热点buffer都来自哪些对象;
select a.owner,a.segment_name,a.segment_type from dba_extents a, (select * from (select addr,ts#,file#,dbarfil,dbablk,tch from x$bh order by tch desc) where rownum<21) b where a.relative_fno=b.dbarfil and a.block_id< =b.dbablk and a.block_id+a.blocks>b.dbablk;
除了查询x$bh之外,也可以从buffer cache的转储信息中,看到buffer header的具体内容
BH (0x25feb12c) file#: 1 rdba: 0x0040b894 (1/47252) class: 1 ba: 0x25cec000
set: 3 blksize: 8192 bsi: 0 set-flg: 0 pwbcnt: 75
dbwrid: 0 obj: 181 objn: 183 tsn: 0 afn: 1
hash: [290c0868,290c0868] lru: [25feb230,25feb0d0]
lru-flags:
ckptq: [NULL] fileq: [NULL] objq: [25feb124,25feb284]
st: XCURRENT md: NULL tch: 1
flags:
LRBA: [0x0.0.0] HSCN: [0xffff.ffffffff] HSUB: [65535]
buffer tsn: 0 rdba: 0x0040b894 (1/47252)
scn: 0x0000.00074869 seq: 0x01 flg: 0x04 tail: 0x48690601
frmt: 0x02 chkval: 0xa2b2 type: 0x06=trans data
在oracle10g以前,数据库的等待事件中,所有的latch等待都被归纳为latch free等待
在statspack的report中,如果在top5等待事件中看到latch free这一等待处于较高的位置
那么就要地行研究和解决了
由于latch free是一个汇总等待事件,我们需要从v$latch视图中获得具体的latch竞争主要
是由哪些latch引起的
如果需要具体确定热块对象,可以从v$latch_children中查询到具体的子latch信息
SQL> select * from ( 2 select addr,child#,gets,misses,sleeps,immediate_gets,immediate_misses, spin_gets 3 from v$latch_children 4 where name='cache buffers chains' 5 order by sleeps desc) 6 where rownum<6; ADDR CHILD# GETS MISSES SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES SPIN_GETS ---------------- ---------- ---------- ---------- ---------- -------------- ---------------- ---------- C00000001888BF70 14784 126455972 1469279 636 2832 1 1468752 C0000000187F9940 11786 48496473 345817 216 2948 0 345623 C000000018645EF0 2864 63718005 332144 211 2645 1 331967 C0000000187CEDA8 10911 48514435 329026 207 3791 0 328844 C00000001877CDF0 9232 63325754 341420 202 3223 0 341256
在x$bh中还存在另外一个关键字段HLADDR，即hash chain latch address,这个字段可以和v$latch_children.addr
进行关联,这样就可以把具体的latch竞争和数据块关联起来,再结合dba_extents视图,就可以找到具体的热块竞争
对象,找到具体热点对象后,可以结合v$sqlarea或v$sqltext,找到频繁操作这些对象的sql,然后对其进行优化,
就可以缓解或解决热块竞争的问题了
SQL> select b.addr,a.ts#,a.dbarfil,a.dbablk,a.tch,b.gets,b.misses,b.sleeps from ( 2 select * from ( 3 select addr,ts#,dbarfil,dbablk,tch,hladdr from x$bh order by tch desc) 4 where rownum<6) a, 5 (select addr,gets,misses,sleeps from v$latch_children 6 where name='cache buffers chains') b 7 where a.hladdr=b.addr 8 / ADDR TS# DBARFIL DBABLK TCH GETS MISSES SLEEPS ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- C00000001886F4D8 7 12 638244 2273 656301 13 0 C00000001876C900 7 11 261996 2320 2972787 89 0 C0000000186BC960 7 11 269628 2220 495549 10 0 C0000000186A9FB8 6 8 135404 2303 3257192 101 0 C000000018634678 7 11 655501 2257 3640151 30614 26
可以使用下面的语句来找到这些热点buffer的对象信息
select a.owner,a.segment_name,a.segment_type from dba_extents a, (select * from (select addr,ts#,file#,dbarfil,dbablk,tch from x$bh order by tch desc) where rownum<21) b where a.relative_fno=b.dbarfil and a.block_id< =b.dbablk and a.block_id+a.blocks>b.dbablk;
或
select b.addr,a.ts#,a.dbarfil,a.dbablk,a.tch,b.gets,b.misses,b.sleeps from ( select * from ( select addr,ts#,dbarfil,dbablk,tch,hladdr from x$bh order by tch desc) where rownum<6) a, (select addr,gets,misses,sleeps from v$latch_children where name='cache buffers chains') b, dba_extents c where a.hladdr=b.addr and c.relative_fno=a.dbarfil and c.block_id< =a.dbablk and c.block_id+c.blocks>a.dbablk
再与v$sqltext或v%sqlarea视图关联可以找到操作这些热块对象的相关sql
select hash_value,sql_fulltext from v$sqlarea where sql_id in( select a.sql_id from v$sqltext a, (select distinct b.owner,b.segment_name,b.segment_type from dba_extents b, (select dbarfil,dbablk from( select dbarfil,dbablk from x$bh order by tch desc) where rownum<11) c where b.relative_fno=c.dbarfil and b.block_id< =c.dbablk and b.block_id+b.blocks>c.dbablk) d where a.sql_text like '%'||d.segment_name||'%' and d.segment_type='TABLE')
找到这些sql语句后,就可以通过优化sql减少数据的访问,避免或优化某些容易引起的争用操作
来减少热块竞争
cache buffer chain
buffer cache的管理有两个重要的数据结构:
hash bucket和cache buffer chain
1. hash bucket和cache buffer chain
可以想象,如果所有的buffer cache中的所有buffer都通过同一个结构来进行管理,当需要确定某个
block在buffer中是否存在时,将需要遍历整个结构,性能会相当低下.
为了提高效率,oracle引入了bucket的数据结构,oracle把管理的所有buffer通过一个内部的hash算法
运算后,存放到不同的hash bucket中,这样通过hash bucket进行分割之后,众多的buffer被分布到一
定数量的bucket之中,当用户需要在buffer中定位数据是否存在时,只需要通过同样的算法来获得hash
值然后到相应的bucket中查找少理的buffer即可确定.每个buffer存放的bucket由buffer的数据块
地址(DBA,Data Block Address)运算决定.
bucket内部,通过cache buffer chain(cache buffer chain是一个双向链表)将所有的buffer通过
buffer header信息联系起来
buffer header存放的是对应数据块的概要信息,包括数据块的文件号,块地址,状态等.要判断数据块
在buffer中是否存在,通过检查buffer header即可确定.
如果多个会话同时对相同的cache buffer chain进行读取时就会产生cache buffer chain的竞争.
先来进行读取的会话持有这个cache buffer chain的latch
从oracle9i开始,对于cache buffer chain的只读访问,其latch可以被共享,也就是说,如果多个会话
都只是来查阅这个cache buffer chain那么大家可以同时进行查阅,但是如果有会话要对这个
cache buffer chian中的buffer header对应的数据块进行操作时那么就只能独享这个latch了.
这就是buffer cache 与latch的竞争
由于buffer根据buffer header进行散列,从而最终决定存入哪一个hash bucket,那么hash bucket的
数量在一定程度上就决定了每个bucket中buffer数量的多少,也就间接影响了搜索buffer header的
性能.
所以在不同版本中,oracle一直在修改算法,优化hash bucket的数量,可以想象,bucket的数量多一些
那么在同一时间就可以有更多的会话可以对不同的hash bucket进行读取.但是更多的hash bucket,
显然需要更多的存放空间,更多的管理成本,所以优化在什么时候都不是简单的一元方程.
hash bucket的设置受一个隐含参数_db_block_hash_buckets的影响.
SQL> select x.ksppinm name,y.ksppstvl value,x.ksppdesc describ
2 from sys.x$ksppi x,sys.x$ksppcv y
3 where
4 x.inst_id=userenv('Instance') and
5 y.inst_id=userenv('Instance') and
6 x.indx=y.indx and x.ksppinm like '%_db_block_hash_buckets%'
7 ;
NAME VALUE DESCRIB
---------------------------- ---------------- ----------------------------------------
_db_block_hash_buckets 4194304 Number of database block hash buckets
对于每个hash bucket，只会有一个cache buffer chain ,当用户试图搜索cache buffer chain时
必须先获得cache buffer chain latch.那么cache buffer chain latch的设置就同样值得研究
在oracle8i之前,对于每一个hash bucket,oracle使用一个独立的hash latch来维护,其缺省的
bucket数量为next_prime(db_block_buffers/4)
由于过于严重的热块竞争,从oracle8i开始,oracle改变了这个算法,先是bucket数量开始增加,
_db_block_hash_bucket增加到了2*db_block_buffers,而_db_block_hash_latchs的数量也发生
变化
当cache buffers少于2052个buffers时:
_db_block_hash_latches=power(2,trunc(log(2,db_block_buffers-4)-1))
当cache buffers多于131075个buffers时:
_db_block_hash_latches=power(2,trunc(log(2,db_block_buffers-4)-6))
当cache buffers在2052与131075个buffers之间时:
_db_block_hash_latches=1024
从oracle8i开始,_db_block_hash_buckets的数量较以前增加了8倍,而_db_block_hash_latchs的
数量增加比较有限,这意味着,每个latch需要管理多个bucket,但是由于bucket数量的成倍增加,
每个bucket中的block的数量得以减少,从而使用少量latch管理更多bucket成为可能
从oracle8i开始,bucket的数量比以前大大增加;通过增加bucket的数量来使得每个bucket上的
buffer的数量大大减少.
在oracle8i之前,_db_block_hash_latches的数量和hash bucket的数量是一致的,每一个latch管理
一个bucket,从oracle8i开始每个lath可以管理多个bucket,由于每个bucket中的buffer header数量
大大降低所以latch的性能反而得到提高
每个bucket存在一条cache buffer chain
buffer header上存在指向具体buffer的指针
下面是测试的一情景
db_cache_size为88MB,此时的_db_block_hash_buckets为32768
SQL> show parameter db_cache_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_cache_size big integer 88M
SQL>
SQL> select x.ksppinm name,y.ksppstvl value,x.ksppdesc describ
2 from sys.x$ksppi x,sys.x$ksppcv y
3 where
4 x.inst_id=userenv('Instance') and
5 y.inst_id=userenv('Instance') and
6 x.indx=y.indx and x.ksppinm like '%_db_block_hash_buckets%'
7 ;
NAME VALUE DESCRIB
-------------------------------- ------------------- --------------------------------------------
_db_block_hash_buckets 32768 Number of database block hash buckets
SQL> show parameter db_block_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192
计算db_cache_size=88MB,有多少个db_block_buffer=11264
SQL> select 88*1024/8 from dual;
88*1024/8
----------
11264
查询一下_db_block_hash_latches=1024所以应证了
当cache buffers在2052与131075个buffers之间时:
_db_block_hash_latches=1024
SQL> select x.ksppinm name,y.ksppstvl value,x.ksppdesc describ
2 from sys.x$ksppi x,sys.x$ksppcv y
3 where
4 x.inst_id=userenv('Instance') and
5 y.inst_id=userenv('Instance') and
6 x.indx=y.indx and x.ksppinm like '%_db_block_hash_latches%'
7 ;
NAME VALUE DESCRIB
-------------------------------------- ------------------- -----------------------------------------
_db_block_hash_latches 1024 Number of database block hash latches
计算一下每个bucket中有多少个buffer header
db_cache_size为88MB,此时的_db_block_hash_buckets为32768
db_block_size=8Kb
SQL> select 88*1024/8/32768 from dual;
88*1024/8/32768
---------------
0.34375
那么说明有的bucket中没有buffer header
SQL> alter session set events 'immediate trace name buffers level 10'; Session 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_3341.trc
跟踪文件中的cache buffer chain的数量正好是
[oracle@jingyong udump]$ grep CHAIN jingyong_ora_3341.trc | wc -l 32768
某些chain上可能没有buffer header信息(被标记为null),这些chain的数据类似如下:
[oracle@jingyong udump]$ grep CHAIN jingyong_ora_3341.trc|head -20 CHAIN: 0 LOC: 0x290c0838 HEAD: [NULL] CHAIN: 1 LOC: 0x290c0840 HEAD: [NULL] CHAIN: 2 LOC: 0x290c0848 HEAD: [NULL] CHAIN: 3 LOC: 0x290c0850 HEAD: [NULL] CHAIN: 4 LOC: 0x290c0858 HEAD: [NULL] CHAIN: 5 LOC: 0x290c0860 HEAD: [NULL] CHAIN: 6 LOC: 0x290c0868 HEAD: [25feb12c,25feb12c] CHAIN: 7 LOC: 0x290c0870 HEAD: [NULL] CHAIN: 8 LOC: 0x290c0878 HEAD: [24fe6dcc,24fe6dcc] CHAIN: 9 LOC: 0x290c0880 HEAD: [NULL] CHAIN: 10 LOC: 0x290c0888 HEAD: [NULL] CHAIN: 11 LOC: 0x290c0890 HEAD: [NULL] CHAIN: 12 LOC: 0x290c0898 HEAD: [233f8c7c,233f8c7c] CHAIN: 13 LOC: 0x290c08a0 HEAD: [NULL] CHAIN: 14 LOC: 0x290c08a8 HEAD: [NULL] CHAIN: 15 LOC: 0x290c08b0 HEAD: [NULL] CHAIN: 16 LOC: 0x290c08b8 HEAD: [NULL] CHAIN: 17 LOC: 0x290c08c0 HEAD: [NULL] CHAIN: 18 LOC: 0x290c08c8 HEAD: [21fed2dc,21fee82c] CHAIN: 19 LOC: 0x290c08d0 HEAD: [NULL]
查看一下chain 6的数据
CHAIN: 6 LOC: 0x290c0868 HEAD: [25feb12c,25feb12c]
BH (0x25feb12c) file#: 1 rdba: 0x0040b894 (1/47252) class: 1 ba: 0x25cec000
set: 3 blksize: 8192 bsi: 0 set-flg: 0 pwbcnt: 75
dbwrid: 0 obj: 181 objn: 183 tsn: 0 afn: 1
hash: [290c0868,290c0868] lru: [25feb230,25feb0d0]
lru-flags:
ckptq: [NULL] fileq: [NULL] objq: [25feb124,25feb284]
st: XCURRENT md: NULL tch: 1
flags:
LRBA: [0x0.0.0] HSCN: [0xffff.ffffffff] HSUB: [65535]
buffer tsn: 0 rdba: 0x0040b894 (1/47252)
scn: 0x0000.00074869 seq: 0x01 flg: 0x04 tail: 0x48690601
frmt: 0x02 chkval: 0xa2b2 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x25CEC000 to 0x25CEE000
25CEC000 0000A206 0040B894 00074869 04010000 [......@.iH......]
25CEC010 0000A2B2 00000001 000000B5 00074869 [............iH..]
25CEC020 1FE80000 00031F02 0040B88B 00140001 [..........@.....]
25CEC030 0000006D 00805685 0022002D 00008000 [m....V..-.".....]
25CEC040 00039FF0 0011000A 000000C7 00800A9E [................]
25CEC050 002600D7 00008000 0007481D 00050400 [..&......H......]
25CEC060 0028FFFF 1E791E70 00001E79 00010001 [..(.p.y.y.......]
25CEC070 00020001 00020000 1F790003 1E701F33 [..........y.3.p.]
25CEC080 1EA21ED5 00000000 00000000 00000000 [................]
25CEC090 00000000 00000000 00000000 00000000 [................]
Repeat 482 times
25CEDEC0 00000000 00000000 00000000 000D006C [............l...]
25CEDED0 02444902 001002C1 00000000 00000000 [.ID.............]
25CEDEE0 00000000 02190000 02FF02C1 C20303C1 [................]
25CEDEF0 C1023509 02C20302 FFFFFF1D 006C8001 [.5............l.]
25CEDF00 500B000D 41424F52 494C4942 C1025954 [...PROBABILITY..]
25CEDF10 00001004 00000000 00000000 00000000 [................]
25CEDF20 C1020F00 C102FF02 FFFFFF03 01FFFFFF [................]
25CEDF30 0D006C80 43530500 0245524F 001003C1 [.l....SCORE.....]
25CEDF40 00000000 00000000 00000000 020F0000 [................]
25CEDF50 02FF02C1 FFFF03C1 FFFFFFFF 006C8001 [..............l.]
25CEDF60 4902000D 02C10244 00000010 00000000 [...ID...........]
25CEDF70 00000000 00000000 02C10219 03C102FF [................]
25CEDF80 0202C102 C20302C1 FFFF1D02 6C8001FF [...............l]
25CEDF90 04001500 302E3824 5ECEFA10 4AAA6474 [....$8.0...^td.J]
25CEDFA0 5730E0F6 1016058C 02C20365 05C10209 [..0W....e.......]
25CEDFB0 0104C102 FFFFFF80 FFFFFFFF FFFFFFFF [................]
25CEDFC0 11FFFFFF F99F5921 C8031661 E625EF53 [....!Y..a...S.%.]
25CEDFD0 03CF388F 0200AC3D 00040004 94B84000 [.8..=........@..]
25CEDFE0 40000000 000094B8 5ECEFA10 4AAA6474 [...@.......^td.J]
25CEDFF0 5730E0F6 1016058C 02C10265 48690601 [..0W....e.....iH]
Block header dump: 0x0040b894
Object id on Block? Y
seg/obj: 0xb5 csc: 0x00.74869 itc: 2 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x40b88b ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0001.014.0000006d 0x00805685.002d.22 C--- 0 scn 0x0000.00039ff0
0x02 0x000a.011.000000c7 0x00800a9e.00d7.26 C--- 0 scn 0x0000.0007481d
data_block_dump,data header at 0x25cec05c
===============
tsiz: 0x1fa0
hsiz: 0x28
pbl: 0x25cec05c
bdba: 0x0040b894
76543210
flag=--------
ntab=4
nrow=5
frre=-1
fsbo=0x28
fseo=0x1e70
avsp=0x1e79
tosp=0x1e79
0xe:pti[0] nrow=1 offs=0
0x12:pti[1] nrow=1 offs=1
0x16:pti[2] nrow=0 offs=2
0x1a:pti[3] nrow=3 offs=2
0x1e:pri[0] offs=0x1f79
0x20:pri[1] offs=0x1f33
0x22:pri[2] offs=0x1e70
0x24:pri[3] offs=0x1ed5
0x26:pri[4] offs=0x1ea2
block_row_dump:
tab 0, row 0, @0x1f79
tl: 39 fb: K-H-FL-- lb: 0x0 cc: 2
curc: 4 comc: 4 pk: 0x0040b894.0 nk: 0x0040b894.0
col 0: [16] fa ce 5e 74 64 aa 4a f6 e0 30 57 8c 05 16 10 65
col 1: [ 2] c1 02
tab 1, row 0, @0x1f33
tl: 70 fb: -CH-FL-- lb: 0x0 cc: 21 cki: 0
col 0: [ 4] 24 38 2e 30
col 1: [16] fa ce 5e 74 64 aa 4a f6 e0 30 57 8c 05 16 10 65
col 2: [ 3] c2 02 09
col 3: [ 2] c1 05
col 4: [ 2] c1 04
col 5: [ 1] 80
col 6: *NULL*
col 7: *NULL*
col 8: *NULL*
col 9: *NULL*
col 10: *NULL*
col 11: *NULL*
col 12: *NULL*
col 13: *NULL*
col 14: *NULL*
col 15: *NULL*
col 16: *NULL*
col 17: *NULL*
col 18: *NULL*
col 19: *NULL*
col 20: [17] 21 59 9f f9 61 16 03 c8 53 ef 25 e6 8f 38 cf 03 3d
tab 3, row 0, @0x1e70
tl: 50 fb: -CH-FL-- lb: 0x0 cc: 13 cki: 0
col 0: [ 2] 49 44
col 1: [ 2] c1 02
col 2: [16] 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 19
col 3: [ 2] c1 02
col 4: *NULL*
col 5: [ 2] c1 03
col 6: [ 3] c2 09 35
col 7: [ 2] c1 02
col 8: [ 3] c2 02 1d
col 9: *NULL*
col 10: *NULL*
col 11: *NULL*
col 12: [ 1] 80
tab 3, row 1, @0x1ed5
tl: 45 fb: -CH-FL-- lb: 0x0 cc: 13 cki: 0
col 0: [ 5] 53 43 4f 52 45
col 1: [ 2] c1 03
col 2: [16] 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 0f
col 3: [ 2] c1 02
col 4: *NULL*
col 5: [ 2] c1 03
col 6: *NULL*
col 7: *NULL*
col 8: *NULL*
col 9: *NULL*
col 10: *NULL*
col 11: *NULL*
col 12: [ 1] 80
tab 3, row 2, @0x1ea2
tl: 51 fb: -CH-FL-- lb: 0x0 cc: 13 cki: 0
col 0: [11] 50 52 4f 42 41 42 49 4c 49 54 59
col 1: [ 2] c1 04
col 2: [16] 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 0f
col 3: [ 2] c1 02
col 4: *NULL*
col 5: [ 2] c1 03
col 6: *NULL*
col 7: *NULL*
col 8: *NULL*
col 9: *NULL*
col 10: *NULL*
col 11: *NULL*
col 12: [ 1] 80
end_of_block_dump
这个chain中存一个BH信息,其中包含”hash: [290c0868,290c0868] lru: [25feb230,25feb0d0]”
“hash: [290c0868,290c0868] “中的两个数据分别代表X$BH中的NXT_HASH和PRV_HASH,也就是指
同一个hash chain上的下一个BH地址和上一个buffer地址.如果某个chain只包含一个BH,
那么这两个值将同时指向该chain地址
“lru: [25feb230,25feb0d0]”中的两个数据分别代表X$BH中的NXT_REPL和PRV_REPL也就是LRU上的
下一个buffer和上一个buffer