Tuesday, April 2, 2013

How to perform a recovery when the first block of a datafile containing the datafile header becomes corrupt

This post continues a series of previous posts related to the recovery process of corrupt data blocks:
1) How to recover contiguous corrupted data blocks using 'recover datafile' RMAN syntax;
2) How to recover sparse corrupted data blocks using 'recover datafile' RMAN syntax;
3) How to recover corrupted data blocks using 'recover datafile' RMAN syntax on a system critical datafile;

Reading the documentation there is a case where it isn't possible to use the block recover feature: it happens when the corrupt block is the first, I mean data block number 1 containing the header of the datafile.
Let's simulate this scenario using the following dd command:
[oracle@localhost trace]$ dd if=/dev/zero of=/home/oracle/app/oracle/oradata/orcl/marcov01.dbf bs=8k conv=notrunc seek=1 count=1
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 0.000432187 seconds, 19.0 MB/s
To check and have a completed list of corrupt blocks as we have already seen we should issue a backup validate command:
RMAN> backup validate tablespace marcov;

Starting backup at 19-03-2013 23:07:11
using channel ORA_DISK_1
RMAN-06169: could not read file header for datafile 11 error reason 7
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 03/19/2013 23:07:11
RMAN-06056: could not access datafile 11
The V$DATABASE_BLOCK_CORRUPTION view as usual should contain data about which blocks are corrupt, but it doesn't show any rows:
SQL> select * from v$database_block_corruption;

no rows selected
What does it happen if I try to recover that first block ? It seems to work fine...
RMAN> recover datafile 11 block 1;

Starting recover at 19-03-2013 23:08:09
using channel ORA_DISK_1

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

Finished recover at 19-03-2013 23:08:10
... but trying again a backup validate command I obtain the same error.
RMAN> backup validate tablespace marcov;

Starting backup at 19-03-2013 23:08:30
using channel ORA_DISK_1
RMAN-06169: could not read file header for datafile 11 error reason 7
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 03/19/2013 23:08:30
RMAN-06056: could not access datafile 11
Ok, it's time to perform a more familiar restore/recover tablespace operation.
So a typical sequence is put that tablespace offline, restore it, recover it and then put it back online:
RMAN> sql 'alter tablespace marcov offline immediate';     

sql statement: alter tablespace marcov offline immediate

RMAN> restore tablespace marcov;     

Starting restore at 19-03-2013 23:13:30
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 03/19/2013 23:13:30
ORA-01135: file 11 accessed for DML/query is offline
ORA-01110: data file 11: '/home/oracle/app/oracle/oradata/orcl/marcov01.dbf'
RMAN-06019: could not translate tablespace name "MARCOV"
Ops... the familiar restore/recover sequence is not working at all.
And I can't even put the tablespace online again.
RMAN> sql 'alter tablespace marcov online';

sql statement: alter tablespace marcov online
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of sql command on default channel at 03/19/2013 23:19:30
RMAN-11003: failure during parse/execution of SQL statement: alter tablespace marcov online
ORA-01122: database file 11 failed verification check
ORA-01110: data file 11: '/home/oracle/app/oracle/oradata/orcl/marcov01.dbf'
ORA-01210: data file header is media corrupt
It seems the familiar restore/recover operation cannot start because of the corrupt data block number 1.
So how can I proceed ? I put again the tablespace offline ...
RMAN> sql 'alter tablespace marcov offline immediate';

sql statement: alter tablespace marcov offline immediate
... and simply removed the entire datafile with the corrupt data block number 1.
[oracle@localhost trace]$ rm /home/oracle/app/oracle/oradata/orcl/marcov01.dbf
Without the entire datafile, the restore tablespace command is able to complete successfully.
RMAN> restore tablespace marcov;

Starting restore at 19-03-2013 23:20:29
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00011 to /home/oracle/app/oracle/oradata/orcl/marcov01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_03_18/o1_mf_nnndf_TAG20130318T224952_8nhz414b_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_03_18/o1_mf_nnndf_TAG20130318T224952_8nhz414b_.bkp tag=TAG20130318T224952
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
Finished restore at 19-03-2013 23:20:32
Also the recover tablespace command is able to finish its operation.
RMAN> recover tablespace marcov;

Starting recover at 19-03-2013 23:20:45
using channel ORA_DISK_1

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

Finished recover at 19-03-2013 23:20:47
The tablespace should be now available again to all the users.
RMAN> sql 'alter tablespace marcov online';

sql statement: alter tablespace marcov online
Ok... It's not true and I know why. During the restore operation RMAN simply used the first available backup piece: o1_mf_nnndf_TAG20130318T224952_8nhz414b_.bkp. But that backup piece contains a datafile with few corrupt data blocks because I forced to skip them during the backup operation. You can verify what I'm saying here in one of my previous posts.
SQL> select count(*) from marcov.t1;
select count(*) from marcov.t1
                            *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 11, block # 10)
ORA-01110: data file 11: '/home/oracle/app/oracle/oradata/orcl/marcov01.dbf'
Let's update the V$DATABASE_BLOCK_CORRUPTION view issuing a backup validate command.
RMAN> backup validate tablespace marcov;

Starting backup at 19-03-2013 23:21:53
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: backup set complete, elapsed time: 00:00:02
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
11   OK     6              553          768             14877710  
  File Name: /home/oracle/app/oracle/oradata/orcl/marcov01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              196             
  Index      0              0               
  Other      0              19              

Finished backup at 19-03-2013 23:21:56
As you can see datafile number 11 has few corrupt blocks.
SQL> select * from v$database_block_corruption;

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
        11          8          1           14877710 CORRUPT
        11          7          1                  0 CORRUPT
        11         10          1                  0 CORRUPT
        11          3          3                  0 CORRUPT
Using the recover corruption list we are able to correct the content of those corrupt blocks.
Just note also how RMAN "failover to previous backup" (skipping the already mentioned o1_mf_nnndf_TAG20130318T224952_8nhz414b_.bkp backup piece and also the next available one, o1_mf_nnndf_TAG20130318T090132_8ngglx76_.bkp) because using the current backup piece is not able to perform and complete the recovery operation.
RMAN> recover corruption list;

Starting recover at 19-03-2013 23:22:36
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_TAG20130318T224952_8nhz414b_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_03_18/o1_mf_nnndf_TAG20130318T224952_8nhz414b_.bkp tag=TAG20130318T224952
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_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:04

Finished recover at 19-03-2013 23:22:43
RMAN after looking into o1_mf_nnndf_TAG20130318T072836_8ng94o0k_.bkp backup piece completes the recovery operation and the T1 table is now really available to all the users.
SQL> select count(*) from marcov.t1;

  COUNT(*)
----------
      1000
Next step will be about how to recover corrupt block using 'recover tablespace' and data block address (dba) RMAN syntax.

That's all.

34 comments:

Anonymous said...

"So a typical sequence is put that tablespace offline, restore it, recover it and then put it back online"

This thing works for me. Restore is successful without any intervention for that datafile.

I am not able to simulate your scenario. Could you please clarify on this?

Marco V. said...

That typical sequence didn't work because I reset the header of the datafile and Oracle sw was not able to update it correctly. The same sequence indeed worked fine just simply deleting the entire datafile simulating a complete loss of that file.

Regards,
Marco V.

Anonymous said...

But when i reset header of the datafile and then performed below steps. This works fine without any intervention(rm, mv) for datafile.

1. Reset header of datafile.
2. Bring datafile offline.
3. Restore datafile.
4. Recover datafile.
5. Bring datafile online.

- Yasir

Marco V. said...

That's really interesting. I will try on other machines with different database and os version.
Could you tell me your database and os version, please ?

Regards,
Marco V.

Anonymous said...

Hi, yeah this post is really fastidious and I have learned lot
of things from it concerning blogging. thanks.

Look into my web-site ... walking calculator

Anonymous said...

Its like you read my mind! You appear to know a lot about this, like you wrote the book in it or something.
I think that you can do with a few pics to drive the message home a bit, but instead of
that, this is excellent blog. A fantastic read.
I will certainly be back.

Feel free to visit my web blog ... virtapay to

Anonymous said...

Admiring the persistence you put into your blog and detailed information you provide.
It's awesome to come across a blog every once in a while that isn't
the same outdated rehashed material. Excellent read!
I've saved your site and I'm including your RSS feeds to my Google
account.

My page: my sex games

Anonymous said...

Very descriptive article, I enjoyed that bit.
Will there be a part 2?

Also visit my web blog: miscrits of sunfall

Anonymous said...

I was able to find good info from your content.


Feel free to surf to my blog: link clicker

Anonymous said...

Hi there! You some kind of skilled? Nice message.

Can you tell me how to subscribe your blog?

Also visit my blog post :: having trouble getting pregnant at 40

Anonymous said...

I believe this is among the most vital information for me.
And i'm satisfied reading your article. But should observation on few common issues, The site taste is ideal, the articles is really nice : D. Excellent activity, cheers

my weblog: free psn code

Anonymous said...

Your style is unique in comparison to other folks I've read stuff from. Thanks for posting when you've got the
opportunity, Guess I will just book mark this blog.


Here is my weblog; Car Boat Videos

Anonymous said...

Hello! This post could not be written any better!
Reading this post reminds me of my good old room mate!
He always kept chatting about this. I will forward this write-up to him.
Fairly certain he will have a good read. Thanks for sharing!


My webpage video hosting platform

Anonymous said...

I've been surfing online more than 3 hours today, yet I never found any interesting article like yours. It is pretty worth enough for me. In my view, if all webmasters and bloggers made good content as you did, the web will be much more useful than ever before.

Feel free to visit my blog post Recover Twitter Password Tutorial

Anonymous said...

I am really impressed together with your writing abilities
and also with the structure on your weblog. Is this a paid subject matter or did you modify it yourself?

Either way stay up the excellent high quality writing, it is rare to peer a nice blog like this one
today..

my web blog: earn money on twitter

Anonymous said...

My brother recommended I may like this blog.
He was once totally right. This post actually made my day.
You can not believe just how so much time I had spent for
this information! Thanks!

my blog - Funny Clips

Anonymous said...

Hello, just wanted to mention, I loved this article.
It was funny. Keep on posting!

Also visit my web site; inception movie discussion

Anonymous said...

Very good information. Lucky me I ran across your blog by chance (stumbleupon).
I have saved it for later!

My blog post: local usa listing directory

Anonymous said...

Quality posts is the crucial to attract the
viewers to go to see the web page, that's what this web page is providing.

Look at my homepage: Get More Youtube Subscribers Tool

Anonymous said...

Heya i am for the primary time here. I came across this board and I to find It truly useful & it helped me out a lot.
I hope to provide something again and aid others such as you aided me.


Have a look at my site: password hacking software

Anonymous said...

always i used to read smaller articles or reviews which also clear their motive, and that is
also happening with this paragraph which I am reading here.


Also visit my web page: pdf password remover

Anonymous said...

Its like you read my mind! You appear to know so much about this, like you
wrote the book in it or something. I think that you could do with
some pics to drive the message home a little bit,
but other than that, this is magnificent blog. A great read.
I will definitely be back.

Also visit my webpage password hacking software

Anonymous said...

I blog frequently and I really appreciate your information.
This article has really peaked my interest. I am going to take a note of your website and
keep checking for new information about once per week.

I opted in for your RSS feed as well.

Look into my webpage twitter password reset

Anonymous said...

If you are going for best contents like me, only pay a quick visit
this site all the time because it offers feature contents, thanks

Check out my web-site ... bypass sharecash

Anonymous said...

Hello! This is my first visit to your blog!
We are a collection of volunteers and starting a new initiative in a community
in the same niche. Your blog provided us valuable information to work on.
You have done a wonderful job!

Also visit my webpage ... free hacking online

Anonymous said...

What's up, this weekend is pleasant designed for me, as this point in time i am reading this enormous educational piece of writing here at my residence.

My web blog :: work at home moms money []

Anonymous said...

Pretty nice post. I just stumbled upon your weblog and wished to mention that I have truly loved browsing your blog posts.
After all I will be subscribing in your feed and I'm hoping you write again very soon!

Look at my blog - lomentum ()

Anonymous said...

I think what you said was very logical. However, consider this, suppose you were to write a killer title?
I ain't saying your content is not solid., but suppose you added a post title to maybe grab folk's attention?
I mean "How to perform a recovery when the first block of a datafile containing the datafile header becomes corrupt" is
a little plain. You ought to look at Yahoo's front page and note how they write post titles to get people interested. You might add a video or a related picture or two to get people excited about everything've got to say.
In my opinion, it could bring your website a little bit more interesting.


Here is my weblog miscrits rare

oakleyses said...

oakley sunglasses, prada handbags, oakley sunglasses, longchamp handbags, longchamp handbags, louboutin shoes, louis vuitton handbags, coach factory outlet, tiffany and co, coach purses, louis vuitton outlet, polo ralph lauren outlet, air max, prada outlet, longchamp outlet, oakley sunglasses cheap, ray ban sunglasses, louboutin outlet, michael kors outlet, michael kors outlet, tiffany and co, burberry outlet, christian louboutin shoes, coach outlet store online, jordan shoes, polo ralph lauren outlet, louboutin, kate spade handbags, michael kors outlet, coach outlet, air max, gucci outlet, michael kors outlet, ray ban sunglasses, chanel handbags, michael kors outlet, tory burch outlet, nike free, kate spade outlet, louis vuitton outlet, burberry outlet, louis vuitton outlet stores, louis vuitton, nike shoes, michael kors outlet

oakleyses said...

abercrombie and fitch, instyler, ghd, bottega veneta, ugg boots, jimmy choo outlet, soccer shoes, ugg pas cher, herve leger, beats by dre, birkin bag, abercrombie and fitch, north face jackets, soccer jerseys, mont blanc, rolex watches, lululemon outlet, celine handbags, nike roshe run, nike trainers, giuseppe zanotti, hollister, wedding dresses, nike huarache, mcm handbags, vans shoes, chi flat iron, babyliss pro, north face outlet, nike roshe, ugg australia, ugg, marc jacobs, barbour, nfl jerseys, p90x, new balance shoes, asics running shoes, ferragamo shoes, mac cosmetics, insanity workout, uggs outlet, reebok outlet, longchamp, valentino shoes

oakleyses said...

converse, air max, gucci, canada goose, juicy couture outlet, canada goose, wedding dresses, moncler, ralph lauren, lancel, montre homme, moncler, louboutin, oakley, karen millen, vans, coach outlet store online, air max, canada goose jackets, ugg, hollister clothing store, louis vuitton, baseball bats, hollister, rolex watches, juicy couture outlet, iphone 6 cases, canada goose uk, canada goose outlet, ugg, moncler, moncler outlet, timberland boots, hollister, supra shoes, moncler, canada goose, converse shoes, toms shoes, moncler, moncler, canada goose, ugg boots, ray ban, parajumpers, canada goose

yanmaneee said...

lebron 10
nike shox for men
hermes handbags
michael kors outlet
adidas stan smith shoes
golden goose sneakers
curry 6
michael kors handbags
air max 2018
yeezy boost 350 v2

noughr said...

replica bags from korea replica bags from korea replica bags sydney

geausleyt said...

check my blog replica gucci handbags click this link here now replica bags from china original site gucci replica