Oracle Data Guard (physical standby) setup

Symptom

Goal: set up disaster recovery and high availability for Oracle with a physical standby (Data Guard); be able to switch to the standby if the primary fails.

1. Put the primary in FORCE LOGGING and ARCHIVELOG mode

For the standby to stay consistent with the primary, all changes must be written to redo. FORCE LOGGING logs even NOLOGGING operations. For ARCHIVELOG mode the database is brought up in MOUNT state.

Oracle

ALTER DATABASE FORCE LOGGING;
-- For ARCHIVELOG:
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

2. Add standby redo log files

Standby redo logs (SRL) should have one more group than the online redo logs and the same size. They are created on both the primary and the standby; they are required for real-time apply. In the example, if online redo is 200M, SRLs are 200M as well.

Oracle

ALTER DATABASE ADD STANDBY LOGFILE ('/u01/oradata/srl01.log') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/oradata/srl02.log') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/oradata/srl03.log') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/oradata/srl04.log') SIZE 200M;

3. Set log_archive_dest and related parameters

On the primary, define dest_2 to ship redo to the standby. Set DB_UNIQUE_NAME, LOG_ARCHIVE_CONFIG (the db_unique_name of both sides), FAL_SERVER, and STANDBY_FILE_MANAGEMENT=AUTO. This way files are created automatically on the standby.

Oracle

ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl_prim,orcl_stby)';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=orcl_stby ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl_stby';
ALTER SYSTEM SET FAL_SERVER='orcl_stby';  -- for role changes; on the standby FAL_SERVER='orcl_prim'
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

4. Create the standby with RMAN DUPLICATE FOR STANDBY

After the password file and static listener/tnsnames are set up on the standby server, RMAN connects to the primary and to the standby (AUXILIARY). FROM ACTIVE DATABASE copies over the network without a backup; DORECOVER applies redo.

Oracle

rman TARGET sys/password@orcl_prim AUXILIARY sys/password@orcl_stby
RUN {
  DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE
  DORECOVER
  NOFILENAMECHECK;
}

5. Start redo apply (MRP) and verify

Start managed recovery while the standby is in MOUNT state; use USING CURRENT LOGFILE for real-time apply. Verify with v$managed_standby that the apply process (MRP0) is running.

Oracle

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
SELECT process, status, sequence# FROM v$managed_standby WHERE process LIKE 'MRP%';

6. Enable Data Guard Broker

The broker centralizes the configuration and reduces failover/switchover to a single command. Set DG_BROKER_START=TRUE on both instances, then create and enable the configuration with dgmgrl. In 12c+ the broker manages redo transport: if LOG_ARCHIVE_DEST_2 was set manually, ADD DATABASE fails with ORA-16698; clear it first with ALTER SYSTEM SET LOG_ARCHIVE_DEST_2=''.

Oracle

ALTER SYSTEM SET DG_BROKER_START=TRUE;
-- In dgmgrl:
CREATE CONFIGURATION dgconf AS PRIMARY DATABASE IS orcl_prim CONNECT IDENTIFIER IS orcl_prim;
ADD DATABASE orcl_stby AS CONNECT IDENTIFIER IS orcl_stby;
ENABLE CONFIGURATION;

7. Monitor the synchronization gap and complete testing

The sequence difference between primary and standby must be monitored continuously; a growing gap indicates a network/apply bottleneck. dgmgrl SHOW CONFIGURATION should show 'SUCCESS'. Optionally test a role change with a switchover.

Oracle

-- dgmgrl (SHOW DATABASE shows the Transport Lag / Apply Lag values):
SHOW CONFIGURATION;
SHOW DATABASE orcl_stby;
-- SQL (standby): SELECT name, value FROM v$dataguard_stats WHERE name LIKE '%lag';