Dataguard log apply rate script

This script is a great way to find the transfer rate of the log apply, this is useful in a number of ways.
With a dataguard physical standby each time you start a managed recovery process there will be this series of 9 rows entered into the view. Each row has a type, but while recovering the standby it is always Media Recovery.

The average apply rate includes time waiting for the redo to arrive. The active time spent applying redo log information is a small proportion of the total elapsed time since starting managed recovery.

The active apply rate therefore gives a better indication of how fast you can actually apply redo on your standby, if you think you are generating redo at a faster rate than this number, then you may well be falling behind on your standby.

As indicated in the documentation this V$RECOVERY_PROGRESS view is actually just a subset of the V$SESSION_LONGOPS view, and while all the information is available there too, the V$RECOVERY_PROGRESS view summarizes the relevant data for your media recovery progress in a standby quite nicely.

I made this in the form of a function to add to a common library

#!/bin/ksh

dg_rate_apply()
{

sqlplus -s "/ as sysdba" <<EOF

set linesize 400
col Values for a65
col Recover_start for a21

select to_char(START_TIME,'dd.mm.yyyy hh24:mi:ss') "Recover_start",
to_char(item)||' = '||to_char(sofar)||' '||to_char(units)||' '|| to_char(TIMESTAMP,'dd.mm.yyyy hh24:mi') "Values"
 from v\$recovery_progress 
where start_time=(select max(start_time) 
                    from v\$recovery_progress);

EOF
}

Leave a Reply

Your email address will not be published. Required fields are marked *