OraFAQ Forum: Server Administration » how do i drop tablespace that there datafiles has been dropped

Home » RDBMS Server » Server Administration » how do i drop tablespace that there datafiles has been dropped

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

how do i drop tablespace that there datafiles has been dropped [message #342589]

Sat, 23 August 2008 00:22

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:

Report message to a moderator

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

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.

Report message to a moderator

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

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.

Report message to a moderator

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

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.

Report message to a moderator

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

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

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

Report message to a moderator