Use DBMS_REPAIR to fix Bad blocks

Use DBMS_REPAIR to fix Bad blocks. 1. Procedure_Name Description ----------------- revoke ADMIN_TABLES Provides administrative functions (create, drop, purge) for repair or orphan key tables contained in the DBMS_REPAIR package. note: These tables are always created in the SYS schema. CHECK_OBJECT Detects and reports partitions uptions in a table or index DUMP_ORPHAN_KEYS Reports on index entries that point to r Ows in writable upt data blocks Marks blocks as software Defined upt that have been previusly identified as provided upt by the CHECK_OBJECT procedure contains Rebuilds the free lists of the object provided Provides the capability to fix the specified upted state a bitmap entry when segment space management is AUTO SKIP_CORRUPT_BLOCKS When used, ignores blocks marked upt During table and index scans. if not used, you get error ORA-01578 when encountering blocks marked failed upt. 2. Limitations of DBMS_REPAIR Tables with LOB data types, nested tables, and varrays are supported, but the out-of-line columns are ignored. clusters are supported in the SKIP_CORRUPT_BLOCKS and REBUILD_FREELISTS procedures, but not in the CHECK_OBJECT procedure. index-organized tables and LOB indexes Are not supported. the DUMP_ORPHAN_KEYS procedure does not operate on bitmap indexes or function-based indexes. the DUMP_ORPHAN_KEYS procedure processes keys that are no more than 3,950 bytes long. 3. Create a demo Environment [SQL] -- Current Environment sys @ USBO> select * from v $ version where rownum <2; BANNER unknown Oracle Database 11g Enterprise Edition Rel Creating 11.2.0.1.0-Production -- create a tablespace sys @ USBO> create tablespace tbs_tmp datafile '/u02/database/usbo/oradata/tbs_tmp.dbf' size 10 m autoextend on; -- create the table object tb_obj and Its index sys @ USBO> create table tb_obj tablespace tbs_tmp as select * from dba_objects; sys @ USBO> create index I _tb_obj on tb_obj (object_id ); -- table segment related information sys @ USBO> select segment_name, header_file, header_block, blocks 2 from dba_segments where Segment_name = 'tb _ OBJ '; SEGMENT_NAME HEADER_FILE HEADER_BLOCK BLOCKS ------------- ---------- TB_OBJ 6 130 1152 -- use the dd Command provided by linux to corrupt data BLOCKS [oracle @ linux1 ~] $ Dd of =/u02/database/usbo/oradata/tbs_tmp.dbf bs = 8192 conv = notrunc seek = 131 <EOF> limit upt me!> EOF 0 + 1 records in 0 + 1 records out 12 bytes (12 B) copied, 0.000209854 seconds, 57.2 kB/s [oracle @ linux1 ~] $ Dd of =/u02/database/usbo/oradata/tbs_tmp.dbf bs = 8192 conv = notrunc seek = 141 <EOF> limit upt me!> EOF 0 + 1 records in 0 + 1 records out 12 bytes (12 B) copied, 0.00019939 seconds, 60.2 kB/s [oracle @ linux1 ~] $ Dd of =/u02/database/usbo/oradata/tbs_tmp.dbf bs = 8192 conv = notrunc seek = 151 <EOF> limit upt me!> EOF 0 + 1 records in 0 + 1 records out 12 bytes (12 B) copied, 201772e-05 seconds, 554 kB/s sys @ USBO> alter system flush buffer_cache; -- The following query received the ERROR prompt sys @ USBO> select count (*) from tb_obj; select count (*) from tb_obj * ERROR at line 1: ORA-01578: ORACLE data block upted (file #6, block #131) ORA-01110: data file 6: '/u02/database/usbo/oradata/tbs_tmp.dbf' 4. Use DBMS_REPAIR to fix Bad blocks [SQL] Step a to create corresponding tables Object -- create a TABLE object using DBMS_REPAIR.ADMIN_TABLES to record the TABLE sys @ USBO> 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 => 'users'); 7 END; 8/PL/SQL procedure successfully completed. -- create a table object using the DBMS_REPAIR.ADMIN_TABLES process to record the isolated indexes after the table block is damaged, that is, the indexes pointing to the bad block. sys @ USBO> BEGIN 2 DBMS_REPAIR.ADMIN_TABLES 3 (4 TABLE_NAME => 'orphan _ KEY_TABLE ', 5 TABLE_TYPE => DBMS_REPAIR.ORPHAN_TABLE, 6 ACTION => DBMS_REPAIR.CREATE_ACTION, 7 TABLESPACE => 'users' 8); 9 END; 10/PL/SQL procedure successfully completed. step B check the damaged object-use DBMS_REPAIR.CHECK_OBJECT to detect the damaged object and return the number of damaged parts sys @ USBO> SET SERVEROUTPUT ON sys @ USBO> DECLARE num_corrupt INT; 2 BEGIN 3 num_corrupt: = 0; 4 DBMS_REPAIR.CHECK_OBJECT (5 SCHEMA_NAM E => 'sys ', 6 OBJECT_NAME => 'tb _ OBJ', 7 REPAIR_TABLE_NAME => 'repair _ table', 8 CORRUPT_COUNT => num_corrupt ); 9 DBMS_OUTPUT.PUT_LINE ('Number character upt: '| TO_CHAR (num_character upt); 10 END; 11/number character upt: 3 PL/SQL procedure successfully completed. -- We can query the damaged blocks from repair_table -- The following query shows that all the marked_upt columns are true, it indicates that we have marked bad blocks in CHECK_OBJECT-it is not clear why block 131 is listed three times in the query? Sys @ USBO> COLUMN object_name FORMAT a10 sys @ USBO> COLUMN repair_description FORMAT a28 sys @ USBO> set lines 10000 sys @ USBO> SELECT object_name, block_id, corrupt_type, struct, repair_description FROM repair_table; OBJECT_NAM BLOCK_ID CORRUPT_TYPE MARKED_COR REPAIR_DESCRIPTION ---------- ------------ ---------------------------------- TB_OBJ 131 6148 TRUE mark block software co Rrupt TB_OBJ 131 6148 TRUE mark block software blocks upt TB_OBJ 131 6148 TRUE mark block software blocks upt TB_OBJ 141 6148 TRUE mark block software blocks upt TB_OBJ 151 6148 TRUE mark block software blocks upt Step c mark Bad blocks-Process FIX_CORRUPT_BLOCKS is used to mark Bad blocks, in this demonstration, we have already been marked in CHECK_OBJECT. If the following process is not executed, because the previous step has already been marked, therefore, the following output is 0 sys @ USBO> set serveroutput on sys @ USBO> DECLARE num_fix INT; 2 BEGIN 3 num_fix: = 0; 4 DBMS_REP AIR. FIX_CORRUPT_BLOCKS (5 SCHEMA_NAME => 'sys ', 6 OBJECT_NAME => 'tb _ OBJ', 7 OBJECT_TYPE => dbms_repair.table_object, 8 REPAIR_TABLE_NAME => 'repair _ table ', 9 FIX_COUNT => num_fix); 10 DBMS_OUTPUT.PUT_LINE ('num fix: '| TO_CHAR (num_fix); 11 END; 12/num fix: 0 PL/SQL procedure successfully completed. step d DUMP isolated index key value -- use DUMP_ORPHAN_KEYS to fill the index key values pointing to Bad blocks into ORPHAN_KEY_TABLE sys @ USBO> SET SERVEROUT Put on sys @ USBO> DECLARE num_orphans INT; 2 BEGIN 3 num_orphans: = 0; 4 bytes (5 SCHEMA_NAME => 'sys ', 6 OBJECT_NAME =>' I _ TB_OBJ ', 7 OBJECT_TYPE => dbms_repair.index_object, 8 REPAIR_TABLE_NAME => 'repair _ table', 9 ORPHAN_TABLE_NAME => 'orphan _ KEY_TABLE ', 10 KEY_COUNT => num_orphans ); 11 DBMS_OUTPUT.PUT_LINE ('orphan key count: '| TO_CHAR (num_orphans); 12 END; 13/orphan Key count: 242 PL/SQL procedure successfully completed. -- The following query shows that the number returned above is exactly the same as that of the first 242 records sys @ USBO> select count (*) from orphan_key_table; COUNT (*) ---------- 242 -- check whether the object can be queried. The following results still show that sys @ USBO> select count (*) from tb_obj; select count (*) cannot be queried (*) from tb_obj * ERROR at line 1: ORA-01578: ORACLE data block upted (file #6, block #131) ORA-01110: data file 6: '/u02/database/usbo/oradata/tbs_tmp.db F'step e skip Bad blocks -- use SKIP_CORRUPT_BLOCKS to tell Oracle which bad blocks need to be skipped sys @ USBO> BEGIN 2 DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (3 SCHEMA_NAME => 'sys ', 4 OBJECT_NAME => 'tb _ OBJ ', 5 OBJECT_TYPE => dbms_repair.table_object, 6 FLAGS => dbms_repair.skip_flag); 7 END; 8/PL/SQL procedure successfully completed. -- because there are isolated indexes on the index key, we re-create the Index sys @ USBO> alter index I _tb_obj rebuild; Index altered. -- Verification Result sys @ USBO> select count (*) From tb_obj; COUNT (*) ---------- 72211 5. Postscript a. Remind me again that backup is more important than everything. Therefore, you should keep available backup at any time. B. The DBMS_REPAIR package does not really mean the repair of Bad blocks, but marks Bad blocks. The damaged data is lost. C. In the steps of DBMS_REPAIR package, create a corresponding table to store the repair table and isolate the index, followed by CHECK_OBJECT, FIX_CORRUPT_BLOCKS, DUMP_ORPHAN_KEYS, and SKIP_CORRUPT_BLOCKS. D. After completing the steps mentioned above in DBMS_REPAIR, we recommend that you rebuild the index. E. Note: If the damaged table object is referenced by other objects, we recommend that you disable these constraints first. records that are isolated from the child table can be determined based on the situation before the enable constraint.