7 Replies Latest reply: Nov 16, 2011 3:26 AM by 900494 RSS

    V$STANDBY_EVENT_HISTOGRAM empty on standby

    user6387903
      Hello,
      the 11gR2 manual says something like this about output of queries against this view:
      SELECT * FROM V$STANDBY_EVENT_HISTOGRAM WHERE NAME = 'apply lag'
      AND COUNT > 0;

      NAME TIME UNIT COUNT LAST_TIME_UPDATED
      --------- --------- -------- ----------- ------------------------
      apply lag 0 seconds 79681 06/18/2009 10:05:00
      apply lag 1 seconds 1006 06/18/2009 10:03:56
      apply lag 2 seconds 96 06/18/2009 09:51:06
      apply lag 3 seconds 4 06/18/2009 04:12:32
      apply lag 4 seconds 1 06/17/2009 11:43:51
      apply lag 5 seconds 1 06/17/2009 11:43:52

      Now on a Linux with 11.2.0.2 where I have physical standby in place and with managed recovery in place (no log gap), I get this:

      SELECT name, value, datum_time, time_computed FROM V$DATAGUARD_STATS
      2 WHERE name like 'apply lag';

      NAME                    VALUE
      -------------------------------- --------------------
      DATUM_TIME          TIME_COMPUTED
      ------------------------------ ------------------------------
      apply lag               +00 00:06:20
      01/17/2011 11:59:55     01/17/2011 11:59:55

      But a query on the view in subject gives no rows....
      SELECT * FROM V$STANDBY_EVENT_HISTOGRAM WHERE NAME = 'apply lag'
      AND COUNT > 0;

      no rows selected

      SQL> select count(*) from V$STANDBY_EVENT_HISTOGRAM;

      COUNT(*)
      ----------
           0

      Any reason for this? Anythging to enable to get it populated?

      Thanks,
      Gianluca
        • 1. Re: V$STANDBY_EVENT_HISTOGRAM empty on standby
          mseberg
          These are two different things that cannot be compared in a one to one.

          V$DATAGUARD_STATS displays how much redo data generated by the primary database is not yet available on the standby database.

          V$STANDBY_EVENT_HISTOGRAM displays the histogram of apply lag on the physical standby.


          As expected when I run these queries against my 11.2 database on Linux I get the same results as you.

          V$DATAGUARD_STATS is used more for apply lag and transport lag information.

          V$STANDBY_EVENT_HISTOGRAM shows apply lag values (do you have a lag value set?)
          • 2. Re: V$STANDBY_EVENT_HISTOGRAM empty on standby
            user6387903
            V$STANDBY_EVENT_HISTOGRAM shows apply lag values (do you have a lag value set?)
            Do you mean if I have set a time lag between the archiving of an online redo log file at the primary site and the application of it at the standby site, to prevent application errors to be propagated for a predefined amount of time?
            No, I have not set any time lag. I have log_archive_dest_2 configured this way on primary:
            service=TESTDR ASYNC DB_UNIQUE_NAME=TESTDR VALID_FOR=(primary_role, online_logfile) COMPRESSION=ENABLE

            Thanks,
            Gianluca
            • 3. Re: V$STANDBY_EVENT_HISTOGRAM empty on standby
              user6387903
              The manual says:
              V$STANDBY_EVENT_HISTOGRAM displays the histogram of apply lag on the physical standby. Each distinct value of apply lag has its own bucket and the count in the corresponding bucket represents the number of occurrences so far. The physical standby samples the apply lag every second and increments the corresponding bucket in the histogram.

              So I understood that if I had two occurrences of redo apply, one with a apply lag of 10 seconds and the other one with apply lag of 20 seconds, I would got something like this from the view:

              NAME TIME UNIT COUNT LAST_TIME_UPDATED
              --------- --------- -------- ----------- ------------------------
              apply lag 10 seconds 1 06/18/2009 10:05:00
              apply lag 20 seconds 1 06/18/2009 10:03:56

              or not?
              • 4. Re: V$STANDBY_EVENT_HISTOGRAM empty on standby
                mseberg
                You are correct.

                If I look for a usage example I find:

                "To obtain a histogram that shows the history of apply lag values since the standby instance was last started."

                SQL> SELECT * FROM V$STANDBY_EVENT_HISTOGRAM WHERE NAME = 'apply lag' -
                AND COUNT > 0;
                NAME TIME UNIT COUNT LAST_TIME_UPDATED
                --------- --------- -------- ----------- ------------------------
                apply lag 0 seconds 79681 06/18/2009 10:05:00
                apply lag 1 seconds 1006 06/18/2009 10:03:56
                apply lag 2 seconds 96 06/18/2009 09:51:06
                apply lag 3 seconds 4 06/18/2009 04:12:32
                apply lag 4 seconds 1 06/17/2009 11:43:51
                apply lag 5 seconds 1 06/17/2009 11:43:52

                So the two queries don't measure the same thing, but it was closer than I thought.

                Good job chasing this!
                • 5. Re: V$STANDBY_EVENT_HISTOGRAM empty on standby
                  user6387903
                  In fact I took the first two lines of output from the manual and adapted them to my example.... ;-)
                  If I find anything I will post here...
                  • 6. Re: V$STANDBY_EVENT_HISTOGRAM empty on standby
                    mseberg
                    V$STANDBY_EVENT_HISTOGRAM displays the histogram of apply lag on the physical standby.


                    To obtain a histogram that shows the history of apply lag values since the standby instance was last started.


                    This view returns rows only on a physical standby database that has been open in real-time query mode.
                    SELECT 
                      * 
                    FROM 
                      V$STANDBY_EVENT_HISTOGRAM 
                    WHERE 
                      NAME = 'apply lag' 
                    AND 
                      COUNT > 0 
                    ORDER BY 
                      last_time_updated; 
                    The information in this view does not persist across an instance shutdown.

                    Best Regards

                    mseberg
                    • 7. Re: V$STANDBY_EVENT_HISTOGRAM empty on standby
                      900494
                      I ran into this view being empty as well, and Google turned up this thread. So, to help future searchers:

                      The answer to why its empty (at least for me) is in the Data Guard Concepts and Administration, in Table 17-1: +"This view returns rows only on a physical standby database that has been open in real-time query mode."+ So, if you're not using the Active Data Guard option, it'll be empty.