Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    SF Bay Area

    Unanswered: How to drop DB with a deleted data file...

    I am a newbie with Oracle. Have tons of experience with SQL Server, Informix and MySQL and database theory.

    I need to know how I can drop a database that was never backed up, with noarchivelog set where the user managed to delete one of the datafiles.

    This is with Oracle 9i. Using OEM, I get the expected error that the file is missing when trying to take the database from Shutdown to Open.

    I can not use SQLPlus to access the database cause it wants an open database, which I can't do because of the missing datafile.

    I tried using RMAN to execute SQL commands to do everything from trying to take both the tablespace and datafile offline, to drop them, to even drop the database but to no availe.

    All I want to do at this point is drop the database so that I can recreate it.

    Thanks, I really appreciate it.


  2. #2
    Join Date
    Nov 2002
    as the database is not running; simply drop all the datafiles and cleanup your listener.ora and tnsnames.ora file
    or call dbca utility and drop the sid with all its assosiated files and entries whereever made

  3. #3
    Join Date
    Apr 2003
    In my opinion, the better option would be to recover the database. You may try this.
    a. go up to db mount phase
    b. off-line the tablespace for which datafile has been lost
    c. then open the database. I may give you the commands step-by-step if u need.

    And for dropping database, it is nothing like sybase or MS-SQL where we use to drop a database. (I am from the same background to Oracle, so i can feel it very well). In Oracle, the db is a logical instance. You hv to delete their linked files...It goes like this:
    1. All the database files
    2. All the COntrol files
    3. All the redo log files
    4. All the temporary tablespace files
    5. And the listener.ora & tnsnames.ora file. (if need or can be left like this).

    Hope that helps.

    Kamesh Rastogi
    - KR

  4. #4
    Join Date
    Apr 2002
    California, USA
    Deleting all datafiles, redo logs and control files (which makes the Oracle db) will do it.

    If you want to remove the Oracle software and you have Windows - then follow this procedure How to remove Oracle DB entries from Windows registry

    Hope that helps,

    clio_usa - OCP DBA 8/8i/9i

  5. #5
    Join Date
    Nov 2003
    SF Bay Area
    Hmmm. Seems like uninstalling Oracle and reinstalling Oracle is a bit heavy handed. I just would like to recover from this. In MS-SQL, you would simply offline and drop the database.

    To delete the files, do you mean that I go the C:/oracle/ora92/target and delete the entire contents? I tried deleting the database directory (which contains the datafiles) and restarted Oracle but was not happy (actually I simply moved them so I could put them back in this case).

    Sorry for the really basic questions. I am in the processes of learning Oracle but am charged with having to administer it while we convert from MS-SQL to Oracle.

Posting Permissions

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