6 Replies Latest reply: Apr 9, 2014 7:16 AM by teits RSS

    Effects of opening a Primary database in resetlogs on Standby database

    $phinx19

      Hi All,

       

      OS: Solaris

      DB:11gR2

       

      Can someone throw some light on the below explanation that is given on oracle docs in case of Recovering Through the OPEN RESETLOGS Statement

       

      If the standby database. . .Then. . .Perform these steps. . .
      Has not applied redo data past the new resetlogs SCN (past the start of the new branch of redo data)Redo Apply automatically takes the new branch of redo.No manual intervention is necessary. The MRP automatically resynchronizes the standby database with the new branch of redo data.

       

      Here is the link of the source:

       

      Managing a Physical Standby Database

       

      Regards,

        • 1. Re: Effects of opening a Primary database in resetlogs on Standby database
          oscrub

          (quick note: Column names and values are all made up just so I could make it easier to understand for the explanation)

          So let's say on your primary database, this is the status of the last redo log:

           

          PRIMARY -

          LAST_SCN          SEQUENCE#          STATUS

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

          21009             41                 INACTIVE

          21986             42                 INACTIVE

          22114             43                 CURRENT

           

          And let's assume that there is some sort of lag in the transport and apply of the logs on the standby, so it's lagging a few logs behind, with the last applied log sequence being #41, and still waiting for sequence 42 and 43.

           

          STANDBY -

          LAST_SCN          SEQUENCE#          STATUS

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

          21009             41                 APPLIED

          21986             42                 TO BE APPLIED

          22114             43                 TO BE APPLIED

           

          So for whatever reason, on the primary you now do a recover and issue the statement ALTER DATABASE OPEN RESETLOGS. This resets the sequence creating a new branch of data, and a new SCN. This new SCN is the one referred to as the 'new resetlogs SCN' in the doc you pasted. So on the PRIMARY it looks like:


          PRIMARY -

          LAST_SCN          SEQUENCE#

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

          1002              1


          The log sequence has reset to 1, and the SCN has also been reset. However on the STANDBY, it fulfills the condition highlighted, ie. the standby has not applied the redo data past the new resetlogs SCN (1002). Thus, the standby automatically takes the new branch of redo, starting with sequence 1. So Data Guard will automatically transfer the remaining logs and apply them in sequence.

           

          STANDBY -

          LAST_SCN          SEQUENCE#          STATUS

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

          21009             41                 APPLIED

          21986             42                 TO BE APPLIED

          22114             43                 TO BE APPLIED

          1002              1                  TO BE APPLIED


          As mentioned in the documentation, in this case no user intervention is required and Oracle will automatically manage the transfer and apply of the needed logs. Sorry if it's a bit confusing, hope this answered your question.

          • 2. Re: Effects of opening a Primary database in resetlogs on Standby database
            $phinx19

            Thanks a lot for your vivid explanation!

             

            "the standby has not applied the redo data past the new resetlogs SCN (1002)"


            Does this phrase means that on the Standby database, redos generated (21986 and 22114 as in this case) before SCN 1002 were not applied before the Resetlogs operation?


            Moreover, when the resetlogs was performed on Primary, oracle still finds two redos of the old sequence  (21986 and 22114 as in this case) pending to be applied on the standby database. Once, these two redos are applied on the standby database, oracle encounters the new sequence with SCN 1002 which oracle applies (I do not know how as there is a complete shift in the sequence) without any manual intervention.


            Going forward on the same path, if oracle was not able to find the scn 21986 and 22114 pending to be applied on the standby database, then post the resetlog when oracle will encounter the new sequence #1 a manual intervention will be required as opposed to the above case.


            Please correct me if I am wrong


            • 3. Re: Effects of opening a Primary database in resetlogs on Standby database
              oscrub

              "the standby has not applied the redo data past the new resetlogs SCN (1002)"

              Does this phrase means that on the Standby database, redos generated (21986 and 22114 as in this case) before SCN 1002 were not applied before the Resetlogs operation?


              Yes, in the example above the logs with SCN 21986 and 22114 where not yet applied before the resetlogs.

               

              Moreover, when the resetlogs was performed on Primary, oracle still finds two redos of the old sequence  (21986 and 22114 as in this case) pending to be applied on the standby database. Once, these two redos are applied on the

              standby database, oracle encounters the new sequence with SCN 1002 which oracle applies (I do not know how as there is a complete shift in the sequence) without any manual intervention.

               

              Yes, they are applied without any manual intervention.

               

              Going forward on the same path, if oracle was not able to find the scn 21986 and 22114 pending to be applied on the standby database, then post the resetlog when oracle will encounter the new sequence #1 a manual intervention will

              be required as opposed to the above case.

               

              If Oracle was not able to find the logs with SCN 21986 and 22114, depending on why the logs were missing, manual intervention would be required in order to transfer and apply the logs to the standby, before the new log with sequence #1 can be applied.

              • 4. Re: Effects of opening a Primary database in resetlogs on Standby database
                oscrub

                Just to give a concrete example, I did a test on one of my VM boxes. The primary database currently shows:

                 

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

                 

                SEQUENCE# STATUS

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

                        34 ACTIVE

                        35 CURRENT

                        33 ACTIVE

                 

                While on the standby, I stopped the redo apply so the status on the standby is:

                 

                SQL> select sequence#, applied from v$archived_log;

                 

                SEQUENCE# APPLIED

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

                        30 YES

                        31 YES

                        32 YES

                        33 NO

                        34 NO

                The standby database being a few logs behind the primary.

                On the primary, I advanced the log a few sequences, shut down, startup mount, and restored and recovered to sequence 37:

                 

                RMAN> run

                2> {

                3> restore database;

                4> set until sequence 37;

                5> recover database;

                6> alter database open resetlogs;

                7> }

                 

                During the open resetlogs operation, I tailed the alert log on the standby database and the following output showed:

                 

                RFS[11]: Identified database type as 'physical standby': Client is ARCH pid 17192

                RFS[11]: New Archival REDO Branch: 844006029 Current: 843649185

                RFS[11]: Selected log 102 for thread 1 sequence 1 dbid -204899919 branch 844006029

                RFS[11]: New Archival REDO Branch(resetlogs_id): 844006029  Prior: 843649185

                RFS[11]: Archival Activation ID: 0xf3d44942 Current: 0xf3cf5ac3

                RFS[11]: Effect of primary database OPEN RESETLOGS

                RFS[11]: Incarnation entry added for Branch(resetlogs_id): 844006029 (ORCL11PS)

                Fri Apr 04 14:07:39 2014

                Setting recovery target incarnation to 2

                Fri Apr 04 14:07:39 2014

                Archived Log entry 21 added for thread 1 sequence 1 ID 0xf3d44942 dest 1:

                 

                So as you can see, the log shows the standby detecting the resetlogs operation on the primary and the new redo branch, and has recieved the next archivelog with sequence 1.

                 

                Checking on the standby, it shows the following status of the archivelogs:

                 

                 

                SEQUENCE# APPLIED

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

                        31 YES

                        32 YES

                        33 NO

                        34 NO

                        35 NO

                        36 NO

                        37 NO

                        38 NO

                         1 NO

                         2 NO

                 

                And after restarting the managed recovery process, it now shows:

                 

                SEQUENCE# APPLIED

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

                        31 YES

                        30 YES

                        19 YES

                        33 YES

                        34 YES

                        35 YES

                        36 YES

                        37 NO

                        38 NO

                         1 YES

                         2 YES

                 

                Archivelogs with sequence 37 and 38 were not applied because of the PITR on the primary up until (but not including!) sequence 37, and then correctly applied the next archivelogs from the new redo branch, with sequence 1 and 2.

                • 6. Re: Effects of opening a Primary database in resetlogs on Standby database
                  teits

                  mark your question as answered. to clean up the forum. thanks