1 2 Previous Next 17 Replies Latest reply: Sep 13, 2013 7:42 AM by maddymahi RSS

    How to recover a datafile in recovery mode

    536587
      Hi,

      We have a datafile that's 0 bytes in size and is in recovery mode. I see that the datafile is showing as available in dba_data_files but in v$datafile, it's showing the status as recover. This is in Production and the tablespace has been in this mode since last november. We are not able to offline drop the datafile as it is in recovery mode. How can this be corrected without bringing the instance down? Any thoughts?

      Thanks.
        • 1. Re: How to recover a datafile in recovery mode
          660111
          You have to restore this datafile from a backup, and then recover it.

          RMAN> RESTORE DATAFILE N;
          RMAN> RECOVER DATAFILE N;
          • 2. Re: How to recover a datafile in recovery mode
            536587
            Ricardo,

            Thank you! We do not have RMAN setup. We only have manual scripts for hot backups that are written to tape.

            Thanks.
            • 3. Re: How to recover a datafile in recovery mode
              26741
              Restore is done by whatever OS utility you use (cp / tar / cpio / NetBackup etc)

              Then, use SQLPlus AS SYSDBA to issue the RECOVER command
              RECOVER DATAFILE n;
              • 4. Re: How to recover a datafile in recovery mode
                536587
                The scenario here is, one of our previous DBA's had added a datafile to one of the data tablespaces with a index datafile name and realized immediately that the naming convention was wrong and changed the size of the datafile to 0 bytes through OEM and then tried to bring it offline. This is how the datafile had gone into a recover mode. We do not have any backups of this datafile for us to recover. What is the best possible way to deal with this issue.

                Thanks.
                • 5. Re: How to recover a datafile in recovery mode
                  Tim Boles
                  I would definitely contact Oracle support for something like this on my production database.

                  Here are some ideas, but some will require some production downtime to complete.

                  1) Export everything that is in the tablespace that contains the datafile. Drop the tablespace including contents, create the tablespace and import everything back in......Not a good solution but it is a solution...of course there are a lot of things to consider like triggers, foreign keys and similar. This should be tested a lot.

                  2) Create logical standby with corrected structure and switch to logical standby for production when complete. This will take a lot of work and testing on a test system.

                  3) Another possibility would be to move tables within this tablespace to a new tablespace and then drop the old tablespace. (Performance hit but safe and no down time).

                  4) If indexes then drop and recreate new indexes (Performance hit but safe and no down time.)

                  Once you get everything cleared out of the tablespace you should be able to drop it including contents and storage.

                  Of course I would try any thing I do out on a test environment before doing anything to my production environment.

                  Regards
                  • 6. Re: How to recover a datafile in recovery mode
                    26741
                    If the datafile was "OFFLINE"d properly, you should be able to issue a RECOVER DATAFILE and then bring it ONLINE again. Oracle should detect that no archivelogs need to be applied to it.

                    If the DBA had done an "ALTER DATABASE DATAFILE .. OFFLINE FOR DROP" and the Database was in NOARCHIVELOG mode, then you can only drop the tablespace. However, as your Database would have been in ARCHIVELOG mode, Oracle would have ignored the "FOR DROP" and done a normal OFFLINE.

                    Have you attempted to issue the RECOVER DATAFILE command ?
                    • 7. Re: How to recover a datafile in recovery mode
                      26741
                      Did he really resize the file to 0 (zero) bytes ? Oracle allowed that ?

                      The file should be a few (probably 2 or 4 Oracle Blocks) KBs in size, at the minimum.

                      In any case, have you attempted a RECOVER DATAFILE command (without having to do a RESTORE) ?
                      • 8. Re: How to recover a datafile in recovery mode
                        Tim Boles
                        I am not sure about your statement "Oracle should detect that no archiuvelogs need to be applied to it."?

                        How would Oracle know that it did not need to apply archivelogs unless it actually looked at the archivelogs for changes. To the best of my knowledge and experience the "OFFLINE"d file will have a file header that contains that last SCN applied to that datafile. When Oracle goes to RECOVER that datafile it will have to travel through each archivelog file checking for updates to that datafile until it gets to the current SCN.

                        If you have experience or documentation to the opposite I would like to hear about it.

                        Thanks
                        Tim
                        • 9. Re: How to recover a datafile in recovery mode
                          26741
                          See

                          http://hemantoracledba.blogspot.com/2009/04/bringing-online-datafile-that-is-in.html
                          • 10. Re: How to recover a datafile in recovery mode
                            536587
                            Hi,

                            We did attempt to do a recover data file, Oracle asks for archive log to be applied from 11/20/08 when this had happened. When we just do a CANCEL without applying the archive log, media recovery is canceled. The data file shows as 0 bytes from OEM.

                            This is the output from v$datafile for that file:
                            STATUS: Recover
                            Bytes:2097152000

                            Output from dba_data_files:

                            STATUS:Available
                            and none of the space related fields in the output from dba_data_files table are populated for this file.


                            Output from v$recover_file:
                            ONLINE_STATUS:OFFLINE

                            Thanks.
                            • 11. Re: How to recover a datafile in recovery mode
                              26741
                              So you now have to bring the file ONLINE.

                              ALTER DATABASE DATAFILE fileid ONLINE;

                              Edited by: Hemant K Chitale on Apr 23, 2009 9:53 AM
                              • 12. Re: How to recover a datafile in recovery mode
                                536587
                                I tried getting it online and this is the error it throws:

                                SQL> alter database datafile 586 online;
                                alter database datafile 586 online
                                *
                                ERROR at line 1:
                                ORA-01113: file 586 needs media recovery
                                ORA-01110: data file 586: '/u25/oradata/prod11i/xkbx06.dbf'
                                • 13. Re: How to recover a datafile in recovery mode
                                  26741
                                  Oracle requires the first archivelog after the file went offline. It does NOT require all the archivelogs since then.

                                  See the example I had posted earlier

                                  http://hemantoracledba.blogspot.com/2009/04/bringing-online-datafile-that-is-in.html
                                  • 14. Re: How to recover a datafile in recovery mode
                                    536587
                                    Very clearly explained. We will try to implement this by getting the archive log from 20th November when this datafile was taken offline.

                                    Thanks.
                                    1 2 Previous Next