Thursday, August 23, 2012

How to recover a corrupted/damaged/lost/canceled NONSYSTEM datafile switching to an image copy (database in ARCHIVELOG mode)

Today I will recover a lost datafile using an image copy as I told in advance at the end of this previous post .

So the first thing I must have is an image copy of my datafiles: to do that I issued the following commands.
[oracle@localhost orcl]$ rman target /
RMAN> backup as copy database;

Starting backup at 19-07-2012 07:45:31
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
output file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_sysaux_80j7dd8l_.dbf tag=TAG20120719T074531 RECID=2 STAMP=789032830
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:40
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/home/oracle/app/oracle/oradata/orcl/system01.dbf
output file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_system_80j7hk9c_.dbf tag=TAG20120719T074531 RECID=3 STAMP=789032923
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:36
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/home/oracle/app/oracle/oradata/orcl/users01.dbf
output file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_users_80j7lkp7_.dbf tag=TAG20120719T074531 RECID=4 STAMP=789032942
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/home/oracle/app/oracle/oradata/orcl/example01.dbf
output file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_example_80j7m0w4_.dbf tag=TAG20120719T074531 RECID=5 STAMP=789032948
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
output file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_undotbs1_80j7m8fq_.dbf tag=TAG20120719T074531 RECID=6 STAMP=789032955
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=/home/oracle/app/oracle/oradata/orcl/APEX_1930613455248703.dbf
output file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_apex_193_80j7mhmf_.dbf tag=TAG20120719T074531 RECID=7 STAMP=789032959
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 19-07-2012 07:49:20

Starting Control File and SPFILE Autobackup at 19-07-2012 07:49:21
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_07_19/o1_mf_s_789032961_80j7mkvb_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 19-07-2012 07:49:22
Now let's simulate a lost datafile of a NONSYSTEM tablespace
[oracle@localhost orcl]$ mv /home/oracle/app/oracle/oradata/orcl/users01.dbf /home/oracle/app/oracle/oradata/orcl/users01_damaged.dbf
The RMAN report command is no more able to identify the size of the lost datafile
[oracle@localhost orcl]$ rman target /
RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name ORCL

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    831      SYSTEM               *** /home/oracle/app/oracle/oradata/orcl/system01.dbf
2    1105     SYSAUX               *** /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
3    40       UNDOTBS1             *** /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
4    0        USERS                *** /home/oracle/app/oracle/oradata/orcl/users01.dbf
5    82       EXAMPLE              *** /home/oracle/app/oracle/oradata/orcl/example01.dbf
6    7        APEX_1930613455248703 *** /home/oracle/app/oracle/oradata/orcl/APEX_1930613455248703.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767 /home/oracle/app/oracle/oradata/orcl/temp01.dbf
In the alert log we can see errors like the following:
...
Thu Jul 19 08:04:52 2012
Errors in file
/home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_m000_8591.trc:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/home/oracle/app/oracle/oradata/orcl/users01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
...
It's time to restore and recover our datafile. First put it offline
RMAN> sql 'alter database datafile 4 offline';

sql statement: alter database datafile 4 offline
Then tell to use the image copy (skipping at least to copy it in the default location)
RMAN> switch datafile 4 to copy;

datafile 4 switched to datafile copy
"/home/oracle/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_users_80j7lkp7_.dbf"
Recover the datafile
RMAN> recover datafile 4;

Starting recover at 19-07-2012 08:06:42
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=46 device type=DISK

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

Finished recover at 19-07-2012 08:06:44
Bring it online
RMAN> sql ' alter database datafile 4 online';

sql statement:  alter database datafile 4 online
From the alert log you can see:
...
Thu Jul 19 08:06:18 2012
alter database datafile 4 offline
Completed: alter database datafile 4 offline
Thu Jul 19 08:06:29 2012
Errors in file
/home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_8535.trc:
ORA-19625: error identifying file
/home/oracle/app/oracle/oradata/orcl/users01.dbf
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
WARNING: switching recovery area datafile copy
/home/oracle/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_users_80j7lkp7_.dbf
as database area datafile.
This datafile is no more accounted into used space. Consider decrementing
db_recovery_file_dest_size parameter value by 235929600 bytes.
Switch of datafile 4 complete to datafile copy 
checkpoint is 13595062
Thu Jul 19 08:06:43 2012
alter database recover datafile list clear
Completed: alter database recover datafile list clear
alter database recover if needed
datafile 4
Media Recovery Start
Serial Media Recovery started
Recovery of Online Redo Log: Thread 1 Group 2 Seq 2 Reading mem 0
Mem# 0: /home/oracle/app/oracle/oradata/orcl/redo02.log
Media Recovery Complete (orcl)
Completed: alter database recover if needed
datafile 4
Thu Jul 19 08:06:56 2012
alter database datafile 4 online
Completed:  alter database datafile 4 online
...
Now the RMAN report command is able again to obtain information about your datafile
RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name ORCL

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    831      SYSTEM               *** /home/oracle/app/oracle/oradata/orcl/system01.dbf
2    1105     SYSAUX               *** /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
3    40       UNDOTBS1             *** /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
4    225      USERS                *** /home/oracle/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_users_80j7lkp7_.dbf
5    82       EXAMPLE              *** /home/oracle/app/oracle/oradata/orcl/example01.dbf
6    7        APEX_1930613455248703 *** /home/oracle/app/oracle/oradata/orcl/APEX_1930613455248703.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767 /home/oracle/app/oracle/oradata/orcl/temp01.dbf
As you can easily see, with this scenario time makes the difference compared with the previous post In this scenario it took only 38 seconds to get back online with the database.
TOTAL TIME: 38
Thu Jul 19 08:06:18 2012
Thu Jul 19 08:06:56 2012
Of course I used a simple test machine, but compared with the previous scenario (one minute and 34 seconds) to get back online a datafile of only 250MB, I spent now only 38 seconds. You should consider this approach if you want to get back your database online as soon as possible. You should test your timesavings, but it could let you save even hours... In the next scenario I will move back that datafile located in the flash recovery area to the original location. That's all.

22 comments:

Anonymous said...

I wanted to thank you for this fantastic read!! I certainly loved every
little bit of it. I have got you book-marked to look at new things you post…
Also see my website: backup camera

Anonymous said...

Nice weblog right here! Additionally your site so much up very fast!
What host are you using? Can I get your affiliate
link for your host? I desire my website loaded up as quickly as yours lol
Review my web blog ... web design miami beach

Anonymous said...

Hurrah! Finally I got a website from where I can in fact take useful facts concerning my study and knowledge.
Here is my page ... green coffee extract

Anonymous said...

Sweet blog! I found it while browsing on Yahoo News.
Do you have any suggestions on how to get listed in Yahoo News?
I've been trying for a while but I never seem to get there! Cheers
Look into my site : cheap cameras for sale

Anonymous said...

I love what you guys tend to be up too. This sort of clever work and coverage!
Keep up the good works guys I've incorporated you guys to my own blogroll.
Check out my site - learn writing fast

Anonymous said...

constantly i used to read smaller articles or reviews
that also clear their motive, and that is also happening with this article which I am reading here.
Also visit my web-site - top commercials

Anonymous said...

Greetings, I do think your blog could possibly be having
internet browser compatibility problems. Whenever
I look at your web site in Safari, it looks fine but when opening in IE, it has some overlapping
issues. I just wanted to provide you with a quick heads up!
Aside from that, excellent site!
Here is my blog ... Electronic ballast

Anonymous said...

It іs the best time to make а few plans for the future and іt's time to be happy. I've leaгn this submit and if I could I want to
rеcommend you some interesting issuеѕ οr tips.
Peгhаps yοu can write next aгticlеѕ regarding this artіcle.
I desire to learn more іssues аbout іt!


Herе iѕ my ωeb page :: payday loan

Anonymous said...

Hi, i feel that i saw you visited my blog so i came to go back the prefer?

.I am attempting to to find things to improve my website!
I guess its ok to use a few of your concepts!!

Feel free to visit my website :: Spazio-kovan

Anonymous said...

Kudos putting this together indeed- Just a quick one here- When you move the database- Plz indicate that the database is completely down..can be confusing

Andy Gregory said...

on the eye patients and it is so common buy eyeglasses online and popular especially in Delhi
http://www.finestglasses.com/

Unknown said...

La poignée du http://www.robinetsale.com/ de style actuel, ont généralement le choix entre le levier ou croisé conception pour la résolution. La robinet levier est généralement droite ou légèrement incurvée, et se trouve dans le robinet de la barre qui a une ou deux handle.Cross robinetterie cuisine adopte intersection, entre les deux bandes de métal, et généralement applicable uniquement aux deux robinet LED de poignée. Elles peuvent être inclinées verticale, Robinetterie Vasque, avec une de chaque c?té de la buse.

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

Unknown said...

The Polarized Given retails for $200 and is available in an elegant yet eye-catching Polished Chrome/OO Grey Polarized combination as well as a Polished Gold/Bronze Polarized color. It is also available in a non-polarized style, the Oakley Given, which warby parker retails from $150 to $160 and comes in a variety of beautiful colors that complement every complexion.Oakley Twentysix.2 ($130-140) - look sleek in sporty street style or like a bold beach beauty with these striking, tear-shaped sunnies that have racy good-looks, making you the center of attention. The Twentysix.2 sunglasses are a blend of oakley prescription glasses sporty-chic meets classic-cool, making them the perfect shades for the glam gal on the go. From the bold yet delicate tear-shaped frame with feminine flair to their sleek and stylish color-contrast arms—there’s no doubt that these shades were meant to steal the spotlight while keeping you fashionably undercover from the sun’s rays. While the oakley holbrook Twentysix.2 shades feature a lightweight and sleek frame that’s super-comfy, they are heavyweights in fashion and durability from their eye-catching good looks to their stylish yet tough O-matter frame.
She took the stage name of Annie Oakley when she married sharpshooter Frank Butler and became known as "Little Sure Shot" when she joined the fake oakleys Buffalo Bill Wild West Show.Annie Oakley Days Festival Activities at this year's Festival include a car show (on Sunday), historical bus tours (including a stop at Annie Oakley's grave site), a kiddie tractor pull, a talent show and presentations of an old time melodrama ("Haunted Hijinks or The Ghastly Ghost of Dead Man's Gulch").Be Oakley Oil Drum Sunglass sure to check out the Family Fun Games offered all three days. Games include a water balloon toss, sack races, a rollin' pin throwing contest, a pole throwing contest and a Super Farmer contest.And don't miss the Annie Oakley Western Arts Showcase (held at the Coliseum) at the Festival featuring bullwhip, roping and knife straight jacket oakley throwing exhibition contests.
Goggles are not just something to protect your eyes anymore. The Oakley Airwave goggles have caller id, GPS, text messaging, Bluetooth and music, not to mention anti-fog technology. You can find Oakley goggles at your nearby Sunglass Hut.One of Ohio's native daughters is renowned Wild West celebrity, sharpshooter and markswoman Annie oakley oil rig sunglasse Oakley who became famous as part of Buffalo Bill's Wild West Show. Every year, Greenville, Ohio. Annie Oakley's birthplace, holds the Annie Oakley Days Festival to pay tribute to Oakley and to perpetuate her memory. Cleveland Old West enthusiasts are invited to join the festivities at York Woods and to enjoy a wide range

Unknown said...

Can you believe that it has been a decade since Spike Lee returned to helm an Air Jordan commercial as his famed character Mars Blackmon to present, at the time, the latest cheap jordans signature in Jordan Brand’s lineup in the Air Jordan XX? The brand has already brought back the silhouette air jordan 11 in a celebratory Laser printed form, but next Saturday, March 14th, we’ll be treated to the ominous Air jordan 5 Jordan 20 ‘Stealth’ in all of its black patent leather glory. The intimidating colorway isn’t necessarily murdered out, jordan shoes as a red accent litters throughout. Nor is it a true authentic in comparison to the original as jordan high heels a faint slash of gold hits the midfoot strap instead of the usual grey. Stick around for a jordans jumpman shoes better view of the latest retro Jordan and grab a pair 3/14 for $230.
The formula of the jordan fltclb'91 success of the Roshe Run quite simple: combine a clean midsole with minimal ridges and lines with a jordan 7 lightweight, mesh-based upper and you’ll get a shoe that’ll fly off the shelves. Much like how the Roshe jordan release became a hit without any hype,

oakleyses said...

jordan shoes, christian louboutin, uggs outlet, michael kors outlet online, uggs on sale, louis vuitton outlet, louis vuitton outlet, louis vuitton, ray ban sunglasses, replica watches, christian louboutin uk, chanel handbags, michael kors outlet online, uggs outlet, longchamp outlet, nike air max, michael kors outlet, burberry handbags, tiffany and co, polo outlet, nike free, nike air max, ugg boots, oakley sunglasses, ray ban sunglasses, michael kors outlet online, oakley sunglasses, christian louboutin outlet, longchamp outlet, prada handbags, gucci handbags, prada outlet, oakley sunglasses wholesale, michael kors outlet, oakley sunglasses, kate spade outlet, christian louboutin shoes, louis vuitton outlet, tory burch outlet, ugg boots, michael kors outlet online, burberry outlet, cheap oakley sunglasses, louis vuitton, ray ban sunglasses, nike outlet, longchamp outlet

Amycarry said...

Bathtub faucet : It is mounted on the top side of the tub for mixing hot and cold water open. Hot and cold can be Waterfall faucet by two pipes called duplex; hoist water lift spiral structure, metal ball-style, ceramic cartridge type and so on. Currently on the market is more single handle Faucet parts.

yanmaneee said...

louboutin shoes
nike basketball shoes
kd shoes
hogan outlet online
fila
coach outlet sale
yeezy 700
nike cortez men
reebok shoes
balenciaga shoes

jasonbob said...

kevin durant shoes
birkin bag
jordan 1 mid
birkin bag
supreme clothing
yeezy boost 350
jordan 6
golden goose outlet
steph curry shoes
off white clothing

PillsOnlineRx said...

Hey, thank you for your blog. It’s good and informative. Keep it up with your good work. Buy MTP Kit Online