7 Replies Latest reply: Mar 20, 2013 9:17 AM by CKPT RSS

    Applied Status is "NO" in v$archived_log in 11.2.0.2 Data Guard

    Arun Natarajan-OC
      Hi Friends,

      I am using 11.2.0.2 Data Guard .

      I could perform Log Switch multiple times on Primary and the new sequence is getting reflected in Standby and the Archived Logs are also creating

      but i could see that in the v$archived_log the applied status is NO. What is the reason?


      Primary:
      ----------------

      SQL> archive log list;
      Database log mode Archive Mode
      Automatic archival Enabled
      Archive destination /data/dg/arch1/chicago/
      Oldest online log sequence 1187
      Next log sequence to archive 1189
      Current log sequence 1189
      SQL>

      SQL> host ls -lt /data/dg/arch1/chicago/ | more
      total 25051512
      -rw-r----- 1 oracle oinstall 2560 Mar 19 19:04 1_1187_799437329.arc
      -rw-r----- 1 oracle oinstall 1024 Mar 19 19:04 1_1188_799437329.arc
      -rw-r----- 1 oracle oinstall 3584 Mar 19 19:04 1_1186_799437329.arc
      -rw-r----- 1 oracle oinstall 6144 Mar 19 19:04 1_1185_799437329.arc
      -rw-r----- 1 oracle oinstall 1536 Mar 19 19:04 1_1184_799437329.arc



      SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

      no rows selected


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

      SEQUENCE# APPLIED
      ---------- ---------
      1175 NO
      1175 NO
      1176 NO
      1176 NO
      1177 NO
      1177 NO
      1178 NO
      1178 NO
      1179 NO
      1179 NO
      1180 NO

      SEQUENCE# APPLIED
      ---------- ---------
      1180 NO
      1181 NO
      1181 NO
      1182 NO
      1182 NO
      1183 NO
      1183 NO
      1184 NO
      1184 NO
      1185 NO
      1185 NO

      SEQUENCE# APPLIED
      ---------- ---------
      1186 NO
      1186 NO
      1187 NO
      1187 NO
      1188 NO
      1188 NO

      710 rows selected.

      SQL>


      Standby:
      --------------

      SQL> archive log list;
      Database log mode Archive Mode
      Automatic archival Enabled
      Archive destination /data/dg/arch1/boston/
      Oldest online log sequence 1185
      Next log sequence to archive 0
      Current log sequence 1189


      SQL> host ls -lt /data/dg/arch1/boston/ | more
      total 25041236
      -rw-r----- 1 oradg oinstall 1024 Mar 19 19:30 1_1188_799437329.arc
      -rw-r----- 1 oradg oinstall 2560 Mar 19 19:30 1_1187_799437329.arc
      -rw-r----- 1 oradg oinstall 3584 Mar 19 19:30 1_1186_799437329.arc
      -rw-r----- 1 oradg oinstall 6144 Mar 19 19:30 1_1185_799437329.arc
      -rw-r----- 1 oradg oinstall 1536 Mar 19 19:30 1_1184_799437329.arc




      SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

      no rows selected


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

      SEQUENCE# APPLIED
      ---------- ---------
      1175 NO
      1176 NO
      1177 NO
      1178 NO
      1179 NO
      1180 NO
      1181 NO
      1182 NO
      1183 NO
      1184 NO
      1185 NO

      SEQUENCE# APPLIED
      ---------- ---------
      1186 NO
      1187 NO
      1188 NO

      Regards,
      DB
        • 1. Re: Applied Status is "NO" in v$archived_log in 11.2.0.2 Data Guard
          mseberg
          Hello;

          A few points :

          This V$ARCHIVE_GAP is not very useful.
          You need to check and post errors that are probably occurring in either the Standby or Primary alert logs.

          Finally run this from the primary and post the results

          http://www.visi.com/~mseberg/data_guard/monitor_data_guard_transport.html

          Best Regards

          mseberg

          Edited by: mseberg on Mar 19, 2013 9:04 AM
          • 2. Re: Applied Status is "NO" in v$archived_log in 11.2.0.2 Data Guard
            user11982706
            Please check your alert.log file on standby database. See if RFS & MPR process are fine or not. Please post last few lines of your alert.log from standby.


            Thanks

            Yogesh
            http://orasteps.wordpress.com/
            • 3. Re: Applied Status is "NO" in v$archived_log in 11.2.0.2 Data Guard
              CKPT
              I could perform Log Switch multiple times on Primary and the new sequence is getting reflected in Standby and the Archived Logs are also creating
              but i could see that in the v$archived_log the applied status is NO. What is the reason?
              In primary the archives were not applied of course they will be created. There was discussion on the same topic last week, please check the thread
              In Standby, applied=No and deleted=Yes

              Standby:
              SEQUENCE# APPLIED
              ---------- ---------
              1186 NO
              1187 NO
              1188 NO
              Can you show what is the output from primary
              SQL> show parameter log_archive_dest_2

              Are you sure MRP is started on standby?

              Can you post output from primary using below script?
              http://www.oracle-ckpt.com/script-to-monitor-primary-and-standby-databases/
              • 4. Re: Applied Status is "NO" in v$archived_log in 11.2.0.2 Data Guard
                Hemant K Chitale
                Do you actually have Recovery (RECOVER MANAGED STANDBY DATABASE) running on the Standby ?


                Hemant K Chitale
                • 5. Re: Applied Status is "NO" in v$archived_log in 11.2.0.2 Data Guard
                  Arun Natarajan-OC
                  Hi Friends,

                  Today when I re-query the database the status changed from NO to YES on both Primary and Standby(Mount and enable Redo Apply). Not sure how.
                  Yesterday both the databases were down and i started both the databases and immeditely i take the query output and i saw NO
                  but now it today it becomes YES. It take some time to start some of the process?



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


                  Primary:
                  -------------

                  1175 YES
                  1175 NO
                  1176 NO
                  1176 YES
                  1177 YES
                  1177 NO
                  1178 YES
                  1178 NO

                  SEQUENCE# APPLIED
                  ---------- ---------
                  1179 YES
                  1179 NO
                  1180 NO
                  1180 YES
                  1181 NO
                  1181 YES
                  1182 YES
                  1182 NO
                  1183 NO
                  1183 YES
                  1184 NO

                  SEQUENCE# APPLIED
                  ---------- ---------
                  1184 YES
                  1185 YES
                  1185 NO
                  1186 YES
                  1186 NO
                  1187 NO
                  1187 YES
                  1188 NO
                  1188 YES
                  1189 NO
                  1189 YES

                  SEQUENCE# APPLIED
                  ---------- ---------
                  1190 YES
                  1190 NO
                  1191 NO
                  1191 YES
                  1192 NO
                  1192 NO

                  710 rows selected.



                  Standby:
                  -----------------


                  1177 YES
                  1178 YES
                  1179 YES
                  1180 YES
                  1181 YES
                  1182 YES
                  1183 YES
                  1184 YES
                  1185 YES

                  SEQUENCE# APPLIED
                  ---------- ---------
                  1186 YES
                  1187 YES
                  1188 YES
                  1189 YES
                  1190 YES
                  1191 YES
                  1192 IN-MEMORY

                  Regards,
                  DB
                  • 6. Re: Applied Status is "NO" in v$archived_log in 11.2.0.2 Data Guard
                    mseberg
                    Hello;
                    Yesterday both the databases were down and i started both the databases and immeditely i take the query output and i saw NO
                    but now it today it becomes YES. It take some time to start some of the process?
                    No is the short answer. However if your apply is many logs behind it may take awhile to catch up.

                    In any event you do not have an issue at this point so please consider closing this question.

                    Best Regards

                    mseberg
                    • 7. Re: Applied Status is "NO" in v$archived_log in 11.2.0.2 Data Guard
                      CKPT
                      839396 wrote:
                      Hi Friends,

                      Today when I re-query the database the status changed from NO to YES on both Primary and Standby(Mount and enable Redo Apply). Not sure how.
                      Yesterday both the databases were down and i started both the databases and immeditely i take the query output and i saw NO
                      but now it today it becomes YES. It take some time to start some of the process?



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


                      Primary:
                      -------------

                      1175 YES
                      1175 NO
                      1176 NO
                      1176 YES
                      1177 YES
                      1177 NO
                      1178 YES
                      1178 NO

                      SEQUENCE# APPLIED
                      ---------- ---------
                      1179 YES
                      1179 NO
                      1180 NO
                      1180 YES
                      1181 NO
                      1181 YES
                      1182 YES
                      1182 NO
                      1183 NO
                      1183 YES
                      1184 NO

                      SEQUENCE# APPLIED
                      ---------- ---------
                      1184 YES
                      1185 YES
                      1185 NO
                      1186 YES
                      1186 NO
                      1187 NO
                      1187 YES
                      1188 NO
                      1188 YES
                      1189 NO
                      1189 YES

                      SEQUENCE# APPLIED
                      ---------- ---------
                      1190 YES
                      1190 NO
                      1191 NO
                      1191 YES
                      1192 NO
                      1192 NO

                      710 rows selected.



                      Standby:
                      -----------------


                      1177 YES
                      1178 YES
                      1179 YES
                      1180 YES
                      1181 YES
                      1182 YES
                      1183 YES
                      1184 YES
                      1185 YES

                      SEQUENCE# APPLIED
                      ---------- ---------
                      1186 YES
                      1187 YES
                      1188 YES
                      1189 YES
                      1190 YES
                      1191 YES
                      1192 IN-MEMORY

                      Regards,
                      DB
                      So probably there are some issues in MRP apply process, Can you check alert log file when the MRP started and stopped last few times? also any errors in alert log?

                      Or you can do test as

                      1) stop sending redo from primary "log_archive_dest_state_2='defer';
                      now perform couple/more of log switches.
                      2) stop MRP
                      3) start sending redo from primary "log_archive_dest_state_2='enable';
                      4) Start MRP

                      So that you can see whether it is frequently applying or there is any issues in applying?