Home » RDBMS Server » Backup & Recovery » RECOVER A LOST DATAFILE WITHOUT USING RMAN (LINUX)
RECOVER A LOST DATAFILE WITHOUT USING RMAN [message #526912] Thu, 13 October 2011 12:35 Go to next message
inception
Messages: 7
Registered: October 2011
Junior Member
I have no backup taken in my oracle server.
Today morning i created a data file and unknowingly deleted it.
Now i need to restore the datafile with its contents without using RMAN. Iam confused could anyone help me in figuring out.
Re: RECOVER A LOST DATAFILE WITHOUT USING RMAN [message #526914 is a reply to message #526912] Thu, 13 October 2011 12:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Are you in ARCHIVELOG mode or not?
Have you an OS backup?
Post what you EXACTLY did and get.
ALWAYS post your Oracle version, with 4 decimals.

Regards
Michel

[Updated on: Thu, 13 October 2011 12:46]

Report message to a moderator

Re: RECOVER A LOST DATAFILE WITHOUT USING RMAN [message #526915 is a reply to message #526912] Thu, 13 October 2011 12:47 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Is the database in archive log mode?
Re: RECOVER A LOST DATAFILE WITHOUT USING RMAN [message #526916 is a reply to message #526915] Thu, 13 October 2011 12:52 Go to previous messageGo to next message
inception
Messages: 7
Registered: October 2011
Junior Member
My database version is 10.2.0.1.0
my database is in archivelog mode
i created tablespace with a datafile in it. and i unknowingly dropped that datafile in unix prompt today morning. I noticed it in evening.
Re: RECOVER A LOST DATAFILE WITHOUT USING RMAN [message #526917 is a reply to message #526916] Thu, 13 October 2011 12:54 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>I noticed it in evening.
If DB was not closed between dropping file & now, then Oracle holds open the data file
& you can obtain complete RMAN level 0 backup
Re: RECOVER A LOST DATAFILE WITHOUT USING RMAN [message #526918 is a reply to message #526916] Thu, 13 October 2011 12:55 Go to previous messageGo to next message
inception
Messages: 7
Registered: October 2011
Junior Member
I do not have any latest backup. My last backup was taken on Monday.
Re: RECOVER A LOST DATAFILE WITHOUT USING RMAN [message #526920 is a reply to message #526918] Thu, 13 October 2011 12:57 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
If DB was not closed between dropping file & now,
then Oracle holds open the data file you can take NEW complete RMAN level 0 backup now
Re: RECOVER A LOST DATAFILE WITHOUT USING RMAN [message #526921 is a reply to message #526918] Thu, 13 October 2011 12:57 Go to previous messageGo to next message
inception
Messages: 7
Registered: October 2011
Junior Member
I want a solution without using RMAN Sad. since RMAN is kind of corrupted.
Re: RECOVER A LOST DATAFILE WITHOUT USING RMAN [message #526922 is a reply to message #526921] Thu, 13 October 2011 12:59 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
iamdeepak wrote on Thu, 13 October 2011 10:57
I want a solution without using RMAN Sad. since RMAN is kind of corrupted.


You are free to not use RMAN & find different solution.
Good Luck with that!
Re: RECOVER A LOST DATAFILE WITHOUT USING RMAN [message #526925 is a reply to message #526922] Thu, 13 October 2011 13:11 Go to previous messageGo to next message
inception
Messages: 7
Registered: October 2011
Junior Member
can someone give me solution without using RMAN. I tried my level best but could not recover the datafile with contents.
Re: RECOVER A LOST DATAFILE WITHOUT USING RMAN [message #526926 is a reply to message #526925] Thu, 13 October 2011 13:32 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Hi - as you are in archivelog mode, it is no big deal. You need to create a new datafile, based on the defnition of the existing (deleted) datafile, then recover it by applying all the archivelogs since it was first created.

The routine is described in te docs, but here it is:

shutdown immediate;
startup mount;
create datafile 'the datafile name' as 'the data file name';
recover datafile 'the datafile name';


[update: correction, the third command above should read
alter database create datafile 'the datafile name' as 'the data file name';
sorry about that.]



[Updated on: Thu, 13 October 2011 15:13]

Report message to a moderator

Re: RECOVER A LOST DATAFILE WITHOUT USING RMAN [message #526927 is a reply to message #526925] Thu, 13 October 2011 13:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ RMAN is NOT corrupted, maybe just you don't know how to use it.
2/ (Before take a backup of your database)
- Take offline the datafile (X is the number of the datafile)
alter database datafile X offline;

- Create an empty datafile
alter database create datafile X as 'deleted file specification';

- recover the file (hoping you have all archived logs since file creation)
alter database recover automatic datafile X;

- take the datafile online
alter database datafile X online;


Regards
Michel

Re: RECOVER A LOST DATAFILE WITHOUT USING RMAN [message #526963 is a reply to message #526927] Fri, 14 October 2011 00:57 Go to previous messageGo to next message
inception
Messages: 7
Registered: October 2011
Junior Member
wow it worked. Thanks a lot John Watson and Michel Cadot.
Re: RECOVER A LOST DATAFILE WITHOUT USING RMAN [message #526970 is a reply to message #526963] Fri, 14 October 2011 01:46 Go to previous messageGo to next message
inception
Messages: 7
Registered: October 2011
Junior Member
As a learning mechanism could you help me in clearing the below doubts
with this same scenario if i had lost only either redo log file or control file then how in that case how can i recover it with using rman and without using rman.

Lets say the my oracle is in archivelog mode and version is same as .10.2.0.1.0
Re: RECOVER A LOST DATAFILE WITHOUT USING RMAN [message #526971 is a reply to message #526970] Fri, 14 October 2011 01:52 Go to previous message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
as a learning mechanism, rather than an emrgency (which I assume your original message was) you should start by reading the relevant docs:
http://www.oracle.com/pls/db102/to_toc?pathname=backup.102%2Fb14191%2Ftoc.htm
Previous Topic: RMAN
Next Topic: rman backup restoration 11.1.0.7 to 11.2.0.2
Goto Forum:
  


Current Time: Thu Mar 28 16:31:09 CDT 2024