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.

27 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

mytapworld said...

If you are cerebration about affairs curtains online,Taps Australia there are assertive factors to accumulate in mind.Kitchen taps To activate with,Shower heads the abundance of baptize accessible to the tap is important while selecting which artefact to buy. Since the algid baptize accumulation is accessible at mains pressure,www.tapworld.com.au it is about not of abundant importance. However,tapworld the tap will charge the appropriate bulk of hot baptize burden to backpack out its functions satisfactorily.

finestglasses said...

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

robinet sale 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...

air max, hollister, true religion outlet, nike blazer, louboutin, ray ban sunglasses, polo ralph lauren, michael kors, true religion jeans, sac guess, sac longchamp, hogan outlet, ralph lauren, vans pas cher, sac louis vuitton, air max pas cher, nike free pas cher, nike free, air max, mulberry, nike roshe run, sac burberry, hollister, vanessa bruno, louis vuitton, lululemon, michael kors pas cher, oakley pas cher, air jordan, ray ban pas cher, new balance pas cher, polo lacoste, converse pas cher, north face, sac louis vuitton, michael kors, sac hermes, nike tn, timberland, louis vuitton uk, longchamp, true religion jeans, nike air max, air force, north face

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

Mike Smith 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

Mike Smith 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,

Zhu Y said...

Our pneumatic factory was founded in 2000,CHECK HERE. In decades of serving China pneumatic industry, we have built our reputation on product knowledge, world class suppliers, application support, and value add services. Our commitment to our employees, customers and vendors is the core of our business beliefs. In our factory, there are more than 80 front-line workers, 3000 squares working shop.Our main products are air Pneumatic Cylinder, solenoid valve and also a lot of pneumatic fittings like Air Gun.

dc Z said...

Buy Kitchen Taps. Save 20-45% off Retail Prices, Free Shipping, No Tax. Complete selection of Kitchen Taps, Pullout Taps, Stainless Steel Taps and more.http://www.findtaps.co.uk/2-kitchen-taps

Cheap Tap said...

All of our products are sold from factory directly, igorous manufacturing and testing processes. You must can find one suit for your house here. We also offering everyday update special price products. The more products you buy, the more discounts will be.Choosing from a large selection of high quality taps at http://www.cheaptap.co.uk/ . Find new design of antique taps, LED taps, waterfall taps and so on.

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

oakleyses said...

sac vanessa bruno, new balance, vans pas cher, ray ban uk, nike blazer pas cher, true religion outlet, michael kors outlet, true religion outlet, replica handbags, polo lacoste, oakley pas cher, coach purses, hollister uk, abercrombie and fitch uk, nike free uk, north face uk, louboutin pas cher, polo ralph lauren, hollister pas cher, nike air max uk, michael kors pas cher, nike air max, true religion jeans, timberland pas cher, nike air max uk, coach outlet, air max, michael kors, jordan pas cher, sac hermes, north face, lululemon canada, coach outlet store online, nike roshe, sac longchamp pas cher, nike air force, mulberry uk, hogan outlet, ralph lauren uk, longchamp pas cher, michael kors, converse pas cher, burberry pas cher, nike roshe run uk, true religion outlet, kate spade, nike free run, nike tn, ray ban pas cher, guess pas cher

oakleyses said...

asics running shoes, babyliss, soccer jerseys, hermes belt, reebok outlet, ipad cases, oakley, iphone cases, soccer shoes, iphone 5s cases, nfl jerseys, north face outlet, abercrombie and fitch, ghd hair, vans outlet, iphone 6 cases, hollister, nike roshe run, wedding dresses, mac cosmetics, lululemon, new balance shoes, jimmy choo outlet, instyler, giuseppe zanotti outlet, p90x workout, s6 case, chi flat iron, iphone 6s cases, longchamp uk, baseball bats, mcm handbags, iphone 6 plus cases, bottega veneta, ferragamo shoes, timberland boots, mont blanc pens, insanity workout, nike air max, nike trainers uk, herve leger, nike huaraches, celine handbags, north face outlet, beats by dre, iphone 6s plus cases, valentino shoes, ralph lauren, hollister clothing, louboutin

oakleyses said...

moncler uk, louis vuitton, thomas sabo, wedding dresses, barbour, moncler, gucci, montre pas cher, supra shoes, hollister, barbour uk, nike air max, karen millen uk, pandora uk, moncler, canada goose uk, coach outlet, ugg, juicy couture outlet, swarovski, canada goose, louis vuitton, moncler outlet, louis vuitton, hollister, ugg,ugg australia,ugg italia, canada goose outlet, replica watches, pandora jewelry, ugg,uggs,uggs canada, moncler, canada goose outlet, ugg pas cher, louis vuitton, juicy couture outlet, swarovski crystal, louis vuitton, canada goose, pandora charms, canada goose outlet, links of london, marc jacobs, lancel, converse, converse outlet, toms shoes, doudoune moncler, moncler, pandora jewelry, canada goose jackets, vans, canada goose

Amy Carry 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.