崩溃恢复的起点和终点 -电脑资料

电脑资料时间：2019-01-01 我要投稿

【www.unjs.com - 电脑资料】

崩溃恢复的起点和终点

准备三个会话：

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>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>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>

----------

LOCATION

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

5_13

----------

LOCATION

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

5_13

----------

LOCATION

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

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 0d000d00 0d000100 00000000 00000000

00000000 02004001 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> 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> 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 例程已经启动，

电脑资料

《崩溃恢复的起点和终点》(https://www.unjs.com)。

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#

----------

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')

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

SESSION A>

SESSION A>select to_number('42','XXXXXXXX') from dual;

TO_NUMBER('42','XXXXXXXX')

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

我们可以看到现在控制文件里记录的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')

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

SESSION A>

SESSION A>select to_number('48','XXXXXXXX') from dual;

TO_NUMBER('49','XXXXXXXX')

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

然后我们看看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')

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

注意到这里差异就产生了。我们刚才用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')

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

SESSION A>

SESSION A>select to_number('49','XXXXXXXX') from dual;

TO_NUMBER('49','XXXXXXXX')

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

好了，我们现在回到上述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;

----------

已选择32行。

OK验证完毕

相关文章