OraFAQ Forum: Backup & Recovery » RECOVER A LOST DATAFILE WITHOUT USING RMAN

Home » RDBMS Server » Backup & Recovery » RECOVER A LOST DATAFILE WITHOUT USING RMAN (LINUX)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

RECOVER A LOST DATAFILE WITHOUT USING RMAN [message #526912]

Thu, 13 October 2011 12:35

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.

Report message to a moderator

Re: RECOVER A LOST DATAFILE WITHOUT USING RMAN [message #526914 is a reply to message #526912]

Thu, 13 October 2011 12:46

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

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

John Watson
Messages: 8960
Registered: January 2010
Location: Global Village

Senior Member

Is the database in archive log mode?

Report message to a moderator

Re: RECOVER A LOST DATAFILE WITHOUT USING RMAN [message #526916 is a reply to message #526915]

Thu, 13 October 2011 12:52

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.

Report message to a moderator

Re: RECOVER A LOST DATAFILE WITHOUT USING RMAN [message #526917 is a reply to message #526916]

Thu, 13 October 2011 12:54

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

Report message to a moderator

Re: RECOVER A LOST DATAFILE WITHOUT USING RMAN [message #526918 is a reply to message #526916]

Thu, 13 October 2011 12:55

inception
Messages: 7
Registered: October 2011

Junior Member

I do not have any latest backup. My last backup was taken on Monday.

Report message to a moderator

Re: RECOVER A LOST DATAFILE WITHOUT USING RMAN [message #526920 is a reply to message #526918]

Thu, 13 October 2011 12:57

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

Report message to a moderator

Re: RECOVER A LOST DATAFILE WITHOUT USING RMAN [message #526921 is a reply to message #526918]

Thu, 13 October 2011 12:57

inception
Messages: 7
Registered: October 2011

Junior Member

I want a solution without using RMAN Sad

. since RMAN is kind of corrupted.

Report message to a moderator

Re: RECOVER A LOST DATAFILE WITHOUT USING RMAN [message #526922 is a reply to message #526921]

Thu, 13 October 2011 12:59

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 . since RMAN is kind of corrupted.

You are free to not use RMAN & find different solution.
Good Luck with that!

Report message to a moderator

Re: RECOVER A LOST DATAFILE WITHOUT USING RMAN [message #526925 is a reply to message #526922]

Thu, 13 October 2011 13:11

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.

Report message to a moderator

Re: RECOVER A LOST DATAFILE WITHOUT USING RMAN [message #526926 is a reply to message #526925]

Thu, 13 October 2011 13:32

John Watson
Messages: 8960
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

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: RECOVER A LOST DATAFILE WITHOUT USING RMAN [message #526963 is a reply to message #526927]

Fri, 14 October 2011 00:57

inception
Messages: 7
Registered: October 2011

Junior Member

wow it worked. Thanks a lot John Watson and Michel Cadot.

Report message to a moderator

Re: RECOVER A LOST DATAFILE WITHOUT USING RMAN [message #526970 is a reply to message #526963]

Fri, 14 October 2011 01:46

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

Report message to a moderator

Re: RECOVER A LOST DATAFILE WITHOUT USING RMAN [message #526971 is a reply to message #526970]

Fri, 14 October 2011 01:52

John Watson
Messages: 8960
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

Report message to a moderator

Previous Topic:	RMAN
Next Topic:	rman backup restoration 11.1.0.7 to 11.2.0.2

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Nov 21 16:14:22 CST 2024