1 2 Previous Next 15 Replies Latest reply: Jul 15, 2013 10:55 PM by Mahir M. Quluzade RSS

    Dataguard: sync primary to 2 standby database

    rontrinidadcabral

      Environment: Oracle 11.2.0.2, ASM, DG, RAC

      Primary: 2 node RAC

      Standby: 2 node RAC

      physical standby 1 - Active DG read only

      physical standby 2 - remote DR running in recovery mode

       

      My primary and standby database is not sync. The last sequence applied and received is not equal. Upon checking, the archivelogs are received in standby database but it appears that archivelog was deleted as part of the RMAN job before it was applied in standby database.

       

      the archivelog backup is on tape and need to restore to apply to standby databases. Could anyone help to give the steps or point me to documents that details the necessary actions on this scenario?

       

      Thanks.


        • 1. Re: Dataguard: sync primary to 2 standby database
          Mahir M. Quluzade

          Hi,

           

          Can you paste here results ?

           

          -- on both side

          select max(Sequence#)  from v$archived_log; 

           

          -- on standby side

           

          select max(Sequence#)  from v$archived_log where applied ='YES' ;

          select *  from  v$archive_gap; 

          select process from v$managed_standby;

           

          Regards

          Mahir M. Qulzuade

          http://www.mahir-quluzade.com

          • 2. Re: Dataguard: sync primary to 2 standby database
            Karan

            Go For RMAN Incremental Backups to Roll Forward a Physical Standby Database

             

             

            In cases where a physical standby database is far behind the primary database, an RMAN incremental backup can be used to roll the standby database forward faster than redo log apply.

             

            Go for Data Guard Scenarios

            • 3. Re: Dataguard: sync primary to 2 standby database
              rontrinidadcabral

              Here's the information from the monitoring script. Also, the message from alert log:

               

              DATABASE_ROLE  DB_UNIQUE_NAME  OPEN_MODE  PROTECTION_MODE  PROTECTION_LEVEL  STARTUP_TIME
              PHYSICAL STANDBY dgcuodsp READ ONLY WITH APPLY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE 20-MAY-2013 07:40:41
              Thread#  Difference  Last_Seq_Received  Last_Seq_Applied
              1 3 1949 1946
              2 1 1527 1526
              3 2 1470 1468
              4 2 247 245
              5 3 243 240


              NAME  VALUE  UNIT  TIME_COMPUTED  DATUM_TIME
              transport lag +00 00:00:00 day(2) to second(0) interval 07/10/2013 23:50:23 07/10/2013 23:50:22
              apply lag +05 23:41:51 day(2) to second(0) interval 07/10/2013 23:50:23 07/10/2013 23:50:22


              DEST_NAME  RECOVERY_MODE  STATUS  TYPE  DATABASE_MODE  DESTINATION  DB_UNIQUE_NAME
              LOG_ARCHIVE_DEST_1 MANAGED REAL TIME APPLY VALID LOCAL OPEN_READ-ONLY USE_DB_RECOVERY_FILE_DEST dg<instancename>
              LOG_ARCHIVE_DEST_2 IDLE VALID UNKNOWN UNKNOWN <instancename>_dgmgrl <instancename>
              LOG_ARCHIVE_DEST_3 IDLE DEFERRED UNKNOWN UNKNOWN <instancename> <instancename>
              LOG_ARCHIVE_DEST_4 MANAGED REAL TIME APPLY VALID LOCAL OPEN_READ-ONLY /backup_archivelog/<instancename> dg<instancename>

               

               

              alert log:

               

              RFS[33]: No standby redo logfiles created for thread 2

              Tue Jul 09 02:00:14 2013

              Media Recovery Log +FLASH/dgcuodsp/archivelog/2013_07_05/thread_2_seq_1527.6988.819936455

              Media Recovery Log +FLASH/dgcuodsp/archivelog/2013_07_09/thread_5_seq_241.32590.820288517

              RFS[33]: Opened log for thread 2 sequence 1528 dbid 242319701 branch 785780437

              Media Recovery Waiting for thread 1 sequence 1947

              Fetching gap sequence in thread 1, gap sequence 1947-1947

              Tue Jul 09 02:00:29 2013

              Fetching gap sequence in thread 1, gap sequence 1947-1947

              Tue Jul 09 02:00:39 2013

              Fetching gap sequence in thread 1, gap sequence 1947-1947

              • 4. Re: Dataguard: sync primary to 2 standby database
                Mahir M. Quluzade

                can you paste here on primary :

                show parameter Log_Archvie_dest_

                 

                I think, your transport is stopped on primary  database and you have archive gap on sequence 1947.

                 

                Can  you check  this sequence archivelog have in  primary or backup  of this archivelog?

                when  you restore this archivelog transport service can  transport this archivelog automatically.

                 

                on primary:


                RMAN>list backup of archivelog sequence 1947;

                RMAN> restore archivelog sequence 1947;

                 

                Start Redo Apply on standby database;

                 

                Regards

                Mahir

                • 5. Re: Dataguard: sync primary to 2 standby database
                  rontrinidadcabral

                  Hi Mahir,

                   

                  Thanks for your response. Logs are transported but not applied. When I check the +FLASH/archivelog in Standby DB, the sequence 1947-1948 do not exist. When I list the backup, 1947 exist. And the backup script will backup archivelog delete input.

                   

                  Can you specify the steps? I have 2 standby databases. - Start Redo Apply on standby database;

                   

                  LIST BACKUP OF ARCHIVELOG FROM SEQUENCE 1947;


                  BS Key  Size       Device Type Elapsed Time Completion Time
                  ------- ---------- ----------- ------------ ---------------
                  95235318 876.75M    SBT_TAPE    00:02:18     05-JUL-13
                          BP Key: 95235322   Status: AVAILABLE  Compressed: NO  Tag: DGCUODSP1_LOG_20130705_192859
                          Handle: dgcuodp_prod_al_2498_1_820006145   Media: @aaaj7

                    List of Archived Logs in backup set 95235318
                    Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
                    ---- ------- ---------- --------- ---------- ---------
                    1    1947    184716984  03-JUL-13 186152882  05-JUL-13

                  • 6. Re: Dataguard: sync primary to 2 standby database
                    rontrinidadcabral

                    when  you restore this archivelog transport service can  transport this archivelog automatically -

                     

                    this means that I just need to restore the required sequence of archivelog in primary and it will automatically re transported/ or transported to standby database?

                     

                    thanks

                    • 7. Re: Dataguard: sync primary to 2 standby database
                      Mahir M. Quluzade

                      Yes, it means redo apply is running on standby database then, request to primary  for 1947  sequence for resolve redo gap.

                      Then transport service send 1947 sequence to  standby. If archivelog have in place.

                      This process name is Automatic Redo  Gap  Resolution

                       

                       

                      Regards

                      Mahir M. Quluzade

                      • 8. Re: Dataguard: sync primary to 2 standby database
                        rontrinidadcabral

                        Thanks for your response. I am not at the office at the moment but need to prepare to do this on Sunday. So at that time maybe I have 5 sequence lag behind. Is automatic redo gap resolution new in 11g and always enable. Apologies. I am just starting to learn DG in my new work so I am just starting to learn the technology.

                         

                        We are using netbackup and the backup is on tape. So restore archivelog sequence <number> will automatically restore the archivelog back on primary FRA. that's in +FLASH of ASM. right?

                         

                        Here's the log_archive_dest_

                         

                        SYS@cuodsp1 SQL>show parameter log_archive_dest_

                        NAME                                 TYPE        VALUE
                        ------------------------------------ ----------- ------------------------------
                        log_archive_dest_1                   string      LOCATION=USE_DB_RECOVERY_FILE_
                                                                         DEST VALID_FOR=(ALL_LOGFILES,A
                                                                         LL_ROLES) DB_UNIQUE_NAME=cuods
                                                                         p
                        log_archive_dest_10                  string
                        log_archive_dest_11                  string
                        log_archive_dest_12                  string
                        log_archive_dest_13                  string
                        log_archive_dest_14                  string
                        log_archive_dest_15                  string
                        log_archive_dest_16                  string

                        NAME                                 TYPE        VALUE
                        ------------------------------------ ----------- ------------------------------
                        log_archive_dest_17                  string
                        log_archive_dest_18                  string
                        log_archive_dest_19                  string
                        log_archive_dest_2                   string      service="dgcuodsp", LGWR ASYNC
                                                                          NOAFFIRM delay=0 optional com
                                                                         pression=disable max_failure=0
                                                                          max_connections=1 reopen=300
                                                                         db_unique_name="dgcuodsp" net_
                                                                         timeout=30, valid_for=(all_log
                                                                         files,primary_role)
                        log_archive_dest_20                  string

                        NAME                                 TYPE        VALUE
                        ------------------------------------ ----------- ------------------------------
                        log_archive_dest_21                  string
                        log_archive_dest_22                  string
                        log_archive_dest_23                  string
                        log_archive_dest_24                  string
                        log_archive_dest_25                  string
                        log_archive_dest_26                  string
                        log_archive_dest_27                  string
                        log_archive_dest_28                  string
                        log_archive_dest_29                  string
                        log_archive_dest_3                   string      service=drcuodsp valid_for=(ST
                                                                         ANDBY_LOGFILES,STANDBY_ROLE) D

                        NAME                                 TYPE        VALUE
                        ------------------------------------ ----------- ------------------------------
                                                                         B_UNIQUE_NAME=drcuodsp
                        log_archive_dest_30                  string
                        log_archive_dest_31                  string
                        log_archive_dest_4                   string      location="+FLASH",  valid_for=
                                                                         (STANDBY_LOGFILE,STANDBY_ROLE)
                        log_archive_dest_5                   string
                        log_archive_dest_6                   string
                        log_archive_dest_7                   string
                        log_archive_dest_8                   string
                        log_archive_dest_9                   string
                        log_archive_dest_state_1             string      enable

                        NAME                                 TYPE        VALUE
                        ------------------------------------ ----------- ------------------------------
                        log_archive_dest_state_10            string      enable
                        log_archive_dest_state_11            string      enable
                        log_archive_dest_state_12            string      enable
                        log_archive_dest_state_13            string      enable
                        log_archive_dest_state_14            string      enable
                        log_archive_dest_state_15            string      enable
                        log_archive_dest_state_16            string      enable
                        log_archive_dest_state_17            string      enable
                        log_archive_dest_state_18            string      enable
                        log_archive_dest_state_19            string      enable
                        log_archive_dest_state_2             string      ENABLE

                        NAME                                 TYPE        VALUE
                        ------------------------------------ ----------- ------------------------------
                        log_archive_dest_state_20            string      enable
                        log_archive_dest_state_21            string      enable
                        log_archive_dest_state_22            string      enable
                        log_archive_dest_state_23            string      enable
                        log_archive_dest_state_24            string      enable
                        log_archive_dest_state_25            string      enable
                        log_archive_dest_state_26            string      enable
                        log_archive_dest_state_27            string      enable
                        log_archive_dest_state_28            string      enable
                        log_archive_dest_state_29            string      enable
                        log_archive_dest_state_3             string      enable

                        NAME                                 TYPE        VALUE
                        ------------------------------------ ----------- ------------------------------
                        log_archive_dest_state_30            string      enable
                        log_archive_dest_state_31            string      enable
                        log_archive_dest_state_4             string      ENABLE
                        log_archive_dest_state_5             string      enable
                        log_archive_dest_state_6             string      enable
                        log_archive_dest_state_7             string      enable
                        log_archive_dest_state_8             string      enable
                        log_archive_dest_state_9             string      enable

                        • 9. Re: Dataguard: sync primary to 2 standby database
                          rontrinidadcabral

                          If sequence 1947 is transported - as shown in Log Received is 1949. So automatic redo gap resolution will not work? So what I need to do is to restore the archive log in standby not in primary? and then the ff. command? thanks

                           

                          alter database recover managed standby database cancel;

                          alter database recover managed standby database using current logfile disconnect;

                           

                          DATABASE_ROLE  DB_UNIQUE_NAME  OPEN_MODE  PROTECTION_MODE  PROTECTION_LEVEL  STARTUP_TIME

                          PHYSICAL STANDBY dgcuodsp READ ONLY WITH APPLY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE 20-MAY-2013 07:40:41

                          Thread#  Difference  Last_Seq_Received  Last_Seq_Applied

                          1 3 1949 1946

                          2 1 1527 1526

                          3 2 1470 1468

                          4 2 247 245

                          5 3 243 240

                          • 10. Re: Dataguard: sync primary to 2 standby database
                            Mahir M. Quluzade

                            did you  using broker?

                             

                            You must change Log_archive_dest_2 and dest_3

                             

                            alter system set log_archive_dest_state_2=DEFER;

                            alter system set log_archive_dest_state_3=DEFER;

                             

                            alter system set log_archive_dest_2='service=dgcuodsp ASYNC  NOAFFIRM db_unique_name=dgcuodsp valid_for=(all_logfiles,primary_role)' sid="*";

                            alter system set log_archive_dest_3='service=drcuodsp ASYNC NOAFFIRM db_unique_name=drcuodsp valid_for=(all_logfiles,primary_role)' sid="*";


                            alter system set log_archive_dest_state_2=ENABLE;

                            alter system set log_archive_dest_state_3=ENABLE;

                             

                             

                            On Both physical standby database:



                            alter database recover managed standby database using current logfile disconnect;

                            alter database recover managed standby database using current logfile disconnect;

                             

                             

                            Paste here after changes :

                             

                            on Primary

                             

                            select protection_mode from v$database;

                            select process from  v$managed_Standby where process like 'NL%';

                             

                            select dest_id, status from  v$archive_dest

                            where dest_id <= 3;

                             

                            on standby databases:

                            select process from  v$managed_Standby where process like 'MRP%';

                             

                            Why you using on primary database ?

                            location="+FLASH",  valid_for=(STANDBY_LOGFILE,STANDBY_ROLE)

                            Standby  redo  log wil use when database role is change :



                            Regards

                            Mahir


                            • 11. Re: Dataguard: sync primary to 2 standby database
                              Mahir M. Quluzade

                              Then restore also 1948, 1949 archived logs.

                              Your transport is not running.  Please change log_archive_dest_2 and dest_3 as my before this reply.

                              • 12. Re: Dataguard: sync primary to 2 standby database
                                rontrinidadcabral


                                Do I need to re create the standby control file?

                                • 13. Re: Dataguard: sync primary to 2 standby database
                                  Mahir M. Quluzade

                                  Hi,

                                   

                                  No,  not need.

                                  Please mark as answered for your thread,  if your question is answered, for clear forum, please! 

                                   

                                  Regards

                                  Mahir M. Quluzade

                                  • 14. Re: Dataguard: sync primary to 2 standby database
                                    rontrinidadcabral

                                    Hi,

                                     

                                    I just restored the archivelogs and it automatically apply on standby. while doing it, I monitored the alert log to know which arch is currently being fetch and from there i know which arch need to be restored.

                                     

                                    the issue now is i wonder why it takes time to used up the arch logs, after almost 9 hrs it is still using sequence 245 and 249 only. but i think this is a different issue and will mark this issue as answered. thanks for your input.

                                     

                                    SYS@dgcuodsp1 SQL>SET lines 1000
                                    COLUMN NAME FORMAT A30
                                    COLUMN VALUE FORMAT A16
                                    COLUMN TIME_COMPUTED FORMAT A24
                                    SELECT * FROM v$dataguard_stats WHERE NAME IN ('transport lag', 'apply lag');SYS@dgcuodsp1 SQL>SYS@dgcuodsp1 SQL>SYS@dgcuodsp1 SQL>SYS@dgcuodsp1 SQL>

                                    NAME                           VALUE            UNIT                           TIME_COMPUTED            DATUM_TIME
                                    ------------------------------ ---------------- ------------------------------ ------------------------ ------------------------------
                                    transport lag                  +00 00:00:00     day(2) to second(0) interval   07/15/2013 12:33:08      07/15/2013 12:33:06
                                    apply lag                      +00 09:05:17     day(2) to second(0) interval   07/15/2013 12:33:08      07/15/2013 12:33:06

                                    SYS@dgcuodsp1 SQL>select a.thread#, a.Last_Seq_Received, b.Last_Seq_Applied
                                      2  from
                                      3             (SELECT thread#, MAX (sequence#) Last_Seq_Received
                                            FROM v$archived_log
                                      4    5          GROUP BY thread#
                                            ORDER BY 1) a,
                                      6    7          (  SELECT thread#, MAX (sequence#) Last_Seq_Applied
                                      8              FROM v$archived_log
                                               WHERE applied IN ('YES', 'IN-MEMORY')
                                      9   10          GROUP BY thread#
                                            ORDER BY 1) b
                                    11   12   WHERE a.thread# = b.thread#;

                                       THREAD# LAST_SEQ_RECEIVED LAST_SEQ_APPLIED
                                    ---------- ----------------- ----------------
                                             1              1953             1952
                                             2              1530             1529
                                             3              1473             1473
                                             4               249              248
                                             5               245              244


                                    RFS[28]: Opened log for thread 5 sequence 245 dbid 242319701 branch 785780437
                                    Archived Log entry 7926 added for thread 5 sequence 245 rlc 785780437 ID 0xe725855 dest 2:
                                    Mon Jul 15 06:13:57 2013
                                    RFS[31]: Opened log for thread 4 sequence 249 dbid 242319701 branch 785780437
                                    Archived Log entry 7927 added for thread 4 sequence 249 rlc 785780437 ID 0xe725855 dest 2:
                                    Mon Jul 15 09:06:21 2013
                                    RFS[31]: Opened log for thread 4 sequence 249 dbid 242319701 branch 785780437
                                    Archived Log entry 7936 added for thread 4 sequence 249 rlc 785780437 ID 0xe725855 dest 2:
                                    Mon Jul 15 09:06:59 2013
                                    RFS[28]: Opened log for thread 5 sequence 245 dbid 242319701 branch 785780437
                                    Archived Log entry 7938 added for thread 5 sequence 245 rlc 785780437 ID 0xe725855 dest 2:
                                    Mon Jul 15 10:16:10 2013
                                    RFS[28]: Opened log for thread 5 sequence 245 dbid 242319701 branch 785780437
                                    Archived Log entry 7941 added for thread 5 sequence 245 rlc 785780437 ID 0xe725855 dest 2:
                                    Mon Jul 15 11:06:38 2013
                                    RFS[31]: Opened log for thread 4 sequence 249 dbid 242319701 branch 785780437
                                    Archived Log entry 7943 added for thread 4 sequence 249 rlc 785780437 ID 0xe725855 dest 2:
                                    Mon Jul 15 12:25:29 2013
                                    RFS[28]: Opened log for thread 5 sequence 245 dbid 242319701 branch 785780437
                                    Archived Log entry 7947 added for thread 5 sequence 245 rlc 785780437 ID 0xe725855 dest 2:

                                    1 2 Previous Next