RECOVERY URGENT!!!

[vinodkmg]

Running oracle 7.x on AIX unix.

I was in the process of dropping the tablespaces which are no longer required.so when i issued a command drop tablespace <> including contents...when it was processing i deleted the file from the O/S.

while i got this error.so how to get rid of this and drop that tablespace.I dont have offline backup and the database is running in noarchive

ERROR at line 1:
ORA-01116: error in opening database file 46
ORA-01110: data file 46: '/local/musuw00/dat01/oradata/ORANGE/rrrr_indx_01.dbf'
ORA-07368: sfofi: open error, unable to open database file.
IBM AIX RISC System/6000 Error: 2: No such file or directory

[Gopinath]

Hi,
Shutdown the database using shutdown immediate/normal.
Startup mount the datbase and drop the datafile, ie
startup mount;
alter database datafile 'file_name' offline drop;
then open the database, alter database open;
and then drop the tablespace:drop tablespace tablespace_name including contents;
I think This should work;

[coolmandba]

vinodkmg,

I don't think 'alter database datafile 'file_name' offline drop'
would work in this case, since the datafile is missing and the database wouldn't be able to find the file.

I just posted a thread similar to Gopinath's situation, and I tried that yesterday it didn't work, unless I was mistaken.

Please take a look of my thread 'Urgent! Recovery datafile'.

[gnagesh]

Hi,

Try this. Please let me know If i am wrong.

Just you create one a file using unix command with the same name. You can do like this.

$>/local/musuw00/dat01/oradata/ORANGE/rrrr_indx_01.dbf

the above command will create a zero byte file in the specified directory. Then you can proceed further.

[Gopinath]

I have simulated the above situation, and the log is reproduced below
When the instance was running, i deleted the datafile data.dbf of the tablespace data.
then tried dropping the tablespace, the rest is shown below.

SVRMGR> drop tablespace data including contents;
drop tablespace data including contents
*
ORA-01116: error in opening database file 5
ORA-01110: data file 5: '/mount1/oradata/test805/data.dbf'
ORA-27041: unable to open file
Intel SVR4 UNIX Error: 2: No such file or directory
Additional information: 3
SVRMGR> shutdown immediate;
ORA-01116: error in opening database file 5
ORA-01110: data file 5: '/mount1/oradata/test805/data.dbf'
ORA-27041: unable to open file
Intel SVR4 UNIX Error: 2: No such file or directory
Additional information: 3

SVRMGR> alter database datafile '/mount1/oradata/test805/data.dbf' offline dro
p;
Statement processed.


SVRMGR> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SVRMGR> startup mount;
ORACLE instance started.
Total System Global Area 4475936 bytes
Fixed Size 56352 bytes
Variable Size 3936256 bytes
Database Buffers 409600 bytes
Redo Buffers 73728 bytes
Database mounted.
SVRMGR> select name, status from v$datafile;
NAME
STATUS
--------------------------------------------------------------------------------
-------
/mount1/oradata/test805/oradata/test805/system01.dbf
SYSTEM
/mount1/oradata/test805/oradata/test805/rbs01.dbf
ONLINE
/mount1/oradata/test805/oradata/test805/temp01.dbf
ONLINE
/mount1/oradata/test805/oradata/test805/tools01.dbf
OFFLINE
/mount1/oradata/test805/data.dbf
RECOVER
5 rows selected.
SVRMGR> alter database datafile '/mount1/oradata/test805/data.dbf' offline drop;
Statement processed.
SVRMGR> select name, status from v$datafile;
NAME
STATUS
--------------------------------------------------------------------------------
-------
/mount1/oradata/test805/oradata/test805/system01.dbf
SYSTEM
/mount1/oradata/test805/oradata/test805/rbs01.dbf
ONLINE
/mount1/oradata/test805/oradata/test805/temp01.dbf
ONLINE
/mount1/oradata/test805/oradata/test805/tools01.dbf
OFFLINE
/mount1/oradata/test805/data.dbf
RECOVER
5 rows selected.
SVRMGR> alter database open;
Statement processed.
SVRMGR> drop tablespace data including contents;
Statement processed.
SVRMGR> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SVRMGR> startup
ORACLE instance started.
Total System Global Area 4475936 bytes
Fixed Size 56352 bytes
Variable Size 3936256 bytes
Database Buffers 409600 bytes
Redo Buffers 73728 bytes
Database mounted.
Database opened.
SVRMGR> select name, status from v$datafile;
NAME
STATUS
--------------------------------------------------------------------------------
-------
/mount1/oradata/test805/oradata/test805/system01.dbf
SYSTEM
/mount1/oradata/test805/oradata/test805/rbs01.dbf
ONLINE
/mount1/oradata/test805/oradata/test805/temp01.dbf
ONLINE
/mount1/oradata/test805/oradata/test805/tools01.dbf
OFFLINE
4 rows selected.

So finally, the tablespace data is gone.

[SANJAY_G]

What Gopinath did is the correct way to do this. I too have done such recovery before.

Sanjay