Oracle DBA – A lifelong learning experience

Managing dataguard – monitoring scripts

Posted by John Hallas on July 16, 2009

I am providing three scripts which I find useful in ensuring that the standby database is keeping up with the primary database in Dataguard setup.

@last

LOGS             TIME
---------------- ------------------
Last applied   :  16-JUL-09:09:24:16
Last received :  16-JUL-09:09:28:36
    select 'Last applied  : ' Logs, to_char(next_time,'DD-MON-YY:HH24:MI:SS') Time
    from v$archived_log
    where sequence# = (select max(sequence#) from v$archived_log where applied='YES')
    union
    select 'Last received : ' Logs, to_char(next_time,'DD-MON-YY:HH24:MI:SS') Time
    from v$archived_log
   where sequence# = (select max(sequence#) from v$archived_log);

@dg_stats

NAME                   VALUE                  UNIT
---------------------- ---------------------- -----------------------------------
apply finish time      +00 00:02:07.2         day(2) to second(1) interval
apply lag              +00 00:01:59           day(2) to second(0) interval
estimated startup time 16                     second
standby has been open  N
transport lag          +00 00:00:00           day(2) to second(0) interval

                                              Time Computed: 16-JUL-2009 09:33:16

   

select
    NAME Name,
    VALUE Value,
    UNIT Unit
    from v$dataguard_stats
    union
    select null,null,' ' from dual
    union
    select null,null,'Time Computed: '||MIN(TIME_COMPUTED)
   from v$dataguard_stats;

A final option is to use v$standby_log

@last_redo

Redo onsite
--------------------
16-JUL-2009 09:42:44

   

select to_char(max(last_time),'DD-MON-YYYY HH24:MI:SS') "Redo onsite"
     from v$standby_log

None of them that clever, but all of them are useful.  Thanks to colleague Simon Ryan for pulling them together.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: