Oracle-database Bad blocks

1. What are bad database blocks?
First, let's take a look at the format and structure of the database block:
The data block of the database has a fixed format and structure, divided into three layers:
Cache layer
Transaction layer
Data layer

When we read and write data blocks, the database checks the consistency of the data blocks to be read and written, including: 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. Database Bad blocks are divided into two types: Logical Bad blocks and Physical Bad blocks.

Logically writable upt: example: the block was writable upted by an Oracle internal error but does not appear to be media writable upt
Media bad upt: incorrect checksum/wrong data block address/impossible block type

Ii. Impact of Bad blocks on databases

If the database has bad blocks, the alarm log file of the database contains the following error messages:
Ora-1578 and Ora-600 and trace file in bdump directory where the first parameter value for a Ora-600 error ranges from [2000]-[8000], different values indicate problems at different layers of data blocks, 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.

Iii. Causes of Bad blocks
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

Iv. Handling of Bad blocks
1. First collect the corresponding information about the bad block, search from the AlertSID. log file or 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, relative_fno "RFN"
From dba_data_files
Union All
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 = <AFN>
AND <BL> 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:
? 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 statement:
Alter database datafile 'name _ of_file 'ONLINE;

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

1 2 Next Page