1 2 3 Previous Next 39 Replies Latest reply on Sep 10, 2008 3:25 PM by 26741 Go to original post
      • 30. Re: How to recover the database when some of the archive log file get delet
        626479
        i'm totally agree with you ...

        my post was not oppose to your suggestion ...
        • 31. Re: How to recover the database when some of the archive log file get deleted.
          534533
          Hello Leo Anderson,
          Sorry for the delay.

          Currently we having some problem in our machine and we are not able to logon. I will porvide the requested information asap.
          Regarding to your other question.

          Our development database is running in archivelog mode, but they don't have backups scheduled and have no recovery catalog.
          When the database was in running condition, disk got full, so some archivelogs were deleted manually. After this we restarted the DB, and now DB is not coming up.
          To resolve we tried following steps
          -----------------------------------------------------------------------------------
          QL> startup
          ORACLE instance started.

          Total System Global Area 1444383504 bytes
          Fixed Size 731920 bytes
          Variable Size 486539264 bytes
          Database Buffers 956301312 bytes
          Redo Buffers 811008 bytes
          Database mounted.
          ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


          SQL> alter database open resetlogs;
          alter database open resetlogs
          *
          ERROR at line 1:
          ORA-01113: file 1 needs media recovery
          ORA-01110: data file 1: '/export/home/oracle/dev/ADVFRW/ADVFRW.system'


          SQL> recover datafile '/export/home/oracle/dev/ADVFRW/ADVFRW.system'
          ORA-00283: recovery session canceled due to errors
          ORA-01610: recovery using the BACKUP CONTROLFILE option must be done


          SQL> recover database using backup controlfile;
          ORA-00279: change 215548705 generated at 09/02/2008 17:06:10 needed for thread
          1
          ORA-00289: suggestion :
          /export/home/oracle/dev/ADVFRW/ADVFRW.archivelog1/LOG_ADVFRW_1107_1.ARC
          ORA-00280: change 215548705 for thread 1 is in sequence #1107


          Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
          /export/home/oracle/dev/ADVFRW/ADVFRW.archivelog1/LOG_ADVFRW_1107_1.ARC
          ORA-00308: cannot open archived log
          '/export/home/oracle/dev/ADVFRW/ADVFRW.archivelog1/LOG_ADVFRW_1107_1.ARC'
          ORA-27037: unable to obtain file status
          SVR4 Error: 2: No such file or directory
          Additional information: 3

          ------------------------------------------------------------------------------------

          Thanks
          With Regards
          Hemant Joshi.
          • 32. Re: How to recover the database when some of the archive log file get deleted.
            534533
            Hello Anderson,
            Please find requested query.

            -------------------------------------------------------------------------------------------------------
            SQL> select sequence#, member from v$log a, v$logfile b where a.group#=b.group# order by sequence#;

            SEQUENCE# MEMBER
            ---------- -----------------------------------------------------------------
            1102 /export/home/oracle/dev/ADVFRW/ADVFRW.redo151
            1103 /export/home/oracle/dev/ADVFRW/ADVFRW.redo141
            1104 /export/home/oracle/dev/ADVFRW/ADVFRW.redo161
            1105 /export/home/oracle/dev/ADVFRW/ADVFRW.redo111
            1106 /export/home/oracle/dev/ADVFRW/ADVFRW.redo121
            1107 /export/home/oracle/dev/ADVFRW/ADVFRW.redo131

            6 rows selected.

            SQL> select group#, sequence# from v$log;

            GROUP# SEQUENCE#
            ---------- ----------
            1 1105
            2 1106
            3 1107
            4 1103
            5 1102
            6 1104
            -------------------------------------------------------------------------------------------------------
            Can I follow the steps as described by Mr. Hemant.




            Thanks
            With Regards
            Hemant Joshi.
            • 33. Re: How to recover the database when some of the archive log file get deleted.
              534533
              Hello Hemant.
              Thanks for the help.
              Thanks to all of them who are in the discussion of the problem.

              Problem is now resolved.


              With Regards
              Hemant Joshi.
              • 34. Re: How to recover the database when some of the archive log file get deleted.
                534533
                Hello Hemant,
                I would like to know how you concluded that we should use redolog file for recovering the DB?


                Thanks
                With Regards
                Hemant.
                • 35. Re: How to recover the database when some of the archive log file get deleted.
                  595286
                  So, please run
                  SQL> startup mount
                  
                  SQL> recover database
                  
                  {code}when it will ask for log sequence 1107, don't press enter, past /export/home/oracle/dev/ADVFRW/ADVFRW.redo131 and then only ENTER
                  
                  it will succeed and you'll be able to do {code:java}alter database open{code}WITHOUT resetlogs
                  
                  Edited by: Leo Anderson on 10 sept. 2008 12:11                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
                  • 36. Re: How to recover the database when some of the archive log file get deleted.
                    534533
                    Hello Anderson,
                    Thanks for the your response.
                    I would like to know how you concluded that we should use redolog file for recovering the DB?


                    Thanks
                    With Regards
                    Hemant Joshi
                    • 37. Re: How to recover the database when some of the archive log file get deleted.
                      595286
                      Hi,

                      It is in fact quite easy.
                      We knew Oracle was looking for logsequence 1107. The archived log does not exist so either it was deleted, or it was never generated. As you delete some files, the 1st solution could be possible BUT as there was only an instance crash so older archived logs are not required for crash recovery. There are need when restoring files from old backup, which is not our case.

                      So, the missing log sequence is not archived =&gt; this is a redo log.
                      To identify the required online redo log, we just have to query v$log as I suggest you.

                      Once the logfile identified, we can play the recover specifying the redo log when asking for logsequence 1107.

                      And that's over. We win ! :)
                      • 38. Re: How to recover the database when some of the archive log file get deleted.
                        26741
                        Can I follow the steps as described by Mr. Hemant
                        Yes, you could have and did probably do so. We presume.
                        Seeing that you have been attempting this issue for a few days and have had varying and conflicting and dangerous advice and there were time gaps in updates to this thread and that responses seem to be to the last post in the thread.
                        • 39. Re: How to recover the database when some of the archive log file get deleted.
                          26741
                          deleted the double posting as forums seems to be extremely unstable (if this was a database, duplicate entries wouldn't have been acceptable to the "management")

                          Edited by: Hemant K Chitale on Sep 10, 2008 11:23 PM
                          1 2 3 Previous Next