Three methods of repairing database bad block with oracle8i

oracle| Data | database
In the case of sun cluster dual-machine switching, accidental power outages, or other situations where the shared disk is not mount, it is necessary to use FSCK to repair the shared disk. After the repair completes, in the database starts the process, but also appears "The block of data blocks, cannot start the database" The phenomenon, at this time, may according to the different data block damage type, detects and fixes the error. Here are three ways to use oracle8i to repair damaged blocks of data.

One, the data block is corrupted, the error code is ORA-01578

ORA-1115 I/O ERROR READING block

Typically followed by ora-737x errors and operating system errors (such as error number 5 in Unix)

Causes:

1. Hardware problem (disk controller problem or disk issue)

2. Damage to the physical block of data (usually caused by the previous reason)

3. When handling jumbo files, followed by error code ORA-7371

Determine the cause of the failure and how to recover:

1. View the occurrence of other ORA-1115 errors in the Alert.log file:

1 if a file pointing to a different disk is a problem with the disk controller, see V$datafile, which files are located under the controller and go to step two.

2 If you point to a different file on the same disk, it is a problem with the disk and go to step two.

3 If you point to the same file, execute the following statement to find the file name:

SELECT Segment_name,segment_type from dba_extents WHERE file_id=< file number > and < block number > BETWEEN block_id
and block_id+blocks-1;

Where the file number and block number are indicated in ORA-1115, if the query continues to point to a table or index, rebuild them.

2. If the file is System tablespace, or in Noarchivelog mode, close the database and go to step fourth.

3. If the database is in Archivelog mode, the database should still be closed, and if the database cannot be closed, the corresponding data file will be taken offline: ALTER database datafile ' filename ' OFFLINE;

4. Try copying the data files to another disk.

5. If the copy fails, the file is lost.

6. STARTUP MOUNT;

7. Rename the data file to a file that was successfully copied to another disk:

ALTER DATABASE RENAME file ' old path filename ' to ' new path filename ';

8. ALTER DATABASE OPEN;

9. RECOVER datafile filename;

ALTER DATABASE datafile ' filename ' ONLINE;

Second, rollback section needs to be restored

If the rollback segment is in the need recovery state, you need to perform the following steps to restore it:

1. View all online table spaces and data files

2. Add event = "10015 Trace name Context Forever,level 10" in the Init.ora file, which will generate a trace file containing information about the transaction and rollback.

3. Close and reopen the database.

4. To view the trace file, you should have the error recovery TX (#,#) object #. TX (#,#), which indicates the transaction information, where the object #与sys. Dba_objects the same object_id.

5. Use the following query to find the objects that are recovering:

SELECT owner,object_name,object_type,status from dba_objects WHERE object_id=<object #>;

6. You must delete the object to release the rollback block.

Common methods of detecting and repairing damaged blocks:

(i) Using initialization parameters db_block_checking and Db_block_checksum.

When a block is changed, the db_block_checking is logically validated against the block. Will prevent 10210 and 10211 errors from occurring.

(ii) using the Dbms_repair package, the Dbmsrpr.sql and PRVTRPR.PLB generate the information that the package generates a corrupted block in a particular table.

1. Dbms_repair. Admin_tables is used to create and delete tables that store damaged blocks. Where Table_type is: repair_table (table), orphan_table (index), ACTION: create_action (CREATE TABLE), purge_action (delete irrelevant data), DROP_ ACTION (delete table). Cases:

Dbms_repair.admin_tables (' repair_table ', Dbms_repair. Repair_table,dbms_repair. Create_action, ' temp_data ');

2. Dbms_repair. Check_object checks the table, index, and block in the partition for damage. Where object_type is: table_object (table), Index_object (index), repair_table_name (table for storing corrupted block information). Cases:

Dbms_repair.check_object (' Oratrain ', ' locations ', CORRUPT_COUNT=&GT;:CC);

3. Use the following statement to query for block corruption information:

SELECT object_name, Relative_file_no, block_id, Marked_corrupt, Corrupt_description, repair_description from Repair_ Table

4. The blocks are marked as damaged: dbms_repair.fix_corrupt_blocks (' Oratrain ', ' locations ', FIX_COUNT=&GT;:FC);

5. Skip damaged block: Dbms_repair.skip_corrupt_blocks (' Oratrain ', ' locations ');

Where object_type is: table_object (table), Cluster_object (index).

6. To rebuild a corrupted free list using rebuild_freelists: dbms_repair.rebuild_freelists

7. Find the index that points to the damaged block by using the following methods:

(1) Create a table that holds an index to a bad block

(2) Dbms_repair.dump_orphan_keys (' Oratrain ', ' loc_pk ',

Orphan_table_name=> ' Orphan_tab1 ', KEY_COUNT=&GT;:KC);

(3) SELECT index_name, COUNT (*) from orphan_key_table WHERE table_name = ' CLASSES ' GROUP by index_name;

(4) Rebuilding an index with the orphan keys

Limit: Cannot parse index-organized tables and LOB Indexes,dump_orphan_keys cannot operate on bitmap and function-based indexes.

(iii) using SQL commands analyze table| INDEX ... VALIDATE STRUCTURE

Utlvalid.sql. Create a invalid_rows table containing corrupted block information, ANALYZE table VALIDATE STRUCTURE cascade validate tables and indexes simultaneously.

(iv) Use of dbverify

Dbverify is an external tool, so it has little impact on the database. can be used to verify the integrity of the backup file before copying the backup file back to its original location and to locate the data block corruption. The order is as follows:

DBV/OPT/ORACLE/DB02/ORADATA/DATA01.DBF start=1 end=500 Logfile=dbv.log