12 Replies Latest reply: Sep 21, 2011 2:11 AM by 593075 RSS

    OPEN RESETLOGS errors

    user13129655
      Oracle 10.2.0.4

      I created a clone database using rman but when I try to open I get the error msgs below. I cannot locate the archivelog that is complaining. The end result is that I just want to force the database open.

      SQL> startup
      ORACLE instance started.

      Total System Global Area 2147483648 bytes
      Fixed Size 2085464 bytes
      Variable Size 301993384 bytes
      Database Buffers 1828716544 bytes
      Redo Buffers 14688256 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-01194: file 1 needs more recovery to be consistent
      ORA-01110: data file 1: '/a0167/d26/oradata/prd002lp/system01.dbf'


      SQL> recover database using backup controlfile until cancel;
      ORA-00279: change 15655000145 generated at 05/07/2011 11:32:04 needed for
      thread 1
      ORA-00289: suggestion : /a0167/d21/arch1/prd002lppry_1_28173_649685255.arc
      ORA-00280: change 15655000145 for thread 1 is in sequence #28173


      Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

      ORA-00308: cannot open archived log
      '/a0167/d21/arch1/prd002lppry_1_28173_649685255.arc'
      ORA-27037: unable to obtain file status
      IBM AIX RISC System/6000 Error: 2: No such file or directory
      Additional information: 3



      Any suggestions?
        • 1. Re: OPEN RESETLOGS errors
          Mark D Powell
           I created a clone database using rman ...
          How specifically, did you use rman. Rman duplicate procedure or other? Reference the section of the manual that you followed. It may be necessary to post the actual commands used.

          The message that file 1 needs additional media recovery means that the database would be in an inconsistent state (basically useless) if you were able to force it open. Information in the archived redo logs needs to be applied. You need to complete the duplication process correctly.

          HTH -- Mark D Powell --
          • 2. Re: OPEN RESETLOGS errors
            CKPT
            ORA-00289: suggestion : /a0167/d21/arch1/prd002lppry_1_28173_649685255.arc
            ORA-00280: change 15655000145 for thread 1 is in sequence #28173
            Are you performing any cloning from HOT BACKUP?
            do you have thread# 1 and sequence# 28173?

            If so

            SQL> recover database using backup controlfile until cancel;

            Give the archive full path when it prompts. CHeck the status as " NO LONGER NEEDED FOR RECOVERY" then give "CANCEL" and then open database in resetlogs.
            • 3. Re: OPEN RESETLOGS errors
              779621
              1. Perform a fake recovery :

              SQL> recover database using backup controlfile until cancel;


              2. select CANCEL


              3. SQL> alter database open resetlogs;



              Regards,
              Girish
              • 4. Re: OPEN RESETLOGS errors
                user13129655
                THanks all,

                I do not have the the archive log file for thread 1 in sequence #28173.

                HELP !



                SQL> recover database using backup controlfile until cancel;
                ORA-00279: change 15655000145 generated at 05/07/2011 11:32:04 needed for
                thread 1
                ORA-00289: suggestion : /a0167/d21/arch1/prd002lppry_1_28173_649685255.arc
                ORA-00280: change 15655000145 for thread 1 is in sequence #28173


                Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
                cancel
                ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
                ORA-01194: file 1 needs more recovery to be consistent
                ORA-01110: data file 1: '/a0167/d26/oradata/prd002lp/system01.dbf'
                • 5. Re: OPEN RESETLOGS errors
                  sb92075
                  I do not have the the archive log file for thread 1 in sequence #28173.
                  What happened to it?
                  How did it go missing?
                  • 6. Re: OPEN RESETLOGS errors
                    askraks
                    rman > recover database noredo;

                    Note: you will loose data.
                    • 7. Re: OPEN RESETLOGS errors
                      CKPT
                      SQL> recover database using backup controlfile until cancel;
                      ORA-00279: change 15655000145 generated at 05/07/2011 11:32:04 needed for
                      thread 1
                      ORA-00289: suggestion : /a0167/d21/arch1/prd002lppry_1_28173_649685255.arc
                      ORA-00280: change 15655000145 for thread 1 is in sequence #28173


                      Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
                      cancel
                      ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
                      ORA-01194: file 1 needs more recovery to be consistent
                      ORA-01110: data file 1: '/a0167/d26/oradata/prd002lp/system01.dbf'
                      It seems test database, you have two options

                      1)
                      SQL> recover database using backup controlfile until cancel;
                      give CANCEL

                      If it is really cancelled then "ALTER DATABASE OPEN RESETLOGS";

                      2) which is undocumented parameter, which is not safe to work.

                      Shutdown the instances and add allowresetlogs_corruption=true* and then
                      SQL> startup mount
                      SQL> alter database open resetlogs;
                      • 8. Re: OPEN RESETLOGS errors
                        Girish Sharma
                        Shutdown the instances and add allowresetlogs_corruption=true and then
                        Sorry, but are you from Oracle Support ? With due respect, do you know underscore parameters are suggested by only Oracle Support and use of underscore parameter may cause ORA-600 and/or anything ?

                        Regards
                        Girish Sharma
                        • 9. Re: OPEN RESETLOGS errors
                          user13129655
                          Still no luck:

                          SQL> recover database using backup controlfile until cancel;
                          ORA-00279: change 15655000145 generated at 05/07/2011 11:32:04 needed for
                          thread 1
                          ORA-00289: suggestion : /a0167/d21/arch1/prd002lppry_1_28173_649685255.arc
                          ORA-00280: change 15655000145 for thread 1 is in sequence #28173


                          Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
                          cancel
                          ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
                          ORA-01194: file 1 needs more recovery to be consistent
                          ORA-01110: data file 1: '/a0167/d26/oradata/prd002lp/system01.dbf'


                          ORA-01112: media recovery not started


                          SQL> alter database open resetlogs;
                          alter database open resetlogs
                          *
                          ERROR at line 1:
                          ORA-01194: file 1 needs more recovery to be consistent
                          ORA-01110: data file 1: '/a0167/d26/oradata/prd002lp/system01.dbf'
                          • 10. Re: OPEN RESETLOGS errors
                            593075
                            Sorry for replying to this old thread, but i wonder if someone has more information about it.

                            I think i remember that i ran into a similar situation quite a while ago and that time i needed to point the recover process to an online redo log (!).

                            But what if lets say you have the following situation:

                            - Doing a hot backup some day. Preserving all archive logs. Backup controlfile to trace. The script contains "...create controlfile resetlogs...".
                            - After a few days you decide to use this hot backup to recreate some TEST DB.
                            (So you copy your backup files to desired locations, create controlfile from the trace file, create init.ora, etc)

                            Everything works fine until you issue recover database using backup controlfile until cancel. Acutally also this command seems to works as expected, at least as long as it will accept the archived log files you have.

                            But then you suddenly run into the situation the original poster describes: the recovery process is still asking for another log sequence, but you no longer have any archived log files. You issue "cancel" and try to open with resetlogs but then get the error the original poster describes.

                            Where could the log sequence the recover command is asking for be? There are no online redo logs in this situation that i could apply and i also ran out of archived redo logs.
                            • 11. Re: OPEN RESETLOGS errors
                              Hemant K Chitale
                              That is why you MUST issue "ALTER SYSTEM ARCHIVE LOG CURRENT ;" after the end of the Hot Backup of the database but before you "preserve" your archivelogs. Thus, the online redo log that was current when the database backup ended must be archived and preserved.

                              Hemant K Chitale
                              • 12. Re: OPEN RESETLOGS errors
                                593075
                                Hello Hemant,

                                thank you very much for this hint.

                                Unfortunately the hotback i have here comes from a non-self-made hot backup script, but now i at least understand how to preserve this information for a later restore / recovery.

                                Kind regards

                                Edited by: user590072 on 21.09.2011 09:11