missing datafile
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: missing datafile

  1. #1
    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

  2. #2
    Join Date
    Jul 2002
    Location
    California
    Posts
    128

    Exclamation

    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...


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width