missing datafile

[feroz]

Hi all,

Im having some problem to drop a missing datafile. Here the scenario:

I took a backup copy of datafile from prod machine and restored on test machine. Since I dont have enough space on test machine I exluded some of datafile in controlfile sql which I took from live machine (alter database backup controlfile to trace). My intention is to re-create new controlfile. The database is up and running as we speak here. My concern is the error message that I got from alert log file.

Dictionary check beginning
Tablespace 'SMMT_HK' #15 found in data dictionary,
but not in the controlfile. Adding to controlfile.
File #9 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00009' in the controlfile.
This file can no longer be recovered so it must be dropped.
File #25 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00025' in the controlfile.
This file can no longer be recovered so it must be dropped.
Dictionary check complete

By any chance I could drop a missing datafile from the dba_data_files table? as when i query total byte for each tablespace it included the missing one.

Anybody could give me some advice, please? Thank you in advance.

regards
feroz

[alapps]

feroz,

If your datafile(s) is/are missing and you want to remove them from your database layout (i.e. the database will not open) you can drop them with the following:

*** Put the database in MOUNT state ***
Start the instance
Mount the database
Do NOT open then database


*** Offline drop the datafile ***
ALTER DATABASE DATAFILE '[fully qualified datafile name]' OFFLINE DROP;

This will remove the datafile from the data dictionary and control files (be sure to remove all datafiles associated with the datafile you just dropped).

*** Open the database ***
you should be able to open the database (less the datafile(s) and tablespace(s) associated with the dropped datafile(s))

Hope this helps...