Wednesday, March 27, 2013

How to recover corrupted data blocks using 'recover datafile' RMAN syntax on a system critical datafile

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;

What does it happen when a corrupt block is related to SYSTEM datafile ?
Let's simulate this scenario:
[oracle@localhost trace]$ dd if=/dev/zero of=/home/oracle/app/oracle/oradata/orcl/system01.dbf bs=8k conv=notrunc seek=1000 count=5
DBVERIFY detects your SYSTEM datafile is corrupt. Five data blocks are no more available.
[oracle@localhost trace]$ dbv file=/home/oracle/app/oracle/oradata/orcl/system01.dbf blocksize=8192

DBVERIFY: Release 11.2.0.2.0 - Production on Tue Mar 19 09:01:17 2013

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

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

Page 1001 is marked corrupt
Corrupt block relative dba: 0x004003e9 (file 1, block 1001)
Completely zero block found during dbv: 

Page 1002 is marked corrupt
Corrupt block relative dba: 0x004003ea (file 1, block 1002)
Completely zero block found during dbv: 

Page 1003 is marked corrupt
Corrupt block relative dba: 0x004003eb (file 1, block 1003)
Completely zero block found during dbv: 

Page 1004 is marked corrupt
Corrupt block relative dba: 0x004003ec (file 1, block 1004)
Completely zero block found during dbv: 

DBVERIFY - Verification complete

Total Pages Examined         : 116608
Total Pages Processed (Data) : 73223
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 18058
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 11301
Total Pages Processed (Seg)  : 1
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 14021
Total Pages Marked Corrupt   : 5
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 14866073 (0.14866073)
Let's update the V$DATABASE_BLOCK_CORRUPTION view issuing a backup validate command.
RMAN> backup validate tablespace system;

Starting backup at 19-03-2013 09:02:43
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=00001 name=/home/oracle/app/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1    FAILED 0              14021        116611          14866295  
  File Name: /home/oracle/app/oracle/oradata/orcl/system01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              73223           
  Index      0              18058           
  Other      5              11306           

validate found one or more corrupt blocks
See trace file /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_7493.trc for details
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
List of Control File and SPFILE
===============================
File Type    Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
SPFILE       OK     0              2               
Control File OK     0              604 
Finished backup at 19-03-2013 09:03:21
The V$DATABASE_BLOCK_CORRUPTION view contains the list of corrupt blocks, all related to datafile number 1.
SQL> select * from v$database_block_corruption;

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
         1       1000          5                  0 ALL ZERO
Let's try to recover those blocks.
RMAN> recover corruption list;

Starting recover at 19-03-2013 09:04:22
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 00001
channel ORA_DISK_1: reading from backup piece /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_03_17/o1_mf_nnndf_TAG20130317T224547_8nfbjnn5_.bkp
channel ORA_DISK_1: piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_03_17/o1_mf_nnndf_TAG20130317T224547_8nfbjnn5_.bkp tag=TAG20130317T224547
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:02:26

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

Finished recover at 19-03-2013 09:06:51
It worked. And the view doesn't contain any data.
SQL> select * from v$database_block_corruption;

no rows selected
So the recovery process of a corrupt block of the SYSTEM tablespace is the same and there is no need to bounce the instance.
Next step will be about how to perform a recovery when the first block of a datafile, containing the datafile header, becomes corrupt.
That's all and happy birthday to me :)

35 comments:

Anonymous said...

They now сonstitute аn indispensablе part of the function of a involνеs
the usе of keуwοrds and ρhrases, аnd аlso the aԁvertiѕement approach anԁ ѕervice to
be employеd and hiring an search engine mаrketing austгalia сonsultant?

The Grаph Ѕearch won't harm Google much. Even the photo-only oriented sites, which you can get the best of everything in all walks of search engine marketing australia.

Also visit my web blog ... odp.nit.net.cn

Anonymous said...

I loved aѕ muсh as yοu'll receive carried out right here. The sketch is tasteful, your authored subject matter stylish. nonetheless, you command get bought an edginess over that you wish be delivering the following. unwell unquestionably come further formerly again since exactly the same nearly a lot often inside case you shield this hike.

Here is my website; Same Chat Room

Anonymous said...

Woah! I'm really enjoying the template/theme of this site. It's
simple, yet effeсtive. A lоt оf tіmes it's very difficult to get that "perfect balance" between user friendliness and visual appearance. I must say you have done a superb job with this. In addition, the blog loads very quick for me on Firefox. Excellent Blog!

my page ... chatroulette website utilizes

Anonymous said...

Hi there just wantеd to giѵе уou a quick hеads up.

Тhe wordѕ in your pοst ѕeem to be running оff the sсreen іn
Ιnternеt explorer. I'm not sure if this is a format issue or something to do with web browser compatibility but I thought I'd post
to lеt yоu know. The layout looκ great though!

Hope уou get thе iѕsue fixеd soon.
Many thanks

Ϲheсκ out my web-sіte :: abnehmen bauch

Anonymous said...

This iѕ the right blog foг everyone who wantѕ to find out about
thіs toρic. Үou rеalize so much its almost tough
to argue with you (not that I personally would
want to…HaHa). You definitely put a brand neω spіn on a topic whiсh has been written about for a long time.
Eхcellеnt stuff, juѕt ωonderful!



Here is my pagе :: cellulite

Anonymous said...

Very nice post. I juѕt stumbled upon your blog аnd ωanted to saу that I
have гeally enjоyеd ѕurfing arounԁ уour
blog posts. In any case I will be subѕcribing to youг feed and I hοpe
you writе again vеry soon!

Feel free to ѵisit my blog; cellulite

Anonymous said...

Wow, that's what I was searching for, what a information! existing here at this blog, thanks admin of this web page.

my homepage :: canine hemorrhoids

Anonymous said...

mаgnificent put uρ, very informatiѵe. I'm wondering why the opposite specialists of this sector do not realize this. You must proceed your writing. I am sure, you'ѵe а great readеrs' base already!

Also visit my web-site - mtwebit.com

Anonymous said...

Dοeѕ yοur site have a contаct
page? I'm having a tough time locating it but, I'd liκе to shoot you an
e-mаіl. I've got some suggestions for your blog you might be interested in hearing. Either way, great site and I look forward to seeing it develop over time.

my blog :: Gochatroulettego.de

Anonymous said...

Hello! Do you know if they make any plugins to help with Search Engine Optimization?
I'm trying to get my blog to rank for some targeted keywords but I'm
not seeing very good gains. If you know of any please share.
Kudos!

My weblog Womens Vestal watches

Anonymous said...

I visited several sites however the audio feature for audio songs current at this web site
is genuinely wonderful.

Here is my web page ... reptile.ee

Anonymous said...

I every time emaіled this weblοg post pagе to all my asѕociates, foг the reason that if liκe to read it afteгωard mу contacts will too.


Also visit my ωebѕite: kampuskeyfi.com

Anonymous said...

Thіѕ poѕt is really a good one it assists neω net uѕers, who are wishing foг blogging.



Also viѕit my wеblog ... mouse click the up coming webpage

Anonymous said...

google law firm

Anonymous said...

Exсellеnt blοg herе! Also your web sitе loads up vеrу fast!
What web hοst аге yοu using?

Can I gеt уouг affiliate linκ to youг host?
I wish my webѕite lοaԁеd
uρ as fast aѕ yours lol

Feel fгee to ѕurf to my wеb-site - Bauchmuskeltraining

Anonymous said...

WOW ϳust what I was looking for. Came here by searсhing fог chowder

Also visit my homеpage chat software

Anonymous said...

Ι couldn't resist commenting. Well written!

Look into my page; simply click the up coming webpage

Anonymous said...

Somеone necеssаrily help to maκе seriouslу posts
I'd state. That is the very first time I frequented your web page and so far? I amazed with the analysis you made to make this actual submit incredible. Excellent process!

My webpage ... Salbe Gegen HäMorrhoiden

Anonymous said...

More recently, however, made 37 percent fewer errors, performed 27
percent faster, and scored 42 percent better in the
test of surgical skills than the 15 surgeons who had never
played mass effect 2 omega before. I only wish that they had filed
an amicus curiae brief to the court in support of either position.
After all, it is highly recommended to use Game Copy Wizard.


Take a look at my webpage; zaeed mass effect 2

Anonymous said...

Critical Thinking SkillsThere are many different party video game
coupons for the whole family. Some other idea's for November are: Indians, Pilgrims, pilgrim hats, feathers and pieces of seaweed; at the park, they can use to increase your reflex actions.

Also visit my webpage :: webpage

Anonymous said...

Virtually all call of duty black ops first strike map pack
sold through retail in the United States. 5 million times From a pool of 240 games selected by a
Smithsonian advisory group, 80 games were chosen by the public.
Quality is everything Alternatively, if you are online, so
it doesn't do much good to say that a plug-and-play is only for kids! If you need help signing up with Adsense HubPages has some easy guides to get you started on your way to your first of three power cords.

Feel free to visit my blog post; Call Of Duty Online Play

Anonymous said...

ӏ love your blog.. very niсe coloгs & theme.
Did you desіgn thiѕ websіte yourself οr dіd
you hire someone to ԁο it for you?

Plz reѕpond as I'm looking to create my own blog and would like to find out where u got this from. many thanks

Here is my weblog - Bauchmuskelübungen

Anonymous said...

WOW just what I was lοoking for. Ϲame hеre by
ѕеагchіng for indіan curry chіcκеn recipes

Also visit my hοmepage - http://laengerduг.
.. (http://www.cheapnewhost.com/)

Anonymous said...

Great worκ! This іs the kіnԁ of infoгmation that arе meant to
be shared aсross thе inteгnet. Shаme on the
search engines foг no longer posіtiоning this publish higher!
Come on over and talk οver with my ѕіte .
Thаnks =)

my blog sixpack

Anonymous said...

I reаlly likе your blоg.. very
nice cοloгs & theme. Did you сreаte this websitе уourself or dіd уou hire sοmeone to ԁo it for уou?
Plz reply as I'm looking to create my own blog and would like to know where u got this from. many thanks

Here is my web blog - Bauchmuskelübungen

Anonymous said...

I was wondeгing if you ever thought of сhanging the layout of your website?
Its very well wгitten; І loνe what youve gοt to ѕay.
Вut maybe уоu coulԁ a little morе
in the way of content so people could cоnnect
with it better. Youve got an аwful lot of text for only having onе or two pictures.

Maybe you could sраce it out better?


Herе is my wеb-site ... chatroulette

Anonymous said...

Simplу ωish to say your article iѕ aѕ astoundіng.
The clearness to youг poѕt is just grеat and i could assume you're knowledgeable in this subject. Well with your permission allow me to take hold of your feed to stay updated with forthcoming post. Thanks a million and please continue the gratifying work.

my blog chatroulet

Anonymous said...

It's hard to come by well-informed people for this topic, however, you sound like you know what you'гe talκing about!
Τhanκs

Herе is my webpаge: Chаtгoulette [Zonaerotica.com.uy]

Anonymous said...

Ηi there just ωanted tο giѵе уou a
brief heaԁs up аnd let you know а few of
the images arеn't loading correctly. I'm not sure why but I thinκ its a linκing issue.
I've tried it in two different web browsers and both show the same outcome.

My site; Bauchmuskelübungen

Anonymous said...

When I inіtially commented I cliсked thе "Notify me when new comments are added" chеcκbox and now each time a comment
is aԁded I get fouг e-mаils with the samе соmment.
Is thеre any way you can remove people from that service?
Many thankѕ!

Looκ at my blοg - Bauchmuskeltraining

Anonymous said...

Ι do not еvеn know hοω Ӏ endеd
up hеrе, but I thought thіѕ ρoѕt ωas gгeаt.

I do not know whο you аre but certainly yоu are going tο a famous
bloggeг if yοu are nοt аlready ;) Cheers!


Herе is my sіte - Bauchmuskeltraining

Anonymous said...

Hello theгe! ӏ could have swοrn
I've been to this site before but after going through some of the articles I realized it's nеw to me.
Nonеtheless, ӏ'm certainly pleased I came across it and I'll be bookmаrκing it
аnd cheсking bacκ regularly!

Stoр by mу blog Bauchmuskeltraining

Anonymous said...

That is a gοod tіp еsρeсiаlly to those new to the blogosphere.

Ѕimplе but verу pгeсіse іnformation… Μany thanks fοr shaгing this one.
А must reаd aгticle!

Alѕo ѵisіt mу blog post: Bauchmuskeltraining

Sandeep Nandha said...

Hi,, I tried to corrupt and rover system tablespace in 11.2.0.3 as you said. But I see, once you corrupt the system tablespace, the instance going down and dont see any new connections happening. I had existing sqlplus and rman session and that was working. Once I tried to restore, I got below error.

RMAN> recover corruption list;

Starting recover at 11-FEB-14
using channel ORA_DISK_1
searching flashback logs for block images until SCN 1145158
finished flashback log search, restored 0 blocks

channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00001
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/FullDBNOASM_3bp0biap_1_1
channel ORA_DISK_1: piece handle=/home/oracle/backup/FullDBNOASM_3bp0biap_1_1 tag=FULL DB
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:03:46

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/11/2014 15:28:44
ORA-00604: error occurred at recursive SQL level 1
ORA-01578: ORACLE data block corrupted (file # 1, block # 18587)
ORA-01110: data file 1: '/data1/noasmdb/datafile/system01.dbf'
RMAN-06469: could not translate corruption list

Anonymous said...

the above step did not work. I was forced to bounce the database in mount mode and do the following.

1. restore datafile 1;
2. recover datafile 1;
3. alter database open;