Handling Bad blocks in Oracle databases (1)

1. What is bad database blocks?

First, let's take a rough look at the format and structure of the database block-The database data block has a fixed format and structure, divided into three layers of cache layer, transaction layer, data layer. When we read and write data blocks, the database will check the consistency of the data blocks to be read and written, it includes the data block type, the address information of the data block, the SCN Number of the data block, and the header and tail of the data block. If any inconsistency is found, the database will mark the data block as a bad block. There are two types of Bad blocks in the database: Logical Bad blocks and Physical Bad blocks.

Impact of two bad blocks on the database

If the database has bad blocks, there will be some of the following error messages in the database's alarm Log file Ora-1578 and Ora-600 and trace file in bdump directory, the range of the first parameter value of the Ora-600 error is [2000]-[8000], and different values indicate the problem of different layers of the data block, as shown in the following table:

Range block layer

Cache layer 2000-4000

Transaction layer 4000-6000

Data layer 6000-8000

Objects affected by bad blocks may be data dictionary tables, rollback segment tables, temporary segments, user data tables, and indexes. Different objects have different processing methods for generating Bad blocks.

Causes of three bad parts

Oracle calls standard C system functions to read and write data blocks. Therefore, Bad blocks may be caused by the following reasons:

Hardware I/O Error

OS I/O errors or buffer Problems

Memory or paging Problems

Disk repair tools

A part of a data file is being overwritten.

An error occurred while accessing an unformatted system block from Oracle.

Partial data file Overflow

Oracle or OS bug

Solution to four bad Blocks

1. First collect the corresponding information about the bad and fast, search from the AlertSID. log File or from the trace file, and find the following information:

Ora-1578 file # (RFN) block #

Ora-1110 file # (AFN) block #

Ora-600 file # (AFN) block #

RFN indicates relative_fno.

AFN indicates file_id.

Select file_name, tablespace_name, file_id "AFN", relative_fno "RFN" From dba_data_files; Select file_name, tablespace_name, file_id, relative_fno "RFN" From dba_temp_files;

2. Determine the object with bad Blocks

SELECT tablespace_name, segment_type, owner, segment_name, partition_name FROM Dba_extents WHERE file_id = and Between block_id AND block_id + blocks-1;

Through the preceding query statement, you can find out what is the current bad block object and what type of object it is. Note that if the temp file contains bad blocks, no records are returned.

3. determine the corresponding processing method based on the object type found in 2:

Common objects with bad blocks are:

Object under Sys user

Rollback segment

Temporary Section

Index or partition Index

Table

Common solutions include:

Restore data files

Recover only bad blocks (available for 9i or later versions)

Store data through ROWID RANGE SCAN

Use DBMS_REPAIR

Use EVENT

4. Introduction to specific solutions

Methods for restoring data files:

If the database is archived and has a complete physical backup, you can use this method to restore the database.

The procedure is as follows:

1) first offline the affected data file and execute the following statement

Alter database datafile 'name _ file' OFFLINE;

2) Keep the data files with bad blocks, and then copy the backup data files. If the path of the recovered data file is different, run the following statement:

Alter database rename file 'old _ name' TO 'new _ name ';

3) restore the data file and execute the following statement:

Recover datafile 'name _ of_file ';

4) after Online recovery, execute the following statements:

Alter database datafile 'name _ of_file 'ONLINE;