Wednesday, March 20, 2013

How to recover contiguous corrupted data blocks using 'recover datafile' RMAN syntax

In this post I want to simulate a corrupt block recovery: honestly it happened to me only two or three times several years ago and it's generally caused by the I/O subsystem or because of software bugs.
Naturally you should consider this kind of recovery when you have few corrupt blocks in a large datafile otherwise you should consider other familiar kind of recovery.
To simulate a recover of corrupt blocks I begin to create first a new tablespace formed by one small datafile, a user, a table and some rows:
SQL> create tablespace marcov datafile '/home/oracle/app/oracle/oradata/orcl/marcov01.dbf' size 1M autoextend on next 5M maxsize 50M;

Tablespace created.

SQL> DROP USER MARCOV CASCADE;
DROP USER MARCOV CASCADE
          *
ERROR at line 1:
ORA-01918: user 'MARCOV' does not exist


SQL> CREATE USER MARCOV
  2  IDENTIFIED BY MARCOV
  3  DEFAULT TABLESPACE MARCOV
  4  TEMPORARY TABLESPACE TEMP
  5  PROFILE DEFAULT
  6  ACCOUNT UNLOCK;

User created.

SQL> GRANT CONNECT, CREATE TABLE, CREATE SESSION TO MARCOV;

Grant succeeded.

SQL> ALTER USER MARCOV QUOTA UNLIMITED ON MARCOV;

User altered.

SQL> CREATE TABLE MARCOV.T1 (a number, b char(1000));

Table created.

SQL> insert into MARCOV.T1 select level, dummy from dual
  2  connect by level <= 1000;

1000 rows created.

SQL> commit;

Commit complete.
Let's update the statistics on this new table...
SQL> BEGIN
  2  SYS.DBMS_STATS.GATHER_TABLE_STATS (
  3  OwnName        => 'MARCOV'
  4  ,TabName        => 'T1'
  5  ,Estimate_Percent  => 1
  6  ,Method_Opt        => 'FOR ALL COLUMNS SIZE 1'
  7  ,Degree            => 4
  8  ,Cascade           => TRUE
  9  ,No_Invalidate     => FALSE);
 10  END;
 11  /

PL/SQL procedure successfully completed.
... and let's see how many blocks this segment has and how many are used:
SQL> SELECT NUM_ROWS, BLOCKS
  2  FROM SYS.DBA_TABLES
  3  WHERE TABLE_NAME = 'T1'
  4  AND OWNER = 'MARCOV';

  NUM_ROWS     BLOCKS
---------- ----------
      1000        244

SQL> select header_block, blocks, extents from dba_segments where segment_name='T1';

HEADER_BLOCK     BLOCKS    EXTENTS
------------ ---------- ----------
          10        256         17
From a sqlplus session I want to dump some blocks of the new datafile using the following command:
SQL> alter system dump datafile '/home/oracle/app/oracle/oradata/orcl/marcov01.dbf' block min 1 block max 10;

System altered.
and few of them are reported here (I have omitted several lines):
vi /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_7255.trc
...
buffer tsn: 40 rdba: 0x02c00003 (11/3)
scn: 0x0000.00e2a62a seq: 0x01 flg: 0x04 tail: 0xa62a1e01
frmt: 0x02 chkval: 0x4dcc type: 0x1e=KTFB Bitmapped File Space Bitmap
Hex dump of block: st=0, typ_found=1
...
buffer tsn: 40 rdba: 0x02c00004 (11/4)
scn: 0x0000.00e2a047 seq: 0x01 flg: 0x04 tail: 0xa0471e01
frmt: 0x02 chkval: 0x823c type: 0x1e=KTFB Bitmapped File Space Bitmap
Hex dump of block: st=0, typ_found=1
...
buffer tsn: 40 rdba: 0x02c00005 (11/5)
scn: 0x0000.00e2a049 seq: 0x01 flg: 0x04 tail: 0xa0491e01
frmt: 0x02 chkval: 0xc235 type: 0x1e=KTFB Bitmapped File Space Bitmap
Hex dump of block: st=0, typ_found=1
...
buffer tsn: 40 rdba: 0x02c00006 (11/6)
scn: 0x0000.00e2a04b seq: 0x01 flg: 0x04 tail: 0xa04b1e01
frmt: 0x02 chkval: 0x022e type: 0x1e=KTFB Bitmapped File Space Bitmap
Hex dump of block: st=0, typ_found=1
...
Running DBVERIFY on the datafile no error or corrupt block is detected:
[oracle@localhost trace]$ dbv file=/home/oracle/app/oracle/oradata/orcl/marcov01.dbf blocksize=8192

DBVERIFY: Release 11.2.0.2.0 - Production on Mon Mar 18 07:29:48 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = /home/oracle/app/oracle/oradata/orcl/marcov01.dbf

DBVERIFY - Verification complete

Total Pages Examined         : 768
Total Pages Processed (Data) : 196
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 19
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 553
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 14853855 (0.14853855)
Using the following command, dd resets two blocks of the datafile starting from the fourth.
[oracle@localhost trace]$ dd if=/dev/zero of=/home/oracle/app/oracle/oradata/orcl/marcov01.dbf bs=8k conv=notrunc seek=4 count=2
2+0 records in
2+0 records out
16384 bytes (16 kB) copied, 0.00047551 seconds, 34.5 MB/s
Let's dump again the same few blocks of the datafile ...
SQL> alter system dump datafile '/home/oracle/app/oracle/oradata/orcl/marcov01.dbf' block min 1 block max 10;

System altered.
... and compare them with the previous blocks. Blocks marked as (11/4) and (11/5) are totally reset and currently identified as (0/0):
vi /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_7255.trc
...
buffer tsn: 40 rdba: 0x02c00003 (11/3)
scn: 0x0000.00e2a62a seq: 0x01 flg: 0x04 tail: 0xa62a1e01
frmt: 0x02 chkval: 0x4dcc type: 0x1e=KTFB Bitmapped File Space Bitmap
Hex dump of block: st=0, typ_found=1
...
buffer tsn: 40 rdba: 0x00000000 (0/0)
scn: 0x0000.00000000 seq: 0x01 flg: 0x01 tail: 0x00000001
frmt: 0x02 chkval: 0x0000 type: 0x00=unknown
Hex dump of block: st=0, typ_found=0
...
buffer tsn: 40 rdba: 0x00000000 (0/0)
scn: 0x0000.00000000 seq: 0x01 flg: 0x01 tail: 0x00000001
frmt: 0x02 chkval: 0x0000 type: 0x00=unknown
Hex dump of block: st=0, typ_found=0
...
buffer tsn: 40 rdba: 0x02c00006 (11/6)
scn: 0x0000.00e2a04b seq: 0x01 flg: 0x04 tail: 0xa04b1e01
frmt: 0x02 chkval: 0x022e type: 0x1e=KTFB Bitmapped File Space Bitmap
Hex dump of block: st=0, typ_found=1
...
Now DBVERIFY is able to find two corrupt blocks on the datafile:
[oracle@localhost trace]$ dbv file=/home/oracle/app/oracle/oradata/orcl/marcov01.dbf blocksize=8192

DBVERIFY: Release 11.2.0.2.0 - Production on Mon Mar 18 07:49:40 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = /home/oracle/app/oracle/oradata/orcl/marcov01.dbf
Page 4 is marked corrupt
Corrupt block relative dba: 0x02c00004 (file 11, block 4)
Completely zero block found during dbv: 

Page 5 is marked corrupt
Corrupt block relative dba: 0x02c00005 (file 11, block 5)
Completely zero block found during dbv: 

DBVERIFY - Verification complete

Total Pages Examined         : 768
Total Pages Processed (Data) : 196
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 17
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 553
Total Pages Marked Corrupt   : 2
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 14853855 (0.14853855)
During a backup if RMAN detects corrupt blocks it terminates the command: the default threshold of total number of physical and logical corruptions permitted on a file is zero, so no corrupt blocks are tolerated.  
MAXCORRUPT is the setting you can use to modify this behaviour and tolerate more than one corrupt block. Have a look at the following example, considering that I have two corrupt blocks. When I specify 1 as maxcorrupt setting my backup still fails:
RMAN> run {
2> set maxcorrupt for datafile 11 to 1;
3> backup datafile 11;
4> }

executing command: SET MAX CORRUPT

Starting backup at 18-03-2013 09:01:05
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00011 name=/home/oracle/app/oracle/oradata/orcl/marcov01.dbf
channel ORA_DISK_1: starting piece 1 at 18-03-2013 09:01:05
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 03/18/2013 09:01:06
ORA-19566: exceeded limit of 1 corrupt blocks for file /home/oracle/app/oracle/oradata/orcl/marcov01.dbf
When I specify to tolerate at most two corrupt blocks the backup command is able to complete successfully (backup piece: o1_mf_nnndf_TAG20130318T090132_8ngglx76_.bkp). It should be clear anyway that I was able to complete a backup including a datafile containing two corrupt blocks!!!
RMAN> run {
2> set maxcorrupt for datafile 11 to 2;
3> backup datafile 11;
4> }

executing command: SET MAX CORRUPT

Starting backup at 18-03-2013 09:01:32
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00011 name=/home/oracle/app/oracle/oradata/orcl/marcov01.dbf
channel ORA_DISK_1: starting piece 1 at 18-03-2013 09:01:33
channel ORA_DISK_1: finished piece 1 at 18-03-2013 09:01:34
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_03_18/o1_mf_nnndf_TAG20130318T090132_8ngglx76_.bkp tag=TAG20130318T090132 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 18-03-2013 09:01:34

Starting Control File and SPFILE Autobackup at 18-03-2013 09:01:34
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2013_03_18/o1_mf_s_810378094_8ngglyk1_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 18-03-2013 09:01:35
After RMAN detects these kind of problems it writes which blocks in a datafile were marked corrupt on the V$DATABASE_BLOCK_CORRUPTION view and, until these corrupt blocks are not repaired, the rows remain in the view. Datafile 11 seems to have 2 corrupt blocks starting from block number 4:
SQL> select * from v$database_block_corruption;

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
        11          4          2                  0 ALL ZERO
On the alert log you can find the following errors:
...
Mon Mar 18 09:01:05 2013
Hex dump of (file 11, block 4) in trace file /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_7493.trc
Corrupt block relative dba: 0x02c00004 (file 11, block 4)
Completely zero block found during backing up datafile
Reread of blocknum=4, file=/home/oracle/app/oracle/oradata/orcl/marcov01.dbf. found same corrupt data
Reread of blocknum=4, file=/home/oracle/app/oracle/oradata/orcl/marcov01.dbf. found same corrupt data
Reread of blocknum=4, file=/home/oracle/app/oracle/oradata/orcl/marcov01.dbf. found same corrupt data
Reread of blocknum=4, file=/home/oracle/app/oracle/oradata/orcl/marcov01.dbf. found same corrupt data
Reread of blocknum=4, file=/home/oracle/app/oracle/oradata/orcl/marcov01.dbf. found same corrupt data
Hex dump of (file 11, block 5) in trace file /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_7493.trc
Corrupt block relative dba: 0x02c00005 (file 11, block 5)
Completely zero block found during backing up datafile
Reread of blocknum=5, file=/home/oracle/app/oracle/oradata/orcl/marcov01.dbf. found same corrupt data
Reread of blocknum=5, file=/home/oracle/app/oracle/oradata/orcl/marcov01.dbf. found same corrupt data
Reread of blocknum=5, file=/home/oracle/app/oracle/oradata/orcl/marcov01.dbf. found same corrupt data
Reread of blocknum=5, file=/home/oracle/app/oracle/oradata/orcl/marcov01.dbf. found same corrupt data
Reread of blocknum=5, file=/home/oracle/app/oracle/oradata/orcl/marcov01.dbf. found same corrupt data
Deleted Oracle managed file /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_03_18/o1_mf_nnndf_TAG20130318T090105_8nggl1fw_.bkp
Checker run found 1 new persistent data failures
...
To perform a recovery of these blocks the database has to be in ARCHIVELOG mode, could be either in MOUNT or OPEN mode and it is not necessary to put the affected datafiles offline. Let's try to recover first one block, specifying the following command:
RMAN> recover datafile 11 block 4;

Starting recover at 18-03-2013 09:37:06
using channel ORA_DISK_1

channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00011
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_03_18/o1_mf_nnndf_TAG20130318T090132_8ngglx76_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_03_18/o1_mf_nnndf_TAG20130318T090132_8ngglx76_.bkp tag=TAG20130318T090132
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01
failover to previous backup

channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00011
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_03_18/o1_mf_nnndf_TAG20130318T072836_8ng94o0k_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_03_18/o1_mf_nnndf_TAG20130318T072836_8ng94o0k_.bkp tag=TAG20130318T072836
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 18-03-2013 09:37:11
As you can see RMAN is able to look in a older backup untill it find a useful backup piece. The first backup piece used by RMAN was indeed o1_mf_nnndf_TAG20130318T090132_8ngglx76_.bkp, containing the same corrupt blocks because I previously used the set maxcorrupt option. Only one block was recovered so the V$DATABASE_BLOCK_CORRUPTION view is still populated, but with a different information. Only one block seems to be corrupt on datafile 11:
SQL> select * from v$database_block_corruption;

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
        11          5          1                  0 ALL ZERO
Let's check again the dump of the datafile.
SQL> alter system dump datafile '/home/oracle/app/oracle/oradata/orcl/marcov01.dbf' block min 1 block max 10; 

System altered.
The block number (11/4) now contains useful and valid information, whereas block number (11/5) is still corrupt and identified by (0/0):
vi /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_7255.trc
...
buffer tsn: 40 rdba: 0x02c00004 (11/4)
scn: 0x0000.00e2a047 seq: 0x01 flg: 0x04 tail: 0xa0471e01
frmt: 0x02 chkval: 0x823c type: 0x1e=KTFB Bitmapped File Space Bitmap
Hex dump of block: st=0, typ_found=1
...
buffer tsn: 40 rdba: 0x00000000 (0/0)
scn: 0x0000.00000000 seq: 0x01 flg: 0x01 tail: 0x00000001
frmt: 0x02 chkval: 0x0000 type: 0x00=unknown
Hex dump of block: st=0, typ_found=0
...
buffer tsn: 40 rdba: 0x02c00006 (11/6)
scn: 0x0000.00e2a04b seq: 0x01 flg: 0x04 tail: 0xa04b1e01
frmt: 0x02 chkval: 0x022e type: 0x1e=KTFB Bitmapped File Space Bitmap
Hex dump of block: st=0, typ_found=1
...
Let's try to recover the block number 5:
RMAN> recover datafile 11 block 5;

Starting recover at 18-03-2013 09:42:35
using channel ORA_DISK_1

channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00011
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_03_18/o1_mf_nnndf_TAG20130318T090132_8ngglx76_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_03_18/o1_mf_nnndf_TAG20130318T090132_8ngglx76_.bkp tag=TAG20130318T090132
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:00
failover to previous backup

channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00011
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_03_18/o1_mf_nnndf_TAG20130318T072836_8ng94o0k_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_03_18/o1_mf_nnndf_TAG20130318T072836_8ng94o0k_.bkp tag=TAG20130318T072836
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:00

starting media recovery
media recovery complete, elapsed time: 00:00:03

Finished recover at 18-03-2013 09:42:40
Again RMAN "failover to previous backup", it is able to recover block number 5 using the next available backup piece (o1_mf_nnndf_TAG20130318T072836_8ng94o0k_.bkp) and the V$DATABASE_BLOCK_CORRUPTION is finally empty.
SQL> select * from  v$database_block_corruption;

no rows selected
Next post will be about how to recover sparse corrupt blocks.

That's all.

16 comments:

goutham said...

hi

good.doc.excellent presentation.very useful.thanks for sharing your experience.



thank you
k.goutham

Marco V. said...

Thanks for your comment!!!

Anonymous said...

Maκе Money with Clickbank EfficientlyIf you want tο know about varіous рrοducts.
All eCommerсe shopping cart sоlutiоns have nοt been able to successfully
аttain, the choicе of the dallaѕ ѕearch
engіnе optіmіzation company of New York would also ensuгe that you haνe
in mіnd. In the internet-bаѕed world each fiгm on
the basis of the resources made available tο the global market.
Ah, that ѕ a conflіct of interest.


Alsо visit my wеbsite - improve search engine results

Anonymous said...

Use a full name that matches the contents of one unit before you move on to
the tactic in a week and I'll be damned if I haven't cut
12 shots off my handicap. Use user generated content to make them distinct.
There has never been a poor writer, but there has to be good.
So many times, new students of Google forget about the importance of geo-tags and other location specific verbiage
to keep your site gaining top ranking and high visibility.


Also visit my web page - charlotte search engine optimization

Anonymous said...

Eveгуоne loves it ωhen ρeople get tоgethег and ѕhаre νiews.
Great site, ѕtick with іt!

Also ѵіѕit mу webраge .
.. hemoroide

Anonymous said...

I am аctuallу glаd tο glance at
this web ѕite postѕ which consiѕtѕ
οf plenty of usеful facts, thanκs for ρrovіding these іnfoгmation.


Feel frеe tο visit mу blog pοѕt; verdopple deine dates

Anonymous said...

Hellο! I ϳust ωanteԁ tο ask
if уou eveг have any problеms ωith hackегs?
Mу laѕt blog (wordρrеѕs) was haсκeԁ and I еnded uρ losіng
severаl ωeeκs of hаrԁ work due tο no ԁata bacκup.
Do you have any solutіons to рrotect аgainѕt hackеrs?


Stoρ bу my homepage - elvalle.elvigia.net

Anonymous said...

What's up colleagues, how is all, and what you want to say regarding this article, in my view its really amazing in support of me.

Look at my web page Skin treatment

Anonymous said...

Hi! I could have sworn I've been to this website before but after checking through some of the post I realized it's new to me.
Nonetheless, I'm definitely glad I found it and I'll be
bookmarking and checking back frequently!

Also visit my homepage: Garcinia Cambogia

Anonymous said...

It will seem impossible to select where to go, even after wearing all the layers with regular cold-storm chasing forum heavy gloves.
Leaving a Toy Breed Dog needs to live outside, be sure you are prepared and
have a carpet insert covering the centre area of the mat.


Feel free to surf to my blog post ... storm alley

Anonymous said...

Appгеciatіon to my fаther who shared
wіth me regаrding this wеblog, this weblοg is genuіnelу amazing.



Feel free tо viѕіt my web-ѕite samenerguss

Anonymous said...

Thanκs in fаvor οf sharing suсh a faѕtidіous idea, piecе of writing is good,
thats whу i have rеad it еntiгely

my wеbpage :: random chat

Anonymous said...

Greetings! Very useful advicе in this pаrticular аrticle!
It's the little changes that produce the largest changes. Many thanks for sharing!

Take a look at my website - chatroullette - -

Anonymous said...

Mу programmer iѕ trying to persuade me to mоve to .
nеt frоm РHP. I haѵe alwayѕ dіslikеd the idea beсause of the exрensеs.
Вut he's tryiong none the less. I'vе bееn usіng
Moѵable-type on ѵаrious webѕites
fοr about a yeаr and аm worried about ѕwitching to аnother platfοrm.
I hаve heaгd gooԁ things about blogеngine.
net. Iѕ there a way I cаn imρort all my ωordpresѕ
posts into it? Any help would be really аppreciatеd!



Look into my ρagе; chatrolette

Anonymous said...

Неу I know this is off topic but Ι wаs wοndering іf
you knew of аny widgets I could аdԁ to mу blog that
automatіcally tweet mу newеѕt twitter updatеѕ.
I've been looking for a plug-in like this for quite some time and was hoping maybe you would have some experience with something like this. Please let me know if you run into anything. I truly enjoy reading your blog and I look forward to your new updates.

Also visit my web site ... Mouse Click The following post

Anonymous said...

Еvеry weekеnd і used tο
visit this wеbsitе, fοr the reason thаt i want
enjoyment, as thiѕ this site conatіοnѕ truly faѕtіdiouѕ funny stuff toο.


Mу blog ... cellulite