This discussion is archived
14 Replies Latest reply: May 2, 2013 2:35 PM by Justin_Mungal RSS

accidentally deleted empty datafile at OS level, and have no backup of it!!

1006768 Newbie
Currently Being Moderated
Oracle 11G

Hello
After creating a new datafile, it was accidentally deleted at OS level (AIX).
Because it was new there are no backups of it.
Now tablespace thinks there are 3 datafiles in it, but one of them is missing.

Is it possible to recreate an empty data file on-the-fly?
It has let me set the datafile to offline, but wouldnt let me do anything else.

Now my backups are failing as it recognises :
"the status of database file is RECOVER"

Any advice would be greatly appreciated
  • 1. Re: accidentally deleted empty datafile at OS level, and have no backup of it!!
    sb92075 Guru
    Currently Being Moderated
    user6737818 wrote:
    Oracle 11G

    Hello
    After creating a new datafile, it was accidentally deleted at OS level (AIX).
    Because it was new there are no backups of it.
    Now tablespace thinks there are 3 datafiles in it, but one of them is missing.

    Is it possible to recreate an empty data file on-the-fly?
    It has let me set the datafile to offline, but wouldnt let me do anything else.

    Now my backups are failing as it recognises :
    "the status of database file is RECOVER"

    Any advice would be greatly appreciated
    the datafile is really not gone until after DB shutsdown

    Best take an online RMAN backup ASAP!
  • 2. Re: accidentally deleted empty datafile at OS level, and have no backup of it!!
    Iordan Iotzov Expert
    Currently Being Moderated
    Also, try to make a logical backup (DataPump) of your data.

    Iordan Iotzov
  • 3. Re: accidentally deleted empty datafile at OS level, and have no backup of it!!
    1006768 Newbie
    Currently Being Moderated
    thanks- will do RMAN backup asap, but in the mean time:
    The datafile is definately away as it was deleted at unix level.
    How do I get oracle to be aware that it is no longer part of tablespace.
    When I tried to drop it I just get error messages that file doesnt exist.

    every few minutes there is an ebtry in alert log -
    ORA-01115: IO error reading block from file 10 (block # 1)

    Thanks
  • 4. Re: accidentally deleted empty datafile at OS level, and have no backup of it!!
    sb92075 Guru
    Currently Being Moderated
    user6737818 wrote:
    thanks- will do RMAN backup asap, but in the mean time:
    The datafile is definately away as it was deleted at unix level.
    How do I get oracle to be aware that it is no longer part of tablespace.
    When I tried to drop it I just get error messages that file doesnt exist.

    every few minutes there is an ebtry in alert log -
    ORA-01115: IO error reading block from file 10 (block # 1)

    Thanks
    YOU are making a bad situation WORSE!

    01115, 00000, "IO error reading block from file %s (block # %s)"
    // *Cause:  Device on which the file resides is probably offline
    // *Action: Restore access to the device                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
  • 5. Re: accidentally deleted empty datafile at OS level, and have no backup of it!!
    JohnWatson Guru
    Currently Being Moderated
    No problem, recovering a file that was never backed up is a standard training exercise (at least, it is if are trained by me :) )
    Just do this:
    create datafile 'fully_qualified_file_name' as 'fully_qualified_file_name' ;
    recover datafile 'fully_qualified_file_name' ;
    alter database datafile 'fully_qualified_file_name' online;
    this will create a new, empty, file based on the definition of the missing file, and apply all the redo since it originally created. no data loss.
    --
    John Watson
    Oracle Certified Master DBA
    http://skillbuilders.com

    Edited by: JohnWatson on May 2, 2013 8:03 PM
    Error! First step should read
    alter database create datafile.....
  • 6. Re: accidentally deleted empty datafile at OS level, and have no backup of it!!
    Dom Brooks Guru
    Currently Being Moderated
    See http://jarneil.wordpress.com/2013/04/23/recovering-from-rm-rf-on-a-datafile/ for a non-standard alternative approach.
  • 7. Re: accidentally deleted empty datafile at OS level, and have no backup of it!!
    Justin_Mungal Journeyer
    Currently Being Moderated
    JohnWatson wrote:
    No problem, recovering a file that was never backed up is a standard training exercise (at least, it is if are trained by me :) )
    Just do this:
    create datafile 'fully_qualified_file_name' as 'fully_qualified_file_name' ;
    recover datafile 'fully_qualified_file_name' ;
    alter database datafile 'fully_qualified_file_name' online;
    this will create a new, empty, file based on the definition of the missing file, and apply all the redo since it originally created. no data loss.
    --
    John Watson
    Oracle Certified Master DBA
    http://skillbuilders.com
    Can you run through an example of this?

    SQL> create datafile '/u01/app/oracle/oradata/ORCL/datafile/justin2.dbf' as '/u01/app/oracle/oradata/ORCL/datafile/justin2.dbf';
    create datafile '/u01/app/oracle/oradata/ORCL/datafile/justin2.dbf' as '/u01/app/oracle/oradata/ORCL/datafile/justin2.dbf'
    *
    ERROR at line 1:
    ORA-00901: invalid CREATE command
  • 8. Re: accidentally deleted empty datafile at OS level, and have no backup of it!!
    1006768 Newbie
    Currently Being Moderated
    Justin
    I think you have to type"alter database" first, but check this.
    I'm waiting on backup to finish before I run it.
    Will let you know how I get on
  • 9. Re: accidentally deleted empty datafile at OS level, and have no backup of it!!
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    alter database create databfile "" as ""
  • 10. Re: accidentally deleted empty datafile at OS level, and have no backup of it!!
    Justin_Mungal Journeyer
    Currently Being Moderated
    Osama_mustafa wrote:
    alter database create databfile "" as ""
    Well, I was asking him to go through an example for the OP because 1) The command isn't valid, and 2) If you delete a data file at the OS level and try to create one of the same name, it will fail because the database still thinks that file exists. So it seems to me that there is more to it than those three steps.
  • 11. Re: accidentally deleted empty datafile at OS level, and have no backup of it!!
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    Check
    http://msutic.blogspot.com/2011/06/recover-lost-datafile-without-valid.html
  • 12. Re: accidentally deleted empty datafile at OS level, and have no backup of it!!
    1006768 Newbie
    Currently Being Moderated
    JohnWatson - you are a saviour - your answer worked perfectly.
    Thank you, thank you, thank you!!!
  • 13. Re: accidentally deleted empty datafile at OS level, and have no backup of it!!
    Justin_Mungal Journeyer
    Currently Being Moderated
    Osama_mustafa wrote:
    Check
    http://msutic.blogspot.com/2011/06/recover-lost-datafile-without-valid.html
    Thanks Osama.

    John: That's a neat trick. I learned something new from you today.
  • 14. Re: accidentally deleted empty datafile at OS level, and have no backup of it!!
    Justin_Mungal Journeyer
    Currently Being Moderated
    JohnWatson wrote:
    No problem, recovering a file that was never backed up is a standard training exercise (at least, it is if are trained by me :) )
    Just do this:
    create datafile 'fully_qualified_file_name' as 'fully_qualified_file_name' ;
    recover datafile 'fully_qualified_file_name' ;
    alter database datafile 'fully_qualified_file_name' online;
    this will create a new, empty, file based on the definition of the missing file, and apply all the redo since it originally created. no data loss.
    --
    John Watson
    Oracle Certified Master DBA
    http://skillbuilders.com

    Edited by: JohnWatson on May 2, 2013 8:03 PM
    Error! First step should read
    alter database create datafile.....
    Also, make sure to take the data file offline before creating the new one (maybe that was implied):
    SQL> alter database datafile 5 offline drop;
    
    Database altered.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points