Configure Oracle 12c Dataguard Physical Standby

Description:

In this post I will describe the step by step configuration of Oracle 12c Dataguard physical standby setup with dataguard broker(DGMGRL) on Oracle Linux 7

We will be covering the following steps:

Primary Server setup

1. Enable archive log mode
2. Enable Force logging
3. Create standby redologs SRLs
4. Check db_name and db_unique_name
5. Set standby_file_management
6. TNS and listener Setup
7. Copy pfile and password to standby

Standby Server setup

1. Modify the pfile
2. Create necessary directories
3. Create standby database using RMAN Duplicate

Configure Dataguard broker

1. Enable dataguard broker
2. Register the database with dataguard broker
3. Add standby database to dataguard broker
4. Enable configuration

Primary Server setup

1. Enable archive log mode

Ensure the primary database is in archivelog mode. If not enable it.

alter system set log_archive_dest_1='LOCATION=/u01/app/oracle/arch';
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

2. Enable force logging:

Make sure Force logging is enabled on the database.

ALTER DATABASE FORCE LOGGING;
ALTER SYSTEM SWITCH LOGFILE;

3. Create standby redo logs on the primary database

Create the standby redo logs to support standby role. The recommended number of SRLs is (Number of redologs + 1). The SRLs size must be same as online redo logs size.

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 12 ('/u01/oradata/dev12c/std_redo12.log') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 13 ('/u01/oradata/dev12c/std_redo13.log') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 14 ('/u01/oradata/dev12c/std_redo14.log') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 15 ('/u01/oradata/dev12c/std_redo15.log') SIZE 200M;

4. Check pfile for db_name and db_unique_name

Make sure primary database has DB_UNIQUE_NAME set. If not set it using ALTER SYSTEM SET command.

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      dev12c
SQL> show parameter unique

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      dev12c
SQL>

5. Set STANDBY_FILE_MANAGEMENT to AUTO

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

6. TNS and LISTENER setup(for both primary & standby)

Tnsnames configuration:

DEV12C =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ol7-db.localdomain)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dev12c)
    )
  )

DEV12CDR =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ol7-db-dr.localdomain)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dev12c)
    )
  )


LISTENER_DEV12C =
  (ADDRESS = (PROTOCOL = TCP)(HOST = ol7-db.localdomain)(PORT = 1522))

Listener.ora configuration

LISTENER12C =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ol7-db.localdomain)(PORT = 1522))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
    )
  )

SID_LIST_LISTENER12C =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = dev12c_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/product/12.2.0/dbhome_1)
      (SID_NAME = dev12c)
    )
  )

Restart the listener(On both primary & standby)

lsnrctl stop listener12c
lsnrctl start listener12c

7. Copy pfile and password file to standby server

create pfile='/tmp/initdev12c.ora' from spfile;
scp initdev12c.ora oracle@ol7-db-dr:/tmp
scp $ORACLE_HOME/dbs/orapwdev12c oracle@ol7-db-dr:/u01/app/oracle/product/12.2.0/dbhome_1/dbs

Standby Server setup

1. modify the pfile copied from primary and make change for the below parameters

*.db_name=dev12c
*.db_unique_name=dev12cdr
enable_pluggable_database=true
log_file_name_convert='dummy','dummy'

2. Create the necessary directories

mkdir -p /u01/app/oracle/admin/dev12c/adump
mkdir -p /u01/oradata/dev12c/pdbseed
mkdir -p /u01/oradata/dev12c/pdb12c
mkdir -p /u01/app/oracle/fra
mkdir -p /u01/app/oracle/arch

3. Create standby using RMAN DUPLICATE

Start the auxiliary database instance using the pfile

export ORACLE_SID=dev12c
sqlplus "/as sysdba"
startup nomount pfile='/u01/initdev12c.ora';

Connect to RMAN and issue the duplicate command,

rman target sys/idgroup@dev12c auxiliary sys/idgroup@dev12cdr
DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER NOFILENAMECHECK;

Configure Datagurar Broker

1. Enable dataguard broker on both primary & standby

ALTER SYSTEM SET dg_broker_start=true;

2. On primary side: register the database with dataguard broker

dgmgrl sys/idgroup@dev12c
create configuration dgconfig as primary database is dev12c connect identifier is dev12c;

o/p:
[oracle@ol7-db admin]$ dgmgrl sys/idgroup@dev12c
DGMGRL for Linux: Release 12.2.0.1.0 - Production on Sat Jul 17 12:25:24 2021

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected to "dev12c"
Connected as SYSDBA.
DGMGRL>
DGMGRL> create configuration dgconfig as primary database is dev12c connect identifier is dev12c;
Configuration "dgconfig" created with primary database "dev12c"
DGMGRL>

3. On primary side: add the standby database

add database dev12cdr as connect identifier is dev12cdr maintained as physical;

o/p:
DGMGRL> add database dev12cdr as connect identifier is dev12cdr maintained as physical;
Database "dev12cdr" added
DGMGRL>

4. On Primary side: enable the configuration

enable configuration;

o/p:
DGMGRL> enable configuration;
Enabled.
DGMGRL>

Check the configuration

DGMGRL> show configuration

Configuration - dgconfig

  Protection Mode: MaxPerformance
  Members:
  dev12c   - Primary database
    dev12cdr - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 41 seconds ago)
DGMGRL>
DGMGRL> show database dev12c

Database - dev12c

  Role:               PRIMARY
  Intended State:     TRANSPORT-ON
  Instance(s):
    dev12c

Database Status:
SUCCESS

DGMGRL>
DGMGRL> show database dev12cdr

Database - dev12cdr

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

Database Status:
SUCCESS

DGMGRL>

Hope this helps…