This article mainly describes how to deal with bad blocks in the Oracle database. For bad blocks, the method of processing is different, and this article will roughly introduce these methods. Because the database runs longer, due to the aging of hardware devices, the chances of bad blocks will get more and bigger, so as a DBA, how to solve the bad block problem of the database, it has become an important topic.
One: What is the bad block of a database
First let's take a look at the format and structure of the database block.
Database data blocks have fixed formats and structures, divided into three layers: Cache Layer, Transaction Layer, Data Layer. When we read the write write operation on the data block, the database will consistently check the data block to be read, including: the type of data block, the address information of the data block, the SCN number of the data block, and data The head and tail of the block. If there is any inconsistent information, the database will mark this data block as a bad block. Database's bad blocks are divided into two, logical bad blocks and physical disadvantages.
Second: The impact of bad blocks on database
If the database appears bad blocks, there is some error information in the alarm log file of the database: ORA-1578 and ORA-600 and TRACE FILE IN BDUMP DIRECTORY, where ORA-600 error has the first parameter value of [[ 2000] - [8000], different values represent problems with 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 affecting bad blocks may be data dictionary tables, rollback segments, temporal sections, user data tables, and indexes. Different objects have the same processing method after the bad blocks.
Three: The reason for bad blocks
Oracle calls the system function of the standard C, read and write the data block, so bad blocks may be produced by the following reasons:
Ø Hardware I / O error
Ø I / O error or buffer problem of operating system
Ø Memory or paging problem
Ø Disk Repair Tool
Ø Part of a data file is being covered
Ø Oracle tries to access an unformatted system block failed
Ø Data file part overflow
Ø BUG of Oracle or operating system
4: Decades of bad blocks
1. Collect the corresponding information about bad fast, from the alertsid.log file or from the trace file, find some information, for example: the following information:
ORA-1578 File # (rfn) block #
ORA-1110 File # (AFN) Block #
ORA-600 File # (AFN) Block #
Where RFN is Relative_FNO
AFN said 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 of the abrupt block:
SELECT tablespace_name, segment_type, owner, segment_name, partition_name FROM dba_extents WHERE file_id =
3. Determine the corresponding processing method according to the type of object being queried in 2
Common objects with bad blocks are:
Ø SYS users under the user
Ø Rollback segment
Ø Times
Ø Index or partition index
Ø Table
Commonly used processing methods are:
Ø Restore data files
Ø only recover bad block (9i or later version available)
Ø Save data through Rowid Range Scan
Ø Use dbms_repair
Ø Using Event
4. Introduction of specific processing methods
Ø Restore data file method:
If the database is in the archive, and there is a complete physical backup, you can use this method to recover.
Proceed as follows:
1) First offline affected data file, perform the following statement:
Alter Database DataFile 'Name_File' Offline;
2) Keep a data file with a bad block, then copy the backup data file. If the recovered data file requires different paths, the following statement is performed:
Alter Database Rename File 'Old_name' to 'New_Name';
3) Restore data files, perform the following statement:
Recover datafile 'name_of_file';
4) ONLINE recovered data file, perform the following statement:
ALTER DATABASE DATAFILE 'NAME_OF_FILE' Online;
Ø only recover bad block (9i or later version available)
Using this method requires the database version
9.2.0
Although the Catalog database of RMAN is required, the database is archived, and there is a complete physical backup.
Proceed as follows:
Use the RMAN's blockRecover command:
Rman> Run {BlockRecover DataFile 5 block 11,16;}
You can also force a backup before an SCN number to restore the data block.
Run> 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, do the following statement:
SELECT DBMS_ROWID.ROWID_CREATE (1,
2) Get the maximum value of the ROW ID in the bad block, perform the following statement:
Select DBMS_ROWID.ROWID_CREATE (1,
CREATE TABLE SALVAGE_TABLE AS SELECT * from Corrupt_Tab Where 1 = 2;
4) Save those data that do not have bad blocks into the temporary table, perform the following statement:
INSERT INTO SALVAGE_TABLE SELECT / * ROWID (A) * / * from
INSERT INTO SALVAGE_TABLE SELECT / * ROWID (A) * / * from
5) According to the data reconstruction table in the temporary table, the index on the table is restricted.
Ø Use 10231 diagnostic events, skip the bad block when doing a full mete scan
Can set at the session level:
Alter Session Set Events '10231 Trace Name Context Forever, Level 10'
You can also set on the database level, add: event = "10231 Trace Name Context Forever, Level 10", then restart the database.
Then remove the data that does not have a bad block from the table where there is a bad block, performs 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 index and restrictions on the table.
Ø Recovery using the DBMS_REPAIR package
Use the dbms_repair to mark a table with a bad block, skip the bad block when doing a full table scan, perform the following statement:
Execute dbms_repair.skip_corrupt_blocks ('
Then use the EXP tool or the CreateTable as SELECT to remove no bad block data, then rebuild the table, the index and restrictions on the table.
5: Method for pre-discovered bad blocks
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, and the bad blocks in the following are not detected:
Ø The bad blocks above HWM will not find
Ø The bad block exists in the index will not find
Ø The bad block in the data dictionary will not find
2. If it is only a bad block check for a table in the database, you can use the method of the Analyze Table TableName Validate Structure Cascade to detect the bad block, which performs a bad block check, but will not mark the bad block for Corrupt, and the results of the test are saved. User_dump_dest directory in the user trace file.
3. Use Oracle's specialized tool DBV to check the bad blocks, the specific syntax is as follows:
Keyword description (default)
-------------------------------------------------- - File to verify the file (none)
START start block (the first block of the file)
END End Block (the last block of the file)
BlockSize logic block size (2048)
Logfile output log (none)
FEEDBACK display progress (0)
Parfile parameter file (none)
Userid username / password (no)
Segment_ID segment ID (TSN.Relfile.block) (None)
E.g:
DBV file = system01.dbf blocksize = 8192
DBVERIFY: RELEASE
9.2.0
.5.0 - Production ON Saturday November 27:29:13 2004
CopyRight (C) 1982, 2002, Oracle Corporation. All Rights Reserved.
DBVERIFY - Verification is starting: file = system01.dbf
DBVERIFY - Verification Completion
Total number of pages: 32000
Total number of pages (data): 13261
Failure page Total number (data): 0
Total number of processed pages (index): 2184
Failure page Total (index): 0
Total number of pages of processing (others): 1369
Total total page (segment): 0
Failure top number (segment): 0
Empty page Total number: 15186
Total number of pages marked as damaged: 0
Outstanding page total number: 0
Note: Because DBV requires File, you must be a file extension, so if you store nude devices, you must use the LN link naked device to a file, and then check this link file with DBV.
