8 Replies Latest reply: Apr 18, 2013 1:53 AM by Hemant K Chitale RSS

    my Active dataguard is not applying archives.

    VIKHARAHMED
      Dears,

      Following is my setup
      =====================

      Primary:

      prod on RAC with 2 nodes
      Database : Oracle 11.2.0.3
      OS OEL 5.7
      storage : ASM
      +DATADG
      +FLASHDG


      Standby:

      sync on single node
      Database : Oracle 11.2.0.3
      storage : ASM
      +DATADG
      +FLASHDG


      I've configured Active dataguard successfully and was able to apply archivelogs successfully instantly.
      Archive log locations are +FLASHDG/prod/ARCHIVELOG/ AND /u02/arch  --- on primary

      Archive log locations are +FLASHDG/stby/ARCHIVELOG/ AND /u02/arch  --- on standby




      On Standby
      ===========
      SQL> select 'Using Active Data Guard' ADG from v$managed_standby m,v$database d where m.process like 'MRP%' ;

      ADG
      -----------------------
      Using Active Data Guard

      SQL> select open_mode,database_role,PROTECTION_MODE,PROTECTION_LEVEL from v$database;

      OPEN_MODE DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL
      -------------------- ---------------- -------------------- --------------------
      READ ONLY WITH APPLY PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE

      SQL>


      Upto now everything is ok, means same data is sync on primary and standby. As my archive location was getting full, i've used this SQL query to clean them



      SELECT 'alter diskgroup '||dg.name||' drop file
      ''+'||dg.name||''||SYS_CONNECT_BY_PATH(al.name,'/')||''';'
      FROM v$asm_alias al, v$asm_file fi, v$asm_diskgroup dg
      WHERE al.file_number = fi.file_number(+)
      AND al.group_number = dg.group_number
      AND fi.type = 'ARCHIVELOG'
      START WITH alias_index = 0
      CONNECT BY PRIOR al.reference_index = al.parent_index;



      SELECT 'alter diskgroup '||dg.name||' drop file
      ''+'||dg.name||''||SYS_CONNECT_BY_PATH(al.name,'/')||''';'
      FROM v$asm_alias al, v$asm_file fi, v$asm_diskgroup dg
      WHERE al.file_number = fi.file_number(+)
      AND al.group_number = dg.group_number
      AND fi.type = 'BACKUPSET'
      START WITH alias_index = 0
      CONNECT BY PRIOR al.reference_index = al.parent_index;

      In order to delete ARCHIVE logs which are stored in ASM FLASHDG, which will have a type of "ARCHIVELOG" and "BACKUPSET" i've used this above two queries.
      After performing this steps , my Active dataguard is not applying archives.


      On the primary server, check the latest archived redo log
      SELECT sequence#, first_time, next_time
      FROM v$archived_log
      ORDER BY sequence#;

      2809 16-APR-13 16-APR-13
      2809 16-APR-13 16-APR-13
      2809 16-APR-13 16-APR-13
      2810 16-APR-13 16-APR-13
      2810 16-APR-13 16-APR-13
      2810 16-APR-13 16-APR-13
      2811 16-APR-13 16-APR-13
      2811 16-APR-13 16-APR-13
      2811 16-APR-13 16-APR-13
      2812 16-APR-13 16-APR-13
      2812 16-APR-13 16-APR-13
      2812 16-APR-13 16-APR-13
      2813 16-APR-13 16-APR-13
      2813 16-APR-13 16-APR-13
      2814 16-APR-13 16-APR-13
      2814 16-APR-13 16-APR-13
      2815 16-APR-13 16-APR-13
      2815 16-APR-13 16-APR-13
      2816 16-APR-13 16-APR-13
      2816 16-APR-13 16-APR-13
      2817 16-APR-13 16-APR-13
      2817 16-APR-13 16-APR-13
      2818 16-APR-13 16-APR-13
      2818 16-APR-13 16-APR-13
      2819 16-APR-13 16-APR-13
      2819 16-APR-13 16-APR-13
      2820 16-APR-13 16-APR-13
      2820 16-APR-13 16-APR-13
      2821 16-APR-13 16-APR-13
      2821 16-APR-13 16-APR-13
      2822 16-APR-13 16-APR-13
      2822 16-APR-13 16-APR-13
      2823 16-APR-13 16-APR-13
      2823 16-APR-13 16-APR-13
      2824 16-APR-13 16-APR-13
      2824 16-APR-13 16-APR-13
      2825 16-APR-13 16-APR-13
      2825 16-APR-13 16-APR-13
      2826 16-APR-13 16-APR-13
      2826 16-APR-13 16-APR-13
      2827 16-APR-13 16-APR-13
      2827 16-APR-13 16-APR-13
      2828 16-APR-13 16-APR-13
      2828 16-APR-13 16-APR-13
      2829 16-APR-13 16-APR-13
      2829 16-APR-13 16-APR-13
      2830 16-APR-13 16-APR-13
      2830 16-APR-13 16-APR-13
      2831 16-APR-13 16-APR-13
      2831 16-APR-13 16-APR-13
      2832 16-APR-13 16-APR-13
      2832 16-APR-13 16-APR-13
      2833 16-APR-13 16-APR-13
      2833 16-APR-13 16-APR-13
      2834 16-APR-13 16-APR-13
      2834 16-APR-13 16-APR-13
      2835 16-APR-13 16-APR-13
      2835 16-APR-13 16-APR-13
      2836 16-APR-13 16-APR-13
      2836 16-APR-13 16-APR-13
      2837 16-APR-13 16-APR-13
      2837 16-APR-13 16-APR-13
      2838 16-APR-13 16-APR-13
      2838 16-APR-13 16-APR-13
      2839 16-APR-13 16-APR-13
      2839 16-APR-13 16-APR-13
      2840 16-APR-13 16-APR-13
      2840 16-APR-13 16-APR-13
      2841 16-APR-13 16-APR-13
      2841 16-APR-13 16-APR-13
      2842 16-APR-13 16-APR-13
      2842 16-APR-13 16-APR-13
      2843 16-APR-13 16-APR-13
      2843 16-APR-13 16-APR-13
      2844 16-APR-13 16-APR-13
      2844 16-APR-13 16-APR-13
      2845 16-APR-13 16-APR-13
      2845 16-APR-13 16-APR-13
      2846 16-APR-13 16-APR-13
      2846 16-APR-13 16-APR-13


      Check the new archived redo log has arrived at the standby server and been applied.

      SELECT sequence#, first_time, next_time, applied
      FROM v$archived_log
      ORDER BY sequence#;
      2801 15-APR-13 15-APR-13 YES
      2801 15-APR-13 15-APR-13 YES
      2802 15-APR-13 15-APR-13 YES
      2802 15-APR-13 15-APR-13 YES
      2803 15-APR-13 15-APR-13 YES
      2803 15-APR-13 15-APR-13 YES
      2804 15-APR-13 15-APR-13 YES
      2804 15-APR-13 15-APR-13 YES
      2805 15-APR-13 15-APR-13 YES
      2805 15-APR-13 15-APR-13 YES
      2806 15-APR-13 15-APR-13 YES
      2806 15-APR-13 15-APR-13 YES
      2807 15-APR-13 15-APR-13 YES
      2807 15-APR-13 15-APR-13 YES
      2808 15-APR-13 16-APR-13 YES
      2808 15-APR-13 16-APR-13 YES
      2809 16-APR-13 16-APR-13 YES
      2809 16-APR-13 16-APR-13 YES
      2810 16-APR-13 16-APR-13 YES
      2810 16-APR-13 16-APR-13 YES
      2811 16-APR-13 16-APR-13 YES
      2811 16-APR-13 16-APR-13 YES
      2812 16-APR-13 16-APR-13 IN-MEMORY
      2812 16-APR-13 16-APR-13 YES


      So could anyone help me to resolve this issue. Anticipating your response at the earliest.

      Regards,
      VIKHAR

      Edited by: VIKHARAHMED on Apr 16, 2013 9:38 AM
        • 1. Re: my Active dataguard is not applying archives.
          Mahir M. Quluzade
          Can you paste here last 25-30 sentence in Standby alert log file,

          Mahir
          • 2. Re: my Active dataguard is not applying archives.
            Hemant K Chitale
            As my archive location was getting full, i've used this SQL query to clean them
            You intended to delete every ArchiveLog
            --- without verifying if
            (a) the ArchiveLogs had been backed up
            (b) the Standby was consistent with the Primary (i.e. the Standby wasn't lagging)
            ??

            How have you configured Redo Shipping to the Standby ? Is it LGWR SYNC ? Or ASYNC (possibly falling back to ArchiveLogs if the Standby is unable to catch up with the Primary) ?


            Hemant K Chitale
            • 3. Re: my Active dataguard is not applying archives.
              VIKHARAHMED
              Here is the alert log file

              Media Recovery Log +FLASHDG/stby/archivelog/2013_04_16/thread_2_seq_1476.1987.812900733
              Tue Apr 16 18:56:42 2013
              Primary database is in MAXIMUM PERFORMANCE mode
              RFS[1]: Assigned to RFS process 16803
              RFS[1]: Selected log 12 for thread 2 sequence 1485 dbid 220323208 branch 808484882
              Tue Apr 16 18:56:42 2013
              RFS[2]: Assigned to RFS process 16807
              RFS[2]: Opened log for thread 2 sequence 1484 dbid 220323208 branch 808484882
              Archived Log entry 6324 added for thread 2 sequence 1484 rlc 808484882 ID 0xd21a288 dest 10:
              Tue Apr 16 19:09:02 2013
              RFS[1]: Selected log 11 for thread 2 sequence 1486 dbid 220323208 branch 808484882
              Tue Apr 16 19:09:02 2013
              Archived Log entry 6325 added for thread 2 sequence 1485 ID 0xd21a288 dest 1:
              Archived Log entry 6326 added for thread 2 sequence 1485 ID 0xd21a288 dest 2:
              Tue Apr 16 19:12:23 2013
              RFS[1]: Selected log 12 for thread 2 sequence 1487 dbid 220323208 branch 808484882
              Tue Apr 16 19:12:23 2013
              Archived Log entry 6327 added for thread 2 sequence 1486 ID 0xd21a288 dest 1:
              Archived Log entry 6328 added for thread 2 sequence 1486 ID 0xd21a288 dest 2:
              Tue Apr 16 19:14:41 2013
              "alert_stby.log" 845L, 33990C 782,1 94%
              Tue Apr 16 19:17:27 2013
              Archived Log entry 6331 added for thread 2 sequence 1488 ID 0xd21a288 dest 1:
              Archived Log entry 6332 added for thread 2 sequence 1488 ID 0xd21a288 dest 2:
              Tue Apr 16 19:25:51 2013
              RFS[1]: Selected log 11 for thread 2 sequence 1490 dbid 220323208 branch 808484882
              Tue Apr 16 19:25:51 2013
              Archived Log entry 6333 added for thread 2 sequence 1489 ID 0xd21a288 dest 1:
              Archived Log entry 6334 added for thread 2 sequence 1489 ID 0xd21a288 dest 2:
              Tue Apr 16 19:51:02 2013
              RFS[1]: Selected log 12 for thread 2 sequence 1491 dbid 220323208 branch 808484882
              Tue Apr 16 19:51:02 2013
              Archived Log entry 6335 added for thread 2 sequence 1490 ID 0xd21a288 dest 1:
              Archived Log entry 6336 added for thread 2 sequence 1490 ID 0xd21a288 dest 2:
              Tue Apr 16 20:37:12 2013
              RFS[1]: Selected log 11 for thread 2 sequence 1492 dbid 220323208 branch 808484882
              Tue Apr 16 20:37:12 2013
              Archived Log entry 6337 added for thread 2 sequence 1491 ID 0xd21a288 dest 1:
              Archived Log entry 6338 added for thread 2 sequence 1491 ID 0xd21a288 dest 2:
              Tue Apr 16 21:08:23 2013
              RFS[1]: Selected log 12 for thread 2 sequence 1493 dbid 220323208 branch 808484882
              Tue Apr 16 21:08:23 2013
              Archived Log entry 6339 added for thread 2 sequence 1492 ID 0xd21a288 dest 1:
              Archived Log entry 6340 added for thread 2 sequence 1492 ID 0xd21a288 dest 2:
              Tue Apr 16 22:16:06 2013
              RFS[1]: Selected log 11 for thread 2 sequence 1494 dbid 220323208 branch 808484882
              Tue Apr 16 22:16:06 2013
              Archived Log entry 6341 added for thread 2 sequence 1493 ID 0xd21a288 dest 1:
              Archived Log entry 6342 added for thread 2 sequence 1493 ID 0xd21a288 dest 2:
              Tue Apr 16 22:31:46 2013
              RFS[1]: Selected log 12 for thread 2 sequence 1495 dbid 220323208 branch 808484882
              Tue Apr 16 22:31:46 2013
              Archived Log entry 6343 added for thread 2 sequence 1494 ID 0xd21a288 dest 1:
              Archived Log entry 6344 added for thread 2 sequence 1494 ID 0xd21a288 dest 2:
              Wed Apr 17 09:31:59 2013
              RFS[1]: Selected log 11 for thread 2 sequence 1496 dbid 220323208 branch 808484882
              Wed Apr 17 09:32:00 2013
              Archived Log entry 6345 added for thread 2 sequence 1495 ID 0xd21a288 dest 1:
              Archived Log entry 6346 added for thread 2 sequence 1495 ID 0xd21a288 dest 2:
              • 4. Re: my Active dataguard is not applying archives.
                Hemant K Chitale
                The alert.log shows receipt of Thread#2 archivelogs only, not Thread#1. Your Primary database is a RAC database with at least two threads. The query on the Primary that you posted seemed to have been on Thread#1 whose archivelogs are not visible at the Standby.


                Hemant K Chitale
                • 5. Re: my Active dataguard is not applying archives.
                  VIKHARAHMED
                  (a) the ArchiveLogs had been backed up
                  I've deleted stby archivelogs which were already archived on stby.

                  (b) the Standby was consistent with the Primary (i.e. the Standby wasn't lagging)
                  Yes, it is consistent.

                  How have you configured Redo Shipping to the Standby ? Is it LGWR SYNC ? Or ASYNC

                  *.log_archive_dest_10='service=stby LGWR ASYNC NOAFFIRM max_failure=10 max_connections=12 reopen=180 valid_for=(online_logfiles,primary_role) db_unique_name=stby'

                  Regards,
                  VIKHAR

                  Edited by: VIKHARAHMED on Apr 17, 2013 12:41 AM
                  • 6. Re: my Active dataguard is not applying archives.
                    Mahir M. Quluzade
                    VIKHARAHMED wrote:
                    (a) the ArchiveLogs had been backed up
                    I've deleted stby archivelogs which were already archived on stby.
                    This archived redo logs is applied ? I think no.
                    (b) the Standby was consistent with the Primary (i.e. the Standby wasn't lagging)
                    Yes, it is consistent.
                    How to you checked ?
                    can you paste here result of,

                    --on primary 
                    
                    select max(Sequence#) from  gv$archive_log; 
                    
                    -- on standby 
                    select max(Sequence#) from  v$archive_log where applied ='YES';
                    
                    or 
                    
                    select current_scn  form  v$database; --- both  side
                    >
                    How have you configured Redo Shipping to the Standby ? Is it LGWR SYNC ? Or ASYNC

                    *.log_archive_dest_10='service=stby LGWR ASYNC NOAFFIRM max_failure=10 max_connections=12 reopen=180 valid_for=(online_logfiles,primary_role) db_unique_name=stby'
                    Why you using MAX_CONNECTIONS atribiute? Documentation says : MAX_CONNECTION valid values 1-5.

                    Thanks
                    Mahir M. Quluzade
                    • 7. Re: my Active dataguard is not applying archives.
                      1002948
                      Copy those missing archives and paste at standby location

                      and then use the command

                      alter database register logfiles '...'


                      Varun
                      • 8. Re: my Active dataguard is not applying archives.
                        Hemant K Chitale
                        The alert.log shows
                        Media Recovery Log +FLASHDG/stby/archivelog/2013_04_16/thread_2_seq_1476.1987.812900733
                        the last archivelog being applied as THREAD#2 SEQUENCE#1476.

                        It shows subsequent THREAD#2 files received. It doesn't show them being applied.
                        It doesn't show receipt of THREAD#1 files.


                        Hemant K Chitale