Database: Bad Block ORA-01115 in database, ORA-02072, ORA-01578

To solve the problem of Bad blocks in a database, the processing record is as follows:

 

1.Based on the error message, use DBV to check whether the file contains bad blocks. If yes, continue and use the following SQL statement to query whether the bad block is index or data, if it is an index, delete and re-create the index. If it is data, it is troublesome and you need to perform the next step.

Select segment_name, segment_type from dba_extents
Where file_id = <file_number> and <block_number> between block_id
And block_id + blocks-1

2.Before doing this, back up the table where the data is located, such as exp or create newtable. Note that the second method backs up indexes and constraints. If you are lucky, you can back up all the data. It's easy to delete, recreate, and bring in the original table. If you are lucky, if some data cannot be exported in the bad partition, you must use event 10231 to skip the bad partition and get the good data first. The SQL statement is as follows:

Alter system set events '10231 trace name context forever, level 10'

Create new table or exp to export data. Use the following SQL statement to close the diagnosis event after export.

Alter system set events '''10231 trace name context off''

3.Step 2 if you are lucky, data may be lost. To ensure that the data is not lost, there are three methods:

A. if the database archive is full, take out the most recent backup of the data file in which the table is located, use DBV to check whether there are no bad blocks, use it to restore the data of the official database. The procedure is "alter database datafile <file_name> offline" --> copy the bad data file --> copy the backup file --> "Recover datafile <file_name>" --> "alter Database datafile <file_name> online ".

B. Find the person in charge of the AP and spell out the lost data based on the data association between tables.

C. if you are a 9i Database and use RMAN for backup, RMAN provides a command to recover bad blocks from the RMAN backup. The database can be in the online status during restoration. The command is as follows:

Blockrecover datafile <file_number> block <block_number> from backupset;

4.In fact, the above recovery actions can also be used as the recovery of the system tablespace. If an undo segment breaks down, you can use the following steps:

A. First back up the rollbacksegment script (especially in the dictionary mode), stop the listener, and disconnect all sessions to see if there is any activity in V $ transaction, and delete it if any.

B. Enable the database to be restored in restrict mode by re-establishing the Undo tablespace and rollback segment, completing the establishment, and putting the rollback segment online.

C. After confirming that the database can be enabled properly, remove the old rollback segment offline and the old tablespace.

If the database cannot be opened, the backup undo file must be used for recovery, minimizing data loss. In addition, Oracle provides an implicit parameter "_ corrupt upted_rollback_segments = (damaged segment name)", which forcibly opens the database. However, when the database is inconsistent, after opening the database, create a database exp, rebuild the database, and import imp. Otherwise, the recovered database may be at great risk.

After writing so much, I found out how to explain the bad block to the database restoration. In fact, the processing of Bad blocks is easy to solve as long as the backup work is well done. If there is no backup, it depends on luck.

 

 

 

Appendix: usage instructions of the dbverify Tool
The main purpose of the dbverify tool is to check the physical structure of the data file, including whether the data file is damaged, whether there are logical Bad blocks, and what types of data contained in the data file.

The dbverify tool can verify online or offline data files. Data files can be accessed regardless of whether the database is opened or not.

 

1. You can use help to view DBV Command Parameters

C:/> DBV help = y

Dbverify: Release 11.1.0.7.0-production on Tuesday December 15 23:35:24 2009

Copyright (c) 1982,200 7, Oracle. All rights reserved.

Keyword description (default)

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

File the file to be verified (none)

Start start block (the first block of the file)

End end block (the last block of the file)

Blocksize logical block size (8192)

Logfile output log (none)

Display progress of feedback (0)

Parfile parameter file (none)

Userid username/password (none)

Segment_id segment ID (TSN. relfile. Block) (none)

Maximum chunks to be verified by high_scn (none)

(Scn_wrap.scn_base or SCN)

 

 

2. Instructions for use

Dbverify
File input file name
Start block address
End end block address
Blocksize specifies the blocksize.
Logfile specifies the log file
Display Feedback Progress
Help help
Parfile parameter file

3. Simple syntax

3.1 DBV file = t_db1.dbf feedback = 100

 

E:/APP/Administrator/oradata/orcl> DBV file = users01.dbf blocksize = 8192

Dbverify: Release 11.1.0.7.0-production on Tuesday December 15 23:54:55 2009

Copyright (c) 1982,200 7, Oracle. All rights reserved.

 

Dbverify-Start verification: file = E:/APP/Administrator/oradata/orcl/users01.dbf

 

 

Dbverify-verification completed

Total number of checked pages: 640

Total number of processed pages (data): 91

Total number of failed pages (data): 0

Total number of processed pages (INDEX): 33

Total number of failed pages (INDEX): 0

Total number of processed pages (Others): 496

Total number of processed pages (segments): 0

Total number of failed pages (segments): 0

Total number of blank pages: 20

Total number of pages marked as damaged: 0

Total number of inbound pages: 0

Total number of encrypted pages: 0

Maximum block SCN: 904088 (0.904088)

 

Note: To enter the directory where data files are stored, run the command. Otherwise, no data file is found.

 

The 3.2 DBV tool is also used when the database is opened to verify the usage of the specified segment:

DBV userid = username/password segment_id = TSN. relfile. Block

Dbverify Verification Section
Userid specifies the user name and password
Segment_id specifies the verification segment
Logfile specifies the log file
Display Feedback Progress
Help help
Parfile parameter file

 

In this method, you need to query the table space ID, the data file ID where the field header is located, and the tablespace ID where the field header is located. To obtain this information, you can query the sys_dba_segs view by using sys. Note that the sys_user_segs view provided in the Oracle document can only query the segments of SYS users. to query segment information of common users, you need to access sys_dba_segs.

Itpub personal space | * Ed

 

--------------------------------------------------------------------------------
B + pnj )_
SQL> Create Table Dave (ID number );

The table has been created.

SQL> select tablespace_id, header_file, header_block from sys_dba_segs where segment_name = 'Dave ';

Tablespace_id header_file header_block

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

0 1 89976

E:/APP/Administrator/oradata/orcl> DBV userid = system/admin segment_id = 0.1.89976

 

Dbverify: Release 11.1.0.7.0-production on Wednesday December 16 00:19:02 2009

Copyright (c) 1982,200 7, Oracle. All rights reserved.

Dbverify-Start verification: segment_id = 0.1.89976

Dbverify-verification completed

 

Total number of checked pages: 9

Total number of processed pages (data): 0

Total number of failed pages (data): 0

Total number of processed pages (INDEX): 0

Total number of failed pages (INDEX): 0

Total number of processed pages (Others): 9

Total number of processed pages (segments): 0

Total number of failed pages (segments): 0

Total number of blank pages: 0

Total number of pages marked as damaged: 0

Total number of inbound pages: 0

Total number of encrypted pages: 0

Maximum block SCN: 912858 (0.912858)

 

Note: This method requires the database to be open.

 

4. Verify data copy

Because DBV can verify the data file when the instance is closed, DBV can also verify the copy of the data file. This copy refers to the data file copied through the Copy command of RMAN or the command CP of the operating system, rather than the backup set format generated by RMAN.

 

--------------------------------------------------------------------------------
E:/apps/Administrator/oradata/orcl> DBV file = users01bak. DBF blocksize = 8192

Dbverify: Release 11.1.0.7.0-production on Wednesday December 16 00:30:17 2009

Copyright (c) 1982,200 7, Oracle. All rights reserved.

Dbverify-Start verification: file = E:/APP/Administrator/oradata/orcl/users01bak. DBF

Dbverify-verification completed

Total number of checked pages: 640

Total number of processed pages (data): 91

Total number of failed pages (data): 0

Total number of processed pages (INDEX): 33

Total number of failed pages (INDEX): 0

Total number of processed pages (Others): 496

Total number of processed pages (segments): 0

Total number of failed pages (segments): 0

Total number of blank pages: 20

Total number of pages marked as damaged: 0

Total number of inbound pages: 0

Total number of encrypted pages: 0

Maximum block SCN: 904088 (0.904088)

 

 

Notes:

1. For the dbverify tool, the higher version can automatically identify the lower version of the database. For example, if the 11g DBV accesses the 9i Database, the lower version of DBV accesses the higher version and reports the following errors:

Dbverify-verification starting: file = E:/Oracle/oradata/Dave/test01.dbf

Imported page 1-Possible Media damage

 

2. view the data file number and block number of the bad data block. You can perform a full table scan on the table, for example:

Select count (*) from tablename;

 

If there is a bad block, an error will be reported during scanning.