Thursday, January 17, 2013

How to recover from a loss of a nonsystem tablespace on the same location while the database is closed

In a previous post we saw how to proceed when you lose a non-system tablespace while the database is open.
Today I'm going to describe another way to restore it while the database is not open: for some reasons your database crashed and while trying to start it up you are getting "ORA-01157: cannot identify/lock data file %s - see DBWR trace file".

Let's simulate a loss of the EXAMPLE tablespace, in my case formed by only one datafile:
[oracle@localhost ~]$ ll /home/oracle/app/oracle/oradata/orcl/example01*
-rw-rw---- 1 oracle oracle 85991424 Nov 30 02:08 /home/oracle/app/oracle/oradata/orcl/example01.dbf
[oracle@localhost orcl]$ rm example01.dbf 
The database is not open and if I try to execute startup command it signals ORA-01157 error. That error means instance was not able to open the example01.dbf (data)file.
[oracle@localhost orcl]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Fri Jan 11 03:15:10 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  456146944 bytes
Fixed Size                  1344840 bytes
Variable Size             360712888 bytes
Database Buffers           88080384 bytes
Redo Buffers                6008832 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/home/oracle/app/oracle/oradata/orcl/example01.dbf'
If you look at the alert log the same error and a trace file are generated
...
Fri Jan 11 03:15:44 2013
ALTER DATABASE OPEN
Errors in file /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_dbw0_12904.trc:
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/home/oracle/app/oracle/oradata/orcl/example01.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Errors in file /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_12961.trc:
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/home/oracle/app/oracle/oradata/orcl/example01.dbf'
ORA-1157 signalled during: ALTER DATABASE OPEN...
...
Even if I know how to solve this kind of problem, I would like to take some of your time and use a new RMAN feature, the Data Recovery Advisor.
It can detect and show current restore and recovery problems occurring in your database, advices you about their resolution and even execute for you all the RMAN commands to fix problems.

When I remember to use Data Recovery Advisor I usually perform the following four commands:
RMAN> list failure; 
RMAN> list failure  detail;
RMAN> advice failure ;
RMAN> repair failure preview;
Let's see what Data Recovery Advisor shows us executing my previous commands. The instance is in MOUNT state and this is the output executing LIST FAILURE command:
[oracle@localhost ~]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Fri Jan 11 05:20:45 2013

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

connected to target database: ORCL (DBID=1229390655, not open)

RMAN> list failure;

using target database control file instead of recovery catalog
List of Database Failures
=========================

Failure ID Priority Status    Time Detected       Summary
---------- -------- --------- ------------------- -------
363        HIGH     OPEN      11-01-2013 03:15:46 One or more non-system datafiles are missing
As you can see one database failure exists from "11-01-2013 03:15:46" and it has a failure id as 363, it's status is still OPEN having also a HIGH priority.
We want now to have more details on that specific failure id: simply execute the following command to know example01.dbf datafile is missing and because it contains some objects they are not available.
RMAN> list failure 363 detail;

List of Database Failures
=========================

Failure ID Priority Status    Time Detected       Summary
---------- -------- --------- ------------------- -------
363        HIGH     OPEN      11-01-2013 03:15:46 One or more non-system datafiles are missing
  Impact: See impact for individual child failures
  List of child failures for parent failure ID 363
  Failure ID Priority Status    Time Detected       Summary
  ---------- -------- --------- ------------------- -------
  9865       HIGH     OPEN      11-01-2013 03:15:46 Datafile 5: '/home/oracle/app/oracle/oradata/orcl/example01.dbf' is missing
    Impact: Some objects in tablespace EXAMPLE might be unavailable
To obtain advices on how to solve your current failure run the following command.
It will show the manual or automated actions required to repair your database. The commands you can use to solve your issue automatically are contained into the repair script.
RMAN> advise failure 363;

List of Database Failures
=========================

Failure ID Priority Status    Time Detected       Summary
---------- -------- --------- ------------------- -------
363        HIGH     OPEN      11-01-2013 03:15:46 One or more non-system datafiles are missing

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK
analyzing automatic repair options complete

Mandatory Manual Actions
========================
no manual actions available

Optional Manual Actions
=======================
1. If file /home/oracle/app/oracle/oradata/orcl/example01.dbf was unintentionally renamed or moved, restore it

Automated Repair Options
========================
Option Repair Description
------ ------------------
1      Restore and recover datafile 5  
  Strategy: The repair includes complete media recovery with no data loss
  Repair script: /home/oracle/app/oracle/diag/rdbms/orcl/orcl/hm/reco_4119770863.hm
You can now open with a text editor your repair script to look at the suggested commands, but why not use again RMAN client and the REPAIR FAILURE PREVIEW command ?
As you can see RMAN displays the repair commands without actually running them.
RMAN> repair failure preview;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /home/oracle/app/oracle/diag/rdbms/orcl/orcl/hm/reco_4119770863.hm

contents of repair script:
   # restore and recover datafile
   restore datafile 5;
   recover datafile 5;
   sql 'alter database datafile 5 online';
So it's time to recover our tablespace executing the restore command and the name of your lost tablespace:
RMAN> restore tablespace example;

Starting restore at 11-01-2013 06:45:05
using channel ORA_DISK_1

channel ORA_DISK_1: restoring datafile 00005
input datafile copy RECID=32 STAMP=804375950 file name=/home/oracle/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_example_8gz9rctt_.dbf
destination for restore of datafile 00005: /home/oracle/app/oracle/oradata/orcl/example01.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00005
output file name=/home/oracle/app/oracle/oradata/orcl/example01.dbf RECID=0 STAMP=0
Finished restore at 11-01-2013 06:45:12
After your tablespace was restored back from your backup pieces it's time to execute the recover command.
RMAN> recover tablespace example;

Starting recover at 11-01-2013 06:45:18
using channel ORA_DISK_1

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

Finished recover at 11-01-2013 06:45:19
The tablespace is now recovered we can try to open our database. for your users.
RMAN> alter database open;

database opened
An extract of the alert log taken during the restore and recover process...
...
Fri Jan 11 06:45:10 2013
Restore of datafile copy /home/oracle/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_example_8gz9rctt_.dbf complete to datafile 5 /home/oracle/app/oracle/oradata/orcl/example01.dbf
  checkpoint is 14769061
Fri Jan 11 06:45:19 2013
alter database recover datafile list clear
Completed: alter database recover datafile list clear
alter database recover if needed
 tablespace EXAMPLE
Media Recovery Start
Serial Media Recovery started
Recovery of Online Redo Log: Thread 1 Group 2 Seq 38 Reading mem 0
  Mem# 0: /home/oracle/app/oracle/oradata/orcl/redo02.log
  Mem# 1: /home/oracle/app/oracle/oradata/orcl/redo02b.log
Media Recovery Complete (orcl)
Completed: alter database recover if needed
 tablespace EXAMPLE
alter database open
...
That's all.

31 comments:

Anonymous said...

I have read so many articles or reviews regarding the blogger lovers however this article
is genuinely a pleasant post, keep it up.

Here is my website forgot my password

Anonymous said...

My partner and I stumbled over here different page and thought I might as well check things out.
I like what I see so now i'm following you. Look forward to looking at your web page again.

Here is my site; pdf Password Remover

Anonymous said...

You can certainly see your expertise within the work you write.
The world hopes for more passionate writers like you who are not afraid to say how they believe.
All the time follow your heart.

Also visit my page ... optimine minecraft

Anonymous said...

Wonderful article! This is the type of information that are meant to be shared across the
internet. Shame on the seek engines for now not positioning this
put up higher! Come on over and consult with my website .
Thanks =)

Feel free to visit my website minecraft Download

Anonymous said...

Good information. Lucky me I recently found your blog by chance (stumbleupon).
I have bookmarked it for later!

Also visit my page :: sharecash bypass

Anonymous said...

If some one wishes to be updated with latest technologies then he
must be visit this website and be up to date every day.


My blog post marketing Search online

Anonymous said...

Very descriptive post, I liked that a lot.
Will there be a part 2?

Feel free to visit my webpage Candy Food Art

Anonymous said...

You actually make it seem so easy with your presentation but I find this topic to be
actually something which I think I would never understand.
It seems too complex and very broad for me.
I'm looking forward for your next post, I'll try to get the hang of it!


Also visit my web page :: landwirtschafts simulator download

Anonymous said...

fantastic points altogether, you just received a emblem new reader.

What might you recommend in regards to your publish that you
simply made some days in the past? Any sure?

My website; farmville manager

Anonymous said...

Hey there, You've done an excellent job. I'll definitely digg
it and personally recommend to my friends. I'm sure they'll be benefited from this website.


Here is my page ... dragonvale cheats

Anonymous said...

Yes! Finally something about website cheats.

Feel free to visit my blog post Cityville wiki

Anonymous said...

This piece of writing is truly a fastidious one it helps new internet visitors, who are wishing in
favor of blogging.

Take a look at my webpage: how to make a website and make money

Anonymous said...

Hi my friend! I want to say that this post is amazing,
nice written and include almost all important infos.

I would like to look more posts like this .

Here is my web page; castleville cheat codes

Anonymous said...

It is actually a great and helpful piece of
info. I'm satisfied that you just shared this useful information with us. Please keep us up to date like this. Thanks for sharing.

Here is my page - $20 PSN Card

Anonymous said...

This is really interesting, You're an excessively professional blogger. I've
joined your rss feed and look ahead to looking for extra of your fantastic post.
Also, I have shared your site in my social networks

Feel free to surf to my web blog :: sharecash surveys

Anonymous said...

Hi there this is kind of of off topic but I was wondering if blogs use WYSIWYG editors
or if you have to manually code with HTML.
I'm starting a blog soon but have no coding know-how so I wanted to get guidance from someone with experience. Any help would be enormously appreciated!

Also visit my web blog :: free psn codes generator download

Anonymous said...

Hi, its pleasant paragraph about media print,
we all be familiar with media is a great source of information.


Feel free to surf to my weblog ... minecraft download

Anonymous said...

We are a group of volunteers and opening a new scheme in our community.
Your website offered us with valuable info to work on. You have done
an impressive job and our whole community will be grateful to you.


Feel free to visit my page: free minecraft giftcode ()

Anonymous said...

I seldom drop comments, however i did a few searching and
wound up here "How to recover from a loss of a nonsystem tablespace on the same location while the database is closed".
And I do have some questions for you if you don't mind. Is it simply me or does it give the impression like a few of these comments appear as if they are coming from brain dead people? :-P And, if you are posting at additional online social sites, I would like to follow anything new you have to post. Could you list of the complete urls of your community pages like your twitter feed, Facebook page or linkedin profile?

Here is my blog post: Oph Crack

Anonymous said...

Spot on with this write-up, I honestly think this amazing site needs a great deal more attention.

I'll probably be back again to see more, thanks for the information!

Take a look at my web-site Free Minecraft

Anonymous said...

Very descriptive post, I loved that a lot.
Will there be a part 2?

Stop by my web site :: Working New Adf.ly Clicker 2013

Anonymous said...

These are in fact great ideas in concerning blogging.
You have touched some fastidious things here.
Any way keep up wrinting.

Also visit my page; dragon vale

Anonymous said...

Hi, Neat post. There is an issue with your website in web
explorer, could test this? IE nonetheless is the marketplace leader
and a big portion of other people will omit your fantastic writing because of this problem.



My blog - dragons

Anonymous said...

With havin so much content do you ever run into any issues
of plagorism or copyright infringement? My site has
a lot of completely unique content I've either created myself or outsourced but it looks like a lot of it is popping it up all over the internet without my permission. Do you know any ways to help protect against content from being ripped off? I'd truly appreciate it.


Here is my blog post - sharecash downloader 2013

Coach Factory said...

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

Coach Factory said...

hogan sito ufficiale, mulberry, montre femme, new balance pas cher, hollister, karen millen, lululemon, hollister, hollister, louis vuitton, oakley pas cher, ralph lauren, burberry, longchamp soldes, louis vuitton uk, nike roshe, air force, nike trainers, nike free pas cher, timberland, ray ban uk, nike air max, longchamp, north face pas cher, nike huarache, north face, nike roshe run, tn pas cher, vans outlet, vans pas cher, hermes pas cher, ralph lauren pas cher, lacoste, michael kors, michael kors, nike free, vanessa bruno, michael kors, barbour, sac guess, sac louis vuitton, air max, sac louis vuitton, longchamp, louboutin, air jordan, ray ban pas cher, abercrombie and fitch, nike blazer, converse pas cher

Coach Factory said...

moncler outlet, ghd, uggs on sale, canada goose pas cher, lululemon outlet, canada goose, moncler, celine handbags, canada goose outlet, juicy couture outlet, ugg boots, herve leger, north face jackets, nfl jerseys, soccer jerseys, mac cosmetics, instyler ionic styler, rolex watches, ugg outlet, valentino shoes, canada goose outlet, p90x3, beats by dre, birkin bag, babyliss pro, moncler, marc jacobs, ugg boots, abercrombie and fitch, moncler, mont blanc, north face outlet, mcm handbags, wedding dresses, canada goose outlet, chi flat iron, supra shoes, juicy couture outlet, ugg, insanity, reebok outlet, new balance shoes, roshe run, bottega veneta, asics shoes, giuseppe zanotti, ferragamo shoes, jimmy choo shoes, soccer shoes, canada goose uk

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