Recovery of Oracle Database bad blocks

Analog Block Bad block:

For data blocks that have inconsistent data blocks, if the current database is backed up and in archive mode, the data block can be recovered using the Rman tool block recovery, which is the simplest and most effective, and can be done while the data file is online without data loss. Data block corruption of the database that is backed up may cause data loss or data loss, depending on the object in which the bad block occurred, such as if the index block is corrupted, the data is not lost, the index is rebuilt, and the data loss occurs on the table or partition table data block.

1. Recovery method without data loss

---blockrecover blockrecover datafile xx block xx;--with Rman tools to repair a single bad block blockrecover corruption list;--repair all bad blocks sql> Select * FROM Livan.test;select * from Livan.test                    *error to line 1:ora-01578:oracle data block corrupted (file # 6, Block # Ora-01110:data file 6: '/U02/APP/ORADATA/PSDB/LIVAN_TBS01.DBF '

 [[email protected] u02]$ rman target/recovery manager:release 10.2.0.4.0-production on Thu Feb 5 17:02:23 20  15Copyright (c) 1982, Oracle. All rights reserved.connected to target Database:psdb (dbid=1410134833) rman> blockrecover datafile 6 block 12; Starting blockrecover at 05-feb-15using target database control file instead of recovery catalogallocated Channel:ora_dis K_1channel ora_disk_1:sid=142 Devtype=diskchannel ora_disk_1:restoring block (s) channel ora_disk_1:specifying Block (s) to restore from backup setrestoring blocks of datafile 00006channel ora_disk_1:reading from backup Piece/u02/ps Db_backup/full_psdb_870868610channel ora_disk_1:restored block (s) from BACKUP piece 1piece handle=/u02/psdb_backup/ full_psdb_870868610 tag=tag20150205t115650channel ora_disk_1:block Restore complete, elapsed time:00:00:01starting Media recoverymedia Recovery complete, elapsed time:00:00:03finished blockrecover at 05-feb-15 

[[email protected] u02]$ sqlplus '/as sysdba ' sql*plus:release 10.2.0.4.0-production on Thu Feb 5 17:04:15 2015Copyright (c) 1982, Oracle.  All rights reserved.connected to:oracle Database 10g Enterprise Edition Release 10.2.0.4.0-productionwith the Partitioni Ng, OLAP, Data Mining and Real application testing optionssql> select * from Livan.test;        ID NAME----------------------------------------         1 Beijing         2 Shanghai         3 Shandong

If the table on the bad block is not updated recently, you can also use the bbed Copy command to copy a block of data from a recent backup, specifically not shown.

2. There may be data loss recovery (in case no backup is not archived)

---user table data corruption

<1> number of data items under normal conditions

Sql> Select COUNT (*) from test;  COUNT (*)----------     50604

<2> Make a bad block

Select Rowid,dbms_rowid.rowid_relative_fno (ROWID) rel_fno,dbms_rowid.rowid_block_number (ROWID) Blockno,dbms_        Rowid.rowid_row_number (ROWID) Rownofrom livan.test; Bbed> Set DBA 6,76 dba 0x0180004c (25165900 6,76) bbed> d/v dba 6,76 offset 0 File:/u02/app/orad ATA/PSDB/LIVAN_TBS01.DBF (6) block:76 offsets:0 to 127 dba:0x0180004c----------------------------------------- --------------06a20000 4c008001 d3220800 00000104 L.? L ...? ... b8510000 01000000 ddce0000 b4220800 l glimpse ..... Smoke..?.. 00000000 03003201 41008001 ffff0000 l ... 2.A ..... 00000000 00000000 00000000 00800000 l .... b4220800 00000000 00000000 00000000 L?.............., ..... 00000000 00000000 00000000 00000000 L ...... ..... 00000000 00000000 00000000 00000000 L ...... .....  00000000 00000000 00000000 00019200 l .... <16 bytes per line>bbed> modify/x 12345 dba 6,76 offset 0 (in)               File:/u02/app/oradata/psdb/livan_tbs01.dbf (6) block:76offsets:0 to 127 dba:0x0180004c------------------------------------------------------------------------012 34500 4c008001 d3220800 00000104 b8510000 01000000 ddce0000 b4220800 00000000 03003201 41008001 ffff0000 00000000 0000000 0 00000000 00800000 b4220800 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 0 0000000 00000000 00000000 00000000 00019200 <32 bytes per line>bbed> sum playbbed-00202:invalid parameter (play ) bbed> sum apply Check value for File 6, Block 76:current = 0xd0fa, required = 0XD0FA sql> alter system flush BU Ffer_cache; System altered.  Sql> Select COUNT (*) from Test;select count (*) from Test*error on line 1:ora-01578:oracle data block corrupted (file # 6, Block #) Ora-01110:data file 6: '/U02/APP/ORADATA/PSDB/LIVAN_TBS01.DBF '

Found our 6th file 76th data block corrupted, reported ORA-0178 error, we know that as long as the database reported ORA-01578 error,
Description The data block has been identified as: "Software Corrupt"

<3> confirm the type of bad block

Sql> Select Segment_name,partition_name,segment_type,owner,tablespace_name  2 from  sys.dba_extents  3  where File_id=&afn  4 and  &bad_block_id between block_id and block_id + blocks-1; Enter value for Afn:6old   3:where file_id=&afnnew   3:where file_id=6enter value for Bad_block_id:76old   4:and &bad_block_id between block_id and block_id + blocks-1new 4:and, between block_id and   block_id + blocks -1segment_name    partition_name       segment_type       OWNER      tablespace_name-------------------------- -------------------------------------------------------------------TEST                                 TABLE              Livan      Livan_tbs

Our data corruption is found on our user table and no backup data is lost.

<4> Mark bad block as "software corrupt"

In the 2nd step when the full table scan has reported ORA-01578 error, indicating that the data block has been identified as: "Software corrupt", under normal circumstances can skip this step. We use the Dbms_repair package to demonstrate the marked bad block as "software corrupt"

Use the Dbms_repair package to refer to: http://blog.itpub.net/8494287/viewspace-1357457/

--Using the Dbms_repair package you must first create a repair table two tables:

Sql> begin  2  dbms_repair.admin_tables (  3  table_name=> ' repair_table ',  4  Table_type =>dbms_repair.repair_table,  5  action=>dbms_repair.create_action,  6  tablespace=> ' LIVAN _tbs ');     7  end;  8  /pl/sql procedure successfully completed. sql>  col object_name for a20sql> select Owner,object_name,object_type  2 from  dba_objects  3  where object_name like '%repair_table% '; OWNER                          object_name          object_type---------------------------------------------------------------------SYS                            Repair_table         tablesys                            dba_repair_table     VIEW

--Create orphan key table again

Sql> begin  2  dbms_repair.admin_tables (  3  table_type=>dbms_repair.orphan_table,  4  Action=>dbms_repair.create_action,  5  tablespace=> ' Livan_tbs ');     6  end;  7  /pl/sql procedure successfully completed. Sql> Select Owner,object_name,object_type  2 from  dba_objects  3  where object_name like '%O Rphan_key_table% '; OWNER                          object_name          object_type----------------------------------------------------- ----------------SYS                            orphan_key_table     tablesys                            dba_orphan_key_table VIEW

--Check the object and the results will be placed in the repair_table we created earlier

Sql> set Serveroutput onsql> declare  2  rpr_count int;  3  begin  4  rpr_count:=0;  5  Dbms_repair.check_object (  6  schema_name=> ' Livan ',  7  object_name=> ' TEST ',  8  repair_table_name=> ' repair_table ',  9  corrupt_count=>rpr_count);   Ten  Dbms_output.put_line (' Repair count: ' | | To_char (Rpr_count)); One  end;  /repair Count:1pl/sql procedure successfully completed.

Check out 1 bad blocks.

--Check the bad block result of the checksum

Sql> Select Object_name,block_id,corrupt_type,marked_corrupt,  2  corrupt_description,repair_ Description  3 from  repair_table;object_nam   block_id corrupt_type marked_cor corrupt_descrip repair_ DESCRIPTION---------------------------------------------------------------------------------------TEST               76         6148 TRUE                       mark block software corrupt

We know that when Marked_corrupt is true, identifying the data block has been identified software corrupt

---Identify the bad block as software corrupt (re-demo)

Sql> declare  2  fix_count int;  3  begin  4  fix_count:=0;  5  dbms_repair.fix_corrupt_blocks (  6  schema_name=> ' Livan ',  7  object_name=> ' TEST ',  8  Object_type=>dbms_repair.table_object,  9  repair_table_name=> ' repair_table ', 10  fix_count=>fix_count);  dbms_output.put_line (' Fix count: ' | | To_char (Fix_count)); The  end of the  /fix Count:0pl/sql procedure successfully completed.

--Check again, because it has been marked as software corrupt, so there is no change in this sign

Sql> Select Object_name,block_id,corrupt_type,marked_corrupt,  2  corrupt_description,repair_ Description  3 from  repair_table;object_nam   block_id corrupt_type marked_cor corrupt_descrip repair_ DESCRIPTION---------------------------------------------------------------------------------------TEST               76         6148 TRUE                       mark block software corrupt

is not marked as oftware corrupt, marked_corrupt column will display false

<5> Check Other related objects

Check how many index entries point to the record of the bad block

Sql> Select Index_name from dba_indexes  2  where table_name in (SELECT DISTINCT object_name from repair_table) ; Index_name------------------------------Index_test

Query found an index pointing to this bad block

--Check how much index data there is

Sql> set Serveroutput onsql> declare  2  key_count int;  3  begin  4  key_count:=0;  5  Dbms_repair.dump_orphan_keys (  6  schema_name=> ' Livan ',  7  object_name=> ' Index_test ',  8  object_type=>dbms_repair.index_object,  9  repair_table_name=> ' repair_table ', 10  orphan_table_name=> ' orphan_key_table ', one by one  key_count=>key_count);  dbms_output.put_line (' Orphan key count: ' | | To_char (Key_count));  /orphan Key Count:146pl/sql procedure successfully completed. Sql> Select Index_name,count (*) from orphan_key_table  2  GROUP by Index_name;index_name                       Count (*)--- -------------------------------------index_test                            146

You can see 146 pieces of data pointing to the bad block.

<6> Skip bad blocks using Dbms_repair.skip_corrupt_blocks or 10231 event mode

Sql> Select COUNT (*) from Livan.test;select count (*) from Livan.test                           *error on line 1:ora-01578:oracle data block Corrupted (File # 6, Block #) Ora-01110:data file 6: '/u02/app/oradata/psdb/livan_tbs01.dbf ' sql> begin  2  DB Ms_repair.skip_corrupt_blocks (  3  schema_name=> ' Livan ',  4  object_name=> ' TEST ',  5  Object_type=>dbms_repair.table_object,  6  flags=>dbms_repair.skip_flag);  7  end;  8  /pl/sql procedure successfully completed. Sql> Select COUNT (*) from livan.test;  COUNT (*)----------     50458

You can see that when the Dbms_repair.skip_corrupt_blocks data is finished, the data is normally accessed, but the statistics are
146 less (50604-50458), that is, the data on our bad block is not counted and skipped.

---skip using 10231 events

Sql> Select COUNT (*) from livan.test;  COUNT (*)----------     50458sql> begin  2  dbms_repair.skip_corrupt_blocks (  3  schema_name=> ' Livan ',  4  object_name=> ' TEST ',  5  object_type=>dbms_repair.table_object,  6  Flags=>dbms_repair.noskip_flag);  7  end;  8  /pl/sql procedure successfully completed.  Sql> Select COUNT (*) from Livan.test;select count (*) from Livan.test                           *error on line 1:ora-01578:oracle data block Corrupted (File # 6, Block #) Ora-01110:data file 6: '/U02/APP/ORADATA/PSDB/LIVAN_TBS01.DBF ' sql> alter session set Events ' 10231 Trace name context forever,level 10 '; Session altered. Sql> Select COUNT (*) from livan.test;  COUNT (*)----------     50458          sql> alter session set events ' 10231 Trace name context off '; Session altered.  

<7> Rebuilding tables and indexes using CTAs methods

Sql> CREATE TABLE Test_bak as SELECT * from Test; Table created. Sql> CREATE index Idx_test_bak on Test_bak (object_id), index created.--rebuild index statement sql> alter indexes index_test rebuild ONL Ine;index altered.

<8> repairing the original table using the Freelists method of rebuilding objects

Use this method to prevent the bad block from being added to the freelists later notice that this approach applies only to the table space manually managed by the segment space (SEGMENT space MANAGEMENT MANUAL), otherwise it will be reported ora-10614:operation not Allowed on this segment error

Sql> begin  2  dbms_repair.rebuild_freelists (  3  schema_name=> ' Livan ',  4  object_name = ' TEST ',  5  object_type=>dbms_repair.table_object);  6  end;  7/begin*error at line 1:ora-10614:operation No allowed on this  segmentora-06512:at "SYS. Dbms_repair ", Line 401ora-06512:at Line 2

<9> data in Bad blocks

If the data in the bad block is not lost, you can only try other methods to recover the bad block content, some of which are available to third-party pay tools, or try the following:

* Attempt to recover the contents of an indexed column from an indexed content

* Try using Logminer to dig from the log

* Contact Oracle Support, some tools to interpret the contents of the data block.

Recovery of Oracle Database bad blocks