OraFAQ Forum: Backup & Recovery

Home » RDBMS Server » Backup & Recovery » Datafile missing

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

Datafile missing [message #271547]

Tue, 02 October 2007 00:41

win3vin
Messages: 35
Registered: April 2007
Location: Malaysia

Member

Hi

one of the datafile was accidentally deleted physically in OS by someone. The datafile was recently added and is in recover state(offline).

How do I remove it from our database and dictionary? Can I add back after I drop it?

Environment:
Oracle 9.2.0.7 under Win2003. The DB backup is using export/import method

Report message to a moderator

Re: Datafile missing [message #271552 is a reply to message #271547]

Tue, 02 October 2007 00:47

Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...

Senior Member

Quote:

How do I remove it from our database and dictionary?

Query dba_data_files and see location. and then alter tablespace .. drop datafile location;

Quote:

Can I add back after I drop it?

Yes.

Report message to a moderator

Re: Datafile missing [message #271561 is a reply to message #271547]

Tue, 02 October 2007 01:03

Michel Cadot
Messages: 68653
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

How do I remove it from our database and dictionary?

Drop the tablespace

Quote:

Can I add back after I drop it?

Restore and recover the tablespace (if you are in ARCHIVELOG mode).

Quote:

The DB backup is using export/import method

Aaargh! You're down, export is not a backup method and you've just learned it.
Next time use real backup method like RMAN.

Regards
Michel

Report message to a moderator

Re: Datafile missing [message #271566 is a reply to message #271547]

Tue, 02 October 2007 01:11

win3vin
Messages: 35
Registered: April 2007
Location: Malaysia

Member

Could I use this method to recreate the files?

1)Recreate the datafile.
> alter database create datafile '/tmp/sample.dbf' as '/tmp/sample.dbf' size 10240 reuse.

2)Bring the file online.
> alter database datafile '/tmp/sample.dbf' online;

3)Recover the datafile.
> Recover database;

Report message to a moderator

Re: Datafile missing [message #271568 is a reply to message #271566]

Tue, 02 October 2007 01:37

Michel Cadot
Messages: 68653
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Only if you are in ARCHIVELOG mode and have all the archived logs since original creation of the file.

Regards
Michel

Report message to a moderator

Re: Datafile missing [message #271580 is a reply to message #271547]

Tue, 02 October 2007 02:01

win3vin
Messages: 35
Registered: April 2007
Location: Malaysia

Member

i don't wish to drop the tablespace as there are other files in there. Could I remove only the datafile?

Report message to a moderator

Re: Datafile missing [message #271585 is a reply to message #271580]

Tue, 02 October 2007 02:10

Michel Cadot
Messages: 68653
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

No you can't.

Regards
Michel

Report message to a moderator

Previous Topic:	Listener error
Next Topic:	lost system tablespace datafile in oracle8i

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon May 20 16:28:46 CDT 2024