Home » RDBMS Server » Server Administration » how do i drop tablespace that there datafiles has been dropped
how do i drop tablespace that there datafiles has been dropped [message #342589] Sat, 23 August 2008 00:22 Go to next message
paul.cj
Messages: 3
Registered: August 2008
Location: Lagos
Junior Member

To bring up my database
I get this error.
Meanwhile the datafiles beiing refered to has been lost due to media failure and no backup
How do i come out of this.


Errors in file /ora_dump/CONCILIA/bdump/concilia_dbw0_160076.trc:
ORA-01157: cannot identify/lock data file 121 - see DBWR trace file
ORA-01110: data file 121: '/ora_data08/CONCILIA/USAGE_INDEX23_01.dbf'
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information:


Re: ORA-01157: cannot identify/lock data file 121 - see DBWR trace file [message #342592 is a reply to message #342589] Sat, 23 August 2008 01:12 Go to previous messageGo to next message
nazbrian
Messages: 36
Registered: July 2008
Member
Hi,

Do you have archive backup? If so u can apply archive and re-create.

Otherwise, incomplete recovery Embarassed

Naz.
Re: ORA-01157: cannot identify/lock data file 121 - see DBWR trace file [message #342596 is a reply to message #342589] Sat, 23 August 2008 01:25 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Meanwhile the datafiles beiing refered to has been lost due to media failure and no backup

Without backup you can't do anything.
Re: ORA-01157: cannot identify/lock data file 121 - see DBWR trace file [message #342600 is a reply to message #342592] Sat, 23 August 2008 01:35 Go to previous messageGo to next message
paul.cj
Messages: 3
Registered: August 2008
Location: Lagos
Junior Member

I have some few archives because there is no backup initiall and the archive location gets to 100% and shut everything stop, so i have to move some out to another location and zipped some inside the archive location ;this creates more space in archive location and and everything started working fine.
So i take backup of the archive to another location with delete input,so all those that were moved out to another location and those that were zipped inside the archive location were not backed up.I noticed this when i ran crosscheck archivelog all;

also a lot of datafiles are nolonger existing in the os level and no backup at all;
Tell me exactly what to do at this moment to open the database.This is a production database.

Thanks and regards for your quick response.
Re: ORA-01157: cannot identify/lock data file 121 - see DBWR trace file [message #342604 is a reply to message #342600] Sat, 23 August 2008 01:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64135
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Startup mount, put the file in "offline drop" mode then you can open the database, drop the tablespace and recreate it and what was in it. Given its name I think (and hope) it is just recreate indexes.

Regards
Michel
how do i drop tablespace that there datafiles has been dropped [message #342636 is a reply to message #342589] Sat, 23 August 2008 08:00 Go to previous messageGo to next message
paul.cj
Messages: 3
Registered: August 2008
Location: Lagos
Junior Member

pls need your input on the actual command to use.
Re: how do i drop tablespace that there datafiles has been dropped [message #342638 is a reply to message #342636] Sat, 23 August 2008 08:57 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW the Posting Guidelines as stated in URL above
Re: how do i drop tablespace that there datafiles has been dropped [message #342649 is a reply to message #342636] Sat, 23 August 2008 10:08 Go to previous message
Michel Cadot
Messages: 64135
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL Reference
ALTER DATABASE
DROP TABLESPACE

Regards
Michel
Previous Topic: Rebuild and Move index to another Tablespace
Next Topic: How to change the character set in oracle
Goto Forum:
  


Current Time: Thu Dec 08 05:55:24 CST 2016

Total time taken to generate the page: 0.05486 seconds