Configure Active DataGuard and DG BROKER

Oracle Active Data Guard allows DBAs and users to run real-time queries on a physical standby system for reporting.

Queries and reports can be offloaded from the production system to a synchronized physical standby database – all queries at the standby database return up-to-date results.

1. Make sure archive log enabled in primary database.

1
ARCHIVE LOG LIST

2. Enabled forced logging and try to switch logfile

1
2
ALTER DATABASE FORCE LOGGING;
ALTER SYSTEM SWITCH LOGFILE;

The DB_UNIQUE_NAME values of the primary and standby database should be used in the DG_CONFIG setting of the LOG_ARCHIVE_CONFIG parameter. For this example, the standby database will have the value “TESTDB_DG”.

1
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(TESTDB,TESTDB_DG)';

3. Set suitable remote archive log destinations for both the database

1
2
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=TESTDB_DG NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=TESTDB_DG';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

4. The LOG_ARCHIVE_FORMAT and LOG_ARCHIVE_MAX_PROCESSES parameters must be set to appropriate values and the REMOTE_LOGIN_PASSWORDFILE must be set to exclusive

1
2
3
ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=4;
ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;

5. Set the parameter in primary database

1
2
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
ALTER SYSTEM SET FAL_SERVER=TESTDB_DG

6. Entries for the primary and standby databases in “$ORACLE_HOME/network/admin/tnsnames.ora” files on both servers

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
TESTDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = lnx01.oracle.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = testdb)
    )
  )
TESTDB_DG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = lnx02.oracle.com)(PORT = 1521))
    (CONNECT_DATA =
      (UR=A)
      (SERVER = DEDICATED)
      (SERVICE_NAME = testdb_dg)
    )
  )

7. Create standby Control file and PFILE

1
2
ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/testdb_dg.ctl';
CREATE PFILE =’/tmp/inittestdb_dg.ora’ from SPFILE;

8. Change the necessary parameter according to Standby Database

1
2
3
*.db_unique_name='TESTDB_DG'
*.fal_server='TESTDB'
*.log_archive_dest_2='SERVICE=TESTDB ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=TESTDB'

Step for Standby Database preparation

1. Create the necessary directories on the standby server

1
2
3
mkdir -p /u01/app/oracle/product/product/18.0.0/dbhome_1/
mkdir -p /u01/app/oracle/diag
mkdir -p /u01/app/oracle/admin/testdb/adump

2. Copy password file from primary to standby database

1
2
3
4
5
6
7
Login to primary database and copy the password file to local file system
ASMCMD> cp pwdtestdb.288.1042913219 /tmp
copying +DATA/TESTDB/PASSWORD/pwdtestdb.288.1042913219 -> /tmp/pwdtestdb.288.1042913219
From standby server do sftp to primary database server 
sftp> get pwdtestdb.288.1042913219 /tmp
Fetching /tmp/pwdtestdb.288.1042913219 to /tmp/pwdtestdb.288.1042913219
/tmp/pwdtestdb.288.1042913219

3. Create standby database using DUPLICATE command

1
2
3
4
$ export ORACLE_SID=TESTDB_DG
$ export ORACLE_HOME= /u01/app/oracle/product/18.0.0.0/db_1/
$ sqlplus / as sysdba
STARTUP NOMOUNT PFILE='/tmp/inittestdb_dg.ora';

4. Connect to RMAN, specifying a full connect string for both the TARGET and AUXILIARY instances

1
2
3
4
5
rman TARGET sys/****@testdb AUXILIARY sys/*****@testdb_dg
 
DUPLICATE TARGET DATABASE
  FOR STANDBY
  FROM ACTIVE DATABASE;

5. Switch standby database to Read only mode

1
2
3
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE OPEN READ ONLY;

6. Start MRP process in standby database

1
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Enable DATAGUARD BROKER

1. Connect to both databases (primary and standby) and issue the following command

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
ALTER SYSTEM SET dg_broker_start=true;
 
dgmgrl sys/*****@testdb
 
Connected as SYSDBA.
 
Add Primary database:
 
DGMGRL> CREATE CONFIGURATION my_dg_config AS PRIMARY DATABASE IS testdb CONNECT IDENTIFIER IS testdb;
 
Add Standby database:
 
DGMGRL> ADD DATABASE testdb_dg AS CONNECT IDENTIFIER IS testdb_dg MAINTAINED AS PHYSICAL;
 
DGMGRL> ENABLE CONFIGURATION;

2. Check configuration and status of the databases from the broker

1
2
3
DGMGRL> SHOW CONFIGURATION;
DGMGRL> SHOW DATABASE TESTDB;
DGMGRL> SHOW DATABASE TESTDB_DG

3. Switch over and Fail over database using DATAGUARD broker

1
2
3
4
5
6
7
8
9
10
$ dgmgrl sys/*****@testdg
DGMGRL for Linux: Release 18.0.0.0.0 - Production on Sat Sep 1 09:39:33 2018
Version 18.3.0.0.0
 
Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.
 
Welcome to DGMGRL, type "help" for information.
Connected as SYSDBA.
DGMGRL> SWITCHOVER TO testdb_dg;
DGMGRL> FAILOVER to testdb_dg

ORACLE DBA