How to Setup Data Guard Physical Standby in Oracle Database 18c

This post applies to 12c and 19c as well, it introduced two popular ways for creating a standby database: RMAN DUPLICATE and from primary database backup

Introduction

This post demonstrates how to build an Oracle 18c physical standby database managed by data guard.  The method should also apply to 12c and 19c as well.

Subscribe to get access

Read more of this content when you subscribe today.

Recover Standby Database By Using RMAN Incremental Backup

By using RMAN incremental backups to synchronize a physical standby database, it is fast and easy without installing datafiles or history archive logs required.

This post demonstrates how to recover standby database with a big gap behind primary database in 11g.

Stop the managed recovery process (MRP)

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

On standby database, get the lowest SCN

SQL> SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN
-----------
7.8880E+12

SQL> set numwidth 16
SQL> /

CURRENT_SCN
----------------
7888036454134

SQL> select min(fhscn) from x$kcvfh;

MIN(FHSCN)
----------------
7883734410408

SQL> select min(f.fhscn) from x$kcvfh f, v$datafile d
where f.hxfil =d.file#
and d.enabled != 'READ ONLY' ; 

MIN(F.FHSCN)
----------------
7883734410408

SQL> select CHECKPOINT_CHANGE# from v$datafile_header;

MIN(CHECKPOINT_CHANGE#)
-----------------------
7883734410408

On primary database, RMAN backup from SCN of previous step

Take a SCN backup, and copy backup onto standby server.

RMAN> BACKUP INCREMENTAL FROM SCN 7883734410408 DATABASE 
      FORMAT '/tmp/testdb_styby_%U' tag 'INCFORSTANDBY';

Prepare Standby ControlFile and New Datafiles

a) Create a standby control file from primary, and then copy onto standby database server. Replace the standby control file by using this newly created one.

-- From primary
--
SQL>ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/TESTDBSTY.ctl';

-- From Standby
--
RMAN> SHUTDOWN IMMEDIATE ;
RMAN> STARTUP NOMOUNT; 
RMAN> RESTORE STANDBY CONTROLFILE FROM '/tmp/TESTDBSTY.ctl';

b) For new datafiles which are in Primary but not in standby database, copy them from primary to standby database server. Then catalog current all standby database datafiles, since the control file has been replaced by primary one.

RMAN> CATALOG START WITH '+DG1/tetsdbsty/datafile/'; 

c) Switch database to copy from RMAN.

RMAN> SWITCH DATABASE TO COPY;

Recover Standby Database

Catalog incremental backup, then recover standby database by using RMAN incremental backup of primary database.

RMAN> CATALOG START WITH '/tmp/testdb_styby';

RMAN> RECOVER DATABASE NOREDO;

Start the MRP process of standby database.

SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE
     DISCONNECT FROM SESSION;

Check DataGuard Health

There is no gap between primary database and standby database now.

DGMGRL> show configuration;
Configuration - dg_oemrep
Protection Mode: MaxPerformance
Databases:
oemrep -    Primary database
stboemrep - Physical standby database

Fast-Start Failover: Disabled

Configuration Status:
SUCCESS
DGMGRL> show database "stboemrep";

Database - stboemrep

Enterprise Manager Name: OEMREP

Role:                    PHYSICAL STANDBY
Intended State:          APPLY-ON
Transport Lag:           0 seconds (computed 1 second ago)
Apply Lag:               0 seconds (computed 1 second ago)
Apply Rate:              4.93 MByte/s
Real Time Query:         OFF
Instance(s):
STBOEMREP

Database Status:
SUCCESS

DGMGRL>

Create Standby Database From RMAN Backup

Create a RAC Standby Database from Overnight RMAN Tape Backups with 20 steps.

This post demonstrates how to build a RAC standby database by using overnight  RMAN ( Netbackup ) backups in 11.2.

Subscribe to get access

Read more of this content when you subscribe today.