Emre Baransel - Oracle Blog

You may want to calculate the apply rate of your standby database if you need performance tuning on your dataguard environment. Also if you are “Using a Physical Standby Database for Read/Write Testing and Reporting” in 10g or Snapshot Standby in 11g, you may need this value to calculate how much time does your standby database needs to re-syncronize in a point of time.

There are some ways of getting a value as log apply rate. For example, the following query shows the active and average apply rate.

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);

Recover_start Values
--------------------- -----------------------------------------------------------------
22.04.2010 09:02:38 Log Files = 83 Files
22.04.2010 09:02:38 Active Apply Rate = 8448 KB/sec
22.04.2010 09:02:38 Average Apply Rate = 3642 KB/sec
22.04.2010 09:02:38 Redo Applied = 85288 Megabytes
22.04.2010 09:02:38 Last Applied Redo = 2147483647 SCN+Time 21.04.2010 11:08
22.04.2010 09:02:38 Active Time = 23931 Seconds
22.04.2010 09:02:38 Apply Time per Log = 270 Seconds
22.04.2010 09:02:38 Checkpoint Time per Log = 16 Seconds
22.04.2010 09:02:38 Elapsed Time = 23974 Seconds

You should now that these values have some weakness in calculation and may not reflect your actual apply rate. “Active Apply Rate” is very momentary and changeable, where “Average Apply Rate” includes the time that MRP process waits for the archive log to arrive, into calculation.

Another and more accurate way may be collecting the archivelogs without applying (i mean stop recovery and keep standby mounted) and then starting recovery, monitoring alertlog and doing some calculations. For example 6 archivelogs were applied in 5 minutes and your log file size is 512mb. So apply rate is 10.2mbps. This value is more reliable but it may be time consuming to perform this process whenever you want to measure the speed.

Now let’s see what Oracle says in “Oracle Database 10g Best Practices: Data Guard Redo Apply and Media Recovery” paper:

By this way you will have different results in each test you perform. But this doesn’t mean that the method is wrong. The value depends on what kind of operation is your standby database performing (while applying the archivelog) and an average of multiple test results will be accurate enough. I prepared a shell script to perform this test several times and calculate the average. It takes two snapshots of the MRP0 process by waiting 10 seconds. If the snapshots are suitable to calculate the recovery rate (block number on second snapshot is bigger then the one on first snapshot and not equal to zero) it saves the recovery rate value. Then outputs the average of last 50 calculation. Here it is:

#!/usr/bin/sh

############# VARIABLES

#######################

export ORACLE_HOME=/…….

export ORACLE_SID=XXXX

export HOME=/home/oracle

export WORK_PATH=$HOME/DB_SCRIPTS/

export LBS=512

############# DEFINE

###################

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

export PATH=$PATH:$ORACLE_HOME/bin

export TNS_ADMIN=$ORACLE_HOME/network/admin

############## MRP SNAP BY 10 seconds

###################################

$ORACLE_HOME/bin/sqlplus -s '/ as sysdba' << EOF > $WORK_PATH/snap.log

set linesize 300;

SELECT PROCESS, SEQUENCE#, THREAD#, block#, BLOCKS, TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS')

time from v\$MANAGED_STANDBY WHERE PROCESS='MRP0';

EOF

snap1=`cat $WORK_PATH/snap.log |tail -2 |head -1 |awk '{print $4}'`

echo $snap1

sleep 10

$ORACLE_HOME/bin/sqlplus -s '/ as sysdba' << EOF > $WORK_PATH/snap.log

set linesize 300;

SELECT PROCESS, SEQUENCE#, THREAD#, block#, BLOCKS, TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS')

time from v\$MANAGED_STANDBY WHERE PROCESS='MRP0';

EOF

snap2=`cat $WORK_PATH/snap.log |tail -2 |head -1 |awk '{print $4}'`

echo $snap2

############## CALCULATE THE MOMENTARY RATE

###########################################

if [[ "$snap1" != "0" && "$snap2" != "0" && "$snap1" -lt "$snap2" ]]

then

echo "ok"

mrpspeed=$(( `expr "$snap2" - "$snap1"` * $LBS ))

echo 'scale=4;'$mrpspeed'/10485760' | bc >> $WORK_PATH/mrpspeedlog

else

echo "not ok"

exit

fi

############# KEEP LAST 50 VALUE

###############################

tail -50 $WORK_PATH/mrpspeedlog > $WORK_PATH/temporary.log

mv $WORK_PATH/temporary.log $WORK_PATH/mrpspeedlog

############# CALCULATE THE AVERAGE

###################################

n=0

sum=0

while read x

do

sum=`echo "scale=4;$sum+$x" |bc`

    if [ "$?" -eq "0" ]; then

    n=`expr $n + 1`

    fi

done < $WORK_PATH/mrpspeedlog

echo "scale=2;$sum/$n" | bc > $WORK_PATH/averagespeed.txt

-->

-->