Summary of Oracle data block damage recovery

Run the DBV command to check whether the data file contains bad blocks before recovery.

dbv file=d:\oracle\oradata\mydb\RONLY.DBF blocksize=8192

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

select count(*) from tablename;

1. If no backup is available:

1.1 Use exp/imp for restoration

In this case, data may be lost. In this case, you should export the data, recreate the table, and then import the data to restore the damaged data in the data block as much as possible, however, export is not allowed when there are bad blocks. the following command:

Exp test/test file=t.dmp tables=t;

The Export command reports a ORA-01578 error during execution, prompting the file with the file number and which block in the file is corrupted, such as: ORA-01578: ORACLE data block damaged file No. 4, Block No. 35)

For the above prompts, first query the objects that are damaged:

Select tablespace_name,segment_type,owner,segment_name From dba_extents Where file_id=4 and 35 between block_id and block_id+blocks-1;

If the damaged block is an index, it can be solved by re-indexing. If the damaged block is the data segment_type is table), you can set the following internal event to skip the bad block for the Exp operation.

Alter session set events=’10231 trace name context forever,level 10’;

Then run the Export command again to export the relevant Table, then run the Drop Table command to delete the relevant Table, and then recreate the Table and finally import the data.

1.2 use DBMS_REPAIR for restoration

Using DBMS_REPAIR also results in data loss. I will not describe it in detail here. If you are interested, you can view the oracle online documentation.

2. Restore with Rman:

First, the latest backup set of Rman must exist, and then execute the following command:

RMAN>backup validate datafile 4;

Check whether the data file no. 4 contains bad Blocks

Run the query:

select * from v$database_block_corruption where file#=4;

If file 4 contains bad blocks, the damaged blocks are displayed in the result set. Execute the following command to restore the damaged blocks:

RMAN>blockrecover datafile 4 block 35 from backupset;

After the command is executed, the broken blocks can be restored without causing data loss. However, the database must be running in archive mode. Otherwise, RMAN cannot be used and the latest database backup has been performed through RMAN.

3. Use bbed for restoration

When using bbed for restoration, you must copy data files.

Bbed is short for block browse edit. It is a tool used to directly view and modify data in data files.

Both windows and linux have

However, compilation is required in linux:

Then add $ ORACLE_HOME/rdbms/lib to the PATH of the environment variable, and then you can directly run bbed in the command.

The default password For BBED is blockedit. For Oracle Internal Use only, be cautious when using Oracle without technical support.

[Oracle @ test oracle] $ cd $ ORACLE_HOME/rdbms/lib [Oracle @ test lib] $ make-f ins_rdbms.mk $ ORACLE_HOME/rdbms/lib/bbed

After entering bbed, you can use help to view help:

BBED> help

1. In-depth analysis of Oracle data block principles
2. Research on the backup and recovery policies of Oracle databases
3. Oracle database backup and recovery