Oracle 数据库坏块处理

情形一，有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 数据库坏块处理

标签：corrupted   记录   segment   null   mit   end   primary   start   and