V$recover_file, missing file

[yls177]

select * from v$recover_file;
FILE# ONLINE ERROR CHANGE# TIME
---------- ------- ------------------ ---------- ---------
67 OFFLINE FILE MISSING 0
1 row selected.


SVRMGR> select * from dba_data_files where file_id='67';
FILE_NAME FILE_ID TABLESPACE_NAME
BYTES BLOCKS STATUS RELATIVE_F AUT MAXBYTES MAXBLOCKS INCREMENT_
-------------------------------------------------------------------------------- ---------- ----------------------------
-- ---------- ---------- --------- ---------- --- ---------- ---------- ----------
/oracle/sid/dbs/MISSING00067 67 PSAPSTABD
1038082048 126719 AVAILABLE 67 NO 0 0 0


the original file is -rw-r----- 1 orasid dba 1038090240 Dec 10 15:45 /oracle/sid/sapdata3/stabd_4/stabd.data4

how can i recover?

[bmycroft]

Do you have a backup of that datafile and all the archivelogs from that backup to the point the datafile was removed?

I think you get these errors when the controlfile has been recreated and a file left out - Oracle KNOWS about the datafile as its in the dictionary so puts a "MISSING" entry in its place.

Read up on
"Note:77338.1
Subject: Datafiles named 'MISSING' " in Metalink.

If you still have the datafile on backup, put it back in place and try recreating the controlfile again. You will also need all the archived redo logs from the point the datafile left the database.

If you DONT have the datafile on backup but all the archived redologs you can probably recreate the datafile with 'alter database create datafile ' and recover - applying all thre redo.

If you DONT have the datafile and dont have all the redo then either go back to your last complete backup containing the file, or you may have to drop the tablespace - depends entirely on whether the database is live/test etc and how much data will be lost, the importance of the data etc.

-B

[yls177]

how about this?

alter tablespace PSAPBTABD offline normal
alter database datafile 'oracle/sid/sapdata3/stabd_4/stabd.data4' online;
alter tablespace PSAPBTABD online;

[bmycroft]

Good start but the problem is that the datafile is now called "/oracle/sid/dbs/MISSING00067" as far as the datadictionary is concerned. Try to a "rename datafile" command so that the dictionary thinks its called the same thing and then try bringing it onlimne - you will still need to do a recovery so will need the archived redo logs. Should be able to do a "recover tablespace PSAPBTABD " and then "alter tablespace PSAPBTABD online"

-B

[yls177]

okay.. this is what i got.....

ORA-01190: controlfile or data file 67 is from before the last RESETLOGS