Drop datafile
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Drop datafile

Hybrid View

  1. #1
    Join Date
    Oct 2008
    Posts
    69

    Drop datafile

    Hi Folks,
    I made a bit of a woopsy with adding a datafile to the wrong tablespace. In my initial panic as soon as i did it then i placed the datafile offline so no data would be written to it (this is a live database). I then thought to drop the datafille but i can't
    Code:
    ERROR at line 1:
    ORA-03264: cannot drop offline datafile of locally managed tablespace
    So i have to take it back online to do the drop but if i try and place online it tells me i need more media recovery (presuming just because the SCN has changed as there is no data in this file).

    Now, it just so happens that RMAN is currently not operational on this database due to the version of Netbackup we are on not supporting 11g.

    can i recover the datafile while the database is open?

    Code:
    alter database recover datafile '/ifs/dpp/live/data/IFSLIVEdts03.dbf';
    alter database recover datafile '/ifs/dpp/live/data/IFSLIVEdts03.dbf'
    *
    ERROR at line 1:
    ORA-00279: change 32018552 generated at 03/25/2011 14:28:22 needed for thread 1
    ORA-00289: suggestion : /ifs/dpp/archivelogs/1_2544_741693934.dbf
    ORA-00280: change 32018552 for thread 1 is in sequence #2544

  2. #2
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    alter database recover datafile '/ifs/dpp/live/data/IFSLIVEdts03.dbf';
    alter database recover datafile '/ifs/dpp/live/data/IFSLIVEdts03.dbf'
    Is the archive log file /ifs/dpp/archivelogs/1_2544_741693934.dbf is exists in archive log destination?

    No need to alter database. Just use

    SQL>recover datafile '/ifs/dpp/live/data/IFSLIVEdts03.dbf';

    when it prompt to Specify log then select AUTO.

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  3. #3
    Join Date
    Oct 2008
    Posts
    69
    hiya,
    Yeah i had already tried that but it says media recovery already started.

    I thought you could only use that command in mount stage though.

  4. #4
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    Is media recovery completed? Is the data file online?

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  5. #5
    Join Date
    Oct 2008
    Posts
    69
    Quote Originally Posted by vnktummala View Post
    Is media recovery completed? Is the data file online?

    Thanks,
    I don't understand why it was saying that as i had never originally asked it to recover the file.
    It still says the same now when i try to place online or give the recover command (remember the database is open, not in mount stage)
    I think i have seen how to do recover datafile in open database using RMAN but as mentioned earlier, RMAN currently unavailable for this database.

    The database has been restarted since i raised this but was an auto restart over the weekend when i wasn't at work.

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by philthee View Post
    ...but it says media recovery already started.
    Does archivelog /ifs/dpp/archivelogs/1_2544_741693934.dbf exists?
    What do you see in alertlog?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  7. #7
    Join Date
    Mar 2001
    Posts
    41
    You can do a recovery on the datafile even when the database is open. You must have the require archivelog to do the recovery on the file, then after all the logs are applied you can place the datafile online.

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