Data Guard Lag and a Truncated 300GB Table
A primary database and a physical standby use Data Guard in maximum performance mode with standby redo logs and real-time apply. RPO is 5 minutes and RTO is 30 minutes. During a storage expansion, the standby stops applying and reports 'Media Recovery Waiting for thread 1 sequence 4421' for 90 minutes. The primary archive destination is 92% full. At 10:05 a batch job truncates a 300GB table; at 11:30 the application team notices. The standby is still 90 minutes behind, and the last archived sequence on the primary is 4462. The DBA must choose between failing over, catching up, or restoring.
1. How do you diagnose and resolve the standby gap, and should you fail over?
Read the worked answer
First, confirm the gap. On the standby, query V$ARCHIVE_GAP and V$MANAGED_STANDBY; on the primary, query V$ARCHIVED_LOG and V$ARCHIVE_DEST_STATUS for the standby destination. The message 'Media Recovery Waiting for thread 1 sequence 4421' means the standby has applied through 4420 and is missing 4421 onward.
Resolve without failover: copy the missing archive logs from the primary to the standby, then register them with ALTER DATABASE REGISTER LOGFILE and let managed recovery continue. If the logs were deleted, restore them from backup or use the Data Guard broker to reinstate. Address the primary archive destination at 92% by increasing DB_RECOVERY_FILE_DEST_SIZE or deleting expired/backed-up logs according to the retention policy.
Do not fail over. The standby is 90 minutes behind, which would lose 85 minutes more than the 5-minute RPO. Failover is only appropriate if the primary is lost or the gap cannot be resolved within the RTO. Catching up is the correct path here; failover would turn a recoverable lag into unacceptable data loss. After catch-up, verify that the standby is applying in real time and that the gap is closed.
2. The table was truncated at 10:05. How do you recover it without restoring the entire database, and what are the caveats?
Read the worked answer
TRUNCATE is DDL and does not go to the recycle bin, so FLASHBACK TABLE and FLASHBACK DROP are not applicable. Two practical options remain.
Flashback Database to a point before 10:05 is fast if flashback logs are enabled and the required restore point exists, but it rewinds the entire database and would lose all other changes after 10:05. That is usually unacceptable in an OLTP system.
Tablespace point-in-time recovery (TSPITR) is the targeted option. Use RMAN to recover the tablespace containing the table to a time just before 10:05, using an auxiliary instance. First run DBMS_TTS.TRANSPORT_SET_CHECK to identify dependencies, then perform TSPITR and export/import or use the recovered tablespace to restore the table. The caveats are significant: other objects in the same tablespace are also rewound, dependent objects such as indexes and constraints must be handled, and the auxiliary instance needs disk space and a compatible Oracle home.
Validate row counts and referential integrity after recovery. If TSPITR is not feasible in the RTO, restore the table from a recent export or a backup of the tablespace and accept the data loss determined by the backup interval.
3. What changes would you make to prevent this lag from becoming an RPO breach next time?
Read the worked answer
The incident exposed three gaps: archive destination capacity, lag monitoring, and recovery testing.
Capacity and retention: the primary archive destination was at 92%, which is too close to failure. Set a deletion policy that removes archived logs only after they are backed up and shipped to the standby. Monitor V$RECOVERY_FILE_DEST and alert at 70% and 85%. Increase the destination size if the backup window and log volume require it.
Lag monitoring: alert on V$DATAGUARD_STATS apply lag and transport lag, not just on database availability. A 90-minute lag should page someone well before it reaches the RPO. Use the Data Guard broker for configuration and fast-start failover, and verify that the observer is correctly placed.
Testing: periodically simulate a log gap and practice registering missing logs. Test TSPITR and table-level recovery in a non-production environment so the team knows the actual RTO. A backup that has never been restored is only a hypothesis; the same is true of a standby that has never been failed over or caught up under pressure.