Alter Database Datafile Offline Drop
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Alter Database Datafile Offline Drop

Hybrid View

  1. #1
    Join Date
    Nov 2002
    Location
    INDIA
    Posts
    15

    Alter Database Datafile Offline Drop

    Hi,

    One of my datafile got corrupted and one of the DATA tablespace is in danger. Unfortunately, I do not have any backup of the datafile.
    I referred to metalink, and got some info. I am attaching the metalink document here.

    METALINK DOCUMENT
    -----------------
    Displayed below are the messages of the selected thread.

    Thread Status: Closed

    From: Hans-Gerd Haase 03-Sep-02 10:46
    Subject: Drop datafile before removing it from database

    RDBMS Version: 7.3.4.0.0
    Operating System and Version: HP-UX 10.20
    Error Number (if applicable):
    Product (i.e. SQL*Loader, Import, etc.):
    Product Version: 7.3.4

    Drop datafile before removing it from database

    Hallo
    We have migrated some databases to Oracle 8.x.x and I had to drop some old users/schematas from our oracle test server, which we do not need anymore. But I removed one datafile on unix, after 'drop user xyz cascade', but before setting the tablespace offline and before removing it from the database.
    The database is in noarchive mode, there are no backups of the datafiles, there is only a full export.
    What can I do to solve the problem with the next database start?
    Thanks in advance
    Hans-Gerd
    --------------------------------------------------------------------------------
    From: Oracle, Harm Joris ten Napel 03-Sep-02 10:55
    Subject: Re : Drop datafile before removing it from database

    Hi,

    you need to drop all datafiles of the tablespace this one datafile
    belonged to with the following command:

    ALTER DATABASE DATAFILE '' OFFLINE DROP;

    Then drop the tablespace:

    DROP TABLESPACE INCLUDING CONTENTS;

    The 'OFFLINE DROP' syntax was especially designed for disasters like
    this or practices where for instance you don't backup temp or index
    tablespaces and you need this to be able to recover the database with
    only the really important datafiles,

    greetings,

    Harm
    --------------------------------------------------------------------------------
    From: Hans-Gerd Haase 03-Sep-02 11:10
    Subject: Re : Re : Drop datafile before removing it from database

    Hi

    it works fine,
    thanks for the quick answer.

    Hans-Gerd
    --------------------------------------------------------------------------------
    From: Navtej Boparai 03-Sep-02 16:24
    Subject: Re : Drop datafile before removing it from database

    Do the follwing steps.
    SQL > Shutdown Immediate;
    SQL > Startup Mount;
    SQL > Alter database datafile '' offline drop;
    SQL > Alter database open;
    SQL > drop tablespace ;
    SQL > COMMIT:
    thanx
    Navtej Boparai
    --------------------------------------------------------------------------------

    From: Oracle, Debra Chase 03-Sep-02 21:10
    Subject: Re : Drop datafile before removing it from database

    Thanks for the good input.

    Deb
    Oracle Corporation



    I was trying to simulate this in a test database before doing it on my devolopment DB.problem

    1. Created a tablespace called “TEST” with two datafiles one with 100 MB size and othe one is 20 MB.
    2. Created a user “testuser” with default tablespace “TEST”
    3. Logged in with testuser and created two tables.
    4. Deteled the second datafile of TEST tablespace (size 20 MB) from OS.
    5. Tried shutting down the DB
    6. It gave me the error and could not shutdown the database.
    7. I did a shutdown abort and the DB is down now.
    8. Tried starting the DB. I got me ORA-01110 and ORA-01116
    9. Droped the 20 MB datafile by ALTER DATABASE DATAFILE OFFLINE DROP
    10 Started the database.
    11. Logged in with testuser
    12 I could see all the tables which I created in “TEST tablespace!!


    Now my doubt is,

    Metalink document says after this exercise we need to give DROP TABLESPACE TABLESPACE_NAME

    In my case I have not given this command and still able to access the tables from TEST tablespace.

    Could you please tell me

    1. Why am I able to access tables from TEST even after droping the datafile.
    2. If people can access the data after droping the datafile (one or two , not all datafiles) why should we give DROP TABLESPACE.

    Thanks in Advance.
    Regards,
    Ajith

    METALINK DOCUMENT Now my doubt is,

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    you are able because it's reading data from existing datafile, you are just lucky for now, when it needs data from dropped datafile or it needs to allocate freespace you will start get errors

  3. #3
    Join Date
    Nov 2002
    Location
    INDIA
    Posts
    15
    Thanks for the reply. I was thinking the same thing. Needed the confirmation from an experienced person. One more query...

    Even though I have droped the data file, I am able to see the droped file in the dba_data_files. Why it is not getting removed from this view?
    Tablespace had two datafiles and one is corrupted/unavailable now.
    In which table/view this inconsistency is recorded ?

    Regards,
    Ajith

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    well offline drop does not remove file from dictionary that´s why you have to drop tablespace, what you see is expected behaviour

  5. #5
    Join Date
    Nov 2002
    Location
    INDIA
    Posts
    15
    Thaks pando for making it clear.

    Regards,
    Ajith

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