[OCP学习笔记]043-07-处理数据库损坏--模拟实验（2）

4.使用工具进行修复

这里，我们尝试采用多种工具来对损坏的数据块进行修复。前文中我构建了拥有4个坏块的表空间test，数据文件为test01.dbf，大小1MB。为了简化操作，我们备份了他的一个副本test01.bak。以下操作可在每个实验前独立进行，以确保实验样本的一致性，且彼此之间不会产生冲突。

sys@STUDY> host copy test01.bak test01.dbf

sys@STUDY>alter tablespace test online;

Tablespace altered.

sys@STUDY>alter system flush buffer_cache;

System altered.

l 使用BlockRecover...corruption list进行修复

前文中，我们使用RMAN对修改前的正确的test表空间进行了备份，对出现坏块后的test表空间进行了validate。

在做BMR之前，首先确定需要恢复的表空间是否有备份存在。

RMAN> list backup of tablespace "TEST";

List of Backup Sets

===================

BS Key Type LV Size Device Type Elapsed Time Completion Time

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

35 Full 648.00K DISK 00:00:01 2010-06-05 21:15:56

BP Key: 32 Status: AVAILABLE Compressed: NO Tag: TAG20100605T211555

Piece Name: C:ORACLEPRODUCT10.2.0FLASH_RECOVERY_AREASTUDYBACKUPSET2010_06_05O1_MF_NNNDF_TAG20100605T211555_60NMJCWB_.BKP

List of Datafiles in backup set 35

File LV Type Ckp SCN Ckp Time Name

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

14 Full 1921865 2010-06-05 21:15:55 C:ORACLEPRODUCT10.2.0ORADATASTUDYTEST01.DBF

其次，确认需要恢复的数据块信息已经记录到V$BACKUP_CORRUPTION视图中。

sys@STUDY>select * from v$database_block_corruption;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO

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

14 28 1 0 CHECKSUM

14 20 1 0 FRACTURED

14 16 1 0 FRACTURED

执行BMR操作进行恢复。

RMAN> blockrecover corruption list;

Starting blockrecover at 2010-06-05 21:40:09

using channel ORA_DISK_1

channel ORA_DISK_1: restoring block(s) from datafile copy C:ORACLEPRODUCT10.2.0FLASH_RECOVERY_AREASTUDYDATAFILEO1_MF_TEST_60NML16T_.DBF

starting media recovery

media recovery complete, elapsed time: 00:00:03

Finished blockrecover at 2010-06-05 21:40:14

再次进行验证

RMAN> backup validate tablespace "TEST";

Starting backup at 2010-06-05 21:43:14

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00014 name=C:ORACLEPRODUCT10.2.0ORADATASTUDYTEST01.DBF

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02

Finished backup at 2010-06-05 21:43:16

再次查询V$DATABASE_BLOCK_CORRUPTION确定坏块已经被修复。

sys@STUDY>select * from v$database_block_corruption;

no rows selected

类似的，大家可以尝试使用镜像来进行坏块的恢复，两者过程大致相同。

l 使用BlockRecover...File#...Block#进行恢复

RMAN> blockrecover datafile 14 block 16, 20, 24, 28;

Starting blockrecover at 2010-06-05 21:54:13

using channel ORA_DISK_1

channel ORA_DISK_1: restoring block(s) from datafile copy C:ORACLEPRODUCT10.2.0FLASH_RECOVERY_AREASTUDYDATAFILEO1_MF_TEST_60NML16T_.DBF

starting media recovery

media recovery complete, elapsed time: 00:00:07

Finished blockrecover at 2010-06-05 21:54:21

dbv file=test01.dbf

DBVERIFY: Release 10.2.0.1.0 - Production on Sat Jun 5 21:55:14 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

DBVERIFY - Verification starting : FILE = test01.dbf

DBVERIFY - Verification complete

Total Pages Examined : 128

Total Pages Processed (Data) : 53

Total Pages Failing (Data) : 0

Total Pages Processed (Index): 8

Total Pages Failing (Index): 0

Total Pages Processed (Other): 17

Total Pages Processed (Seg) : 0

Total Pages Failing (Seg) : 0

Total Pages Empty : 50

Total Pages Marked Corrupt : 0

Total Pages Influx : 0

Highest block SCN : 1921792 (0.1921792)

修复完毕。

l 使用DBMS_REPAIR软件包进行恢复

首先确定在16、20、24、28数据块中存放记录的数量。

sys@STUDY>select count(rowid) from t1 where dbms_rowid.rowid_block_number(rowid) in (16, 20, 24, 28);

COUNT(ROWID)

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

130

建立repair_table和orphan_table，用于存放坏块和索引中对应的孤键记录。

BEGIN

DBMS_REPAIR.ADMIN_TABLES (

TABLE_NAME => 'REPAIR_TABLE',

TABLE_TYPE => dbms_repair.repair_table,

ACTION => dbms_repair.create_action,

TABLESPACE => 'TEST');

END;

/

BEGIN

DBMS_REPAIR.ADMIN_TABLES (

TABLE_NAME => 'ORPHAN_KEY_TABLE',

TABLE_TYPE => dbms_repair.orphan_table,

ACTION => dbms_repair.create_action,

TABLESPACE => 'TEST');

END;

/

使用CHECK_OBJECT过程检测坏块

SET SERVEROUTPUT ON

DECLARE

num_corrupt INT;

BEGIN

num_corrupt := 0;

DBMS_REPAIR.CHECK_OBJECT (

SCHEMA_NAME => 'SYS',

OBJECT_NAME => 'T1',

REPAIR_TABLE_NAME => 'REPAIR_TABLE',

CORRUPT_COUNT => num_corrupt);

DBMS_OUTPUT.PUT_LINE('number corrupt: ' || TO_CHAR (num_corrupt));

END;

/

查询repair_table，查看检测到的坏块的信息：

sys@STUDY>select BLOCK_ID, CORRUPT_TYPE, MARKED_CORRUPT, REPAIR_DESCRIPTION from repair_table;

BLOCK_ID CORRUPT_TYPE MARKED_COR REPAIR_DESCRIPTION

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

16 6148 TRUE mark block software corrupt

20 6148 TRUE mark block software corrupt

24 6148 TRUE mark block software corrupt

28 6148 TRUE mark block software corrupt

这里marked_corrupt被标记为true，应该是系统在执行CHECK_OBJECT过程中自动完成了FIX_CORRUPT_BLOCKS。如果被标记为flase，需要再运行FIX_CORRUPT_BLOCKS来完成坏块的标记工作。

sys@STUDY>DECLARE

2 num_fix INT;

3 BEGIN

4 num_fix := 0;

5 DBMS_REPAIR.FIX_CORRUPT_BLOCKS (

6 SCHEMA_NAME => 'SYS',

7 OBJECT_NAME=> 'T1',

8 OBJECT_TYPE => dbms_repair.table_object,

9 REPAIR_TABLE_NAME => 'REPAIR_TABLE',

10 FIX_COUNT=> num_fix);

11 DBMS_OUTPUT.PUT_LINE('num fix: ' || TO_CHAR(num_fix));

12 END;

13 /

num fix: 0

PL/SQL procedure successfully completed.

标记了0个坏块，说明CHECK_OBJECT完成了标记工作。

虽然我们对坏块进行了标记，但由于索引并未损坏，因此通过索引来访问数据表，仍然可能引发ORA-01578错误。

sys@STUDY>select * from t1 where table_name='REPCAT$_TEMPLATE_REFGROUPS';

select * from t1 where table_name='REPCAT$_TEMPLATE_REFGROUPS'

*

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 14, block # 28)

ORA-01110: data file 14: 'C:ORACLEPRODUCT10.2.0ORADATASTUDYTEST01.DBF'

"REPCAT$_TEMPLATE_REFGROUPS"对应的记录存放在第28块中，通过索引来访问时，发生错误。

使用DUMP_ORPHAN_KEYS过程来保存坏块中的索引键值，存放到ORPHAN_KEY_TABLE中。

sys@STUDY> SET SERVEROUTPUT ON

1 DECLARE

2 num_orphans INT;

3 BEGIN

4 num_orphans := 0;

5 DBMS_REPAIR.DUMP_ORPHAN_KEYS (

6 SCHEMA_NAME => 'SYS',

7 OBJECT_NAME => 'ID_T1',

8 OBJECT_TYPE => dbms_repair.index_object,

9 REPAIR_TABLE_NAME => 'REPAIR_TABLE',

10 ORPHAN_TABLE_NAME=> 'ORPHAN_KEY_TABLE',

11 KEY_COUNT => num_orphans);

12 DBMS_OUTPUT.PUT_LINE('orphan key count: ' || TO_CHAR(num_orphans));

13 END;

14 /

PL/SQL procedure successfully completed.

注意：如果表有多个索引，需要为每个索引执行DUMP_ORPHAN_KEYS操作。

sys@STUDY>select count(*) from orphan_key_table;

COUNT(*)

----------

130

和存放在16、20、24、28这4个坏块中的记录数量相当。

如果表空间采用的是数据字典管理的话，空块将被记录在freelists链表中。如果坏块发生在FREELIST列表中的中部，则FREELIST列表后面的块都无法访问。为此，我们需要执行REBUILD_FREELISTS过程来修改FREELISTS。

sys@STUDY>BEGIN

2 DBMS_REPAIR.REBUILD_FREELISTS (

3 SCHEMA_NAME => 'SYS',

4 OBJECT_NAME => 'T1',

5 OBJECT_TYPE => dbms_repair.table_object);

6 END;

7 /

BEGIN

*

ERROR at line 1:

ORA-10614: Operation not allowed on this segment

ORA-06512: at "SYS.DBMS_REPAIR", line 400

ORA-06512: at line 2

这里我们采用的是ASSM来进行表空间管理，所以无需执行REBUILD_FREELISTS操作。

执行SKIP_CORRUPT_BLOCKS过程，是后续DML操作跳过坏块

sys@STUDY>BEGIN

2 DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (

3 SCHEMA_NAME => 'SYS',

4 OBJECT_NAME => 'T1',

5 OBJECT_TYPE => dbms_repair.table_object,

6 FLAGS => dbms_repair.skip_flag);

7 END;

8 /

PL/SQL procedure successfully completed.

由于数据和索引仍然存在不一致的问题，因此必须重建索引。

sys@STUDY>alter index id_t1 rebuild;

Index altered.

sys@STUDY>select table_name from t1 where table_name='REPCAT$_TEMPLATE_REFGROUPS';

TABLE_NAME

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

REPCAT$_TEMPLATE_REFGROUPS

注意一点，重建索引一点要先DROP，然后再CREATE，使用REBUILD的方式，重建的数据源来自索引，仍然会导致问题的产生。

sys@STUDY>drop index id_t1;

Index dropped.

sys@STUDY>create index id_t1 on t1(table_name);

Index created.

sys@STUDY>select table_name from t1 where table_name='REPCAT$_TEMPLATE_REFGROUPS';

no rows selected

sys@STUDY>select count(*) from t1;

COUNT(*)

----------

1605

sys@STUDY>select count(*) from dba_tables;

COUNT(*)

----------

1738

我们发现在标识SKIP_CORRUPT_BLOCKS的过程中，一共少了133个记录。然而，ORPHAN_KEY_TABLE中仅记录了130条记录。中间有3个记录丢失了！

[@more@] 《第07章 处理数据库损坏》学习笔记.pdf

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/20162/viewspace-1034208/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/20162/viewspace-1034208/