Rman-based bad block recovery

Reprinted please indicate the source http://blog.csdn.net/guoyjoe/article/details/30965303

The experiment steps are as follows:

1. Use rman to back up the entire database

Recovery Manager: Release 11.2.0.3.0 - Production on Sun Jun 8 15:30:35 2014Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.connected to target database: PROD (DBID=254815294)RMAN> backup database;Starting backup at 08-JUN-14using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=142 device type=DISKchannel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setinput datafile file number=00001 name=/u01/app/oracle/oradata/PROD/system01.dbfinput datafile file number=00005 name=/u01/app/oracle/oradata/PROD/tp01.dbfinput datafile file number=00002 name=/u01/app/oracle/oradata/PROD/sysaux01.dbfinput datafile file number=00003 name=/u01/app/oracle/oradata/PROD/undotbs01.dbfinput datafile file number=00004 name=/u01/app/oracle/oradata/PROD/users01.dbfchannel ORA_DISK_1: starting piece 1 at 08-JUN-14channel ORA_DISK_1: finished piece 1 at 08-JUN-14piece handle=/u01/app/oracle/product/11.2.0/dbs/01pab691_1_1 tag=TAG20140608T153040 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:25channel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setincluding current control file in backup setincluding current SPFILE in backup setchannel ORA_DISK_1: starting piece 1 at 08-JUN-14channel ORA_DISK_1: finished piece 1 at 08-JUN-14piece handle=/u01/app/oracle/product/11.2.0/dbs/02pab69q_1_1 tag=TAG20140608T153040 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01Finished backup at 08-JUN-14

2. Use BBED to destroy Blocks

(1) perform a test on block 20869 of the gyj_t1 table. Now you can find the record.

gyj@PROD> select id,name,dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from gyj_t1;        ID NAME       DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)---------- ---------- ------------------------------------ ------------------------------------         1 oracledba                                     5                                20869

(2) Use BBED to destroy the seq of block 20869

[root@jfdb ~]# su - oracle[oracle@jfdb ~]$ bbed parfile=par.txtPassword: BBED: Release 2.0.0.0.0 - Limited Production on Sun Jun 8 15:33:10 2014Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.************* !!! For Oracle Internal Use only !!! ***************BBED> set file 5   block 20869        FILE#           5        BLOCK#          20869BBED> map /v File: /u01/app/oracle/oradata/PROD/tp01.dbf (5) Block: 20869                                 Dba:0x01405185------------------------------------------------------------ KTB Data Block (Table/Cluster) struct kcbh, 20 bytes                      @0           ub1 type_kcbh                           @0           ub1 frmt_kcbh                           @1           ub1 spare1_kcbh                         @2           ub1 spare2_kcbh                         @3           ub4 rdba_kcbh                           @4           ub4 bas_kcbh                            @8           ub2 wrp_kcbh                            @12          ub1 seq_kcbh                            @14          ub1 flg_kcbh                            @15          ub2 chkval_kcbh                         @16          ub2 spare3_kcbh                         @18       struct ktbbh, 72 bytes                     @20          ub1 ktbbhtyp                            @20          union ktbbhsid, 4 bytes                 @24          struct ktbbhcsc, 8 bytes                @28          sb2 ktbbhict                            @36          ub1 ktbbhflg                            @38          ub1 ktbbhfsl                            @39          ub4 ktbbhfnx                            @40          struct ktbbhitl[2], 48 bytes            @44       struct kdbh, 14 bytes                      @100         ub1 kdbhflag                            @100         sb1 kdbhntab                            @101         sb2 kdbhnrow                            @102         sb2 kdbhfrre                            @104         sb2 kdbhfsbo                            @106         sb2 kdbhfseo                            @108         sb2 kdbhavsp                            @110         sb2 kdbhtosp                            @112      struct kdbt[1], 4 bytes                    @114         sb2 kdbtoffs                            @114         sb2 kdbtnrow                            @116      sb2 kdbr[1]                                @118      ub1 freespace[8038]                        @120      ub1 rowdata[30]                            @8158     ub4 tailchk                                @8188    BBED> p kcbhstruct kcbh, 20 bytes                       @0          ub1 type_kcbh                            @0        0x06   ub1 frmt_kcbh                            @1        0xa2   ub1 spare1_kcbh                          @2        0x00   ub1 spare2_kcbh                          @3        0x00   ub4 rdba_kcbh                            @4        0x01405185   ub4 bas_kcbh                             @8        0x00176fed   ub2 wrp_kcbh                             @12       0x0000   ub1 seq_kcbh                             @14       0x01   ub1 flg_kcbh                             @15       0x06 (KCBHFDLC, KCBHFCKV)   ub2 chkval_kcbh                          @16       0xc140   ub2 spare3_kcbh                          @18       0x0000BBED> m /x ff offset 14Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y File: /u01/app/oracle/oradata/PROD/tp01.dbf (5) Block: 20869            Offsets:   14 to  525           Dba:0x01405185------------------------------------------------------------------------ ff0640c1 00000100 00003945 0000eb6f 17000000 00000200 32008051 40010700  20008b02 00002c49 c0002301 02000080 0000876d 17000300 1a007503 0000bf1f  c0002501 0c000120 0000ed6f 17000000 00000000 00000001 0100ffff 14007a1f  741f741f 00000100 7a1f0000 00000000 00000000 00000000 00000000 00000000  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000  00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000  <32 bytes per line>BBED> sum applyCheck value for File 5, Block 20869:current = 0xc1be, required = 0xc1be

(3) Check for Bad blocks

BBED> verifyDBVERIFY - Verification startingFILE = /u01/app/oracle/oradata/PROD/tp01.dbfBLOCK = 20869Block 20869 is corruptCorrupt block relative dba: 0x01405185 (file 0, block 20869)Fractured block found during verificationData in bad block: type: 6 format: 2 rdba: 0x01405185 last change scn: 0x0000.00176fed seq: 0xff flg: 0x06 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0x6fed0601 check value in block header: 0xc1be computed block checksum: 0x0DBVERIFY - Verification completeTotal Blocks Examined         : 1Total Blocks Processed (Data) : 0Total Blocks Failing   (Data) : 0Total Blocks Processed (Index): 0Total Blocks Failing   (Index): 0Total Blocks Empty            : 0Total Blocks Marked Corrupt   : 1Total Blocks Influx           : 2Message 531 not found;  product=RDBMS; facility=BBED

(4) Are you sure you cannot query the records in Table gyj_t1?

gyj@PROD> alter system flush buffer_cache;System altered.gyj@PROD> select id,name,dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from gyj_t1;select id,name,dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from gyj_t1*ERROR at line 1:ORA-01578: ORACLE data block corrupted (file # 5, block # 20869)ORA-01110: data file 5: '/u01/app/oracle/oradata/PROD/tp01.dbf'

3. Use RMAN to verify data file No. 5

RMAN> backup validate datafile 5;Starting backup at 08-JUN-14using channel ORA_DISK_1channel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setinput datafile file number=00005 name=/u01/app/oracle/oradata/PROD/tp01.dbfchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:07List of Datafiles=================File Status Marked Corrupt Empty Blocks Blocks Examined High SCN---- ------ -------------- ------------ --------------- ----------5    FAILED 0              114          64000           1659211     File Name: /u01/app/oracle/oradata/PROD/tp01.dbf  Block Type Blocks Failing Blocks Processed  ---------- -------------- ----------------  Data       1              58883             Index      0              4221              Other      0              782             validate found one or more corrupt blocksSee trace file /u01/app/oracle/diag/rdbms/prod/PROD/trace/PROD_ora_2750.trc for detailsFinished backup at 08-JUN-14

4. Check the bad parts of file No. 5

gyj@PROD> select FILE#,BLOCK#,BLOCKS,CORRUPTION_CHANGE#,CORRUPTION_TYPE from v$database_block_corruption where file#=5;     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO---------- ---------- ---------- ------------------ ---------         5      20869          1                  0 FRACTURED

5. Use rman to restore Bad blocks

RMAN> blockrecover datafile 5 block  20869;Starting recover at 08-JUN-14using channel ORA_DISK_1channel ORA_DISK_1: restoring block(s)channel ORA_DISK_1: specifying block(s) to restore from backup setrestoring blocks of datafile 00005channel ORA_DISK_1: reading from backup piece /u01/app/oracle/product/11.2.0/dbs/01pab691_1_1channel ORA_DISK_1: piece handle=/u01/app/oracle/product/11.2.0/dbs/01pab691_1_1 tag=TAG20140608T153040channel ORA_DISK_1: restored block(s) from backup piece 1channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01starting media recoverymedia recovery complete, elapsed time: 00:00:01Finished recover at 08-JUN-14

6. Check the bad block of file no. 5 again, and the message has been sent (if there are records, execute backup validate datafile 5 again ;)

gyj@PROD> select FILE#,BLOCK#,BLOCKS,CORRUPTION_CHANGE#,CORRUPTION_TYPE from v$database_block_corruption where file#=5;no rows selected

7. Check the gyj_t1 record in the table, indicating that the table is successfully restored.

gyj@PROD> select id,name,dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from gyj_t1;        ID NAME       DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)---------- ---------- ------------------------------------ ------------------------------------         1 oracledba                                     5                                20869