block corrupt demo

下面的实验是如何处理Oracle 块损坏的情况：

1.创建测试表空间
SQL> create tablespace corrupt_tbs datafile '/u01/app/oracle/oradata/PROD/corrupt01.dbf' size 100M;

Tablespace created.

2.在该表空间上建立测试表
SQL> create table corrupt tablespace corrupt_tbs as select * from dba_objects;

Table created.

3，RMAN 做个备份，要养成好的习惯，多修改时一定要备份，DBA备份为重中之重
[oracle@even ~]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Mon Jan 14 00:15:59 2013

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

connected to target database: PROD (DBID=215185106)

RMAN> backup tablespace corrupt_tbs;
4，模拟损坏数据文件(只能update,eg:把ABC该成BCD,否则会破坏ORACLE的存储结构,就恢复不了了)
[oracle@even ~]$vi -b /u01/app/oracle/oradata/PROD/corrupt01.dbf

5，全表扫描表发现报 01578
SQL> select count(*) from corrupt;
select count(*) from corrupt
                     *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 15)
ORA-01110: data file 6: '/u01/app/oracle/oradata/PROD/corrupt01.dbf'

6，检查数据块
[oracle@even ~]$ cd /u01/app/oracle/oradata/PROD
[oracle@even PROD]$ dbv file=corrupt01.dbf blocksize=8192

DBVERIFY: Release 10.2.0.1.0 - Production on Mon Jan 14 00:24:53 2013

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

DBVERIFY - Verification starting : FILE = corrupt01.dbf
Page 15 is marked corrupt
Corrupt block relative dba: 0x0180000f (file 6, block 15)
Bad check value found during dbv:
Data in bad block:
 type: 6 format: 2 rdba: 0x0180000f
 last change scn: 0x0000.0009545f seq: 0x1 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x545f0601
 check value in block header: 0xaca1
 computed block checksum: 0x21b

 

DBVERIFY - Verification complete

Total Pages Examined         : 12800
Total Pages Processed (Data) : 690
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 28
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 12081
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 0
Highest block SCN            : 611468 (0.611468)

7，export ORACLE_SID=PROD
rman target /
RMAN> BACKUP CHECK LOGICAL VALIDATE tablespace corrupt_tbs;

8,RMAN检查具体坏块,查看哪些具体坏块错误
SQL> select * from v$database_block_corruption;

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
         6         15          1                  0 CHECKSUM

9,通过Rman恢复块损坏:
RMAN> blockrecover datafile 6 block 15;

Starting blockrecover at 14-JAN-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=153 devtype=DISK

channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00006
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/PROD/backupset/2013_01_14/o1_mf_nnndf_TAG20130114T001623_8h5qgqvj_.bkp
channel ORA_DISK_1: restored block(s) from backup piece 1
piece handle=/u01/app/oracle/flash_recovery_area/PROD/backupset/2013_01_14/o1_mf_nnndf_TAG20130114T001623_8h5qgqvj_.bkp tag=TAG20130114T001623
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:02

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

Finished blockrecover at 14-JAN-13

10，看下是否还有01578错误
SQL> select count(*) from corrupt;

  COUNT(*)
----------
     50319

如果上面的还不行，则需要跳开坏块了
重复上面的实验 1,2,3,4,5,6,7 使用以下操作跳开坏块
alter session set events '10231 trace name context forever, level 10';
或者
begin
dbms_repair.skip_corrupt_blocks(
schema_name => 'HR',
object_name => 'CORRUPT', object_type => dbms_repair.table_object,
flags => dbms_repair.skip_flag);
end;
/

刷新内存
1. alter system flush buffer_cache; # 刷新buffer cache 组件
-- alter system flush shared_pool;
2. alter system checkpoint.