oracle| Data | database | questions
This article mainly describes how to deal with the problem of bad blocks in the Oracle database, for the bad block produced on different objects, the processing method will be different, this article will make a general introduction to these methods. Because the database is running for a long time, due to the aging of the hardware device, the chance of bad block will become more and more, so, as a DBA, how to solve the problem of the database has become an important issue.
One: What is the bad block of the 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. As we read the data blocks, the database checks for the data blocks that are read and written, including the type of data block, the address information of the block, the SCN number of the data block, and the head and tail of the block. If an inconsistent information is found, the database will mark the block as a bad block. The bad block of the database is divided into two kinds, logical bad block and physical bad block.
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 alarm log file of the database: Ora-1578 and Ora-600 and trace file in Bdump directory, where the range of the first parameter values for Ora-600 error is [2000] -[8000], different values represent the problems 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
The objects that are affected by the bad blocks may be data dictionary tables, rollback segment tables, temporary segments, user data tables, and indexes. Different objects produce bad blocks after the processing methods are different.
Three: The cause of the bad block
Oracle invokes standard C's system functions to read and write data blocks, so bad blocks are likely to occur for several reasons:
Ø I/O error in hardware
Ø I/O error or buffering problem for operating system
Ø Memory or paging problem
Ø Disk Repair Tool
Ø a part of a data file is being overwritten
Øoracle attempt to access an unformatted system block failed
Ø partial overflow of data files
Øoracle or OS bugs
Four: The handling method of the bad block
1. Collect the appropriate information about bad 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 the RELATIVE_FNO
AFN says it's 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 of the bad block is:
SELECT Tablespace_name, Segment_type, owner, Segment_name, partition_name from dba_extents WHERE file_id = <AFN> D <BL> between block_id and block_id + blocks–1;
The above query statement can be used to find out what the object of the current bad block is and what type of object it is. 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 appear in bad blocks are:
Objects under the Øsys user
Ø Rolling back segment
Ø Temporary Paragraph
Ø index or partition index
Ø table
Common methods of treatment are:
Ø Recover Data files
Ø only bad blocks are recovered (more than 9i version available)
Ø save data through rowID RANGE SCAN
Ø using Dbms_repair
Ø Use Event
4. Introduction to the specific processing methods
Ø Restore Data File method:
If the database is archived and has a full physical backup, you can use this method to recover.
The steps are as follows:
1) First offline the affected data file, execute the following statement:
ALTER DATABASE datafile ' name_file ' OFFLINE;
2 Keep a bad block of data files, and then 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 of the data file, execute the following statement:
ALTER DATABASE datafile ' name_of_file ' ONLINE;
Ø only bad blocks are recovered (more than 9i version available)
Using this method requires the database version to be 9.2.0, requiring the Rman catalog database to be configured, the database to be archived, and a full physical backup.
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 certain SCN number to recover a block of data.
Rman>run{blockrecover datafile 5 block 11,16 restore until sequence 8505;}
Ø save data through 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>,<rfn>,<bl>,0) from DUAL;
2 to get the maximum value of the row ID in the bad block, execute the following statement:
SELECT dbms_rowid.rowid_create (1,<obj_id>,<rfn>,<bl>+1,0) from DUAL;
3) suggest a temporary table to store those without bad blocks of data, execute the following statement:
CREATE TABLE salvage_table as SELECT * from Corrupt_tab Where 1=2;
4 Save the data in the temporary table that does not have bad blocks, and execute the following statement:
INSERT into salvage_table SELECT/*+ ROWID (a) * * from <owner.tablename> A WHERE ROWID < ' <low_rid> ';
INSERT into salvage_table SELECT/*+ ROWID (a)/* from <owner.tablename> A WHERE ROWID >= '
5 Rebuild the index on the table according to the data of the temporary table, and limit.
Ø use 10231 diagnostic events to skip bad blocks while doing full table scans
You can set at the session level:
ALTER session SET EVENTS ' 10231 TRACE NAME context FOREVER, Level 10 ';
You can also set at the database level, add in initialization parameters: event= "10231 Trace name Context forever, Level 10", and then restart the database.
Then, from the table that has the bad block, remove the data that does not have a 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 rebuilds the indexes and restrictions on the table.
Ø use Dbms_repair Package for recovery
Use Dbms_repair to mark a table with bad blocks, skip bad blocks when doing a full table scan, and execute the following statement:
Execute Dbms_repair. Skip_corrupt_blocks (' <schema> ', ' <tablename> ');
You then use the Exp tool or the CreateTable as Select method to remove the bad block data, and then recreate the table, the index on the table, and the restrictions.
Five: The method of discovering bad blocks in advance
1. If you want to detect all tables in the database, you can use the EXP tool to export the entire database to detect bad blocks. However, there are some flaws in this tool that are not detectable for bad blocks of the following situations:
The bad blocks above ØHWM won't be found.
Ø the bad blocks in the index are not to be found
Ø the bad blocks in the data dictionary are not to be found.
2. If you are only checking bad blocks for tables that are more important in the database, you can use the Analyze table tablename VALIDATE STRUCTURE CASCADE method to detect bad blocks, which perform bad block checks, but do not mark bad blocks as corrupt. The results of the detections are saved in the user trace file in the User_dump_dest directory.
3. Using Oracle's Specialized tool DBV to check for bad blocks, the syntax is as follows:
Keyword description (default)
----------------------------------------------------
File to validate (none)
Start Block (first block of file)
End Block (last block of file)
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-Authentication is starting: FILE = system01.dbf
Dbverify-Verification Complete
Total pages Checked: 32000
Total pages processed (data): 13261
Total number of pages failed (data): 0
Total pages processed (index): 2184
Total number of pages failed (index): 0
Total number of pages processed (other): 1369
Total number of pages processed (paragraph): 0
Total number of failed pages (segments): 0
Total number of empty pages: 15186
Total number of pages marked as corrupted: 0
Total pages to import: 0
Note: Because DBV requires that file be followed by a filename extension, if you store it with a bare device, you must use ln to link the bare device to a file, and then check the linked file with dBV.
