联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
如果在还原出来的数据文件中有坏块,而归档日志和联机日志是正常的,那么在应用日志恢复过程中,会出现什么情况,这里通过一个简单的测试给予其中一种情况的说明
创建测试表
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
SQL> create table t_xifenfei(object_id,object_name) tablespace xifenfei
2 as
3 select object_id,object_name from dba_objects
4 where rownum<11;
Table created.
SQL> col object_name for a30
SQL> select object_id,object_name,
2 dbms_rowid.rowid_relative_fno(rowid)rel_fno,
3 dbms_rowid.rowid_block_number(rowid)blockno
4 from chf.t_xifenfei;
OBJECT_ID OBJECT_NAME REL_FNO BLOCKNO
---------- ------------------------------ ---------- ----------
20 ICOL$ 5 12
44 I_USER1 5 12
28 CON$ 5 12
15 UNDO$ 5 12
29 C_COBJ# 5 12
3 I_OBJ# 5 12
25 PROXY_ROLE_DATA$ 5 12
39 I_IND1 5 12
51 I_CDEF2 5 12
26 I_PROXY_ROLE_DATA$_1 5 12
10 rows selected.
SQL> select name from v$datafile where file#=5;
NAME
--------------------------------------------------------------
/u01/oracle/oradata/XFF/xifenfei01.dbf
SQL> update t_xifenfei set object_name='WWW.XIFENFEI.COM';
10 rows updated.
SQL> commit;
Commit complete.
SQL> create table t_xifenfei_new(object_id,object_name) tablespace xifenfei
2 as
3 select object_id,object_name from dba_objects
4 where rownum<11;
Table created.
SQL> conn / as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
备份数据文件
[oracle@xifenfei XFF]$ cp xifenfei01.dbf ../tmp/ [oracle@xifenfei XFF]$ ll ../tmp/xifenfei01.dbf -rw-r----- 1 oracle oinstall 10493952 Sep 28 19:05 ../tmp/xifenfei01.dbf [oracle@xifenfei XFF]$ date Fri Sep 28 19:05:42 CST 2012
bbed破坏备份文件
[oracle@xifenfei XFF]$ bbed password=blockedit
BBED: Release 2.0.0.0.0 - Limited Production on Fri Sep 28 19:05:59 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> set filename '/u01/oracle/oradata/tmp/xifenfei01.dbf'
FILENAME /u01/oracle/oradata/tmp/xifenfei01.dbf
BBED> set block 12
BLOCK# 12
BBED> set mode edit
MODE Edit
BBED> map
File: /u01/oracle/oradata/tmp/xifenfei01.dbf (0)
Block: 12 Dba:0x00000000
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0
struct ktbbh, 96 bytes @20
struct kdbh, 14 bytes @124
struct kdbt[1], 4 bytes @138
sb2 kdbr[10] @142
ub1 freespace[7666] @162
ub1 rowdata[360] @7828
ub4 tailchk @8188
BBED> p kcbh
struct kcbh, 20 bytes @0
ub1 type_kcbh @0 0x06
ub1 frmt_kcbh @1 0xa2
ub1 spare1_kcbh @2 0x00
ub1 spare2_kcbh @3 0x00
ub4 rdba_kcbh @4 0x0140000c
ub4 bas_kcbh @8 0x0004d7b0
ub2 wrp_kcbh @12 0x000a
ub1 seq_kcbh @14 0x01
ub1 flg_kcbh @15 0x06 (KCBHFDLC, KCBHFCKV)
ub2 chkval_kcbh @16 0xe573
ub2 spare3_kcbh @18 0x0000
BBED> d offset 8188
File: /u01/oracle/oradata/tmp/xifenfei01.dbf (0)
Block: 12 Offsets: 8188 to 8191 Dba:0x00000000
------------------------------------------------------------------------
0106b0d7
<32 bytes per line>
BBED> m /x 11
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /u01/oracle/oradata/tmp/xifenfei01.dbf (0)
Block: 12 Offsets: 8188 to 8191 Dba:0x00000000
------------------------------------------------------------------------
1106b0d7
<32 bytes per line>
BBED> sum apply
Check value for File 0, Block 12:
current = 0xe563, required = 0xe563
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/tmp/xifenfei01.dbf
BLOCK = 12
Block 12 is corrupt
Corrupt block relative dba: 0x0140000c (file 0, block 12)
Fractured block found during verification
Data in bad block:
type: 6 format: 2 rdba: 0x0140000c
last change scn: 0x000a.0004d7b0 seq: 0x1 flg: 0x06
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0xd7b00611
check value in block header: 0xe563
computed block checksum: 0x0
DBVERIFY - Verification complete
Total Blocks Examined : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing (Index): 0
Total Blocks Empty : 0
Total Blocks Marked Corrupt : 1
Total Blocks Influx : 2
修改数据库记录
SQL> startup ORACLE instance started. Total System Global Area 318767104 bytes Fixed Size 1267236 bytes Variable Size 109054428 bytes Database Buffers 201326592 bytes Redo Buffers 7118848 bytes Database mounted. Database opened. SQL> conn chf/xifenfei Connected. SQL> update t_xifenfei set object_name='惜分飞'; 10 rows updated. SQL> update t_xifenfei_new set object_name='惜分飞'; 10 rows updated. SQL> commit; Commit complete. SQL> alter system switch logfile; System altered. SQL> alter system switch logfile; System altered. SQL> alter system switch logfile; System altered. SQL> conn / as sysdba Connected. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down.
利用备份数据文件恢复数据库
[oracle@xifenfei XFF]$ cp xifenfei01.dbf xifenfei01.dbf_bak
[oracle@xifenfei XFF]$ cp ../tmp/xifenfei01.dbf xifenfei01.dbf
[oracle@xifenfei XFF]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Fri Sep 28 19:13:59 2012
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 318767104 bytes
Fixed Size 1267236 bytes
Variable Size 109054428 bytes
Database Buffers 201326592 bytes
Redo Buffers 7118848 bytes
Database mounted.
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: '/u01/oracle/oradata/XFF/xifenfei01.dbf'
--提示数据需要恢复
SQL> recover datafile 5;
ORA-00279: change 42949990720 generated at 09/28/2012 19:04:10 needed for
thread 1
ORA-00289: suggestion : /u01/oracle/oradata/XFF/archivelog/1_24_792679299.dbf
ORA-00280: change 42949990720 for thread 1 is in sequence #24
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
Log applied.
Media recovery complete.
SQL> alter database open;
Database altered.
--利用被破坏的数据文件+归档日志恢复数据库正常
SQL> col object_name for a30
SQL> select object_id,object_name from t_xifenfei;
select object_id,object_name from t_xifenfei
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 12)
ORA-01110: data file 5: '/u01/oracle/oradata/XFF/xifenfei01.dbf'
--提示被破坏的数据块,查询不能完成
--证明坏块之外的数据块还是被正常应用日志
SQL> select object_id,object_name from t_xifenfei_new;
OBJECT_ID OBJECT_NAME
---------- ------------------------------
20 惜分飞
44 惜分飞
28 惜分飞
15 惜分飞
29 惜分飞
3 惜分飞
25 惜分飞
39 惜分飞
51 惜分飞
26 惜分飞
10 rows selected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
dbv检查坏块
[oracle@xifenfei XFF]$ dbv file=xifenfei01.dbf DBVERIFY: Release 10.2.0.4.0 - Production on Fri Sep 28 19:14:52 2012 Copyright (c) 1982, 2007, Oracle. All rights reserved. DBVERIFY - Verification starting : FILE = xifenfei01.dbf DBV-00200: Block, DBA 20971532, already marked corrupt --这里可以看出来，该数据块已经被标志为坏块 DBVERIFY - Verification complete Total Pages Examined : 1280 Total Pages Processed (Data) : 2 Total Pages Failing (Data) : 0 Total Pages Processed (Index): 0 Total Pages Failing (Index): 0 Total Pages Processed (Other): 14 Total Pages Processed (Seg) : 0 Total Pages Failing (Seg) : 0 Total Pages Empty : 1264 Total Pages Marked Corrupt : 1 Total Pages Influx : 0 Highest block SCN : 318700 (10.318700)
查看恢复过程alert日志
Fri Sep 28 19:14:06 2012 Database mounted in Exclusive Mode Completed: ALTER DATABASE MOUNT Fri Sep 28 19:14:06 2012 ALTER DATABASE OPEN ORA-1113 signalled during: ALTER DATABASE OPEN... Fri Sep 28 19:14:11 2012 ALTER DATABASE RECOVER datafile 5 Media Recovery Start parallel recovery started with 2 processes ORA-279 signalled during: ALTER DATABASE RECOVER datafile 5 ... Fri Sep 28 19:14:16 2012 ALTER DATABASE RECOVER CONTINUE DEFAULT Fri Sep 28 19:14:16 2012 --恢复数据库的时候,发现坏块 Media Recovery Log /u01/oracle/oradata/XFF/archivelog/1_24_792679299.dbf Fri Sep 28 19:14:16 2012 Hex dump of (file 5, block 12) in trace file /u01/oracle/admin/XFF/bdump/xff_p001_23011.trc Corrupt block relative dba: 0x0140000c (file 5, block 12) Fractured block found during media recovery Data in bad block: type: 6 format: 2 rdba: 0x0140000c last change scn: 0x000a.0004d7b0 seq: 0x1 flg: 0x06 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0xd7b00611 check value in block header: 0xe563 computed block checksum: 0x0 Reread of rdba: 0x0140000c (file 5, block 12) found same corrupted data --继续恢复 Fri Sep 28 19:14:16 2012 Recovery of Online Redo Log: Thread 1 Group 1 Seq 25 Reading mem 0 Mem# 0: /u01/oracle/oradata/XFF/redo01.log Fri Sep 28 19:14:16 2012 Recovery of Online Redo Log: Thread 1 Group 2 Seq 26 Reading mem 0 Mem# 0: /u01/oracle/oradata/XFF/redo02.log Fri Sep 28 19:14:16 2012 Recovery of Online Redo Log: Thread 1 Group 3 Seq 27 Reading mem 0 Mem# 0: /u01/oracle/oradata/XFF/redo03.log Fri Sep 28 19:14:16 2012 Media Recovery Complete (XFF) Completed: ALTER DATABASE RECOVER CONTINUE DEFAULT Fri Sep 28 19:14:31 2012 alter database open
bbed查看修改相关信息
BBED> set filename '/u01/oracle/oradata/XFF/xifenfei01.dbf'
FILENAME /u01/oracle/oradata/XFF/xifenfei01.dbf
BBED> set block 12
BLOCK# 12
BBED> map
File: /u01/oracle/oradata/XFF/xifenfei01.dbf (0)
Block: 12 Dba:0x00000000
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0
struct ktbbh, 96 bytes @20
struct kdbh, 14 bytes @124
struct kdbt[1], 4 bytes @138
sb2 kdbr[10] @142
ub1 freespace[7666] @162
ub1 rowdata[360] @7828
ub4 tailchk @8188
BBED> p kcbh
struct kcbh, 20 bytes @0
ub1 type_kcbh @0 0x06
ub1 frmt_kcbh @1 0xa2
ub1 spare1_kcbh @2 0x00
ub1 spare2_kcbh @3 0x00
ub4 rdba_kcbh @4 0x0140000c
ub4 bas_kcbh @8 0x00000000
ub2 wrp_kcbh @12 0x0000
ub1 seq_kcbh @14 0xff <--因为被标记为坏块,所以为ff
ub1 flg_kcbh @15 0x04 (KCBHFCKV)
ub2 chkval_kcbh @16 0xe77d
ub2 spare3_kcbh @18 0x0000
--查看数据块中记录
BBED> p *kdbr[5]
rowdata[69]
-----------
ub1 rowdata[69] @7897 0x2c
BBED> x /rnc
rowdata[69] @7897
-----------
flag@7897: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@7898: 0x02
cols@7899: 2
col 0[2] @7900: 3
col 1[16] @7903: WWW.XIFENFEI.COM <--确实没有被恢复,而是直接被跳过
BBED> set mode edit
MODE Edit
BBED> m /x 01 offset 14
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /u01/oracle/oradata/XFF/xifenfei01.dbf (0)
Block: 12 Offsets: 14 to 525 Dba:0x00000000
------------------------------------------------------------------------
01047de7 00000100 00000dcc 000098d7 …………
<32 bytes per line>
BBED> d offset 8188
File: /u01/oracle/oradata/XFF/xifenfei01.dbf (0)
Block: 12 Offsets: 8188 to 8191 Dba:0x00000000
------------------------------------------------------------------------
ff060000 <--这么说明:数据块被标志为坏块的时候，同时会修改tailchk值
<32 bytes per line>
BBED> m /x 01 offset 8188
File: /u01/oracle/oradata/XFF/xifenfei01.dbf (0)
Block: 12 Offsets: 8188 to 8191 Dba:0x00000000
------------------------------------------------------------------------
01060000
<32 bytes per line>
BBED> sum apply
Check value for File 0, Block 12:
current = 0xe77d, required = 0xe77d
--验证块已经标记为正常块
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/XFF/xifenfei01.dbf
BLOCK = 12
DBVERIFY - Verification complete
Total Blocks Examined : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing (Index): 0
Total Blocks Empty : 0
Total Blocks Marked Corrupt : 0
Total Blocks Influx : 0
启动数据库测试
SQL> startup
ORACLE instance started.
Total System Global Area 318767104 bytes
Fixed Size 1267236 bytes
Variable Size 109054428 bytes
Database Buffers 201326592 bytes
Redo Buffers 7118848 bytes
Database mounted.
Database opened.
SQL> conn chf/xifenfei
Connected.
SQL> col object_name for a30
SQL> select object_id,object_name from t_xifenfei;
OBJECT_ID OBJECT_NAME
---------- ------------------------------
20 WWW.XIFENFEI.COM
44 WWW.XIFENFEI.COM
28 WWW.XIFENFEI.COM
15 WWW.XIFENFEI.COM
29 WWW.XIFENFEI.COM
3 WWW.XIFENFEI.COM
25 WWW.XIFENFEI.COM
39 WWW.XIFENFEI.COM
51 WWW.XIFENFEI.COM
26 WWW.XIFENFEI.COM
10 rows selected.
--通过修改数据块的seq_kcbh和tailchk,让这个块恢复正常,但是记录依然丢失，
--因为应用日志恢复之时标记为坏块跳过该块的日志应用
通过实验证明:
1.如果只有数据块异常,应用日志恢复,不一定会出现ORA-600[3020],而是直接把该块标记为坏块,继续应用日志
2.标记坏块其实就是修改seq_kcbh为ff,同时也修改tailchk值
3.经验值:如果在数据库应用日志恢复的时候,如果出现ORA-600[3020]错误,可以使用allow 2 corruption来跳过坏块处理,其实也是修改seq_kcbh为ff,然后让数据库跳过该块的恢复.