DBMS_REPAIR is available from Oracle8i.
We can handle bad blocks with a way to set an EVENT:
http://www.itpub.net/showthread.php?threadid=201766&pagenumber=
However, when the amount of data is large, or when the system is 7 * 24, such a method is not very suitable. Here we use dBMS_REPAIR to process.
SQL> CREATE TABLESPACE BLOCK DATAFILE 'D: /Oracle/oradata/oracle9i/block.dbf' size 5m;
TableSpace Created
SQL> Connect Dlinger / DLingerConnected to Oracle9i Enterprise Edition Release 9.2.0.1.0 Connected As Dlinger
QL> CREATE TABLE TEST TABLESPACE BLOCK AS SELECT * ALL_TABLES;
Table created
SQL> INSERT INTO TEST SELECT *.
806 ROWS INSERTED
SQL> INSERT INTO TEST SELECT *.
1612 ROWS INSERTED
SQL> INSERT INTO TEST SELECT *.
3224 ROWS INSERTED
SQL> INSERT INTO TEST SELECT *.
6448 ROWS INSERTED
SQL> INSERT INTO TEST SELECT *.
Insert Into Test Select * from test
ORA-01653: Table DLinger.Test cannot be expanded through 128 (in the table space block)
SQL> commit;
Commit completion
SQL> SELECT Count (*) from test;
COUNT (*) ---------- 12896
SQL> CREATE INDEX I_TEST ON TEST (TABLE_NAME);
Index created
SQL> ALTER System Checkpoint;
SYSTEM altered
SQL> Connect Sys / SYS AS SYSDBA is connected. SQL> Shutdown Immediate database has been turned off. The database has been uninstalled. Oracle routines have been closed.
- Use UltraEdit to edit block.dbf, modify several characters
SQL> StartUporacle routines have been started.
Total System Global Area 72424008 Bytesfixed Size 453192 bytesvariable size 46137344 bytesdatabase buffers 25165824 bytes Database 667648 BYTES Database Database Loading. The database has been opened. SQL> SELECT Count (*) from dlinger.test; select count (*) from dlinger.test * error is located in Chapter 1: ORA-01578: ORACLE data block corruption (file number 14, block number 160) ORA-01110: Data Document 14: 'D: /oracle/oradata/oracle9i/block.dbf' Use DBV Check: C: / Documents and Settings / Duanl> DBV File = 'D: /oracle/roadata/oracle9i/block.dbf'Blocksize=8192
DBVERIFY: Release 9.2.0.1.0 - Production on Tuesday August 24 19:58:15 2004
CopyRight (C) 1982, 2002, Oracle Corporation. All Rights Reserved.
DBVERIFY - Verification is starting: file = d: /oacle/oradata/oracle9i/block.dbf tagged as damaged page 160 *** Corrupt Block Relative DBA: 0x038000A0 (File 14, Block 160) Bad Check Value Found Database DBV: Data in bad block - type: 6 format: 2 rdba: 0x038000a0 last change scn: 0x0000.0035f5c2 seq: 0x1 flg: 0x06 consistency value in tail: 0xf5c20601 check value in block header: 0x3681, computed block checksum: 0x5bb spare1: 0x0, spare2 : 0x0, spare3: 0x0 ***
DBVERIFY - Verification Completion
Total number of pages: 640 Total number of pages (data): 510 Failed Page Total (data): 0 Total number of pages (index): 0 Failure page Total (index): 0 Total number of pages (other): 9 Total Total Pages (Segment): 0 Number of Failed Total Pages (Segment): 0 Empty Page Total: 120 Total Total Pages Tagged as Damage: 1 Transfer page Total: 0
Use dbms_repair package 1. Create management table: SQL> Connect Sys / sys as sysdbaconnected to oracle9i Enterprise Edition Release 9.2.0.1.0 Connected As Sys
SQL> EXEC DBMS_REPAIR.ADMIN_TABLES ('REPAIR_TABLE', 1, 1, 'Uses');
PL / SQL Procedure SuccessFully Completed
SQL> EXEC DBMS_REPAIR.ADMIN_TABLES ('Orphan_Table', 2, 1, 'Users'); PL / SQL Procedure SuccessFully Complete
2. Check the bad block: dbms_repair.check_Object
SQL> declare 2 cc number; 3 begin 4 dbms_repair.check_object (schema_name => 'DLINGER', object_name => 'TEST', corrupt_count => cc); 5 dbms_output.put_line (a => to_char (cc)); 6 end 7/1
The PL / SQL process has been successfully completed.
See here with dbms_repair.check, check the result of Corrupt_Count = 1, with a block damage, and the result of DBV.
After Check, check the block corruption information in our REPAIR_TABLE.
SQL> SELECT object_name, relative_file_id, block_id, marked_corrupt, corrupt_description, repair_description, 2 CHECK_TIMESTAMP from repair_table; OBJECT_NAME RELATIVE_FILE_ID BLOCK_ID MARKED_CORRUPT CORRUPT_DESCRIPTION REPAIR_DESCRIPTION CHECK_TIMESTAMP -------------- --------- --------------------------------------------- - - ---------------------------------------- Test 14 160 True Mark Block Software Corrupt 2 004-8-24 21:46
In this table, you can see the damaged Block information, information here, and we are consistent with DBV. Our experiment is simulated in 9i. We pay attention to the value of marked_corrupt, and after check_object, it has been identified as true. So you can directly carry out the fourth step. According to the Oracle documentation, at 8i, check_Object will only check the bad block, Marked_Corrupt, you need to use step 3, fix_corrupt_blocks position, modify marked_corrupt to true, and update Check_TimeStamp. Here we have passed the experiment, confirming that the third step is skipped under 9i, which is completely feasible. So whether 8i needs to perform the third step, I have not experimenting, but the speculation should not be skipped. 3. Positioning bad blocks: dbms_repair.fix_corrupt_blocks
The bad block can only be positioned after the bad block information is written to the defined repair_Table. Declare cc number; begindbms_repair.fix_corrupt_blocks (Schema_Name => 'DLinger', Object_name => 'Test', fix_count => cc); dbms_output.put_line (a => to_CHAR (CC));
4. Skip bad blocks:
Although we have positioned bad blocks, if we visit Table: SQL> SELECT Count (*) from Dlinger.dbblock;
Select count (*) from dlinger.dbblock
ORA-01578: Oracle data block damage (file number 14, block 154) ORA-01110: Data file 14: 'd: /oracle/oradata/oracle9i/block.dbf'
Will you get an error message. Here you need to skip the bad blocks with Skip_Corrupt_Blocks:
SQL> EXEC DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (Schema_Name => 'DLINGER', Object_name => 'Test', Flags => 1);
PL / SQL Procedure SuccessFully Completed
SQL> SELECT Count (*) from DLinger.Test;
COUNT (*) ---------- 12850
Lost 12896-12850 = 46 lines of data.
The process on the invalid key index; dump_orphan_keys declarecc number; begindbms_repair.dump_orphan_keys (schema_name => 'DLINGER', object_name => 'I_TEST', object_type => 2, repair_table_name => 'REPAIR_TABLE', orphan_table_name => 'ORPHAN_TABLE' , key_count => CC); end; / SQL> SELECT * FROM ORPHAN_TABLE; SCHEMA_NAME INDEX_NAME IPART_NAME iNDEX_ID TABLE_NAME PART_NAME tABLE_ID KEYROWID KEY DUMP_TIMESTAMP -------------- --------- - - ----------------------------------- ----- ------------------------------------------- -------------------------------- DLinger i_test 30258 test 30257 aaahyxaoawaaaiada0a * BaaaaAamte9htu5SQ19HU0LJ / G 2004-8-25 22: 1dlinger i_test 30258 Test 30257 Aaahyxaowaaaiadasa * BaaaaAamte9htu5SQ19HVEXP / G 2004-8-25 22: 1 .................................... .............. dlinger I_TEST 30258 TEST 30257 AAAHYxAOwAAAIADB0A * BAAAAAAPTE9HTU5SX0xPQkZSQUck / g 2004-8-25 22: 1DLINGER I_TEST 30258 TEST 30257 AAAHYxAOwAAAIADBYA * BAAAAAAMTE9HTU5SX1RZUEUk / g 2004-8-25 22: 1DLINGER I_TEST 30258 TEST 30257 AAAHYxAOwAAAIADAQA * BAAAAAALTE9HTU5SX1VJRCT
