You know what the funny thing about DataGuard is? It's not actually that complicated. Until it is. And when sync lag starts climbing, you suddenly realize you've been treating it like this magical thing that just works, rather than something you actually need to understand.
I had this conversation with a junior DBA last month. She said, "I looked at sync lag, and it was at 50 minutes, but the standby seemed fine, the logs looked fine, everything looked fine." I asked her one question: "Did you check if the standby was actually applying redo?" Turns out, it wasn't. MRP had silently stopped applying, and nobody had noticed.
That's the thing about replication lag. It's not usually catastrophic. It's usually boring. And boring things are easy to miss until suddenly they're not.
The Basics: What We're Actually Measuring
Let me skip the long explanation and just show you what's happening:
We usually measure this in log sequence numbers. If the primary just finished log 12400 and the standby has only applied through log 12360, that's 40 logs of lag. If each log is 500MB, that's 20GB of unapplied redo waiting in the standby's redo log.
Under normal circumstances, this gap should be small. Seconds, maybe a minute. If your primary is generating 1GB of redo per minute and your network can push 5GB per minute, the standby is going to fall behind for a bit when traffic is heavy, then catch up. That's normal. That's life.
But if the gap keeps growing and never shrinks, something is actually broken. And broken is what we're going to talk about.
The Reality Check: Where to Actually Look
Here's what I've learned the hard way: don't start with V views. Start with the alert log. The alert log is where the database tells you what's actually happening.
tail -f $ORACLE_HOME/diag/rdbms/proddb/PRODDB_STBY/trace/alert_PRODDB_STBY.log
Watch this for a few minutes. You'll see lines like:
RFS[2]: Completed archivelog file transfer to standby (size=52M bytes) Redo Apply progress to seqno: 12350 (15 MB/s) RFS[3]: Error opening standby log: ORA-15028 ASM file name not found
That RFS error? That's your problem. RFS is the process that copies redo from primary to standby. If it's complaining, nothing is getting transferred. MRP can't apply redo it doesn't have.
Or sometimes you'll see:
MRP0: WARNING: Possible network disconnect detected (ospid=5678)
That tells you the network between primary and standby is flaky. Maybe losing packets, maybe latency spikes, maybe the connection just dropped.
The point is: the alert log is telling you something. You just have to listen.
When the Alert Log Says Nothing: Time to Query
Sometimes the alert log is clean as a whistle. No errors, no warnings. And lag is still climbing. That's when you actually need to do some detective work.
These queries tell you exactly what's going on:
-- Run this on STANDBY -- What redo has been applied? SQL> SELECT THREAD#, MAX(SEQUENCE#) FROM V$LOG_HISTORY GROUP BY THREAD#; -- Run this on PRIMARY -- What redo exists? SQL> SELECT THREAD#, MAX(SEQUENCE#) FROM V$LOG WHERE ARCHIVED='NO' GROUP BY THREAD#; -- The difference is your lag in log files
Now run this on the standby:
-- Is MRP running and actually applying?
SQL> SELECT PROCESS, PID, SEQUENCE#, BLOCK#, STATUS
FROM V$MANAGED_STANDBY_PROCESS;
-- You want to see something like:
-- PROCESS PID SEQUENCE# BLOCK# STATUS
-- RFS 12345 12361 100 CONNECTED
-- MRP0 12346 12360 1 APPLYING_LOG
-- ARCH 12347 12359 1 CONNECTED
-- If MRP0's SEQUENCE# doesn't change over 5 minutes, it's stuck
That SEQUENCE# column on MRP0 is key. Watch it. If it's not moving, MRP is stuck. If it's moving slowly, the standby is struggling to keep up.
Three Scenarios and What They Mean
Here's the thing about replication lag: it's never random. It's always one of three things. Knowing which one matters.
Scenario One: RFS is receiving, but MRP isn't applying.
This means redo is getting to the standby, but something is preventing it from being applied. Usually it's one of these: MRP crashed and nobody noticed, the standby ran out of disk space so it can't write the redo, or there's a block corruption that's making MRP choke.
Fix: Check if MRP is actually running. If it crashed, restart it. If it's a corruption, you might need to do a resync. If it's disk space, free some up and let MRP catch up.
Scenario Two: MRP is applying, but very slowly.
This usually means either the primary is generating redo so fast that the standby can't keep up, or the standby is under heavy I/O load and can't apply logs quickly.
Check the primary. Is there a huge batch job running? A datapump export? An index rebuild that's generating a ton of redo? Check the standby. Is it CPU-bound or I/O-bound? If it's I/O-bound, the apply process is waiting for disk writes. If it's CPU-bound, MRP itself is just slow.
Scenario Three: Network is the bottleneck.
This is rare in my experience, but it happens. The redo is being generated, RFS is trying to send it, but the network can't keep up. You see high latency between primary and standby, or packet loss.
Check with: ping -c 100 standby_host from the primary. Look for packet loss or high variance in latency. If the network is the problem, talk to your network team. There's not much a DBA can do about WAN latency.
The Real Story: What Usually Happens
I want to tell you something that might sound obvious but isn't: sync lag is rarely actually broken. It's usually just paused.
Like last week. One of our standby databases had lag of about 2 hours. The ops team was freaking out. I looked at the standby and MRP was applying logs at 20MB/sec, which is totally normal. RFS was receiving at the same rate. Everything was working. The reason there was 2 hours of lag is because the primary had been in backup mode for 2 hours, which suspends redo transport. Once backup finished, the standby caught up in about 20 minutes.
Nobody was broken. Everything was working exactly as designed. We just didn't understand what we were looking at.
That's why I always start with the alert log and the simple V views. Because half the time, there's no actual problem. There's just an explanation for why lag is where it is.
The Root Cause Decision Tree (For When It's Actually Broken)
But sometimes it is actually broken. When that happens, this is how I think about it:
If RFS isn't receiving: network problem or primary stopped sending.
If RFS is receiving but MRP isn't applying: standby problem or corruption.
If MRP is applying but lag keeps growing: primary is too fast or standby is too slow.
Each branch has a different fix. And knowing which branch you're on is 80% of the work.
One Last Thing: Monitor Before You Panic
The best defense against sync lag problems is seeing them coming before they're a problem. A simple query scheduled every minute:
SELECT
TRUNC(SYSDATE, 'MI') as check_time,
(SELECT MAX(SEQUENCE#) FROM V$LOG WHERE STATUS='CURRENT')
- (SELECT MAX(SEQUENCE#) FROM V$MANAGED_STANDBY_PROCESS WHERE PROCESS='MRP0')
AS lag_sequences,
(SELECT VALUE FROM V$PARAMETER WHERE NAME='log_archive_dest_2') as dest_status
FROM DUAL;
Store the results in a table. Plot them. Alert when lag starts growing. Most of the time, it'll show you exactly when something changed on the primary. A backup started. A batch job kicked off. Or it'll show you a real problem before your SLA gets breached.
That's the actual secret. Monitoring doesn't prevent problems, but it makes them obvious way earlier. And "obvious at 5 minutes" is better than "obvious at 2 hours."
Bottom Line
DataGuard sync lag isn't complicated. It's just easy to misunderstand because the database doesn't always scream when something's wrong. It whispers in the alert log. It sits quietly while MRP slowly processes redo. It looks fine until suddenly it's not.
The fix is simple: learn to read the alert log. Run a few queries. Understand what RFS and MRP actually do. Then when lag spikes, you'll spend 5 minutes figuring out what's happening instead of 2 hours panicking.
Your disaster recovery setup is only as good as your ability to understand it. So take some time to actually understand it.
Keep learning... Have a great day!!!
Thank you,
Amit Pawar
Email: amitpawar.dba@gmail.com
WhatsApp No: +91-8454841011
