oracle强制拉库跳过recovery,学习笔记:Oracle坏块数据库recover恢复时遇到坏块的解决思路案例...

最新推荐文章于 2023-09-15 09:52:18 发布

勿叹

最新推荐文章于 2023-09-15 09:52:18 发布

阅读量547

点赞数

文章标签： oracle强制拉库跳过recovery

天萃荷净

recover遇到坏块处理本质探讨，记录一次在Oracle数据库recover恢复过程中，遇到数据库坏块无法恢复的解决思路案例

如果在还原出来的数据文件中有坏块,而归档日志和联机日志是正常的,那么在应用日志恢复过程中,会出现什么情况,这里通过一个简单的测试给予其中一种情况的说明

创建测试表

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_oracleplus(object_id,object_name) tablespace oracleplus

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

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/oracleplus01.dbf

SQL> update t_oracleplus set object_name='WWW.oracleplus.COM';

10 rows updated.

SQL> commit;

Commit complete.

SQL> create table t_oracleplus_new(object_id,object_name) tablespace oracleplus

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@oracleplus XFF]$ cp oracleplus01.dbf ../tmp/

[oracle@oracleplus XFF]$ ll ../tmp/oracleplus01.dbf

-rw-r----- 1 oracle oinstall 10493952 Sep 28 19:05 ../tmp/oracleplus01.dbf

[oracle@oracleplus XFF]$ date

Fri Sep 28 19:05:42 CST 2012

bbed破坏备份文件

[oracle@oracleplus XFF]$ bbed password=blockedit

BBED: Release 2.0.0.0.0 - Limited Production on Fri Sep 28 19:05:59 2012

************* !!! For Oracle Internal Use only !!! ***************

BBED> set filename '/u01/oracle/oradata/tmp/oracleplus01.dbf'

FILENAME /u01/oracle/oradata/tmp/oracleplus01.dbf

BBED> set block 12

BLOCK# 12

BBED> set mode edit

MODE Edit

BBED> map

File: /u01/oracle/oradata/tmp/oracleplus01.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/oracleplus01.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/oracleplus01.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/oracleplus01.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/oracleplus

Connected.

SQL> update t_oracleplus set object_name='惜分飞';

10 rows updated.

SQL> update t_oracleplus_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@oracleplus XFF]$ cp oracleplus01.dbf oracleplus01.dbf_bak

[oracle@oracleplus XFF]$ cp ../tmp/oracleplus01.dbf oracleplus01.dbf

[oracle@oracleplus XFF]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Sep 28 19:13:59 2012

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/oracleplus01.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: {=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_oracleplus;

select object_id,object_name from t_oracleplus

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 5, block # 12)

ORA-01110: data file 5: '/u01/oracle/oradata/XFF/oracleplus01.dbf'

--提示被破坏的数据块,查询不能完成

--证明坏块之外的数据块还是被正常应用日志

SQL> select object_id,object_name from t_oracleplus_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@oracleplus XFF]$ dbv file=oracleplus01.dbf

DBVERIFY: Release 10.2.0.4.0 - Production on Fri Sep 28 19:14:52 2012

DBVERIFY - Verification starting : FILE = oracleplus01.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/oracleplus01.dbf'

FILENAME /u01/oracle/oradata/XFF/oracleplus01.dbf

BBED> set block 12

BLOCK# 12

BBED> map

File: /u01/oracle/oradata/XFF/oracleplus01.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

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.oracleplus.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/oracleplus01.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/oracleplus01.dbf (0)

Block: 12 Offsets: 8188 to 8191 Dba:0x00000000

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

ff060000

<32 bytes per line>

BBED> m /x 01 offset 8188

File: /u01/oracle/oradata/XFF/oracleplus01.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/oracleplus01.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/oracleplus

Connected.

SQL> col object_name for a30

SQL> select object_id,object_name from t_oracleplus;

OBJECT_ID OBJECT_NAME

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

20 WWW.oracleplus.COM

44 WWW.oracleplus.COM

28 WWW.oracleplus.COM

15 WWW.oracleplus.COM

29 WWW.oracleplus.COM

3 WWW.oracleplus.COM

25 WWW.oracleplus.COM

39 WWW.oracleplus.COM

51 WWW.oracleplus.COM

26 WWW.oracleplus.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,然后让数据库跳过该块的恢复.

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之学习笔记:Oracle坏块数据库recover恢复时遇到坏块的解决思路案例

勿叹

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle强制拉库跳过recovery,学习笔记:Oracle坏块数据库recover恢复时遇到坏块的解决思路案例...

天萃荷净recover遇到坏块处理本质探讨，记录一次在Oracle数据库recover恢复过程中，遇到数据库坏块无法恢复的解决思路案例如果在还原出来的数据文件中有坏块,而归档日志和联机日志是正常的,那么在应用日志恢复过程中,会出现什么情况,这里通过一个简单的测试给予其中一种情况的说明创建测试表SQL> select * from v$version;BANNER---------------...
复制链接

扫一扫

oracle强制拉库跳过recovery,学习笔记:Oracle坏块 数据库recover恢复时遇到坏块的解决思路案例...

“相关推荐”对你有帮助么？

oracle强制拉库跳过recovery,学习笔记:Oracle坏块数据库recover恢复时遇到坏块的解决思路案例...