Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to deal with the problem of bad blocks in Oracle database". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to deal with the bad block problem in the Oracle database.

One: what is the bad block of the database

First of all, 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, which is divided into three layers: cache layer,transaction layer,data layer. When we read and write to the data block, the database will check the consistency of the data block to read and write, including the type of the data block, the address information of the data block, the SCN number of the data block, and the head and tail of the data block. If inconsistent information is found, the database marks the data block as a bad block. There are two kinds of bad blocks in the database, logical bad blocks and physical bad blocks.

Second, the impact of bad blocks on the database

If a bad block occurs in the database, the alarm log file of the database will contain the following error messages: Ora-1578 and Ora-600 and trace file in bdump directory. The range of the * parameter values of Ora-600 error is [2000]-[8000]. Different values represent problems at different layers of the data block, as shown in the following table:

Range block layerCache layer 2000-4000Transaction layer 4000-6000Data layer 6000-8000

Bad blocks may affect data dictionary tables, rollback segment tables, temporary periods, user data tables, indexes, and so on. Different objects have different ways to deal with bad blocks.

Third, the cause of the bad block.

Oracle calls standard C system functions to read and write data blocks, so bad blocks can be caused by the following reasons:

1 hardware iUnix error 2 operating system iUniver error or buffering problem 3 memory or paging problem 4 disk repair tool 5 part of a data file is being overwritten 6 Oracle attempt to access an unformatted system block failed 7 data file partially overflowed 8 Oracle or operating system bug

Fourth, the treatment of bad blocks.

1. First collect the corresponding information about bad and fast.

From the AlertSID.log file or from the trace file, 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 stands for 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 the object with the bad block is:

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 above query statement, you can find out what the object with the bad block is and what type of object it is. It is important to note that if a bad block appears in the temp file, no record is returned.

3. Determine the corresponding processing method according to the object type queried in 2.

Common objects with bad blocks are:

Object 2 rollback segment 3 temporary period 4 index or partition index 5 table under 1 Sys user

The common treatment methods are:

1 restore data files 2 restore only bad block (9i or above is available) 3 save data through ROWID RANGE SCAN 4 use DBMS_REPAIR5 use EVENT

4. Introduction of specific treatment methods

The method of restoring data files:

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

The steps are as follows:

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

ALTER DATABASE DATAFILE 'name_file' OFFLINE

2) keep the data file with bad blocks, and then copy the backup data file. If the recovered data file requires a different path, execute 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) execute the following statement for the data file recovered by Online:

ALTER DATABASE DATAFILE 'name_of_file' ONLINE

* restore only bad block (available above 9i)

To use this method, the database version 9.2.0 or above is required, the catalog database with Rman is configured, the database is archived, and there is a complete physical backup.

The steps are as follows:

Use the BLOCKRECOVER command of RMAN:

Rman > run {blockrecover datafile 5 block 11pr 16;}

You can also force the use of backups prior to a SCN number to restore blocks.

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

* Save data through ROWID RANGE SCAN

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

SELECT dbms_rowid.rowid_create (1pyrrine pence0) from DUAL

2) get the * value of ROW ID in the bad block and execute the following statement:

SELECT dbms_rowid.rowid_create (1) from DUAL (1)

3) it is recommended that a temporary table store data without 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 have bad blocks to the temporary table and execute the following statement:

INSERT INTO salvage_table SELECT / * + ROWID (A) * / * FROM A WHERE rowid

=''

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

* use 10231 to diagnose events and skip bad blocks when doing a full table scan

Can be set at the session level:

ALTER SESSION SET EVENTS '10231 TRACE NA ME CONTEXT FOREVER, LEVEL 10'

You can also set it at the database level and add the following in the initialization parameters:

Event= "10231 trace name context forever, level 10", then restart the database.

Then take the data that does not exist from the table where there is a bad block, and execute the following statement:

The corrupt_table generated by CREATE TABLE salvage_emp AS SELECT * FROM corrupt_table; * * rename is the name of the original table and rebuilds the indexes and restrictions on the table.

* restore using dbms_repair package

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

Execute DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (',')

Then use exp tools or createtable as select methods to extract data without bad blocks, and then rebuild the table, indexes and restrictions on the table.

Five: the method of discovering bad blocks in advance.

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 defects that cannot be detected for bad blocks in the following situations:

Bad blocks above 1 HWM will not be found. Bad blocks in 2 indexes will not be found. 3 bad blocks in data dictionaries will not be found.

2. If you only check the bad blocks of the more important tables in the database, you can use the ANALYZE TABLE tablename VALIDATE STRUCTURE CASCADE method to detect the bad blocks. It performs the bad block check, but does not mark the bad blocks as corrupt. The test results are saved in the user trace file in the USER_DUMP_DEST directory.

3. Use Oracle's special tool dbv to check for bad blocks. The specific syntax is as follows:

Keyword description (default)-the file to be verified by FILE (none) START starting block (* blocks of files) END end block (* blocks of files) BLOCKSIZE logical block size (2048) LOGFILE output log (none) FEEDBACK display 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 number of pages checked: 32000

Total number of pages processed (data): 13261

Total number of failed pages (data): 0

Total pages processed (index): 2184

Total number of failed pages (index): 0

Total pages processed (other): 1369

Total number of pages processed (segments): 0

Total number of failed pages (segments): 0

Total number of empty pages: 15186

Total number of pages marked as damaged: 0

Total number of pages imported: 0

Note: because dbv requires that file be followed by a file extension, if you store it 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.

At this point, I believe you have a deeper understanding of "how to deal with the problem of bad blocks in the Oracle database". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.