Three methods for restoring bad database blocks using Oracle8i

When you perform a Sun Cluster dual-host switchover, unexpected power failure, or other situations, the shared disk may sometimes fail to be mounted. You need to use fsck to repair the shared disk. After the restoration is completed, the database cannot be started due to "data block corruption" during the database startup process. At this time, the data block damage types can be different, detect and fix errors. This section describes three methods to repair damaged data blocks using Oracle8i.

1. Data Block corruption, error code for ORA-01578

ORA-1115 I/O Error reading Block

Usually followed by ORA-737X errors and operating system errors (such as error number 5 in UNIX)

Cause:

1. Hardware problems (disk controller problems or disk problems)

2. Physical data block damage (usually caused by the previous cause)

3. Handle giant files with error code ORA-7371

How to determine the Fault Cause and recovery:

1. view other ORA-1115 errors in the alert. log file:

1) if the file points to a different disk, It is a disk controller problem. Check v $ datafile, which files are located under the controller, and go to step 2.

2) if it points to different files on the same disk, It is a disk problem. Go to step 2.

3) if it points 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 pointed out in the ORA-1115, if the query continues to point to a table or index, then recreate them.

2. If the file is a system tablespace or in noarchivelog mode, close the database and go to step 4.

3. If the database is in archivelog mode, the database should still be closed. If the database cannot be closed, the corresponding data file will be taken offline: Alter database datafile 'file' offline;

4. Try to copy the data file to another disk.

5. If the copy fails, the file will be lost.

6. startup Mount;

7. Rename the data file to the name of the file successfully copied to another disk:

Alter database rename file 'old path filename 'to 'new path filename ';

8. Alter database open;

9. Recover datafile file name;

Alter database datafile 'file name' online;

2. rollback segments need to be restored

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

1. view all online tablespace and data files

2. Add event = "10015 trace name context forever, level 10" to the init. ora file, which generates a trace file containing the transaction and rollback information.

3. Close and re-open the database.

4. Check the trace file. The error recovery TX (#, #) object #. TX (#, #) should be returned, indicating the transaction information. The object # is the same as the object_id in SYS. dba_objects.

5. Use the following query to find the objects being restored:

Select owner, object_name, object_type, status from dba_objects where object_id = <object #>;

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

Iii. common methods for detecting and repairing damaged blocks:

(1) Use the initialization parameters db_block_checking and db_block_checksum.

When the block changes, db_block_checking checks the block logic. The error 10210 and 10211 will be prevented.

(2) Using the dbms_repair package, dbmsrpr. SQL and prvtrpr. PLB generate the package to generate damaged block information in a specific table.

1. dbms_repair.admin_tables is used to create and Delete tables that store damaged blocks. Table_type: repair_table (table), orphan_table (INDEX), Action: create_action (create table), purge_action (delete irrelevant data), drop_action (delete table ). Example:

Dbms_repair.admin_tables ('repair _ table', dbms_repair.repair_table, dbms_repair.create_action, 'temp _ data ');

2. damaged blocks in dbms_repair.check_object checklist, index, and partition. Here, object_type is table_object (table), index_object (INDEX), and repair_table_name (table used to store damaged block information ). Example:

Dbms_repair.check_object ('orate', 'location', corrupt_count =>: CC );

3. Use the following statement to query the block damage information:

Select object_name, relative_file_no, block_id, marked_corrupt, corrupt_description, repair_description from repair_table;

4. Damaged block flag: dbms_repair.fix_corrupt_blocks ('orate', 'location', fix_count =>: fc );

5. Skip damaged blocks: dbms_repair.skip_corrupt_blocks ('orate', 'location ');

Here, object_type is table_object (table) and cluster_object (index ).

6. Use rebuild_freelists to recreate the corrupted idle list: dbms_repair.rebuild_freelists

7. Use the following method to find the index pointing to the damaged block:

(1) create a table to store index pointing to Bad blocks

(2) dbms_repair.dump_orphan_keys ('orate', 'loc _ pK ',

Orphan_table_name => 'orphan _ tab1', key_count =>: Kc );

(3) Select index_name, count (*) from orphan_key_table where table_name = 'classe' group by index_name;

(4) rebuild an index with orphan keys

Restriction: Indexes-organized tables and LOB indexes cannot be analyzed. dump_orphan_keys cannot perform operations on bitmap and function-based indexes.

(3) Use the SQL command analyze table | index... Validate Structure

Utlvalid. SQL. Create an invalid_rows table that contains damaged block information. Analyze table validate Structure Cascade verifies both the table and index.

(4) use dbverify

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

DBV/opt/Oracle/DB02/oradata/data01.dbf start = 1 end = 500 logfile = DBV. Log