This paper contains the following two knowledge points. The environmental information is shown in the following figure:

1. Deploy Data Guard service;
2. Configuration and use of Data Guard Broker.
   

   I. Deployment of Physical Standby

   1.1 Main Library Preparations

   1.1.1 Enable force logging

   SQL> ALTER DATABASE FORCE LOGGING;

   1.1.2 Create standby database redo file

   SQL> ALTER DATABASE ADD STANDBY LOGFILE SIZE 200M;
   SQL> ALTER DATABASE ADD STANDBY LOGFILE SIZE 200M;
   SQL> ALTER DATABASE ADD STANDBY LOGFILE SIZE 200M;
   SQL> ALTER DATABASE ADD STANDBY LOGFILE SIZE 200M;

   

   1.1.3 Setting Main Library Parameters

   SQL> alter system set log_archive_config='dg_config=(cdb,cdb_stby)' scope=both;
   SQL> alter system set log_archive_dest_2='service=cdb_stby async valid_for=(online_logfile,primary_role) db_unique_name=cdb_stby';
   SQL> alter system set standby_file_management=auto;

   1.1.4 Ensure that the main library is in archiving mode

   

1.1.5 Edit tnsnames.ora

[oracle@db03 admin]$ vi tnsnames.ora 
CDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.120.83)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cdb)
    )
  )

CDB_STBY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.120.82)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cdb)
      (UR=A)
    )
  )

1.1.6 Configure static listening address

Edit the listener.ora file and add the following:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = cdb)
      (ORACLE_HOME = /u01/app/oracle/product/19.0.0/db_1)
      (SID_NAME = cdb)
    )
   (SID_DESC =
      (GLOBAL_DBNAME = cdb_stby)
      (ORACLE_HOME = /u01/app/oracle/product/19.0.0/db_1)
      (SID_NAME = cdb)
    )
)

Keep in mind that the monitoring must be restarted before it can take effect.

1.2 Reserve preparation

1.2.1 Create a backup password file

Copy the password file of the main library directly to the corresponding directory of the standby.

[oracle@db03 dbs]$ scp orapwcdb db02:$ORACLE_HOME/dbs/

1.2.2 Create initialization files

[oracle@db02 ~]$ cd $ORACLE_HOME/dbs
[oracle@db02 dbs]$ echo DB_NAME=cdb>initcdb.ora

1.2.3 Create adump directory

[oracle@db02 ~]$ mkdir -p $ORACLE_BASE/admin/cdb/adump

1.2.4 Configure Static Listener Address

Edit the listener.ora file and add the following:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = cdb)
      (ORACLE_HOME = /u01/app/oracle/product/19.0.0/db_1)
      (SID_NAME = cdb)
    )
   (SID_DESC =
      (GLOBAL_DBNAME = cdb_stby)
      (ORACLE_HOME = /u01/app/oracle/product/19.0.0/db_1)
      (SID_NAME = cdb)
    )
)

Keep in mind that the monitoring must be restarted before it can take effect.

1.2.5 Start Standby to nomount

[oracle@db02 ~]$ sqlplus "/as sysdba"
SQL> startup nomount 

1.2.6 Edit tnsnames.ora

[oracle@db02 admin]$ vi tnsnames.ora 
CDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.120.83)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cdb)
    )
  )

CDB_STBY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.120.82)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cdb)
      (UR=A)
    )
  )

1.3 Creating physical standby database and verification

1.3.1 Create a Standby

Here, duplicate is used to create the standby online, and the following script is run in rman:

[oracle@db03 ~]$ rman target / auxiliary sys/abcABC12@cdb_stby  
run {
        allocate channel prmy1 type disk;
        allocate channel prmy2 type disk;
        allocate channel prmy3 type disk;
        allocate channel prmy4 type disk;
        allocate auxiliary channel stby type disk;
        duplicate target database for standby from active database
        spfile
                set db_unique_name='cdb_stby'
                set fal_client='cdb_stby'
                set fal_server='cdb'
                set standby_file_management='AUTO'
                set log_file_name_convert='/cdb/','/cdb_stby/'
                set db_file_name_convert='/cdb/','/cdb_stby/'
                set log_archive_config='dg_config=(cdb,cdb_stby)'
                set log_archive_dest_2='service=cdb ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=cdb';
}

After the script is successfully executed, verify the status of the standby using the following SQL:

SQL> select status,instance_name from v$instance;

1.3.2 Enable redo log application

Log switching is performed at the primary library node, and redo log application is enabled on the standby.

SQL> alter database recover managed standby database using current logfile disconnect;

1.3.3 Role Validation of Main and Standby Repositories

Perform the following SQL validation on the primary and backup libraries respectively:

--Main library
SQL> select database_role,switchover_status from v$database;

--Reserve library
SQL> select database_role,switchover_status from v$database;

Configuration of DG Broker Service

2.1 broker is enabled in the main and backup Libraries

Perform the following SQL enabled broker on the main standby:

SQL> ALTER SYSTEM SET dg_broker_start=true scope=both;

2.2 Create broker configuration file

Execute the following command to connect to the main library and create a configuration file:

[oracle@db03 ~]$ dgmgrl sys@cdb as sysdba
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Tue May 7 16:22:12 2019
Version 19.3.0.0.0

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

Welcome to DGMGRL, type "help" for information.
Password:
Connected to "cdb"
Connected as SYSDBA.
DGMGRL> create configuration dg_cfg as primary database is cdb connect identifier is cdb;
Configuration "dg_cfg" created with primary database "cdb"
DGMGRL> add database cdb_stby as connect identifier is cdb_stby maintained as physical;
Database "cdb_stby" added
DGMGRL> enable configuration;
enabled.
DGMGRL>

If the standby database is added, the following error is reported:

ORA-16698: LOG_ARCHIVE_DEST_n parameter set for object to be added

On the standby, the value of LOG_ARCHIVE_DEST_2 parameter can be emptied and re-added.

SQL> alter system set LOG_ARCHIVE_DEST_2=' ' scope=both;

2.3 View configuration file information

DGMGRL> show configuration;

Configuration - dg_cfg

  Protection Mode: MaxPerformance
  Members:
  cdb      - Primary database
    cdb_stby - Physical standby database 

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 17 seconds ago)

DGMGRL> show database cdb;

Database - cdb

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

Database Status:
SUCCESS

DGMGRL> show database cdb_stby;

Database - cdb_stby

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 0 seconds ago)
  Apply Lag:          0 seconds (computed 0 seconds ago)
  Average Apply Rate: 2.00 KByte/s
  Real Time Query:    ON
  Instance(s):
    cdb

Database Status:
SUCCESS

2.4 Use dgmgrl to perform master-standby switching

DGMGRL> show configuration;

Configuration - dg_cfg

  Protection Mode: MaxPerformance
  Members:
  cdb      - Primary database
    cdb_stby - Physical standby database 

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 26 seconds ago)

DGMGRL> switchover to cdb_stby;
Performing switchover NOW, please wait...
Operation requires a connection to database "cdb_stby"
Connecting ...
Connected to "cdb_stby"
Connected as SYSDBA.
New primary database "cdb_stby" is opening...
Oracle Clusterware is restarting database "cdb" ...
Connected to an idle instance.
Connected to an idle instance.
Connected to an idle instance.
Connected to an idle instance.
Connected to an idle instance.
Connected to "cdb"
Connected to "cdb"
Switchover succeeded, new primary is "cdb_stby"
DGMGRL> show configuration;

Configuration - dg_cfg

  Protection Mode: MaxPerformance
  Members:
  cdb_stby - Primary database
    cdb      - Physical standby database 

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 15 second ago)