介绍
oracle的坏块修复主要有rman和DBMS_REPAIR包两种方式（不考虑bbed）,现在我们就DBMS_REPAIR脚本方式修复进行测试。
DBMS_REPAIR修复脚本
REM Create the repair table in a given tablespace:
BEGIN
DBMS_REPAIR.ADMIN_TABLES (
TABLE_NAME =>
'REPAIR_TABLE',
TABLE_TYPE => dbms_repair.repair_table,
ACTION => dbms_repair.create_action,
TABLESPACE =>
'&tablespace_name');
END;
/
REM Identify corrupted blocks
for schema.object (it also can be done at partition level with parameter PARTITION_NAME):
set serveroutput on
DECLARE num_corrupt INT;
BEGIN
num_corrupt :=
0;
DBMS_REPAIR.CHECK_OBJECT (
SCHEMA_NAME =>
'&schema_name',
OBJECT_NAME =>
'&object_name',
REPAIR_TABLE_NAME =>
'REPAIR_TABLE',
corrupt_count => num_corrupt);
DBMS_OUTPUT.PUT_LINE(
'number corrupt: ' || TO_CHAR (num_corrupt));
END;
/
REM Optionally display any corrupted block identified by check_object:
select BLOCK_ID, CORRUPT_TYPE, CORRUPT_DESCRIPTION
from REPAIR_TABLE;
REM Mark the identified blocks
as corrupted ( Soft Corrupt - reference Note
1496934.1 )
DECLARE num_fix INT;
BEGIN
num_fix :=
0;
DBMS_REPAIR.FIX_CORRUPT_BLOCKS (
SCHEMA_NAME =>
'&schema_name',
OBJECT_NAME=>
'&object_name',
OBJECT_TYPE => dbms_repair.table_object,
REPAIR_TABLE_NAME =>
'REPAIR_TABLE',
FIX_COUNT=> num_fix);
DBMS_OUTPUT.PUT_LINE(
'num fix: ' || to_char(num_fix));
END;
/
REM Allow future DML statements to skip the corrupted blocks:
BEGIN
DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (
SCHEMA_NAME =>
'&schema_name',
OBJECT_NAME =>
'&object_name',
OBJECT_TYPE => dbms_repair.table_object,
FLAGS => dbms_repair.SKIP_FLAG);
END;
/
测试步骤：
SQL> create tablespace tt datafile size
10M autoextend
on;
SQL> create user tt identified
by tt
default tablespace tt;
User created.
SQL> grant dba to tt;
Grant succeeded.
SQL> conn tt/tt
Connected.
SQL> create table testtest
as
select *
from dba_objects;
Table created.
SQL>
create index i_test
on
testtest(
object_id);
Index created.
SQL> col SEGMENT_NAME format a15
SQL>
select segment_name , header_file , header_block,blocks
from dba_segments
where segment_name =
'TESTTEST';
SEGMENT_NAME HEADER_FILE HEADER_BLOCK BLOCKS
--------------- ----------- ------------ ----------
TESTTEST
201
130
1536
[oracle@test ~]$ dd if=/dev/zero of=/u02/app/oracle/oradata/TEST/datafile/o1_mf_tt_m7kvq4fy_.dbf bs=8192 conv=notrunc seek=266 count=1u02/app/oracle/oradata/TEST/datafile/o1_mf_tt_m7kvq4fy_.dbf bs=8192 conv=notrunc seek=650 count=11+0 records in1+0 records out8192 bytes (8.2 kB) copied, 0.000326904 s, 25.1 MB/s[oracle@test ~]$ dd if=/dev/zero of=/u02/app/oracle/oradata/TEST/datafile/o1_mf_tt_m7kvq4fy_.dbf bs=8192 conv=notrunc seek=520 count=11+0 records in1+0 records out8192 bytes (8.2 kB) copied, 0.000262302 s, 31.2 MB/s[oracle@test ~]$ dd if=/dev/zero of=/u02/app/oracle/oradata/TEST/datafile/o1_mf_tt_m7kvq4fy_.dbf bs=8192 conv=notrunc seek=650 count=11+0 records in1+0 records out8192 bytes (8.2 kB) copied, 0.000314903 s, 26.0 MB/s
select *
from tt.testtest;
ERROR:
ORA-01578: ORACLE data block corrupted (file
# 201, block # 266)
ORA-01110: data file 201:
'/u02/app/oracle/oradata/TEST/datafile/o1_mf_tt_m7kvq4fy_.dbf'
SQL>
@repari.sh
Enter value
for tablespace_name: TT
old
6:
TABLESPACE =>
'&tablespace_name');
new
6:
TABLESPACE =>
'TT');
PL/SQL procedure successfully completed.
Enter value
for schema_name: TT
old
5:
SCHEMA_NAME =>
'&schema_name',
new
5:
SCHEMA_NAME =>
'TT',
Enter value
for object_name: TESTTEST
old
6:
OBJECT_NAME =>
'&object_name',
new
6:
OBJECT_NAME =>
'TESTTEST',
number corrupt:
3
PL/SQL procedure successfully completed.
BLOCK_ID CORRUPT_TYPE
---------- ------------
CORRUPT_DESCRIPTION
--------------------------------------------------------------------------------
266
6148
520
6148
650
6148
Enter value
for schema_name: TT
old
5:
SCHEMA_NAME =>
'&schema_name',
new
5:
SCHEMA_NAME =>
'TT',
Enter value
for object_name: TESTTEST
old
6:
OBJECT_NAME=>
'&object_name',
new
6:
OBJECT_NAME=>
'TESTTEST',
num fix:
0
PL/SQL procedure successfully completed.
Enter value
for schema_name: TT
old
3:
SCHEMA_NAME =>
'&schema_name',
new
3:
SCHEMA_NAME =>
'TT',
Enter value
for object_name: TESTTEST
old
4:
OBJECT_NAME =>
'&object_name',
new
4:
OBJECT_NAME =>
'TESTTEST',
PL/SQL procedure successfully completed.
SQL>
select
count(
*)
from tt.TESTTEST;
COUNT(*)
----------
73125
DBMS_REPAIR SCRIPT (Doc ID 556733.1)
————————————————————————————
微信公众号：天高弋猎
墨天轮：https:
//https://www.modb.pro/u/3738
ITPUB：https:
//blog.itpub.net/69924215/
