DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 21

Thread: URGENT - Datafile

Hybrid View

  1. #1
    Join Date
    Nov 2000
    Posts
    416

    Question URGENT - Datafile

    Oracle 8im is in NO Archive Mode. I made one datafile of a tablespace offline drop and the physical file also dropped.
    But Still Oracle looking for that datafile!!!! Why??
    How can I make it staus unavailable??? I really need to get rid of it
    An ounce of prevention is worth a pound of cure

  2. #2
    Join Date
    Jan 2002
    Location
    Netherlands
    Posts
    1,587
    offline drop will only make the datafile offline
    so the ctl.file will still have info about it.drop the tablespace and then recreate it with new/required datafile(s)

    But if you have other datafiles that have objects of your interest then export them first before doing the...

    DROP TABLESPACE your_tablespace INCLUDING CONTENTS;

    This will remove all info of the tablspce+datafiles from the dict.

    comprende?
    Last edited by Tarry; 11-26-2002 at 10:38 AM.
    Tarry Singh
    I'm a JOLE(JavaOracleLinuxEnthusiast)
    TarryBlogging
    --- Everything was meant to be---

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    you cannot get rid of a datafile from a tablespace, if you have done what you just did you are forced to drop entire tablespace

  4. #4
    Join Date
    Oct 2000
    Posts
    467
    Not sure abt this..but can't he re-create the control files without the dropped datafile ?
    Vinit

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    nop

  6. #6
    Join Date
    May 2002
    Posts
    2,645
    See Note 111316.1 on MetaLink. It is actually one of the better written and cogent articles Oracle has posted on the site.

    Here is an excerpt:
    NOTE: The ALTER DATABASE DATAFILE OFFLINE DROP command, is not meant to allow you to remove a datafile. What the command really means is that you are offlining the datafile with the intention of dropping the tablespace. If you are running in archivelog mode, you can also use:

    ALTER DATABASE DATAFILE OFFLINE;

    instead of OFFLINE DROP. Once the datafile is offline, Oracle no longer attempts to access it, but it is still considered part of that tablespace. This datafile is marked only as offline in the controlfile and there is no SCN comparison done between the controlfile and the datafile during startup (This also allows you to startup a database with a non-critical datafile missing). The entry for that datafile is not deleted from the controlfile to give us the opportunity to recover that datafile.

  7. #7
    Join Date
    Oct 2000
    Posts
    467
    I don't think ALTER DATABASE DATAFILE OFFLINE will work since the db here is in no archivelog mode.
    Vinit

  8. #8
    Join Date
    Nov 2000
    Posts
    416
    1- If I make the datafile offline and if I bounce the database does it becomes online again?

    2- Can I copy one of my active .dbf file to the deleted .dbf file to just cheat the oracle that physical file exist but I don't need irkt to be online, because otherwise I will keep getting error : ORA-01537 cannot add data file 'string' - file already part of database


    3- I am not in a situation to drop tablespace content and create another one, there are huge tables in that tablespace and we are live prod. What can I do workaround? Cheating solution above works I think?
    An ounce of prevention is worth a pound of cure

  9. #9
    Join Date
    May 2002
    Posts
    2,645
    Originally posted by farrokhp
    1- If I make the datafile offline and if I bounce the database does it becomes online again?
    Try it and see. If a tablespace is set to read-only, and you bounce the instance, do you think the tablespace comes back in read-write?


    2- Can I copy one of my active .dbf file to the deleted .dbf file to just cheat the oracle that physical file exist but I don't need irkt to be online, because otherwise I will keep getting error : ORA-01537 cannot add data file 'string' - file already part of database
    Experience is a good teacher. Try that one too and see if it works.


    3- I am not in a situation to drop tablespace content and create another one, there are huge tables in that tablespace and we are live prod. What can I do workaround? Cheating solution above works I think?
    Tell us where you work so we can be on the lookout for an Oracle DBA job posting.

  10. #10
    Join Date
    Nov 2000
    Posts
    416
    Steven is so rude and sarcastic. Now you are waiting that I lose my job and you jump to getting my place. Ok, Nice man! You may cross your finger !!

    Can somebody tell me exact answer to this instead of Steven rude comment:

    Can I copy one of my active .dbf file as deleted .dbf file to somehow cheat the oracle server that physical file exist but I don't need it to be online Right now I am keep getting error : ORA-01537 cannot add data file 'string' - file already part of database


    Appreciate that
    Frank
    An ounce of prevention is worth a pound of cure

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