Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

RMAN Corrupted Blocks Tips

Expert Oracle Tips by Burleson Consulting

March 25, 2012

RMAN of Corrupted Blocks

In this situation, our DBA Bob decides to recover only corrupted blocks of the datafile using the block media recovery feature of RMAN.

First of all, Bob checks the alert.log fileand a trace file which was generated for this corruption as follows:

Hex dump of (file 4, block 76) in trace file c:\oracle\product\10.2.0\admin\db1\udump\db1_ora_2968.trc
Corrupt block relative dba: 0x0100004c (file 4, block 76)
Bad check value found during buffer read
Data in bad block:
 type: 6 format: 2 rdba: 0x0100004c
 last change scn: 0x0000.00086aeb seq: 0x3 flg: 0x06
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x6aeb0603
 check value in block header: 0xa13a
 computed block checksum: 0xb
Reread of rdba: 0x0100004c (file 4, block 76) found same corrupted data

By opening the trace file with the .trc extension that is written in alert.log file, he gets more information:

Corrupt block relative dba: 0x0100004c (file 4, block 76)
Bad check value found during buffer read
Data in bad block:
 type: 6 format: 2 rdba: 0x0100004c
 last change scn: 0x0000.00086aeb seq: 0x3 flg: 0x06
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x6aeb0603
 check value in block header: 0xa13a
 computed block checksum: 0xb
Reread of rdba: 0x0100004c (file 4, block 76) found same corrupted data

To get more information, Bob uses the dbv (dbverify) utility to get a list of all the corrupted blocks:

C:\>dbv file=c:\oracle\product\10.2.0\oradata\db1\users01.dbf
DBVERIFY: Release 10.2.0.1.0 - Production on Sat Oct 17 20:50:00 2009
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
 
DBVERIFY - Verification starting : FILE = c:\oracle\product\10.2.0\oradata\db1\USERS01.DBF
Page 76 is marked corrupt
Corrupt block relative dba: 0x0100004c (file 4, block 76)
Bad check value found during dbv:
Data in bad block:
 type: 6 format: 2 rdba: 0x0100004c
 last change scn: 0x0000.00086aeb seq: 0x3 flg: 0x06
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x6aeb0603
 check value in block header: 0xa13a
 computed block checksum: 0xb
 
DBVERIFY - Verification complete
Total Pages Examined         : 640
Total Pages Processed (Data) : 29
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 2
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 35
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 573
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 0
Highest block SCN            : 551659 (0.551659)
C:\>

It is seen from the above result that Bob has only one corrupted block.

Note: If the count of corrupted data blocks is not too large, then use the blockrecover command and specify the corrupted blocks.  If there are a lot of corrupted data blocks and we do not want to specify them one by one, then you have another option.  Run the blockrecover corruption list command after running the backup or backup validate command. Upon running one of these commands, the v$database_block_corruption view will be populated with a list of the corrupted data blocks.

Bob then runs the backup validate command and populates the v$database_block_corruption view:

SQL>
select * from
 v$database_block_corruption;
no rows selected

RMAN> backup validate datafile 4;
 
SQL>
 select * from
 v$database_block_corruption;
 
     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
         4         76          1                  0 CHECKSUM

Bob uses the first method to recover the corrupted block by running the blockrecover command as follows:

RMAN> blockrecover datafile 4 block 76;
 
Starting blockrecover at 17-OCT-09
using target database control file instead of recovery catalog
 
channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00004
channel ORA_DISK_1: reading from backup piece
C:\ORACLE\product\10.2.0\flash_recovery_area\db1\backupset\2009_10_17\
o1_mf_nnndf_tag20091017t163201_5fmbsl9r_.bkp
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:02
 
starting media recovery
archive log thread 1 sequence 3 is already on disk as file
C:\ORACLE\product\10.2.0\flash_recovery_area\db1\archivelog\2009_10_17\
o1_mf_1_3_5fmbtrk2_.arc
..................
..................
media recovery complete, elapsed time: 00:00:01
Finished blockrecover at 17-OCT-09
RMAN>
SQL>
select * from
 test_corruption;
 
STR
----------
Test

This command restores the specific data block from the backup and recovers the datafile by applying archived redo log files.

Note:  The blockrecover command is not used anymore in Oracle 11g. The new syntax for block recovery is the recover?datafile?

RMAN of Corrupted Data Blocks

Another option to recover this block is running the blockrecover corruption list command. This command recovers all corrupted data blocks that are written in the v$database_block_corruption view. Starting from Oracle 11g, the v$database_block_corruption view is updated when any process or database utility encounters the data block corruption. Moreover, you can validate the specific block at the specific datafile using the validate datafile block command.

Here, Bob tries to recover the corrupted block using this command as follows:

RMAN> blockrecover corruption list;
restoring blocks of datafile 00004
......
......
starting media recovery
Finished blockrecover at 17-OCT-09
 
SQL>
select * from
 test_corruption;
 
STR
----------
Test

As can be seen, blockrecover corruption list recovered all the corrupted data blocks.

It is possible to recover more than one block of multiple datafiles.  It is also possible to recover the data block using the tablespace name and Data Block Address (DBA). To test this, we create two tablespaces and three tables, corrupt all tables and then recover them using different methods.

Create two tablespaces and three tables:

 SQL>
create
 tablespace tbs_one datafile 'c:\tbs_one.dbf' SIZE 1M;
Tablespace created.
 
SQL>
create
 tablespace tbs_two datafile 'c:\tbs_two.dbf' size 1M;0
Tablespace created.
 
SQL>
create
 table tbl_one (str varchar2(10)) tablespace users;
Table created.
 
SQL>
drop
 table tbl_one;
Table dropped.
 
SQL>
create
 table tbl_one (str varchar2(10)) tablespace  tbs_one;
Table created.
 
SQL>
create
 table tbl_two (str varchar2(10)) tablespace tbs_two;
Table created.
 
SQL>
create
 table tbl_three (str varchar2(10)) tablespace users;
Table created.

SQL>
insert into
 tbl_one values('test');
1 row created.
 
SQL> insert into
 tbl_two values('test');
1 row created.
 
SQL>
insert into
 tbl_three values('test');
1 row created.
 
SQL> commit;
 Commit complete.
SQL>

RMAN> backup database plus archivelog;

You have created two tablespaces with three tables and backed up the whole database.  Now we corrupt all three datafiles by opening them in hexadecimal editor and changing the first letter of the row (the letter t), save the file and exit.

To see the corruption, run the backup validate database command from RMAN and check the v$database_block_corrupton view.

RMAN> backup validate database;
 
SQL>
select * from
  v$database_block_corruption;
 
     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTION
---------- ---------- ---------- ------------------ ----------
         5         13          1                  0 CHECKSUM
         6         13          1                  0 CHECKSUM
         4         69          1                  0 CHECKSUM
 
SQL>
select * from
 tbl_one;
 
STR
----------
test
 
SQL> select * from
 tbl_two;
 
STR
----------
test
 
SQL> select * from
 tbl_three;
STR
----------
test

Here, although you have corrupted the datafiles, you are still able to query the tables. In fact, the rows are queried from the database buffer cache, not from datafile.  If you flush the buffer cache, you will not be able to query the tables because now we query them directly from the datafiles.

SQL> alter
 system flush buffer_cache;
System altered.
 
SQL> select * from
 tbl_one;
select * from tbl_one
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 13)
ORA-01110: data file 5: 'c:\tbs_one.dbf'
 
SQL> select * from
 tbl_two;
select * from tbl_two
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 13)
ORA-01110: data file 6: 'c:\tbs_two.dbf'
 
SQL> select * from
 tbl_three;
select * from tbl_three
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 69)
ORA-01110: data file 4:
'C:\ORACLE\product\10.2.0\oradata\db1\users01.dbf'
SQL>

Now recover the first two tablespaces by using the datafile parameter in one command line.  The third tablespace will be recovered using the tablespace parameter.  Now recover all the corrupted data blocks: 

RMAN> blockrecover datafile 5 block 13 datafile 6 block 13;
Starting blockrecover at 18-OCT-09
restoring blocks of datafile 00005
restoring blocks of datafile 00006
............
............
starting media recovery
media recovery complete, elapsed time: 00:00:03
Finished blockrecover at 18-OCT-09
RMAN>

As shown here, there are recovered two different datafiles using one command. Now, using information about Data Block Address (DBA) written in the alert.log file, recover the third tablespace.  Here is the information from the alert.log file:

Hex dump of (file 4, block 69) in trace file c:\oracle\product\10.2.0\admin\db1\udump\db1_ora_2460.trc
Corrupt block relative dba: 0x01000045 (file 4, block 69)
Bad check value found during buffer read
Data in bad block:
 type: 6 format: 2 rdba: 0x01000045
 last change scn: 0x0000.00086381 seq: 0x3 flg: 0x06
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x63810603
 check value in block header: 0xca6
 computed block checksum: 0x56
Reread of rdba: 0x01000045 (file 4, block 69) found same corrupted data
Sun Oct 18 14:59:27 2009
Corrupt Block Found
         tsn = 4, tsname = users
         rfn = 4, blk = 69, rdba = 16777285
         objn = 51349, objd = 51349, object = tbl_three, subobject =
         segment owner = sys, segment type = Table Segment
Sun Oct 18 15:18:11 2009

The data block number of the corrupted block is 16777285 and it resides in the users tablespace.  Now using these values, recover the corrupted block as follows:

RMAN> blockrecover tablespace users dba 16777285;
Starting blockrecover at 18-OCT-09
using channel ORA_DISK_1
..............
..............
Finished blockrecover at 18-OCT-09
RMAN>

Next, query all tables to see whether the corrupted data blocks have been recovered or not using the following command:

SQL> select * from
   tbl_one
  2  union all
  3  select * from
   tbl_two
  4  union all
  5  select * from
   tbl_three;
 
STR
----------
test
test
test
SQL>

 

 

 

 
 
 
Get the Complete
Oracle Backup & Recovery Details 

The landmark book "Oracle Backup & Recovery: Expert secrets for using RMAN and Data Pump " provides real world advice for resolving the most difficult Oracle performance and recovery issues. Buy it for 40% off directly from the publisher.
 


 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright ? 1996 -  2014

All rights reserved by Burleson

Oracle ? is the registered trademark of Oracle Corporation.


 

��
 
 
 
 

 
 
 

 
Oracle performance tuning software 
 
oracle dba poster
Oracle Linux poster