情形一，有RMAN备份
1.构造坏块
SQL> select segment_name , header_file , header_block,blocks
2 from dba_segments where segment_name =‘TESTC‘ and owner=‘SCOTT‘;
SEGMENT_NA HEADER_FILE HEADER_BLOCK BLOCKS
---------- ----------- ------------ ----------
TESTC 4 154 8
[oracle@prim scripts]$ dd of=/oradata/orcl/users01.dbf bs=8192 conv=notrunc seek=155 <<EOF
> Corrupt me!
> EOF
dd of=/oradata/orcl/users01.dbf bs=8192 conv=notrunc seek=157 <<EOF
Corrupt me!
0+1 records in
0+1 records out
12 bytes (12 B) copied, 0.0145481 s, 0.8 kB/s
[oracle@prim scripts]$
[oracle@prim scripts]$ dd of=/oradata/orcl/users01.dbf bs=8192 conv=notrunc seek=157 <<EOF
> Corrupt me!
> EOF
0+1 records in
0+1 records out
12 bytes (12 B) copied, 0.00138089 s, 8.7 kB/s
[oracle@prim scripts]$
[oracle@prim scripts]$
[oracle@prim scripts]$ dd of=/oradata/orcl/users01.dbf bs=8192 conv=notrunc seek=159 <<EOF
> Corrupt me!
> EOF
0+1 records in
0+1 records out
12 bytes (12 B) copied, 0.00227023 s, 5.3 kB/s
2.查询有坏块的表
SQL> select * from scott.TESTC;
select * from scott.TESTC
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 155)
ORA-01110: data file 4: ‘/oradata/orcl/users01.dbf‘
3.RMAN 利用 DRA 修复坏块
[oracle@prim scripts]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Thu Jul 2 11:21:04 2020
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1570665551)
RMAN> validate datafile 4;
Starting validate at 02-JUL-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=400 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=25 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=405 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00004 name=/oradata/orcl/users01.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:07
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4 FAILED 0 37031 46597 2127979
File Name: /oradata/orcl/users01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 5819
Index 0 912
Other 3 2798
validate found one or more corrupt blocks
See trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_27968.trc for details
Finished validate at 02-JUL-20
RMAN> list failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
622 HIGH OPEN 02-JUL-20 Datafile 4: ‘/oradata/orcl/users01.dbf‘ contains one or more corrupt blocks
RMAN> advise failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
622 HIGH OPEN 02-JUL-20 Datafile 4: ‘/oradata/orcl/users01.dbf‘ contains one or more corrupt blocks
analyzing automatic repair options; this may take some time
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
analyzing automatic repair options complete
Mandatory Manual Actions
========================
no manual actions available
Optional Manual Actions
=======================
1. Shut down, mount the database and try flush redo using ALTER SYSTEM FLUSH REDO TO ‘standby name‘ command. Then perform a Data Guard role change (failover). Available standbys: orcl_std.
Automated Repair Options
========================
Option Repair Description
------ ------------------
1 Recover multiple corrupt blocks in datafile 4
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/orcl/orcl/hm/reco_2285196485.hm
RMAN> repair failure;
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/orcl/orcl/hm/reco_2285196485.hm
contents of repair script:
# block media recovery for multiple blocks
recover datafile 4 block 155, 157, 159;
Do you really want to execute the above repair (enter YES or NO)? yes
executing repair script
Starting recover at 02-JUL-20
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
searching flashback logs for block images until SCN 2123598
finished flashback log search, restored 3 blocks
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 02-JUL-20
repair failure complete
4.验证坏块修复后的结果：
RMAN> validate datafile 4;
Starting validate at 02-JUL-20
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00004 name=/oradata/orcl/users01.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4 OK 0 37031 46597 2128902
File Name: /oradata/orcl/users01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 5822
Index 0 912
Other 0 2795
Finished validate at 02-JUL-20
情形二，无任何备份
使用 DBMS_REPAIR 修复(跳过)坏块
create table testc(
col1 number primary key,
col2 varchar2(20),
col3 number(11));
insert into testc values(1,‘AAAAAA‘,13012345678);
insert into testc values(2,‘BBBBBB‘,13112345678);
insert into testc values(3,‘CCCCCC‘,13212345678);
col SEGMENT_NAME for a10
select segment_name , header_file , header_block,blocks
from dba_segments where segment_name =‘TESTC‘ and owner=‘SCOTT‘;
SEGMENT_NA HEADER_FILE HEADER_BLOCK BLOCKS
---------- ----------- ------------ ----------
TESTC 4 154 8
dd of=/oradata/orcl/users01.dbf bs=8192 conv=notrunc seek=155 <<EOF
Corrupt me!
EOF
dd of=/oradata/orcl/users01.dbf bs=8192 conv=notrunc seek=157 <<EOF
Corrupt me!
EOF
dd of=/oradata/orcl/users01.dbf bs=8192 conv=notrunc seek=159 <<EOF
Corrupt me!
EOF
alter system flush buffer_cache;
select * from scott.TESTC;
SQL> select * from scott.TESTC;
select * from scott.TESTC
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 155)
ORA-01110: data file 4: ‘/oradata/orcl/users01.dbf‘
4、使用DBMS_REPAIR修复坏块
step a 创建修复表对象
drop table REPAIR_TABLE purge;
begin
-- Create repair table
dbms_repair.admin_tables (
table_name => ‘REPAIR_TABLE‘,
table_type => dbms_repair.repair_table,
action => dbms_repair.create_action,
tablespace => ‘USERS‘);
end;
/
select owner, object_name, object_type
from dba_objects
where object_name like ‘%REPAIR_TABLE‘;
OWNER OBJECT_NAME OBJECT_TYPE
-------- -------------------- --------------------
SYS DBA_REPAIR_TABLE VIEW
SYS REPAIR_TABLE TABLE
--使用DBMS_REPAIR.ADMIN_TABLES过程创建一个表对象，用于记录在表块损坏后那些孤立索引，也就是指向坏块的那些索引
-- Orphan Key Table
drop table ORPHAN_KEY_TABLE purge;
begin
-- Create orphan key table
dbms_repair.admin_tables (
table_type => dbms_repair.orphan_table,
action => dbms_repair.create_action,
tablespace => ‘USERS‘); -- default TS of SYS if not specified
end;
/
col OBJECT_NAME for a30
col OBJECT_TYPE for a25
select owner, object_name, object_type
from dba_objects
where object_name like ‘%ORPHAN_KEY_TABLE‘;
OWNER OBJECT_NAME OBJECT_TYPE
---------- -------------------- --------------------
SYS ORPHAN_KEY_TABLE TABLE
SYS DBA_ORPHAN_KEY_TABLE VIEW
Step b 校验受损的对象
--使用DBMS_REPAIR.CHECK_OBJECT来检测对象上受损的情形，并返回受损块数
set serveroutput on
declare
rpr_count int;
begin
rpr_count := 0;
dbms_repair.check_object (
schema_name => ‘SCOTT‘,
object_name => ‘TESTC‘,
repair_table_name => ‘REPAIR_TABLE‘,
corrupt_count => rpr_count);
dbms_output.put_line(‘repair count: ‘ || to_char(rpr_count));
end;
/
repair count: 3
--下面我们可以从repair_table查询到受损的块
--从下面的查询中可以看出列marked_corrupt全部为true，表明我们在CHECK_OBJECT已经标注了坏块
SQL> desc repair_table
Name Null? Type
----------------------------------------- -------- ----------------------------
OBJECT_ID NOT NULL NUMBER
TABLESPACE_ID NOT NULL NUMBER
RELATIVE_FILE_ID NOT NULL NUMBER
BLOCK_ID NOT NULL NUMBER
CORRUPT_TYPE NOT NULL NUMBER
SCHEMA_NAME NOT NULL VARCHAR2(30)
OBJECT_NAME NOT NULL VARCHAR2(30)
BASEOBJECT_NAME VARCHAR2(30)
PARTITION_NAME VARCHAR2(30)
CORRUPT_DESCRIPTION VARCHAR2(2000)
REPAIR_DESCRIPTION VARCHAR2(200)
MARKED_CORRUPT NOT NULL VARCHAR2(10)
CHECK_TIMESTAMP NOT NULL DATE
FIX_TIMESTAMP DATE
REFORMAT_TIMESTAMP DATE
set lines 120
col OBJECT_NAME for a12
col REPAIR_DESCRIPTION for a30
col CORRUPT_DESCRIPTION for a25
select object_name, block_id, corrupt_type, marked_corrupt,
corrupt_description, repair_description
from repair_table;
OBJECT_NAME BLOCK_ID CORRUPT_TYPE MARKED_COR CORRUPT_DESCRIPTION REPAIR_DESCRIPTION
------------ ---------- ------------ ---------- ------------------------- ------------------------------
TESTC 155 6148 TRUE mark block software corrupt
TESTC 157 6148 TRUE mark block software corrupt
TESTC 159 6148 TRUE mark block software corrupt
Step c 标记坏块
--过程FIX_CORRUPT_BLOCKS用于标记坏块，在这个演示中，我们在CHECK_OBJECT已经被标注了，如没有执行下面的过程
--由于上一步已经标注，所以下面的输出为0
SET SERVEROUTPUT ON
declare
fix_count int;
begin
fix_count := 0;
dbms_repair.fix_corrupt_blocks (
schema_name => ‘SCOTT‘,
object_name => ‘TESTC‘,
object_type => dbms_repair.table_object,
repair_table_name => ‘REPAIR_TABLE‘,
fix_count => fix_count);
dbms_output.put_line(‘fix count: ‘ || to_char(fix_count));
end;
/
num fix: 0
select object_name, block_id, marked_corrupt from repair_table;
OBJECT_NAME BLOCK_ID MARKED_COR
------------ ---------- ----------
TESTC 155 TRUE
TESTC 157 TRUE
TESTC 159 TRUE
select * from scott.TESTC;
select * from scott.TESTC
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 147)
ORA-01110: data file 4: ‘/oradata/orcl/users01.dbf‘
DBMS_REPAIR.DUMP_ORPHAN_KEYS
==============================
DUMP_ORPHAN_KEYS reports on index entries that point to rows in corrupt data blocks.
SQL> select index_name from dba_indexes where table_name in (select distinct object_name from repair_table);
INDEX_NAME
------------------------------
SYS_C0011945
Step d DUMP孤立的索引键值
--使用DUMP_ORPHAN_KEYS过程将那些指向坏块的索引键值填充到ORPHAN_KEY_TABLE
set serveroutput on
declare
key_count int;
begin
key_count := 0;
dbms_repair.dump_orphan_keys (
schema_name => ‘SCOTT‘,
object_name => ‘SYS_C0011945‘,
object_type => dbms_repair.index_object,
repair_table_name => ‘REPAIR_TABLE‘,
orphan_table_name => ‘ORPHAN_KEY_TABLE‘,
key_count => key_count);
dbms_output.put_line(‘orphan key count: ‘ || to_char(key_count));
end;
/
orphan key count: 3
desc orphan_key_table
Name Null? Type
------------------------- -------- ----------------------------
SCHEMA_NAME NOT NULL VARCHAR2(30)
INDEX_NAME NOT NULL VARCHAR2(30)
IPART_NAME VARCHAR2(30)
INDEX_ID NOT NULL NUMBER
TABLE_NAME NOT NULL VARCHAR2(30)
PART_NAME VARCHAR2(30)
TABLE_ID NOT NULL NUMBER
KEYROWID NOT NULL ROWID
KEY NOT NULL ROWID
DUMP_TIMESTAMP NOT NULL DATE
select index_name, count(*) from orphan_key_table group by index_name;
INDEX_NAME COUNT(*)
------------------------------ ----------
SYS_C0011945 3
Step e 跳过坏块
--使用SKIP_CORRUPT_BLOCKS来告知Oracle哪些坏块需要被跳过
begin
dbms_repair.skip_corrupt_blocks (
schema_name => ‘SCOTT‘,
object_name => ‘TESTC‘,
object_type => dbms_repair.table_object,
flags => dbms_repair.skip_flag);
end;
/
select table_name, skip_corrupt from dba_tables where table_name = ‘TESTC‘;
TABLE_NAME SKIP_COR
------------------------------ --------
TESTC ENABLED
SQL> select * from scott.TESTC;
no rows selected
SQL> insert into scott.testc values (1,‘aaaaaa‘,13111111111);
insert into scott.testc values (1,‘aaaaaa‘,13111111111)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_C0011945) violated
SQL> select * from scott.testc where col1=1;
no rows selected
REBUILD_FREELISTS rebuilds freelists for the specified object.
begin
dbms_repair.rebuild_freelists (
schema_name => ‘SCOTT‘,
object_name => ‘TESTC‘,
object_type => dbms_repair.table_object);
end;
/
---此操作报错
begin
*
ERROR at line 1:
ORA-10614: Operation not allowed on this segment
ORA-06512: at "SYS.DBMS_REPAIR", line 401
ORA-06512: at line 2
--由于索引键上存在孤立索引，因此我们重建索引：
alter index scott.SYS_C0011945 rebuild online;
再次插入数据，没有了主键冲突
SQL> insert into scott.testc values (1,‘aaaaaa‘,13111111111);
1 row created.
SQL> commit ;
Commit complete.
SQL> select * from scott.testc ;
COL1 COL2 COL3
---------- -------------------- ----------
1 aaaaaa 1.3111E+10
Oracle 数据库坏块处理