Oracle Data Guard Interview Questions: 20 Real Scenarios With Commands

Published Sep 2026 · 10 min read

Data Guard questions are where an Oracle DBA interview stops being about definitions. Anyone can recite "physical versus logical standby". What a panel wants to hear is what you do when the standby is two hours behind at 9am, when the archive logs it needs are already gone, or when the broker reports an error you have never seen. These are twenty of those questions, grouped the way an interview usually escalates: lag and gaps first, then errors, then the switchover and failover decisions, then daily operations. Each answer gives the short version to lead with, the detail that shows you have done it, and the command you would actually run. Commands were verified against Oracle 19c and 26ai with the Data Guard broker (DGMGRL).

Lag, gaps and the standby falling behind

1. The standby is two hours behind. How do you tell transport lag from apply lag?

They are different problems with different fixes. Transport lag means redo has not arrived — a network, listening or password problem. Apply lag means redo has arrived but has not been applied — a recovery-process or I/O problem on the standby. Say that distinction first; it is what the question is testing. Then show where you read it.

-- on the standby
SELECT name, value, time_computed
FROM   v$dataguard_stats
WHERE  name IN ('transport lag', 'apply lag');

-- or from the broker
DGMGRL> SHOW DATABASE VERBOSE stby;
--   Transport Lag:  0 seconds
--   Apply Lag:      2 hours 3 minutes

2. V$ARCHIVE_GAP shows missing sequences and the primary has already deleted them. What now?

You cannot ship what no longer exists, and you should not rebuild the standby for a gap. Since 12c the standby can roll itself forward directly from the primary over the network with a single RMAN command, which fetches the missing changes from the live datafiles rather than from archive logs. Stop apply, roll forward, restart apply.

DGMGRL> EDIT DATABASE stby SET STATE='APPLY-OFF';

RMAN> CONNECT TARGET sys@stby
RMAN> RECOVER STANDBY DATABASE FROM SERVICE prim_tns;

DGMGRL> EDIT DATABASE stby SET STATE='APPLY-ON';
DGMGRL> SHOW DATABASE stby;

3. Redo is arriving but apply lag keeps growing. How do you diagnose a slow managed recovery?

First confirm the apply process is actually working rather than waiting: V$DATAGUARD_PROCESS shows what MRP0 and the recovery slaves are doing. Then check the usual throttles — standby redo logs missing or undersized (which silently disables real-time apply), slow standby storage, a single huge transaction, or recovery running with too little parallelism. Real-time apply is only possible when standby redo logs exist.

SELECT name, role, action, client_role FROM v$dataguard_process;
SELECT group#, thread#, bytes/1024/1024 mb, status FROM v$standby_log;
SELECT name, value, unit FROM v$recovery_progress
WHERE  name IN ('Active Apply Rate', 'Average Apply Rate', 'Redo Applied');

4. How many standby redo logs should a standby have, and why does the number matter?

At least one more group per thread than the primary has online redo groups, each the same size as the online logs. Too few, or the wrong size, and the standby drops out of real-time apply and waits for whole archive logs — which is the most common reason a "healthy" standby still shows minutes of apply lag. Mention that the primary needs them too, for the day it becomes the standby.

5. Which archive logs can you delete on the primary without breaking the standby?

Only the ones every standby has already applied — and you let RMAN enforce that instead of judging it by hand. The deletion policy makes the gap in question 2 impossible.

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
-- or, when backups must exist first:
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DISK APPLIED ON ALL STANDBY;

6. The standby's fast recovery area is full and apply has stopped. What do you do?

Free space safely, then fix the cause. On the standby the deletion policy should be APPLIED ON STANDBY, so RMAN reclaims applied logs automatically. If it is full now, delete applied archive logs explicitly and confirm apply resumes; then size the recovery area for the retention you actually need. Never delete unapplied logs — that turns a space problem into a gap.

Errors you will be asked to explain

7. ORA-16857: the standby has been disconnected from the redo source. What do you check?

This is the broker telling you transport has been down longer than its threshold. Work from the primary outward: is the destination enabled and error-free, is the standby listener up, does tnsping resolve from the primary host? The answer is almost always in V$ARCHIVE_DEST_STATUS on the primary.

-- on the primary
SELECT dest_id, status, error FROM v$archive_dest_status WHERE dest_id = 2;
SELECT dest_name, status, error FROM v$archive_dest WHERE dest_id = 2;

8. ORA-16191: "Primary log shipping client not logged on standby". What is it?

A password-file mismatch — the standby's SYS password file no longer matches the primary's, so redo transport cannot authenticate. Copy the primary's password file to the standby, restart transport, and the error clears. From 12.2 onward password-file changes propagate to physical standbys automatically once the files match; before that, every SYS password change had to be copied by hand, which is how the mismatch usually arose.

9. The broker reports ORA-16826: apply service state is inconsistent. What does it mean?

Somebody managed recovery outside the broker — started or stopped apply with SQL, or added a DELAY — so the broker's idea of the state no longer matches reality. The fix is to hand control back to the broker and let it reconcile: toggle apply off and on through DGMGRL, and stop using SQL for apply on a broker-managed configuration.

DGMGRL> EDIT DATABASE stby SET STATE='APPLY-OFF';
DGMGRL> EDIT DATABASE stby SET STATE='APPLY-ON';
DGMGRL> VALIDATE DATABASE stby;

10. STANDBY_FILE_MANAGEMENT was MANUAL and someone added a datafile on the primary. Apply stopped. How do you recover?

Apply stops because the standby cannot create the new file itself. Create the datafile on the standby by hand, restart apply, then set STANDBY_FILE_MANAGEMENT=AUTO so it never happens again. The lesson to state is that AUTO is not optional on a production standby.

11. The broker will not start and DGMGRL cannot find the configuration. Where do you look?

Check DG_BROKER_START=TRUE on both databases, that the two DG_BROKER_CONFIG_FILE parameters point at a writable location — shared storage on RAC — and then read the broker log. On RAC, config files on local disk are the classic cause: the DMON process on the other node cannot see them.

SHOW PARAMETER dg_broker;
-- broker log lives next to the alert log
ls -lt $ORACLE_BASE/diag/rdbms/<db>/<inst>/trace/drc*.log | head -3

Protection modes, switchover and failover

12. In Maximum Availability, the standby goes down. Does the primary hang?

No — and this is the point of the mode. Maximum Availability requires synchronous redo while a standby is available, but if none is, the primary keeps running and the protection level temporarily drops, catching up when the standby returns. Maximum Protection is the mode that halts the primary rather than continue unprotected. Being precise about that difference is the whole question.

13. What do you check before a planned switchover?

Let the broker check it for you, and read every line. VALIDATE DATABASE confirms there is no gap, that redo transport works in both directions, that standby redo logs exist on both sides, and that temp files and flashback are in order. Only then switch over.

DGMGRL> VALIDATE DATABASE VERBOSE stby;
DGMGRL> SWITCHOVER TO stby;
DGMGRL> SHOW CONFIGURATION;

14. After a failover, how do you bring the old primary back as a standby?

Reinstate it, which is only possible if Flashback Database was enabled on it before the failure — the broker flashes it back to the point of divergence and converts it. Without flashback you rebuild it from a backup or from the new primary. Saying "reinstate, if flashback was on; otherwise rebuild" is the complete answer.

DGMGRL> FAILOVER TO stby;
-- once the old primary is mounted again
DGMGRL> REINSTATE DATABASE prim;

15. What has to be true for Fast-Start Failover to work?

An observer running somewhere that is neither the primary nor the standby site, the broker enabled, flashback on both databases so the failed primary can be reinstated, and a protection mode the observer can trust — Maximum Availability, or Maximum Performance with a lag limit. Interviewers listen for the observer placement: on the primary host it fails with the primary.

16. How do you run a test against the standby without breaking it?

Convert it to a snapshot standby. It opens read-write, keeps receiving redo without applying it, and converting back discards every change through flashback and resumes apply. This is the safe way to rehearse a release on production data.

DGMGRL> CONVERT DATABASE stby TO SNAPSHOT STANDBY;
-- test, then
DGMGRL> CONVERT DATABASE stby TO PHYSICAL STANDBY;

Operations and the questions that close the round

17. What is the difference between a physical standby and Active Data Guard?

A physical standby applies redo while mounted; Active Data Guard opens it read-only while redo keeps applying, so reporting can run there in real time. It is a separately licensed option, and you can see which one you are running in one query.

SELECT database_role, open_mode FROM v$database;
-- PHYSICAL STANDBY / MOUNTED           -> plain physical standby
-- PHYSICAL STANDBY / READ ONLY WITH APPLY -> Active Data Guard

18. How do you monitor Data Guard every morning without opening a console?

Three checks, scripted: SHOW CONFIGURATION for broker health, V$DATAGUARD_STATS for both lags, and V$ARCHIVE_GAP for gaps, with the standby alert log tailed for ORA-16 errors. A panel wants to hear that you check it before you are paged, not after.

19. What changes with Data Guard in Oracle AI Database 26ai?

The headline is protection at the pluggable-database level, so a single PDB can be protected or moved without treating the whole container as one unit — which suits consolidated estates far better than CDB-wide standby. The mechanics you already know — DGMGRL, lag views, the deletion policy — carry over unchanged, so the honest answer is "same skills, finer granularity".

20. The most important thing about a Data Guard setup?

That it has been switched over in anger, on a schedule, by someone other than the person who built it. A standby that has never been switched over is a hope, not a disaster-recovery plan. Say that you run switchovers regularly, that the runbook lives outside the database, and that the observer and its host are monitored too.

How to answer these well

Every strong answer above has the same shape: name which of the two lags or two modes you are dealing with, choose the least destructive fix that solves it, and say what you would change so it does not recur. Candidates who jump to "rebuild the standby" lose marks even when a rebuild would work, because it signals they cannot tell a gap from a lag.

The best preparation is to break a standby deliberately: delete an archive log the standby needs, change the SYS password without copying it, fill the recovery area. Fix each one and note where you hesitated — that hesitation is exactly what an interview finds.

FAQ

What is the difference between transport lag and apply lag in Data Guard?

Transport lag is redo that has not yet arrived at the standby — a network, listener or authentication problem. Apply lag is redo that has arrived but has not been applied — a recovery or standby I/O problem. V$DATAGUARD_STATS reports both.

How do you fix an archive gap when the primary has already deleted the logs?

Roll the standby forward over the network with RECOVER STANDBY DATABASE FROM SERVICE <primary> in RMAN, with apply stopped, then restart apply. No rebuild is needed.

Does the primary hang in Maximum Availability mode if the standby is down?

No. The primary continues running and the protection level drops until a standby is available again. Only Maximum Protection halts the primary rather than run unprotected.

What do you need for Fast-Start Failover?

The broker, an observer on a third site, Flashback Database on both databases so the old primary can be reinstated, and Maximum Availability mode or Maximum Performance with a configured lag limit.

Practise these on a real standby

If you want to run these scenarios against a real primary and standby with someone checking your work, our live Oracle Data Guard training builds the whole configuration from scratch — broker, switchovers, Fast-Start Failover and gap resolution — and the recorded Data Guard course lets you work through the same labs at your own pace. For the surrounding decisions, read switchover vs failover — when to use which and the hands-on Fast-Start Failover lab; the companion piece on Oracle RAC interview questions covers the clustering half of the same interview. Oracle's Managing physical standby databases guide for 26ai is the reference to keep open while you practise.