How Drop Tablespace and Recover Oracle Database When Accidentally Delete Datafile

Accident may happens, even though it’s costly one. This is especially the case in Oracle database, which stores the database objects physically in data files on hard disk. Fast hand administrators may accidentally delete some of the datafiles, or the datafiles may get corrupted or unreadable on hard disk failure. You may get also some missing datafiles after database recovery process. In any case, missing a or a few datafiles will cause Oracle database to fail to startup, rendering the whole DB inaccessible.

The solution to the missing datafiles is to drop the affected tablespace where has incomplete datafiles, and then recreate the tablespace and import the data into the tablespace from backup. However, the steps are not so straight forward.

  1. Run SQL*Plus.
  2. Connect to database as SYSDBA with this query:

    CONNECT / AS SYSDBA

  3. Mount the database instead of starting it up:

    STARTUP MOUNT;

  4. Issue the following command to bring the missing datafile offline so that Oracle won’t trying to connect and access the datafile anymore:

    ALTER DATABASE DATAFILE ‘<datafile name with complete path>’ OFFLINE DROP;

    Repeat the command for every datafiles that unaccounted for.

  5. Now start the database proper:

    ALTER DATABASE OPEN;

  6. As the tablespace has damaged, drop it to recreate from fresh backup.

    DROP TABLESPACE <tablespace name> INCLUDING CONTENTS;

  7. Ensure the other datafiles for the tablespace has been deleted, if not, remove them manually from the operating system.

    Tip: How to check datafiles that belong to tablespace.

  8. Continue with the recovery process.

Share and contribute or get technical support and help at My Digital Life Forums.



2 Responses to “How Drop Tablespace and Recover Oracle Database When Accidentally Delete Datafile”

  1. How to Remove and Drop Datafiles from Tablespace in Oracle Database » My Digital Life
    August 4th, 2007 02:57
    1

    [...] If one or more datafiles is missing after a recovery process or accidental deletion, you can use ALTER DATABASE DATAFILE <datafile name> OFFLINE DROP command to make the datafile offline so that database can starts up after which the troubled tablespace can be dropped. (See instruction to recover from missing datafiles.) [...]

  2. LP
    April 2nd, 2008 18:27
    2

    These are only to drop the TS

    but not to recover…

    hence the worst….

Leave a Reply

You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Subscribe without commenting


Custom Search

New Articles

Incoming Search Terms for the Article

oracle drop tablespace - drop tablespace - drop tablespace in oracle - recover tablespace - drop tablespace oracle - drop tablespace offline - offline drop - oracle drop database - alter database drop tablespace - drop datafile - recover oracle datafile - drop oracle tablespace - recover datafile oracle - drop tablespace mount - connect role+dropped accidentally - dropping tablespace in oracle - oracle recover tablespace - oracle recover datafile - DROP DATA FILE - oracle delete datafile - oracle recover database - recover datafile without backup - restore oracle database - drop tablespace missing datafile - tablespace offline drop - recover tablespace - datafile offline drop - oracle datafile accidentally deleted - recover oracle data file - oracle remove tablespace - recover data file oracle - oracle drop table space - ORACLE tablespace missing - oracle recover - dropping a tablespace - recover datafile - oracle accidentally deleted data files - recovery database from datafiles - how can recover data from archive files on oracle? - drop tablespace with missing datafile - recover oracle database missing data file - drop tablespace not login - how to recover oracle database - how to recover oracle datafiles - delete tablespace in oracle - drop temporary tablespace oracle - offline drop tablespace - delete oracle database - drop offline tablespace - offline datafile oracle database -