9 Replies Latest reply: May 23, 2011 10:07 AM by Aman.... RSS

    alter database open resetlogs

    user8647245
      Hi,

      when we do control files recovery using cold backup we will apply archive logs and redo logs also.

      so here we will do complete recovery.

      and when i try to open database with >alter database open;

      it is says use resetlogs or noresetlogs option.

      so here why do we open need the database with >alter database open resetlogs;

      why >alter database open; doesn't work.

      please let me know.

      thank you
        • 1. Re: alter database open resetlogs
          726263
          If your using a backup control file you need to use open resetlogs.

          KDSDBA
          • 2. Re: alter database open resetlogs
            DBA 24*7
            A RESETLOGS is required in either of

            # Recovery using BACKUP CONTROLFILE
            # Incomplete Recovery

            If you are using a backup controlfile, the controlfile is not current --- however it doesn't have the current SCN and Log sequence number.
            The RESETLOGS updates this information back to the controlfile and creates a new incarnation of the database.
            Oracle check to see if you have used a Backup Controlfile and/or done an Incomplete Recovery, in which case it does not allow you to OPEN without the RESETLOGS.
            • 3. Re: alter database open resetlogs
              Aman....
              Its a clause. If you had the redo logs and were able to recover the db using them along with the archivelogs, you have done a complete recovery even when you have used the resetlogs clause.

              Aman....
              • 4. Re: alter database open resetlogs
                726263
                Aman wrote :Its a clause
                Not sure what this has to do with the OP question.
                Sounds like he wants to know WHY alter database open does not work.

                KDSDBA
                • 5. Re: alter database open resetlogs
                  Aman....
                  It has to do with the incarnation of the database which would be impacted when the control file recovery would be done. That's the OPEN clause wont work.

                  Aman....
                  • 6. Re: alter database open resetlogs
                    726263
                    If the OP wasn't so lazy and took the effort to lookup using a backup control file during recovery the OP would
                    have clearly found that out that before running such a command.
                    Oracle requires a RESETLOGS operation AFTER performing recovery using a 
                    backup control file because Oracle needs to update certain structures in the backup 
                    control file before opening the database.
                    KDSDBA
                    • 7. Re: alter database open resetlogs
                      Aman....
                      Oracle needs to update certain structures in the backup control file before opening the database.
                      Would it be possible for you to explain this statement further-what structures would be required to get updated?

                      Aman....
                      • 8. Re: alter database open resetlogs
                        726263
                        You can search for this and you should find more information.
                        As an ace I would think you would know that Oracle updates the backup control file (structures).
                        Are you suggesting that it doesn't ?

                        KDSDBA
                        • 9. Re: alter database open resetlogs
                          Aman....
                          KDSDBA  wrote:
                          You can search for this and you should find more information.
                          As an ace I would think you would know that Oracle updates the backup control file (structures).
                          Are you suggesting that it doesn't ?
                          Hmm you took the question in a wrong way Ksdba. Sure I can search and I guess, I do know a thing or two what happens internally but after reading your statement, I was looking for some more information which I hoped you would share because there is always a possibility to learn something new. Never mind!

                          Aman....