Oracle Standby Recovery Rate Monitoring

Why standby periodically lags during the day?
The script reports the time it took to apply the log, the size of the log, and the redo apply rate for that log.

Diff(sec) reports the actual time difference between redo logs applied on the standby.
Lag(sec) reports the time difference between when the archive log was completed on the primary and when it was applied on the standby.

If  you come across issues with script below please download it from here

rem Reports standby apply rate with lag

SELECT   TIMESTAMP,
completion_time “ArchTime”,
SEQUENCE#,
ROUND ( (blocks * block_size) / (1024 * 1024), 1) “SizeM”,
ROUND (
(TIMESTAMP
– LAG (TIMESTAMP, 1, TIMESTAMP) OVER (ORDER BY TIMESTAMP))
* 24
* 60
* 60,
1
)
“Diff(sec)”,
ROUND (
(blocks * block_size) / 1024
/ DECODE (
( (TIMESTAMP
– LAG (TIMESTAMP, 1, TIMESTAMP) OVER (ORDER BY TIMESTAMP))
* 24
* 60
* 60),
0,
1,
(TIMESTAMP
– LAG (TIMESTAMP, 1, TIMESTAMP) OVER (ORDER BY TIMESTAMP))
* 24
* 60
* 60
),
1
)
“KB/sec”,
ROUND (
(blocks * block_size) / (1024 * 1024)
/ DECODE (
( (TIMESTAMP
– LAG (TIMESTAMP, 1, TIMESTAMP) OVER (ORDER BY TIMESTAMP))
* 24
* 60
* 60),
0,
1,
(TIMESTAMP
– LAG (TIMESTAMP, 1, TIMESTAMP) OVER (ORDER BY TIMESTAMP))
* 24
* 60
* 60
),
3
)
“MB/sec”,
ROUND (
( (LEAD (TIMESTAMP, 1, TIMESTAMP) OVER (ORDER BY TIMESTAMP))
– completion_time)
* 24
* 60
* 60,
1
)
“Lag(sec)”
FROM   v$archived_log a, v$dataguard_status dgs
WHERE   a.name = REPLACE (dgs.MESSAGE, ‘Media Recovery Log’)
AND dgs.FACILITY = ‘Log Apply Services’
ORDER BY   TIMESTAMP DESC;

Another quickie SQL

@Standby> select snaphot_time,thread#,sequence#,applied_scn from v$standby_apply_snapshot;

If your log application is running slow please alter parallel_execution_message_size parameter to increase the buffer size.

From 10G DataGuard:  Set PARALLEL_EXECUTION_MESSAGE_SIZE = 4096

When using parallel media recovery or parallel standby recovery, increasing the PARALLEL_EXECUTION_MESSAGE_SIZE database parameter to 4K (4096) can improve parallel recovery by as much as 20 percent. Set this parameter on
both the primary and standby databases in preparation for switchover operations. Increasing this parameter requires more memory from the shared pool by each parallel execution slave process.

The PARALLEL_EXECUTION_MESSAGE_SIZE parameter is also used by parallel query operations and should be tested with any parallel query operations to ensure there is sufficient memory on the system. A large number of parallel query
slaves on a 32-bit installation may reach memory limits and prohibit increasing the PARALLEL_EXECUTION_MESSAGE_SIZE from the default 2K