终点 崔老师"/>
崩溃恢复的起点和终点 崔老师
准备三个会话：
SESSION A：
SESSION A>create tablespace test datafile '/tmp/test.dbf' size 1m reuse;
表空间已创建。
SESSION A> create table t(id number) tablespace test;
表已创建。
SESSION A> create or replace procedure p_instance_recovery_demo is
2 i number;
3 begin
4 for i in 1..100 loop
5 insert into t values (i);
6 commit;
7 dbms_lock.sleep(1);
8 end loop;
9 end p_instance_recovery_demo;
10 /
过程已创建。
SESSION A>col member for a30;
SESSION A>col member for a30;
SESSION A>select group#,sequence#,archived,status,first_change# from v$log;
GROUP# SEQUENCE# ARCHIVED STATUS FIRST_CHANGE#
---------- ---------- --------- ------------------------------------------------ -------------
1 34 YES INACTIVE 1657465
2 35 YES INACTIVE 1657534
3 36 NO CURRENT 1677594
SESSION A>select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_RECOVE
---------- --------------------- --------------------- ------------------------------ ---------
3 ONLINE /u01/app/oracle/oradata/myorcl NO
/redo03.log
2 ONLINE /u01/app/oracle/oradata/myorcl NO
/redo02.log
1 ONLINE /u01/app/oracle/oradata/myorcl NO
/redo01.log
SESSION A>alter system switch logfile;
系统已更改。
SESSION A>
SESSION A>
SESSION A>select group#,sequence#,archived,status,first_change# from v$log;
GROUP# SEQUENCE# ARCHIVED STATUS FIRST_CHANGE#
---------- ---------- --------- ------------------------------------------------ -------------
1 37 NO CURRENT 1678224
2 35 YES INACTIVE 1657534
3 36 YES ACTIVE 1677594
SESSION A>exec p_instance_recovery_demo;
SESSION b：
SESSION b>
ID
----------
LOCATION
--------------------------------------------------------------------------------
1
5_13
2
5_13
3
5_13
ID
----------
LOCATION
--------------------------------------------------------------------------------
4
5_13
5
5_13
6
5_13
ID
----------
LOCATION
--------------------------------------------------------------------------------
7
5_13
8
5_13
9
5_13
......后面还有很多数据注意让他显示在一个块上，基本上有个20条左右，就可以切换到session C了
SESSION C：
SESSION C>alter system checkpoint;
系统已更改。
SESSION C> shutdown abort;
ORACLE 例程已经关闭。
SESSION A：
SESSION A>exec p_instance_recovery_demo;
BEGIN p_instance_recovery_demo; END;
*
第 1 行出现错误:
ORA-03113: 通信通道的文件结尾进程 ID:
5536
会话 ID: 191 序列号: 7
然后新启一个会话：
BBED> set file 5
FILE# 5
BBED> p kcvfhckp
struct kcvfhckp, 36 bytes @484
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0x00199be6
ub2 kscnwrp @488 0x0000
ub4 kcvcptim @492 0x3081185b
ub2 kcvcpthr @496 0x0001
union u, 12 bytes @500
struct kcvcprba, 12 bytes @500
ub4 kcrbaseq @500 0x00000025
ub4 kcrbabno @504 0x00000042
ub2 kcrbabof @508 0x0010
ub1 kcvcpetb[0] @512 0x02
ub1 kcvcpetb[1] @513 0x00
ub1 kcvcpetb[2] @514 0x00
ub1 kcvcpetb[3] @515 0x00
ub1 kcvcpetb[4] @516 0x00
ub1 kcvcpetb[5] @517 0x00
ub1 kcvcpetb[6] @518 0x00
ub1 kcvcpetb[7] @519 0x00
BBED> set file 5 block 1
FILE# 5
BLOCK# 1
BBED> set offset 500
OFFSET 500
BBED> dump
File: /tmp/test.dbf (5)
Block: 1 Offsets: 500 to 1011 Dba:0x01400001
------------------------------------------------------------------------
25000000 42000000 10007e04 02000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 0d000d00 0d000100 00000000 00000000
00000000 02004001 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
可以看到现在的Thread Checkpoint RBA是00000025.00000042.047e0010
BBED> set file 5 block 13;
FILE# 5
BLOCK# 13
BBED> map /v
File: /tmp/test.dbf (5)
Block: 13 Dba:0x0140000d
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0
ub1 type_kcbh @0
ub1 frmt_kcbh @1
ub1 spare1_kcbh @2
ub1 spare2_kcbh @3
ub4 rdba_kcbh @4
ub4 bas_kcbh @8
ub2 wrp_kcbh @12
ub1 seq_kcbh @14
ub1 flg_kcbh @15
ub2 chkval_kcbh @16
ub2 spare3_kcbh @18
struct ktbbh, 72 bytes @20
ub1 ktbbhtyp @20
union ktbbhsid, 4 bytes @24
struct ktbbhcsc, 8 bytes @28
sb2 ktbbhict @36
ub1 ktbbhflg @38
ub1 ktbbhfsl @39
ub4 ktbbhfnx @40
struct ktbbhitl[2], 48 bytes @44
struct kdbh, 14 bytes @100
ub1 kdbhflag @100
sb1 kdbhntab @101
sb2 kdbhnrow @102
sb2 kdbhfrre @104
sb2 kdbhfsbo @106
sb2 kdbhfseo @108
sb2 kdbhavsp @110
sb2 kdbhtosp @112
struct kdbt[1], 4 bytes @114
sb2 kdbtoffs @114
sb2 kdbtnrow @116
sb2 kdbr[28] @118
ub1 freespace[7846] @174
ub1 rowdata[168] @8020
ub4 tailchk @8188
BBED> p kdbhnrow
sb2 kdbhnrow @102 28--28行数据
BBED>
BBED>
BBED> p *kdbr[28]
BBED-00401: file not found; arguments: [28] --下标以0开始
BBED> p *kdbr[27] --下标27为最后一条数据
rowdata[0]
----------
ub1 rowdata[0] @8020 0x2c
BBED>
BBED>
BBED> x /rnccntnnn
rowdata[0] @8020
----------
flag@8020: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8021: 0x02
cols@8022: 1
col 0[2] @8023: 28 --第28条数据为28
SESSION C：
SESSION C>startup mount
ORACLE 例程已经启动。
Total System Global Area 334036992 bytes
Fixed Size 2228144 bytes
Variable Size 201326672 bytes
Database Buffers 125829120 bytes
Redo Buffers 4653056 bytes
数据库装载完毕。
SESSION A：
SESSION A> select group# from v$log where status='CURRENT';
GROUP#
----------
1
SESSION A>select member from v$logfile where group#=1;
MEMBER
------------------------------
/u01/app/oracle/oradata/myorcl/redo01.log
SESSION A>alter session set events 'immediate trace name controlf level 3';
会话已更改。
SESSION A>
SESSION A>alter system dump logfile '/u01/app/oracle/oradata/myorcl/redo01.log';
系统已更改。
SESSION A>oradebug tracefile_name
/u01/app/oracle/diag/rdbms/myorcl/myorcl/trace/myorcl_ora_6375.trc
***************************************************************************
CHECKPOINT PROGRESS RECORDS
***************************************************************************
(size = 8180, compat size = 8180, section max = 11, section in-use = 0,
last-recid= 0, old-recno = 0, last-recno = 0)
(extent = 1, blkno = 2, numrecs = 11)
THREAD #1 - status:0x2 flags:0x0 dirty:5
low cache rba:(0x25.42.0) on disk rba:(0x25.48.0)
on disk scn: 0x0000.00199bec 04/26/2013 14:19:09
resetlogs scn: 0x0000.00106ee4 04/10/2013 14:52:35
heartbeat: 813797392 mount id: 234341189
THREAD #2 - status:0x0 flags:0x0 dirty:0
low cache rba:(0x0.0.0) on disk rba:(0x0.0.0)
刚才数据文件中的Thread Checkpoint RBA是00000025.00000042.047e0010
控制文件中的low cache rba为0x25.42.0
显然是数据文件中的Thread Checkpoint RBA大
所以它就是数据文件中恢复的起点
然后我们看看数据文件中的Thread Checkpoint RBA ：00000025.00000042.047e0010
转换过来就是Low Cache RBA的logfile sequence是37，logfile block number是66。
SESSION A>select to_number('25','XXXXXXXX') from dual;
TO_NUMBER('25','XXXXXXXX')
--------------------------
37
SESSION A>
SESSION A>select to_number('42','XXXXXXXX') from dual;
TO_NUMBER('42','XXXXXXXX')
--------------------------
66
我们可以看到现在控制文件里记录的On Disk RBA是0x25.49.0，转换过来就是On Disk RBA的logfile sequence是37，logfile block number是73：
SESSION A>select to_number('25','XXXXXXXX') from dual;
TO_NUMBER('25','XXXXXXXX')
--------------------------
37
SESSION A>
SESSION A>select to_number('48','XXXXXXXX') from dual;
TO_NUMBER('49','XXXXXXXX')
--------------------------
72
然后我们看看redolog中的信息，首先来看current redo log file的尾端的插入记录：
REDO RECORD - Thread:1 RBA: 0x000025.00000049.0010 LEN: 0x01f0 VLD: 0x0d
SCN: 0x0000.00199bed SUBSCN: 1 04/26/2013 14:19:10
(LWN RBA: 0x000025.00000049.0010 LEN: 0001 NST: 0001 SCN: 0x0000.00199bec)
CHANGE #1 TYP:2 CLS:1 AFN:5 DBA:0x0140000d OBJ:75987 SCN:0x0000.00199beb SEQ:2 OP:11.2 ENC:0 RBL:0
KTB Redo
op: 0x01 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: F xid: 0x0003.00f.00000396 uba: 0x00c00632.00de.0e
KDO Op code: IRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0140000d hdba: 0x0140000a
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 31(0x1f) size/delt: 6
fb: --H-FL-- lb: 0x2 cc: 1
null: -
col 0: [ 2] c1 21
CHANGE #2 TYP:0 CLS:21 AFN:3 DBA:0x00c000a0 OBJ:4294967295 SCN:0x0000.00199be9 SEQ:2 OP:5.2 ENC:0 RBL:0
ktudh redo: slt: 0x000f sqn: 0x00000396 flg: 0x0012 siz: 136 fbi: 0
uba: 0x00c00632.00de.0e pxid: 0x0000.000.00000000
CHANGE #3 TYP:0 CLS:21 AFN:3 DBA:0x00c000a0 OBJ:4294967295 SCN:0x0000.00199bed SEQ:1 OP:5.4 ENC:0 RBL:0
ktucm redo: slt: 0x000f sqn: 0x00000396 srt: 0 sta: 9 flg: 0x2 ktucf redo: uba: 0x00c00632.00de.0e ext: 2 spc: 6314 fbi: 0
CHANGE #4 TYP:0 CLS:22 AFN:3 DBA:0x00c00632 OBJ:4294967295 SCN:0x0000.00199be9 SEQ:1 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 136 spc: 6452 flg: 0x0012 seq: 0x00de rec: 0x0e
xid: 0x0003.00f.00000396
ktubl redo: slt: 15 rci: 0 opc: 11.1 [objn: 75987 objd: 75987 tsn: 6]
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
0x00000000 prev ctl uba: 0x00c00632.00de.0d
prev ctl max cmt scn: 0x0000.00194640 prev tx cmt scn: 0x0000.00194645
txn start scn: 0x0000.00199beb logon user: 0 prev brb: 12584496 prev bcl: 0 BuExt idx: 0 flg2: 0
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: L itl: xid: 0x0003.005.00000395 uba: 0x00c00632.00de.0d
flg: C--- lkc: 0 scn: 0x0000.00199be9
KDO Op code: DRP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x0140000d hdba: 0x0140000a
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 31(0x1f)
SESSION A> select owner,object_name from dba_objects where object_id=75987;
OWNER OBJECT_NAME
------------------------------ ------------------------------
SYS T
SQL> select utl_raw.cast_to_number('c121') from dual;
UTL_RAW.CAST_TO_NUMBER('C121')
------------------------------
32
注意到这里差异就产生了。我们刚才用BBED查看了表T在磁盘上的最后一条记录，其id值是28。但这里对current redo log file的dump清晰的告诉我们，上述表T的最后一条被成功插入的记录的id值是32也就是说，id为29、30和31，32的那四条记录还在buffer cache里，还没有被写回到数据文件。
另外我们刚才已经从对控制文件的dump内容看到On Disk RBA的值是0x25.48.0，而上述插入id值为32的这条redo record的RBA是0x000025.00000049.0010，即现在的On Disk RBA小于id值为32的这条redo record所在的RBA。如果Oracle在做Instance Recovery的时候只恢复到On Disk RBA，那么就意味着id为32的这条记录就真的丢掉了，这显然是很扯淡的事情，不可能这样的。
上面的内容我们可以看到，现在current redo log file尾端的最后一条redo record对应的RBA是0x000025.00000049.0010，翻译过来就是current redo log file尾端的最后一条redo record对应的logfile sequence是37，logfile block number是73：
SESSION A>select to_number('25','XXXXXXXX') from dual;
TO_NUMBER('25','XXXXXXXX')
--------------------------
37
SESSION A>
SESSION A>select to_number('49','XXXXXXXX') from dual;
TO_NUMBER('49','XXXXXXXX')
--------------------------
73
好了，我们现在回到上述command窗口来把上述数据库open。在open完毕后我们马上紧跟着执行对当前控制文件的dump操作：
SESSION A > alter database open;
SESSION A>alter session set events 'immediate trace name controlf level 3';
会话已更改。
Using 45 overflow buffers per recovery slave
Thread 1 checkpoint: logseq 37, block 66, scn 1678310
cache-low rba: logseq 37, block 66
on-disk rba: logseq 37, block 73, scn 1678316
start recovery at logseq 37, block 66, scn 1678310
SESSION A>select * from t;
ID
----------
1
2
3
4
5
6
7
8
9
10
11
ID
----------
12
13
14
15
16
17
18
19
20
21
22
ID
----------
23
24
25
26
27
28
29
30
31
32
已选择32行。
OK验证完毕
更多推荐
崩溃恢复的起点和终点 崔老师
发布评论