7 Replies Latest reply: Sep 12, 2013 11:47 AM by mseberg RSS

    Logs applied on standby?

    User369327

      hi, we just setup a physical standby for a RAC database (2 instances) 11.2.0.3. Logs are getting applied but when we check the status of the archives on the standby, the last log from one of the instances is always marked as not applied. It is always for the instance opposite from the one that had the most recent log switch

       

      SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

       

      SEQUENCE# APPLIED

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

            4089 YES

            4090 YES

            4091 YES

            4092 YES

            4093 YES

            4094 YES

            4095 YES

            4096 YES

            4097 YES

            4098 YES

            4099 YES

            4100 YES

            4101 YES

            4102 YES

            4103 YES

            4104 YES

            4105 YES

            4106 NO

            4309 YES

            4310 YES

            4311 YES

            4312 YES

            4313 YES

            4314 YES

            4315 YES

            4316 YES

            4317 YES

            4318 YES

            4319 YES

            4320 YES

            4321 YES

            4322 YES

            4323 YES

            4324 YES

            4325 YES

       

      35 rows selected.

       

       

      On the primary instance the logs show as

       

       

      SYS:dfcip12 > archive log list

      Database log mode              Archive Mode

      Automatic archival             Enabled

      Archive destination            +ORA_DATA

      Oldest online log sequence     4105

      Next log sequence to archive   4107

      Current log sequence           4107

      SYS:dfcip12 >

       

      Is this anything to worry about or are we missing something? Appreciate any thoughts. Thanks.

        • 1. Re: Logs applied on standby?
          mseberg

          Hello;

           

          Where are you running this query from ( Primary side or Standby side)?

           

          I would change the query to include DEST_ID and THREAD# if run on the primary side too.

           

          I like this query from the Primary side, change as needed:

           

          Monitor Data Guard Transport

           

          Adding a SYSDATE can help too.

           

          clear screen

          set linesize 100

           

          column STANDBY format a20

          column applied format a10

           

          SELECT NAME AS STANDBY, SEQUENCE#, APPLIED, COMPLETION_TIME FROM V$ARCHIVED_LOG WHERE DEST_ID = 2 AND NEXT_TIME > SYSDATE -1 ORDER BY SEQUENCE#;

           

           

          Best Regards

           

          mseberg

          • 2. Re: Logs applied on standby?
            User369327

            I'm running that query on the standby.

            • 3. Re: Logs applied on standby?
              mseberg

              OK, trying adding the SYSDATE - like I show before and see if you get better results.

               

              Best Regards

               

              mseberg

              • 4. Re: Logs applied on standby?
                User369327

                i did but same results. That one log still shows as not applied.

                 

                 

                STANDBY               SEQUENCE# APPLIED    COMPLETIO

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

                +ORA_DATA/dfcip1/arc       4090 YES        11-SEP-13

                hivelog/2013_09_11/t

                hread_2_seq_4090.874

                .825850059

                 

                +ORA_DATA/dfcip1/arc       4091 YES        11-SEP-13

                hivelog/2013_09_11/t

                hread_2_seq_4091.876

                .825850061

                 

                +ORA_DATA/dfcip1/arc       4092 YES        11-SEP-13

                hivelog/2013_09_11/t

                hread_2_seq_4092.875

                .825850061

                 

                +ORA_DATA/dfcip1/arc       4093 YES        11-SEP-13

                hivelog/2013_09_11/t

                hread_2_seq_4093.881

                .825850201

                 

                +ORA_DATA/dfcip1/arc       4094 YES        11-SEP-13

                hivelog/2013_09_11/t

                hread_2_seq_4094.882

                .825850401

                 

                +ORA_DATA/dfcip1/arc       4095 YES        11-SEP-13

                hivelog/2013_09_11/t

                hread_2_seq_4095.883

                .825850491

                 

                +ORA_DATA/dfcip1/arc       4096 YES        11-SEP-13

                hivelog/2013_09_11/t

                hread_2_seq_4096.889

                .825852423

                 

                +ORA_DATA/dfcip1/arc       4097 YES        11-SEP-13

                hivelog/2013_09_11/t

                hread_2_seq_4097.893

                .825852859

                 

                +ORA_DATA/dfcip1/arc       4098 YES        11-SEP-13

                hivelog/2013_09_11/t

                hread_2_seq_4098.894

                .825852943

                 

                +ORA_DATA/dfcip1/arc       4099 YES        11-SEP-13

                hivelog/2013_09_11/t

                hread_2_seq_4099.919

                .825866869

                 

                +ORA_DATA/dfcip1/arc       4100 YES        11-SEP-13

                hivelog/2013_09_11/t

                hread_2_seq_4100.929

                .825873439

                 

                +ORA_DATA/dfcip1/arc       4101 YES        11-SEP-13

                hivelog/2013_09_11/t

                hread_2_seq_4101.675

                .825879499

                 

                +ORA_DATA/dfcip1/arc       4102 YES        11-SEP-13

                hivelog/2013_09_11/t

                hread_2_seq_4102.794

                .825879895

                 

                +ORA_DATA/dfcip1/arc       4103 YES        11-SEP-13

                hivelog/2013_09_11/t

                hread_2_seq_4103.935

                .825883837

                 

                +ORA_DATA/dfcip1/arc       4104 YES        11-SEP-13

                hivelog/2013_09_11/t

                hread_2_seq_4104.941

                .825888229

                 

                +ORA_DATA/dfcip1/arc       4105 YES        12-SEP-13

                hivelog/2013_09_11/t

                hread_2_seq_4105.947

                .825892671

                 

                +ORA_DATA/dfcip1/arc       4106 NO         12-SEP-13

                hivelog/2013_09_12/t

                hread_2_seq_4106.930

                .825912003

                • 5. Re: Logs applied on standby?
                  mseberg

                  Any chance you have Apply running with "USING CURRENT LOG" ?

                   

                  If yes then this would be normal as Oracle will see the log as soon as its gets created, but not mark it applied until it moves to the next log.

                   

                  An ALTER SYSTEM SWITCH LOGFILE on the primary would shake this out.

                   

                  Best Regards

                   

                  mseberg

                  • 6. Re: Logs applied on standby?
                    User369327

                    HI, we started it with below so unless 'USING CURRENT LOG' is a default then we're not using it

                     

                    alter database recover managed standby database disconnect from session;

                     

                     

                    If i do another switch logfile then the log marked NO is now marked applied YES but the new log that was just archived is marked applied NO

                    • 7. Re: Logs applied on standby?
                      mseberg

                      Odd.

                       

                      My Test Primary

                       

                      SQL> select max(sequence#) from v$archived_log;

                       

                      MAX(SEQUENCE#)

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

                        807

                       

                      SQL> alter system switch logfile;

                       

                      System altered.

                       

                       

                      My Test Standby

                       

                      SQL> select max(sequence#) from v$archived_log;

                       

                      MAX(SEQUENCE#)

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

                        807

                       

                      SQL> /

                       

                      MAX(SEQUENCE#)

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

                        807

                       

                      SQL> /

                       

                      MAX(SEQUENCE#)

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

                        808

                       

                      SQL>

                       

                       

                      It takes a few seconds but it moves to 808.

                       

                      But if I run your query in your first post I get the same results.

                       

                      SEQUENCE# APPLIED

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

                        799 YES

                        800 YES

                        801 YES

                        802 YES

                        803 YES

                        804 YES

                        805 YES

                        806 YES

                        807 YES

                        808 NO

                       

                      So Oracle knows about the log, but has not applied it yet. The Standby alert log will confirm:

                       

                      Media Recovery Waiting for thread 1 sequence 808 (in transit)

                       

                      I don't see an issue.

                       

                      Best Regards

                       

                      mseberg