Practical part: N repair methods for bad Oracle data blocks


The operation of Oracle database will inevitably encounter various errors, such as bad blocks in the data table. At this time, the data of your table can not be accessed. What good way can you recover it?

What, haven't you met?

Then I hope you will meet in the near future. Ha ha, make a joke ~ play and make trouble. Experience must be old!

1, Introduction

Today I'll tell you how to deal with the bad block of the data table!

Physical corruption of Oracle data blocks can generally be divided into two cases:

  • With backup, restore through RMAN
  • No backup, via DBMS_REPAIR repair


In the case of backup, this is an ideal situation. We can recover damaged blocks directly through the RMAN BLOCK MEDIA RECOVERY function.

Here, I do not recommend recovering the entire database or database files to repair these small damaged data blocks, which is a bit of a waste of time.

Refer to official documents: Block Media Recovery with RMAN


What if there is no backup? (PS: backup is more than everything!)

We can use the DBMS that comes with Oracle_ Repair package to achieve repair.

???? Note: use DBMS_ The repair package is not a complete recovery, but marks the bad block and does not access it. This part of the marked data will be lost, which is inevitable.

Refer to MOS documentation: DBMS_REPAIR SCRIPT (Doc ID 556733.1)

2, Actual combat environment preparation

1. Environment installation

Refer to my one click installation script: Oracle database one click installation has never been so simple

cd /Volumes/DBA/voracle/github/single_db
vagrant up
vagrant ssh

2. Test data preparation

Create tablespace:

create tablespace eason datafile '/oradata/orcl/eason.dbf' size 1g autoextend on;

Create user:

create user eason identified by eason default tablespace eason;
grant dba to eason;

To create a test table:

create table hyj as select * from dba_objects;

Create table index:

create index i_hyj on hyj(object_id);

3. View table related information

To view relevant information on a table segment:

select segment_name , header_file , header_block,blocks from dba_segments where segment_name ='HYJ'; 

Find out the data block containing row records:

select distinct dbms_rowid.rowid_block_number(rowid) from eason.hyj order by 1;


1232 rows selected.
select * from dba_extents where segment_name='HYJ';

Note: see header here_ Block and block_ The IDs are inconsistent. In fact, the first block in the first area of a segment is the FIRST LEVEL BITMAP BLOCK, and the SECOND LEVEL BITMAP BLOCK. These two blocks are used to manage free block s. The third block is PAGETABLE SEGMENT HEADER, which is the header in the segment_ BLOCK.

4. RMAN backup

First, let's do a full backup to demonstrate that RMAN fixes bad blocks!

run {
allocate channel c1 device type disk;
allocate channel c2 device type disk;
crosscheck backup;
crosscheck archivelog all;
sql"alter system switch logfile";
delete noprompt expired backup;
delete noprompt obsolete device type disk;
backup database include current controlfile format '/backup/backlv_%d_%T_%t_%s_%p';
backup archivelog all DELETE INPUT;
release channel c1;
release channel c2;

5. Analog bad block

Destroy the contents of 1468, 1688 and 2468 data blocks:

dd if=/dev/zero of=/oradata/orcl/eason.dbf bs=8192 conv=notrunc seek=1468 count=1
dd if=/dev/zero of=/oradata/orcl/eason.dbf bs=8192 conv=notrunc seek=1688 count=1
dd if=/dev/zero of=/oradata/orcl/eason.dbf bs=8192 conv=notrunc seek=2468 count=1

Clear the contents of buffer cache:

alter system flush buffer_cache;

Query the table hyj again. At this time, the query has reported an error and found a bad block:

select * from eason.hyj;

Of course, you can also use bbed for bad block simulation!

6. Bad block check

Here are several ways to find bad blocks:

1. Use the DBV to check the bad blocks of the current file:

dbv file=/oradata/orcl/eason.dbf blocksize=8192;

Using the DBV check, bad blocks are also found!

2. Use rman to check for bad blocks in the database:

## Check the corresponding data file
backup check logical validate datafile 5;
## Check current database
backup validate check logical database;

Combined with V $database_ BLOCK_ The corruption view is more convenient to view:


After checking with RMAN, bad blocks are also found!

3. You can also find the error through the alarm log of the database:

4. Find the corresponding bad table through the snapshot of error information, and fill in the data file ID 5 and bad block ID 1468 in turn:

SELECT tablespace_name, segment_type, owner, segment_name
FROM dba_extents
        WHERE file_id = &fileid
and &blockid between block_id AND block_id + blocks - 1;

The experimental environment is ready. Let's start the actual combat!

3, Actual combat demonstration

Today, I intend to use the above two ways to demonstrate!

1. RMAN repair

Since we have backed up before, we can directly use backup to recover bad blocks:

blockrecover datafile 5 block 1468;
blockrecover datafile 5 block 1688,2468;

Using the same method, repair the bad blocks 16882468 in turn. After the repair is successful, the query has returned to normal!

Check the bad block condition again:

backup validate check logical database;

The bad blocks have been recovered and the data has not been lost!

2,DBMS_REPAIR repair

First, still use dd to simulate bad blocks first:

dd if=/dev/zero of=/oradata/orcl/eason.dbf bs=8192 conv=notrunc seek=3333 count=1
dd if=/dev/zero of=/oradata/orcl/eason.dbf bs=8192 conv=notrunc seek=3368 count=1
dd if=/dev/zero of=/oradata/orcl/eason.dbf bs=8192 conv=notrunc seek=4000 count=1

On the premise of no backup, we can't repair the bad block losslessly, and we need to lose the data corresponding to the bad block.

1. Create a repair table to record the tables to be repaired:

dbms_repair.admin_tables (
     table_name => 'REPAIR_TABLE',
     table_type => dbms_repair.repair_table,
     action => dbms_repair.create_action,
     tablespace => 'USERS');

2. Create an Orphan Key table to record the orphaned indexes after the table block is damaged, that is, the indexes pointing to the bad block:

dbms_repair.admin_tables (
     table_name => 'ORPHAN_KEY_TABLE',
     table_type => dbms_repair.orphan_table,
     action => dbms_repair.create_action,
     tablespace => 'USERS');

3. Check the bad block, detect the damage on the object, and return the number of damaged blocks as 3:

  num_corrupt int;
  num_corrupt := 0;
  schema_name =>'EASON',
  object_name =>'HYJ',
  repair_table_name =>'REPAIR_TABLE',
  corrupt_count =>num_corrupt);
  dbms_output.put_line('number corrupt:' || to_char(num_corrupt));

4. To view damaged block information:

select object_name, block_id, corrupt_type, marked_corrupt, repair_description from repair_table;

Note: here marked_corrupt is marked as TRUE. It should be that the system is executing check_ Fix is automatically completed in the process of object_ CORRUPT_ BLOCKS. If it is marked as FALSE, fix needs to be run again_ CORRUPT_ Blocks to complete the marking of bad blocks.

5. Repair the damaged data blocks. These damaged data blocks are checked after execution_ Generated after object:

  cc number;
  dbms_repair.fix_corrupt_blocks(schema_name => 'EASON',
  object_name => 'HYJ',
  fix_count => cc);
  dbms_output.put_line('Number of blocks fixed: ' || to_char(cc));

0 bad blocks are marked, indicating CHECK_OBJECT finished marking.

6. Using dump_ ORPHAN_ The keys procedure populates orphan with index key values that point to bad blocks_ KEY_ TABLE:

   cc number;
     schema_name => 'EASON',
     object_name => 'I_HYJ', 
     object_type => dbms_repair.index_object,
     repair_table_name => 'REPAIR_TABLE',
     orphan_table_name=> 'ORPHAN_KEY_TABLE',
     key_count => cc
  dbms_output.put_line('Number of orphan keys: ' || to_char(cc));

It indicates that 202 records are damaged or lost!

Note: you must pay attention to the object here_ Name is the index name, not table_name, here dump is the corrupted index information. If the table has multiple indexes, dump needs to be performed for each index_ ORPHAN_ Keys operation.

7. Verify whether the object can be queried. The following results still show that it cannot be queried:

select count(*) from eason.hyj;

8. Skip bad blocks:

     OBJECT_NAME => 'HYJ',
     OBJECT_TYPE => dbms_repair.table_object,
     FLAGS => dbms_repair.skip_flag);

Note: 202 records are lost. The rowid of the lost record can be in orphan_ KEY_ Found in table.

9. Re index:

alter index eason.I_HYJ rebuild;

10. Verification results

So far, the data in the table can be accessed smoothly!

Since the bad block does not disappear, but is marked to skip, you can still view the bad block:


In this way, you can retrieve part of the data or the indexed value, but when you use dbv to check the data file in the table space, the damaged data block will still be displayed.

At this time, you need to export all the data of the table, rebuild the table or table space, and then import the retrieved data into the database. It is recommended to use the command expdp/impdp to completely eliminate the bad blocks detected by dbv.

Write at the end

Backup is more important than everything, and it is also the last line of defense, so please make a backup! eight hundred and eighty-six

Posted by deadoralive on Sun, 05 Dec 2021 21:40:06 -0800