RMAN BLOCKRECOVER坏块修改

RMAN blockrecover

数据库在拥有RMAN有效备份前提下,假如数据库有坏块出现, 我们可以不用使受损的数据文件offline后,还原数据文件,而影响DB, 可以使用RMAN blockrecover实现联机恢复.
创建实验环境.
1 创建对象

   1.1 创建数据文件
SYS> create tablespace ts_corruptblock datafile '/data/test11g/ts_corruptblock01.dbf' size 100M autoextend on maxsize 1000m;
Tablespace created.

  1.2 创建表
JIEYU119> create table tb_bc tablespace ts_corruptblock
  2  as select rownum id,
  3  owner,
  4  object_name
  5  from dba_objects
  6  where rownum <= 10000;
Table created.
JIEYU119> 
JIEYU119> select count(*) from tb_bc;

  COUNT(*)
----------
     10000

2 对DB做Rman备份.

rman target /

RMAN> run{
2> allocate channel jieyu01 type disk;
3> backup tag 'dbfull' format '/data/backup/rman/dbfull_%U.bak' database include current controlfile; 
4> sql 'alter system archive log current';
5> backup filesperset 3 format '/data/backup/rman/arch_%U.bak' archivelog all delete input;
6> release channel jieyu01;
7> }

released channel: ORA_DISK_1
released channel: ORA_DISK_2
released channel: ORA_DISK_3
allocated channel: jieyu01
channel jieyu01: SID=29 device type=DISK
Starting backup at 13-JAN-14
channel jieyu01: starting full datafile backup set
channel jieyu01: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/product/oradata/test11g/system01.dbf
input datafile file number=00005 name=/data/test11g/base_data01.dbf
input datafile file number=00007 name=/data/test11g/ts_corruptblock01.dbf
input datafile file number=00002 name=/u01/product/oradata/test11g/sysaux01.dbf
channel jieyu01: starting piece 1 at 13-JAN-14
channel jieyu01: finished piece 1 at 13-JAN-14
piece handle=/data/backup/rman/dbfull_3iotvnt3_1_1.bak tag=DBFULL comment=NONE
channel jieyu01: backup set complete, elapsed time: 00:01:45
channel jieyu01: starting full datafile backup set
channel jieyu01: specifying datafile(s) in backup set
input datafile file number=00003 name=/u01/product/oradata/test11g/undotbs01.dbf
channel jieyu01: starting piece 1 at 13-JAN-14
channel jieyu01: finished piece 1 at 13-JAN-14
piece handle=/data/backup/rman/dbfull_3jotvo0c_1_1.bak tag=DBFULL comment=NONE
channel jieyu01: backup set complete, elapsed time: 00:00:25
channel jieyu01: starting full datafile backup set
channel jieyu01: specifying datafile(s) in backup set
including current control file in backup set
channel jieyu01: starting piece 1 at 13-JAN-14
channel jieyu01: finished piece 1 at 13-JAN-14
piece handle=/data/backup/rman/dbfull_3kotvo15_1_1.bak tag=DBFULL comment=NONE
channel jieyu01: backup set complete, elapsed time: 00:00:01
channel jieyu01: starting full datafile backup set
channel jieyu01: specifying datafile(s) in backup set
input datafile file number=00004 name=/u01/product/oradata/test11g/users01.dbf
channel jieyu01: starting piece 1 at 13-JAN-14
channel jieyu01: finished piece 1 at 13-JAN-14
piece handle=/data/backup/rman/dbfull_3lotvo19_1_1.bak tag=DBFULL comment=NONE
channel jieyu01: backup set complete, elapsed time: 00:00:01
Finished backup at 13-JAN-14
Starting Control File and SPFILE Autobackup at 13-JAN-14
piece handle=/data/backup/rman/c-978709332-20140113-04.ctl comment=NONE
Finished Control File and SPFILE Autobackup at 13-JAN-14

sql statement: alter system archive log current
Starting backup at 13-JAN-14
current log archived
channel jieyu01: starting archived log backup set
channel jieyu01: specifying archived log(s) in backup set
input archived log thread=1 sequence=186 RECID=117 STAMP=836755505
input archived log thread=1 sequence=187 RECID=118 STAMP=836755505
channel jieyu01: starting piece 1 at 13-JAN-14
channel jieyu01: finished piece 1 at 13-JAN-14
piece handle=/data/backup/rman/arch_3notvo1h_1_1.bak tag=TAG20140113T160505 comment=NONE
channel jieyu01: backup set complete, elapsed time: 00:00:01
channel jieyu01: deleting archived log(s)
archived log file name=/data/test11g/arch/1_186_826803860.arc RECID=117 STAMP=836755505
archived log file name=/data/test11g/arch/1_187_826803860.arc RECID=118 STAMP=836755505
Finished backup at 13-JAN-14
Starting Control File and SPFILE Autobackup at 13-JAN-14
piece handle=/data/backup/rman/c-978709332-20140113-05.ctl comment=NONE
Finished Control File and SPFILE Autobackup at 13-JAN-14
released channel: jieyu01
RMAN>

3  坏块检测
     如下引自oracle联机文档

Oracle Database supports different techniques for detecting, repairing, and monitoring block corruption. The technique depends on whether the corruption is interblock corruption or intrablock corruption. In intrablock corruption, the corruption occurs within the block itself. This corruption can be either physical or logical. In an interblock corruption, the corruption occurs between blocks and can only be logical.

For example, the V$DATABASE_BLOCK_CORRUPTION view records intrablock corruptions, while the Automatic Diagnostic Repository (ADR) tracks all types of corruptions. Table 1 summarizes how the database treats different types of block corruption.

Table 1 Detection, Repair, and Monitoring of Block Corruption

Response	Intrablock Corruption	Interblock Corruption
Detection	All database utilities detect intrablock corruption, including RMAN (for example, theBACKUP command) and the DBVERIFY utility. If a database process can encounter theORA-1578 error, then it can detect the corruption and monitor it.	Only DBVERIFY and the ANALYZE statement detect interblock corruption.
Tracking	The V$DATABASE_BLOCK_CORRUPTION view displays blocks marked corrupt by Oracle Database components such as RMAN commands, ANALYZE, dbv, SQL queries, and so on. Any process that encounters an intrablock corruption records the block corruption in this view and in ADR.	The database monitors this type of block corruption in ADR.
Repair	Repair techniques include block media recovery, restoring data files, recovering with incremental backups, and block newing. Block media recovery can repair physical corruptions, but not logical corruptions. Any RMAN command that fixes or detects that a block is repaired updatesV$DATABASE_BLOCK_CORRUPTION. For example, RMAN updates the repository at end of successful block media recovery. If a BACKUP, RESTORE, or VALIDATE command detects that a block is no longer corrupted, then it removes the repaired block from the view.	You must fix interblock corruption with manual techniques such as dropping an object, rebuilding an index, and so on.

4  模拟数据文件有坏块

  4.1  DB 一个数据坏块处理
   4.1.1  jietestdb$dd of=/data/test11g/ts_corruptblock01.dbf  bs=1000  conv=notrunc seek=100 <      > corruption block!
     > EOF
      0+1 records in
      0+1 records out
     18 bytes (18 B) copied, 0.025548 seconds, 0.7 kB/s

如上步骤说明,检测坏块可以用rman命令(validate)或DBVERIFY(dbv)工具,进行侦测. 侦测后,可以在v$database_block_corruption中查询.在用repair techniques block media recovery(blockrecover)恢复数据文件.
此时,我们只把datafile ts_corruptblock01.dbf创建了坏块,未进行dbv或validate侦测,故视图 v$database_block_corruption中没有数据.

SYS> select * from v$database_block_corruption;
no rows selected

4.1.2 查询数据文件ts_corruptblock01.dbf的file_id.
SYS> set long 100000
SYS> set line 150
SYS> col file_name for a45
SYS> select file_name,file_id from dba_data_files where tablespace_name='TS_CORRUPTBLOCK';

FILE_NAME                                              FILE_ID
---------------------------------------------        ----------
/data/test11g/ts_corruptblock01.dbf                    7

4.1.3 DB坏块侦测.
  如上文说明,可以用dev工具或rman validate命令.
我们这里先用rman命令, validate可以对database或数据文件进行侦测, 此时假如我们不知道DB有没有坏块,我们可以对整个DB进行侦测,
如果后续DB点检时,在DB ALERT预警日志中,发现具体哪个数据文件有坏块,可以用validate datafile进行具体数据文件侦测.

RMAN> validate database;

Starting validate at 13-JAN-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=48 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=33 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=45 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00001 name=/u01/product/oradata/test11g/system01.dbf
input datafile file number=00005 name=/data/test11g/base_data01.dbf
channel ORA_DISK_2: starting validation of datafile
channel ORA_DISK_2: specifying datafile(s) for validation
input datafile file number=00002 name=/u01/product/oradata/test11g/sysaux01.dbf
input datafile file number=00007 name=/data/test11g/ts_corruptblock01.dbf
channel ORA_DISK_3: starting validation of datafile
channel ORA_DISK_3: specifying datafile(s) for validation
input datafile file number=00003 name=/u01/product/oradata/test11g/undotbs01.dbf
channel ORA_DISK_3: validation complete, elapsed time: 00:01:26
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
3    OK     0              191745       262144          2127227   
  File Name: /u01/product/oradata/test11g/undotbs01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0               
  Index      0              0               
  Other      0              70399           

channel ORA_DISK_3: starting validation of datafile
channel ORA_DISK_3: specifying datafile(s) for validation
input datafile file number=00004 name=/u01/product/oradata/test11g/users01.dbf
channel ORA_DISK_3: validation complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4    OK     0              513          640             13158     
  File Name: /u01/product/oradata/test11g/users01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0               
  Index      0              0               
  Other      0              127             

channel ORA_DISK_3: starting validation of datafile
channel ORA_DISK_3: specifying datafile(s) for validation
including current SPFILE in backup set
channel ORA_DISK_3: validation complete, elapsed time: 00:00:00
List of Control File and SPFILE
===============================
File Type    Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
SPFILE       OK     0              2               
channel ORA_DISK_3: starting validation of datafile
channel ORA_DISK_3: specifying datafile(s) for validation
including current control file for validation
channel ORA_DISK_3: validation complete, elapsed time: 00:00:03
List of Control File and SPFILE
===============================
File Type    Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
Control File OK     0              1066            
channel ORA_DISK_2: validation complete, elapsed time: 00:02:24
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2    OK     0              201222       262144          2127225   
  File Name: /u01/product/oradata/test11g/sysaux01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              18023           
  Index      0              13732           
  Other      0              29167           

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
7    FAILED 0              12622        12800           2123568   
  File Name: /data/test11g/ts_corruptblock01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              45              
  Index      0              0               
  Other      1              133             

validate found one or more corrupt blocks
See trace file /u01/product/diag/rdbms/test11g/test11g/trace/test11g_ora_7882.trc for details
channel ORA_DISK_1: validation complete, elapsed time: 00:03:15
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1    OK     0              185534       262144          2127227   
  File Name: /u01/product/oradata/test11g/system01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              60494           
  Index      0              12871           
  Other      0              3245            


File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5    OK     0              127705       128000          798994    
  File Name: /data/test11g/base_data01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              80              
  Index      0              0               
  Other      0              215          
  
Finished validate at 13-JAN-14

由上可以看出,validate 发现坏块. 查看 /u01/product/diag/rdbms/test11g/test11g/trace/test11g_ora_7882.trc  追踪文件.

4.1.4 查询追踪文件 
jietestdb$ vim test11g_ora_7882.trc
Trace file /u01/product/diag/rdbms/test11g/test11g/trace/test11g_ora_7882.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
ORACLE_HOME = /u01/product/oracle
System name:    Linux
Node name:      jietestdb
Release:        2.6.18-194.el5
Version:        #1 SMP Tue Mar 16 21:52:43 EDT 2010
Machine:        i686
VM name:        VMWare Version: 6
Instance name: test11g
Redo thread mounted by this instance: 1
Oracle process number: 36
Unix process pid: 7882, image: oracle@jietestdb (TNS V1-V3)

*** 2014-01-13 16:53:37.966
*** SESSION ID:(33.137) 2014-01-13 16:53:37.966
*** CLIENT ID:() 2014-01-13 16:53:37.966
*** SERVICE NAME:(SYS$USERS) 2014-01-13 16:53:37.966
*** MODULE NAME:(backup full datafile) 2014-01-13 16:53:37.966
*** ACTION NAME:(0000016 STARTED19) 2014-01-13 16:53:37.966

Hex dump of (file 7, block 12)Dump of memory from 0x0202F000 to 0x02031000

*** 2014-01-13 16:53:38.425
202F000 0000A21E 01C0000C 0020453F 04010000  [........?E .....]
202F010 00008130 00000007 0045C080 00000000  [0.........E.....]
202F020 00000000 0000F800 00000000 00000000  [................]
202F030 00000000 00000000 00000000 00000000  [................]
        Repeat 102 times
202F6A0 72726F63 69747075 62206E6F 6B636F6C  [corruption block]
202F6B0 00000A21 00000000 00000000 00000000  [!...............]
202F6C0 00000000 00000000 00000000 00000000  [................]
        Repeat 402 times
2030FF0 00000000 00000000 00000000 453F1E01  [..............?E]
Corrupt block relative dba: 0x01c0000c (file 7, block 12)
Bad check value found during validation
Data in bad block:
 type: 30 format: 2 rdba: 0x01c0000c
 last change scn: 0x0000.0020453f seq: 0x1 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x453f1e01
 check value in block header: 0x8130
 computed block checksum: 0x671
Reread of blocknum=12, file=/data/test11g/ts_corruptblock01.dbf. found same corrupt data
Reread of blocknum=12, file=/data/test11g/ts_corruptblock01.dbf. found same corrupt data
Reread of blocknum=12, file=/data/test11g/ts_corruptblock01.dbf. found same corrupt data
Reread of blocknum=12, file=/data/test11g/ts_corruptblock01.dbf. found same corrupt data
Reread of blocknum=12, file=/data/test11g/ts_corruptblock01.dbf. found same corrupt data

由上可以查询出 数据文件为7(ts_corruptblock01.dbf),block为12有坏块.

4.1.5 此时查询alert预警文件也可以查询出坏块信息.

Mon Jan 13 16:53:38 2014
Hex dump of (file 7, block 12) in trace file /u01/product/diag/rdbms/test11g/test11g/trace/test11g_ora_7882.trc
Corrupt block relative dba: 0x01c0000c (file 7, block 12)
Bad check value found during validation
Data in bad block:
 type: 30 format: 2 rdba: 0x01c0000c
 last change scn: 0x0000.0020453f seq: 0x1 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x453f1e01
 check value in block header: 0x8130
 computed block checksum: 0x671
Reread of blocknum=12, file=/data/test11g/ts_corruptblock01.dbf. found same corrupt data
Reread of blocknum=12, file=/data/test11g/ts_corruptblock01.dbf. found same corrupt data
Reread of blocknum=12, file=/data/test11g/ts_corruptblock01.dbf. found same corrupt data
Reread of blocknum=12, file=/data/test11g/ts_corruptblock01.dbf. found same corrupt data
Reread of blocknum=12, file=/data/test11g/ts_corruptblock01.dbf. found same corrupt data
Mon Jan 13 16:55:58 2014
Checker run found 1 new persistent data failures

4.1.6 查询视图 v$database_block_corruption可以查询出坏块信息.

SYS> select * from v$database_block_corruption;

     FILE#     BLOCK#     BLOCKS    CORRUPTION_CHANGE#    CORRUPTION_TYPE
----------     ----------    ----------       ------------------            ---------
 7          12             1                   0                              CHECKSUM

SYS> 


4.1.7 block media recovery进行数据文件恢复.

RMAN> blockrecover datafile 7 block 12;
Starting recover at 13-JAN-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=51 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=29 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=52 device type=DISK
channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00007
channel ORA_DISK_1: reading from backup piece /data/backup/rman/dbfull_3iotvnt3_1_1.bak
channel ORA_DISK_1: piece handle=/data/backup/rman/dbfull_3iotvnt3_1_1.bak tag=DBFULL
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01
starting media recovery
media recovery complete, elapsed time: 00:00:03
Finished recover at 13-JAN-14

4.1.8  用validate查询一下,数据文件ts_corruptblock01.dbf坏块问题是否解决.
RMAN> validate datafile 7;
Starting validate at 13-JAN-14
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00007 name=/data/test11g/ts_corruptblock01.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:04
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
7    OK     0              12622        12800           2123568   
  File Name: /data/test11g/ts_corruptblock01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              45              
  Index      0              0               
  Other      0              133             
Finished validate at 13-JAN-14

由上可以看出,坏块信息已经解决.查询视图v$database_block_corruption查询坏块信息,已经不存在.

SYS> select * from v$database_block_corruption;
no rows selected

如上为DB 一个坏块的解决方法. 下文为DB多个数据坏块的解决方法.

4.2 DB 多数据坏块解决方法.

4.2.1 创建表
 
JIEYU119> create table tb_testblock tablespace ts_corruptblock 
  2  as select * from dba_objects;

Table created.
JIEYU119> 
JIEYU119> select count(*) from tb_testblock;

  COUNT(*)
----------
     74874

JIEYU119> 
JIEYU119> insert into tb_testblock 
  2  select * from tb_testblock;

74874 rows created.

JIEYU119> /
149748 rows created.
JIEYU119> 
JIEYU119> select count(*) from tb_testblock;

  COUNT(*)
----------
    299496

4.2.2 创建数据文件ts_corruptblock01.dbf 多处有坏块.

jietestdb$dd of=/data/test11g/ts_corruptblock01.dbf bs=8192 conv=notrunc seek=200 < > corrupted block!
> EOF
0+1 records in
0+1 records out
17 bytes (17 B) copied, 0.000142947 seconds, 119 kB/s
jietestdb$
jietestdb$dd of=/data/test11g/ts_corruptblock01.dbf bs=8192 conv=notrunc seek=400 < > corrupted block!
> EOF
0+1 records in
0+1 records out
17 bytes (17 B) copied, 0.00014589 seconds, 117 kB/s
jietestdb$
jietestdb$dd of=/data/test11g/ts_corruptblock01.dbf bs=8192 conv=notrunc seek=600 < > corrupted block!
> EOF
0+1 records in
0+1 records out
17 bytes (17 B) copied, 0.000215209 seconds, 79.0 kB/s

4.2.3  dev工具检测数据文件有无坏块
dev工具可以对数据进行坏块检测. 假如其它数据文件侦测完毕,侦测ts_corruptblock01.dbf时,发现坏块.

jietestdb$ dbv file=/data/test11g/ts_corruptblock01.dbf blocksize=8192;

DBVERIFY: Release 11.2.0.3.0 - Production on Mon Jan 13 17:35:58 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
DBVERIFY - Verification starting : FILE = /data/test11g/ts_corruptblock01.dbf
Page 200 is marked corrupt
Corrupt block relative dba: 0x01c000c8 (file 7, block 200)
Bad header found during dbv: 
Data in bad block:
 type: 99 format: 7 rdba: 0x65747075
 last change scn: 0x636f.6c622064 seq: 0x6b flg: 0x21
 spare1: 0x72 spare2: 0x72 spare3: 0x0
 consistency value in tail: 0xaf242002
 check value in block header: 0xcc0a
 block checksum disabled

Page 400 is marked corrupt
Corrupt block relative dba: 0x01c00190 (file 7, block 400)
Bad header found during dbv: 
Data in bad block:
 type: 99 format: 7 rdba: 0x65747075
 last change scn: 0x636f.6c622064 seq: 0x6b flg: 0x21
 spare1: 0x72 spare2: 0x72 spare3: 0x0
 consistency value in tail: 0xaf160602
 check value in block header: 0xd50a
 block checksum disabled

Page 600 is marked corrupt
Corrupt block relative dba: 0x01c00258 (file 7, block 600)
Bad header found during dbv: 
Data in bad block:
 type: 99 format: 7 rdba: 0x65747075
 last change scn: 0x636f.6c622064 seq: 0x6b flg: 0x21
 spare1: 0x72 spare2: 0x72 spare3: 0x0
 consistency value in tail: 0xaf180602
 check value in block header: 0x80a
 block checksum disabled

DBVERIFY - Verification complete
Total Pages Examined         : 12800
Total Pages Processed (Data) : 4303
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 208
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 8286
Total Pages Marked Corrupt   : 3
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 2142067 (0.2142067)

如上dev工具可以侦测出 数据文件ts_corruptblock01.dbf有3处坏块, (FILE 7,BLOCK 200),  (FILE 7,BLOCK 400 ),  (FILE 7,BLOCK 600 ).

4.2.4  validate查询数据文件ts_corruptblock01.dbf 有坏块
RMAN> validate datafile 7;
Starting validate at 13-JAN-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=51 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=45 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=40 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00007 name=/data/test11g/ts_corruptblock01.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:03
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
7    FAILED 0              8286         12800           2142403   
  File Name: /data/test11g/ts_corruptblock01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              4303            
  Index      0              0               
  Other      3              211           
validate found one or more corrupt blocks
See trace file /u01/product/diag/rdbms/test11g/test11g/trace/test11g_ora_8385.trc for details
Finished validate at 13-JAN-14

4.2.5 查询视图v$database_block_corruption 坏块信息

SYS> select * from v$database_block_corruption;
     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
         7        200          1                  0 CORRUPT
         7        400          1                  0 CORRUPT
         7        600          1                  0 CORRUPT

4.2.6 确认坏块对象.
SYS> set long 100000
SYS> set line 150
SYS> col segmetn_name for a20
SYS> col owner for a10

SYS> select owner,
  2  segment_name,
  3  segment_type,
  4  tablespace_name
  5   from dba_extents
  6   where file_id  =7 and 200
  7  between block_id and block_id + blocks;
OWNER                          SEGMENT_NAME         SEGMENT_TYPE       TABLESPACE_NAME
------------------------------ -------------------- ------------------    ------------------------------
JIEYU119                       TB_TESTBLOCK         TABLE                TS_CORRUPTBLOCK
JIEYU119                       TB_TESTBLOCK         TABLE                TS_CORRUPTBLOCK
   
SYS> select owner,
  2  segment_name,
  3  segment_type,
  4  tablespace_name
  5   from dba_extents
  6   where file_id  =7 and 400
  7  between block_id and block_id + blocks;
OWNER      SEGMENT_NAME         SEGMENT_TYPE       TABLESPACE_NAME
---------- -------------------- ------------------        ------------------------------
JIEYU119   TB_TESTBLOCK         TABLE              TS_CORRUPTBLOCK

SYS> select owner,
  2  segment_name,
  3  segment_type,
  4  tablespace_name
  5   from dba_extents
  6   where file_id  =7 and 600
  7  between block_id and block_id + blocks;

OWNER      SEGMENT_NAME         SEGMENT_TYPE       TABLESPACE_NAME
---------- -------------------- ------------------     ------------------------------
JIEYU119   TB_TESTBLOCK         TABLE              TS_CORRUPTBLOCK

由上可以查询出,三个坏块 (FILE 7,BLOCK 200),  (FILE 7,BLOCK 400 ) ,  (FILE 7,BLOCK 600 ) 全部为jieyu119.tb_testblock表的坏块.

查询表jieyu119.tb_testblock时,的确有坏块.

JIEYU119> select count(*) from tb_testblock;
select count(*) from tb_testblock *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 7, block # 400)
ORA-01110: data file 7: '/data/test11g/ts_corruptblock01.dbf'

4.2.7 DB 多个数据坏块进行修复.

RMAN> blockrecover corruption list;
Starting recover at 13-JAN-14
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00007
channel ORA_DISK_1: reading from backup piece /data/backup/rman/dbfull_3iotvnt3_1_1.bak
channel ORA_DISK_1: piece handle=/data/backup/rman/dbfull_3iotvnt3_1_1.bak tag=DBFULL
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01
starting media recovery
media recovery complete, elapsed time: 00:00:03
Finished recover at 13-JAN-14

查询表 jieyu119.tb_testblock有无坏块.

JIEYU119> select count(*) from tb_testblock;

  COUNT(*)
----------
    299496

查询表jieyu119.tb_testblock坏块恢复的数据,与步骤4.2建表tb_testblock时数据299496数据相同,数据无丢失.

写在最后,DB有效备份非常重要.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28569596/viewspace-1070130/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28569596/viewspace-1070130/