Home » RDBMS Server » Backup & Recovery » Datafile missing
Datafile missing [message #271547] Tue, 02 October 2007 00:41 Go to next message
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
Re: Datafile missing [message #271552 is a reply to message #271547] Tue, 02 October 2007 00:47 Go to previous messageGo to next message
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.
Re: Datafile missing [message #271561 is a reply to message #271547] Tue, 02 October 2007 01:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
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
Re: Datafile missing [message #271566 is a reply to message #271547] Tue, 02 October 2007 01:11 Go to previous messageGo to next message
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;
Re: Datafile missing [message #271568 is a reply to message #271566] Tue, 02 October 2007 01:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
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
Re: Datafile missing [message #271580 is a reply to message #271547] Tue, 02 October 2007 02:01 Go to previous messageGo to next message
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?
Re: Datafile missing [message #271585 is a reply to message #271580] Tue, 02 October 2007 02:10 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No you can't.

Regards
Michel
Previous Topic: Listener error
Next Topic: lost system tablespace datafile in oracle8i
Goto Forum:
  


Current Time: Sat Dec 10 01:10:11 CST 2016

Total time taken to generate the page: 0.06712 seconds