How to handle bad block problems in Oracle database

This article mainly describes how to deal with the problem of bad blocks in the Oracle database, for the bad blocks produced on different objects, the method of processing will be different, this article will be a general introduction to these methods. Because the database has been running for a long time, because of the aging of hardware devices, the chance of bad blocks will become more and more large, so, as a DBA, how to solve the problem of the database has become an important issue of the bad block.

A: What is a bad chunk of a database

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 to the data block, the database checks the data blocks to read and write, including: the type of the block, the address information of the block, the SCN number of the data block, and the head and tail of the data block. If you find that there is inconsistent information, the database will mark the block as bad. The bad blocks of the database are divided into two kinds, logical bad blocks and physical bad blocks.

Second: The impact of bad blocks on the database

If there is a bad block in the database, there are some error messages in the database's alarm log file: Ora-1578 and Ora-600 and trace file in Bdump directory, where the range of the first parameter value of the Ora-600 error is [2000] -[8000], different values represent problems with 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

Bad blocks can affect objects such as the data dictionary table, the rollback segment table, the temporary segment, the User data table, and the index. Different objects are treated differently when they produce bad blocks.

Three: The cause of the bad block

Oracle calls the system functions of standard C to read and write data blocks, so bad blocks can be generated for several reasons:

? I/O errors for hardware

? Operating system I/O error or buffering issues

? Memory or paging issues

? Disk Repair Tool

? A portion of a data file is being overwritten

? Oracle failed to access a system block that was not formatted

? Data file Partial Overflow

? Oracle or operating system bugs

Four: How to deal with bad blocks

1. Gather the appropriate information about the bad fast, either from the AlertSID.log file or from the trace file, and find some information such as the following:

Ora-1578 file# (RFN) block#

Ora-1110 file# (AFN) block#

Ora-600 file# (AFN) block#

where RFN represents Relative_fno.

AFN means 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 what is the object that has the bad block:

SELECT Tablespace_name, Segment_type, owner, Segment_name, partition_name from dba_extents WHERE file_id = and between bl ock_id and block_id + blocks–1;

With this query, you can find out what the object is and what type of object is currently in existence for the bad block. It is important to note that if there is a bad block in the temp file, there is no record returned.

3. According to the type of object queried in 2, the corresponding processing method is determined.

Common objects that have bad blocks are:

? SYS user under the object

? Rollback segment

? Temporary segment

? Index or partition index

? Table

Common methods of processing are:

? Recovering data files

? Recover only blocks that are bad (9i or later available)

? Saving data with rowID RANGE SCAN

? Using Dbms_repair

? Using the event

4. Introduction to specific Treatment methods

? To recover a data file method:

This method can be used to recover a database if it is in archive mode and has a full physical backup.

The steps are as follows:

1) First offline the affected data file, execute the following statement:

ALTER DATABASE datafile ' name_file ' OFFLINE;

2) Keep the data files with bad blocks and copy the backed up data files. If the recovered data file requires a different path, execute the following statement:

ALTER DATABASE RENAME FILE ' old_name ' to ' new_name ';

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

RECOVER datafile ' Name_of_file ';

4) After the online recovery data file, execute the following statement:

ALTER DATABASE datafile ' name_of_file ' ONLINE;

? Recover only blocks that are bad (9i or later available)

Using this method requires that the database version is 9.2.0 or more, the catalog database for Rman is configured, the database is archived, and a complete physical backup is required.

The steps are as follows:

Use the Blockrecover command for Rman:

Rman>run{blockrecover datafile 5 Block 11, 16;}

You can also force a backup to be used before a SCN number to recover a block of data.

Rman>run{blockrecover datafile 5 block 11,16 restore until sequence 8505;}

? Saving data with rowID RANGE SCAN

1) First get the minimum value of the row ID in the bad block and execute the following statement:

SELECT dbms_rowid.rowid_create (1,<obj_id>,,, 0) from DUAL;

2) Get the maximum value of the row ID in the bad block and execute the following statement:

SELECT dbms_rowid.rowid_create (1,<obj_id>,,+1,0) from DUAL;

3) It is recommended that a temporary table store the data with no bad blocks and execute the following statement:

CREATE TABLE salvage_table as SELECT * from Corrupt_tab Where 1=2;

4) Save the data that does not exist in the bad block to the staging table, and execute the following statement:

INSERT into salvage_table SELECT/*+ ROWID (a) */* from A WHERE ROWID < ' <low_rid> ';

INSERT into salvage_table SELECT/*+ ROWID (a) */* from A WHERE ROWID >= '

5) Rebuild the table according to the data in the temporary table, and reconstruct the index on the table, limit.

? Use 10231 diagnostic events to skip bad blocks when doing a full table scan

Can be set at session level:

ALTER SESSION SET EVENTS ' 10231 TRACE NAME CONTEXT FOREVER, Level 10 ';

You can also set it at the database level by adding: event= "10231 Trace name Context forever, Level 10" in the initialization parameters, and then restart the database.

Then remove the data from the table that has the bad block without the bad block and execute the following statement:

CREATE TABLE salvage_emp as SELECT * from corrupt_table;

The last rename generated corrupt_table is the name of the original table, and the indexes and restrictions on the table are rebuilt.

? Recovering using the Dbms_repair package

Use Dbms_repair to mark a table with bad blocks, skip the bad block when doing a full table scan, and execute the following statement:

Execute Dbms_repair. Skip_corrupt_blocks ("', '");

Then use the Exp tool or the CreateTable as Select method to remove the bad block data, then rebuild the table, index and limit on the table.

Five: The pre-discovery method of the bad block

1. If you want to detect all the tables in the database, you can use the EXP tool to export the entire database to detect bad blocks. However, this tool has some flaws, which are not detectable for the following bad blocks:

? The bad blocks above HWM will not be found.

? The bad blocks that exist in the index will not be found.

? Bad blocks in the data dictionary are not found.

2. If you are only making bad block checks on the tables that are important in the database, you can use the Analyze table tablename VALIDATE STRUCTURE CASCADE method to detect the bad block, which performs the check of the bad block, but does not mark the bad block as corrupt, The results of the detection are saved in the user trace file in the User_dump_dest directory.

3. Use Oracle's specialized tools DBV to check for bad blocks, with the following syntax:

Keyword description (default)

----------------------------------------------------

File to validate (none)

Start Start Block (the first block of a file)

End Block (last block of file)

BLOCKSIZE Logic block Size (2048)

LOGFILE output log (none)

FEEDBACK Show Progress (0)

Parfile parameter file (none)

UserID username/password (none)

SEGMENT_ID segment ID (Tsn.relfile.block) (none)

For example:

DBV file=system01.dbf blocksize=8192

Dbverify:release 9.2.0.5.0-production on Saturday November 27 15:29:13 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Dbverify-Validation is starting: FILE = system01.dbf

Dbverify-Verification Complete

Total pages Checked: 32000

Total pages processed (data): 13261

Total pages failed (data): 0

Total pages processed (index): 2184

Total pages failed (index): 0

Total pages processed (other): 1369

Total number of pages processed (segment): 0

Total number of failed pages (segment): 0

Total pages Empty: 15186

Total pages marked as damaged: 0

Total pages in import: 0

Note: Because DBV requires that file must be followed by a filename extension, if it is stored with a bare device, you must use ln to link the bare device to a file, and then use DBV to check the linked file.

Ext.: http://blog.chinaunix.net/uid-12380499-id-5750941.html

How to handle bad block problems in an Oracle database