14 Replies Latest reply: May 2, 2013 4:35 PM by Justin_Mungal RSS

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

    1006768
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          alter database create databfile "" as ""
                          • 10. Re: accidentally deleted empty datafile at OS level, and have no backup of it!!
                            Justin_Mungal
                            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
                              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
                                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
                                  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
                                    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.