使用DBMS_REPAIR包跳过坏块

最新推荐文章于 2023-02-12 22:00:52 发布

Floating warm sun

最新推荐文章于 2023-02-12 22:00:52 发布

阅读量449

点赞数

原文链接：https://www.modb.pro/db/193821

版权

1、创建测试表

SQL> conn help/admin
Connected.
SQL> create table h1 as select * from dba_objects;

Table created.

SQL> select table_name from user_tables;

TABLE_NAME
———————
H1

SQL> select count(*) from h1;

COUNT(*)
——————
86414

2、查看表的相关信息

select segment_name,header_file,header_block,blocks from dba_segments where segment_name ='H1';

SEGMENT_NAME HEADER_FILE HEADER_BLOCK BLOCKS
------------ ----------- ------------ ------
H1               12	         130       1280

select distinct dbms_rowid.rowid_block_number(rowid) from help.h1 order by 1;

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
                          131
                          132
                          133
                          134
                          135
                          136
                          137
                          138
                          139
                          140
                          141
                          142
                          143
                          145
                          146
                          147
                          148
                          149
                          150
                          151
                          152
                          153
                          154
                          155
                          156
                          157
                          158
                          159
                          161
                          162
                          163
                          164
                         ....
						  
1234 rows selected.

select * from dba_extents where segment_name='H1';

OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID   BYTES   BLOCKS RELATIVE_FNO
----- ------------ --------------- ------------ -------------- --------- ------- --------- ------- -------- ----------
help	  H1			           TABLE		 help			0	       12        128	65536	   8	    12
help	  H1			           TABLE		 help			1	       12        136	65536	   8	    12
help	  H1			           TABLE		 help			2	       12        144	65536	   8	    12
help	  H1			           TABLE		 help			3	       12        152	65536	   8	    12
help	  H1			           TABLE		 help			4	       12        160	65536	   8        12
help	  H1			           TABLE		 help			5	       12        168	65536	   8	    12
help	  H1			           TABLE		 help			6	       12        176	65536	   8	    12
help	  H1			           TABLE		 help			7	       12        184	65536	   8	    12
help	  H1			           TABLE		 help			8	       12        192	65536	   8	    12
help	  H1			           TABLE		 help			9	       12        200	65536	   8	    12
help	  H1			           TABLE		 help			10	       12        208    65536	   8	    12
help	  H1			           TABLE		 help			11	       12        216	65536	   8	    12
help	  H1			           TABLE		 help			12	       12        224	65536	   8	    12
help	  H1			           TABLE		 help			13	       12        232	65536	   8	    12
help	  H1			           TABLE		 help			14	       12        240	65536	   8	    12
help	  H1			           TABLE		 help			15	       12        248	65536	   8        12
help	  H1			           TABLE		 help			16	       12        256    1048576	  128	    12
help	  H1			           TABLE		 help			17	       12        384    1048576	  128	    12
help	  H1			           TABLE		 help			18	       12        512    1048576	  128	    12
help	  H1			           TABLE		 help			19	       12        640    1048576	  128	    12
help	  H1			           TABLE		 help			20	       12        768    1048576	  128	    12
help	  H1			           TABLE		 help			21	       12        896    1048576	  128	    12
help	  H1			           TABLE		 help			22	       12       1024    1048576	  128	    12
help	  H1			           TABLE		 help			23	       12       1152    1048576	  128	    12
help	  H1			           TABLE		 help			24	       12       1280    1048576	  128	    12

25 rows selected.

3、模拟坏块

dd if=/dev/zero of=/oradata/orcl/help01.dbf bs=8192 conv=notrunc seek=888 count=1
dd if=/dev/zero of=/oradata/orcl/help01.dbf bs=8192 conv=notrunc seek=1122 count=1

报错如下：
ERROR:
ORA-01578: ORACLE data block corrupted (file # 12, block # 888)
ORA-01110: data file 12: ‘/oradata/orcl/help01.dbf’

4、检查坏块

（1）dbv检查坏块
dbv file=/oradata/orcl/help01.dbf blocksize=8192
DBVERIFY - Verification starting : FILE = /oradata/orcl/help01.dbf
Page 888 is marked corrupt
Corrupt block relative dba: 0x03000378 (file 12, block 888)
Completely zero block found during dbv:

Page 1122 is marked corrupt
Corrupt block relative dba: 0x03000462 (file 12, block 1122)
Completely zero block found during dbv:

DBVERIFY - Verification complete

Total Pages Examined : 25600
Total Pages Processed (Data) : 1232
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 155
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 24211
Total Pages Marked Corrupt : 2
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 5206042 (0.5206042)

经检查共有两个坏块。

（2）rman检查坏块

RMAN> backup check logical validate datafile 12;
SQL> select * from V$DATABASE_BLOCK_CORRUPTION;

   FILE#     BLOCK#	  BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ------ ------------------ ---------
	12	      888	    1		  0             ALL ZERO
	12	     1122	    1		  0             ALL ZERO

5、跳过坏块

在没有备份的情况下，无法做到无损修复，需要损失坏块的数据。（存在备份 block recover就可以）

（1）创建表，用来记录修复的表
begin
dbms_repair.admin_tables (
table_name => ‘REPAIR_TABLE’,
table_type => dbms_repair.repair_table,
action => dbms_repair.create_action,
tablespace => ‘USERS’);
end;
/

创建ORPHAN_表（格式如此），用于记录那些指向坏块的索引（存在索引则执行）
begin
dbms_repair.admin_tables (
table_name => ‘ORPHAN_INDEX_TABLE’,
table_type => dbms_repair.orphan_table,
action => dbms_repair.create_action,
tablespace => ‘USERS’);
end;
/

（2）检查坏块
set serveroutput on
declare
num_corrupt int;
begin
num_corrupt := 0;
DBMS_REPAIR.CHECK_OBJECT (
schema_name =>‘help’,
object_name =>‘H1’,
repair_table_name =>‘REPAIR_TABLE’,
corrupt_count =>num_corrupt);
dbms_output.put_line(‘number corrupt:’ || to_char(num_corrupt));
end;
/

number corrupt:2

PL/SQL procedure successfully completed.

（3）查看受损的块信息

select object_name,block_id,corrupt_type,marked_corrupt,repair_description from REPAIR_TABLE;

OBJECT_NAME	  BLOCK_ID CORRUPT_TYPE MARKED_COR REPAIR_DESCRIPTION
----------- ---------- ------------ ---------- ------------------------------
H1		       888	       6148        TRUE	   mark block software corrupt
H1		      1122	       6148        TRUE	   mark block software corrupt

注意：这里 marked_corrupt 被标记为 TRUE，应该是系统在执行 CHECK_OBJECT 过程中自动标记，若为 FALSE，需要再运行 FIX_CORRUPT_BLOCKS 来完成坏块的标记工作。

declare
cc number;
begin
dbms_repair.fix_corrupt_blocks(schema_name => ‘help’,
object_name => ‘H1’,
fix_count => cc);
dbms_output.put_line('Number of blocks fixed: ’ || to_char(cc));
end;
/

（4）将指向坏块的索引填充到ORPHAN_INDEX_TABLE表中（存在索引则执行）
declare
cc number;
begin
dbms_repair.dump_orphan_keys
(
schema_name => ‘help’,
object_name => ‘I_H1’,
object_type => dbms_repair.index_object,
repair_table_name => ‘REPAIR_TABLE’,
orphan_table_name=> ‘ORPHAN_INDEX_TABLE’,
key_count => cc
);
dbms_output.put_line('Number of orphan keys: ’ || to_char(cc));
end;
/

Number of orphan keys: 137

PL/SQL procedure successfully completed.

表示丢失137条数据。

注意：此处一定要注意object_name是索引名，而不是表名，如果表有多个索引，需要为每个索引执行 DUMP_ORPHAN_KEYS 操作。

（5）跳过坏块
begin
dbms_repair.skip_corrupt_blocks (
schema_name => ‘help’,
object_name => ‘H1’,
object_type => dbms_repair.table_object,
flags => dbms_repair.skip_flag);
end;
/

（6）验证结果

alter index help.i_h1 rebuild;

SQL> select count(*) from help.h1;

  COUNT(*)
----------
     86277

和之前统计的86414条数据对比发现查到丢失137条数据是对的。