For a mission-critical Oracle database, saying 'we'll restore from backup' can't be considered a disaster recovery plan since it amounts to an outage that could last for several hours. Oracle Data Guard overcomes this by keeping one or more standby databases continuously synchronized with the primary one, in such a way that a failure doesn't require you to start from a backup tape.
The walkthrough explains how to set up a physical standby for Oracle Database 19c: this involves creating a byte-for-byte copy of the primary database which remains synchronized by receiving and applying redo in real time. The document is presented in the form of a checklist outlining the various phases rather than as a continuous series of commands, since that is actually the correct way to proceed—you should verify each stage before moving on to the next, otherwise you'll end up spending your troubleshooting time trying to work out which of the ten steps failed.
The chain you're building
Before touching a config file, it helps to hold the whole picture in your head:
Primary Database → Redo Generation → Redo Transport → Standby Redo Logs → Redo Apply (MRP) → Synchronized Standby
Each of the stages mentioned is there so that one link in that chain can be made reliable. The same order that you should follow when troubleshooting is the one that applies if synchronization breaks at a later stage.
- Move the primary into ARCHIVELOG mode
As long as the primary database is archiving its logs rather than overwriting them, the redo data shipped by Data Guard will only last until it is shipped. If your primary database is not already operating in ARCHIVELOG mode, this requirement is absolute and must be addressed first since the entire downstream operation depends on having access to archived redo.
- Enable FORCE LOGGING
Even if ARCHIVELOG mode is enabled, some operations (such as bulk loads and certain index builds) are capable of using NOLOGGING and completely omit the writing of redo - in that case the changes would never make it to the standby. FORCE LOGGING eliminates this gap by ensuring that everything is logged with no exceptions.
- Enable the Flashback Database (optional, but advisable)
Although a Flashback Database is not absolutely necessary for a physical standby, it proves useful when carrying out role transitions and in the case of someone accidentally entering a DELETE statement without a WHERE clause. It provides a quick method of rewinding rather than carrying out a full restore.
- Set up the standby redo log files (SRLs)
People often fail to allocate adequate resources for this step. The standby database must have its own set of Standby Redo Logs to receive the incoming redo - and as a general rule it is:
At least one more SRL group per redo thread than the primary has online redo log groups
Each SRL must be sized the same as the primary's online redo logs
A common fundamental cause of standbys slowly falling behind is having too few or undersized SRLs.
- Arrange TNS and the listener
The primary and standby databases must be able to contact each other using the service names based on their DB_UNIQUE_NAME. Firstly, configure the listener and the tnsnames.ora entries on both sides, then check the connectivity from each host using tnsping before proceeding any further – attempting to debug a Data Guard error that is in fact a network issue will waste real time.
- Set up the Data Guard initialisation settings.
This is the parameter set that actually defines the relationship between the two databases:
Parameter Role
DB_UNIQUE_NAME Uniquely identifies each database in the Data Guard configuration
LOG_ARCHIVE_CONFIG Declares the list of DB_UNIQUE_NAMEs allowed to participate
LOG_ARCHIVE_DEST_1 Local archive destination
LOG_ARCHIVE_DEST_2 Remote destination - where redo ships to the standby
FAL_SERVER Where to fetch redo gaps from if the standby falls behind
STANDBY_FILE_MANAGEMENT Set to AUTO so datafile changes on the primary propagate automatically
REMOTE_LOGIN_PASSWORDFILE Required for the password-file-based authentication Data Guard relies on
If you get this wrong (or if the information differs between the primary and standby systems), you'll end up spending a considerable amount of time looking at the alert logs later on.
- Set up the password file
The primary and standby databases authenticate each other using the password file, not relying on OS trust; therefore, both databases must have a synchronised password file with identical SYS credentials.
- Get the standby server ready
The Oracle version, the patch level, and the directory structure for the datafiles, redo logs, and archive destinations are all the same, and it is at this point that you would install the Oracle software (even if a database is not yet created) on the standby host.
- Use RMAN to create a physical standby.
With everything above in place, RMAN can build the standby directly from the running primary:
sql
DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE;
It retrieves a current copy of the primary over the network and places it into standby mode, thus eliminating the need for the older 'backup, copy, restore' procedure altogether.
Begin the Managed Recovery (MRP).
It is the Managed Recovery Process that actually applies the incoming redo in order to keep the standby up to date; if the MRP is not running, then nothing that comes after RMAN duplication is of any significance.
- Check the Managed Recovery Process
Do not suppose that it began smoothly - you should look for MRP0 in the process list and make sure it is actually being applied, not halted.
- Verify redo transport
You need to make sure that the redo process is in fact switching the system from the primary to the standby. The archive log sequence numbers on both sides should be closely aligned with each other - any increasing gap between them should be your earliest indication of a problem.
Check that the Data Guard synchronisation is working.
In addition to checking the sequence numbers, verify on the standby instance that the standby redo logs are being used as they should be and make sure that the apply process isn't quietly stalled.
- Understand apply lag
The delay involved in applying the redo at the standby site is known as 'apply lag'. Although some lag is normal, particularly when large transactions are being processed, or there is I/O contention at the standby, a lag that continues to increase rather than remaining constant indicates that the standby is falling behind, and your recovery point objective for a real failover is equal to that lag.
- Common troubleshooting scenarios
The usual suspects, roughly in order of how often you'll hit them:
MRP0 not running - recovery was never (re)started after an interruption
Standby falling behind - undersized SRLs, network bandwidth, or apply-side I/O contention
TNS/listener failures - connectivity broke after a restart or firewall change
Missing archive logs - a gap in the sequence that FAL_SERVER couldn't resolve
Misconfigured SRLs - wrong size or too few groups, caught by checking V$STANDBY_LOG
- Should you add Data Guard Broker?
A broker doesn't have to set up a physical standby, but it does centralise the configuration, make the process of switching over or failing over simple by involving just a single command, and provide fast-start failover if that is needed. In any situation that goes beyond a basic two-database arrangement, the extra level is worth it.
- Production readiness checklist
Before calling this "done," validate across all five of these, not just "the standby exists":
Primary configuration - ARCHIVELOG, FORCE LOGGING, and Data Guard parameters all confirmed
Network - connectivity verified in both directions, not just primary → standby
Standby state - MRP running, SRLs correctly sized and populated
Data Guard health - transport and apply lag both within acceptable bounds
Operational readiness - monitoring in place, and switchover/failover procedures actually documented and tested
The final point is the one that people omit; a standby which has never been switched over is one that you in fact don't know works.
The real takeaway
Setting up a physical standby involves more than simply running a series of SQL and RMAN commands; it requires setting up and verifying the entire chain from redo generation all the way through to a synchronized standby, step by step. You should test the connectivity before you create the standby. After the standby has been created, you must make sure that MRP is actually running. You should continuously monitor the transport and apply lag. And you shouldn't regard the environment as being ready for production until you've carried out an actual switchover, not just watched the standby remain in sync.
Data Guard provides you with the means to achieve high availability and disaster recovery; however, the reliability only comes about as a result of properly configuring it, continuously monitoring it, and having recovery procedures that you have actually tested, not merely those that are written down. Read in detail
Top comments (0)