DataGuard Broker Configuration in Oracle 19C

Step 1. Enable the DGMGRL Listener Both sides:

[oracle@server1 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = pearl)
      (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
      (SID_NAME = pearl)
    )
 (SID_DESC =
      (GLOBAL_DBNAME = pearl_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
      (SID_NAME = pearl)
    )
  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.31.201)(PORT = 1521))
  )

ADR_BASE_LISTENER = /u01/app/oracle

Step 2. Check current state on DG Broker in Both Side:

show parameter DG_BROKER_START

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start                      boolean     FALSE

Step 3. Verify the GAP Both Sides:

SQL>  select status,instance_name,database_role,protection_mode from v$database,v$instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE    PROTECTION_MODE
------------ ---------------- ---------------- --------------------
OPEN         pearl            PRIMARY          MAXIMUM PERFORMANCE

SQL>  select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
            10

Step 4. Run Both side and verify

alter system set dg_broker_start=true;

show parameter dg_broker_config;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1               string      /u01/app/oracle/product/19.0.0
                                                 /dbhome_1/dbs/dr1pearl.dat
dg_broker_config_file2               string      /u01/app/oracle/product/19.0.0
                                                 /dbhome_1/dbs/dr2pearl.dat

SQL> alter system set LOG_ARCHIVE_DEST_2=''; (run Both database)

Step 5. Run DGMGRL in Primary Server:

[oracle@server1 dbs]$ dgmgrl
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Mon Feb 17 23:38:01 2025
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.
DGMGRL>
DGMGRL>
DGMGRL> connect  sys/admin123@pearl
Connected to "pearl"
Connected as SYSDBA.

 Step 6. Create the configuration and add primary database:

DGMGRL> create configuration 'test1' as primary database is 'pearl' connect identifier is pearl;
Configuration "test1" created with primary database "pearl"
DGMGRL>
DGMGRL> show configuration;

Configuration - test1

  Protection Mode: MaxPerformance
  Members:
  pearl - Primary database

Fast-Start Failover:  Disabled

Configuration Status:
DISABLED

Step 7. Add the standby database in Dataguard broker:

DGMGRL>  add database 'muthu' as connect identifier is muthu maintained as physical;
Database "muthu" added

Step 8. Enable the Dataguard broker configuration:

DGMGRL> enable configuration;
Enabled.

DGMGRL> show configuration;

Configuration - test1

  Protection Mode: MaxPerformance
  Members:
  pearl - Primary database
    muthu - Physical standby database
      Warning: ORA-16809: multiple warnings detected for the member

Fast-Start Failover:  Disabled

Configuration Status:
WARNING   (status updated 5 seconds ago)

DGMGRL> show database muthu;

Database - muthu

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

  Database Warning(s):
    ORA-16856: transport lag could not be determined
    ORA-16857: member disconnected from redo source for longer than specified threshold

Database Status:
WARNING

Step 9. set the lag threshold

DGMGRL>  edit database muthu set property TransportLagThreshold =0;
Property "transportlagthreshold" updated
DGMGRL>
DGMGRL> edit database muthu set property ApplyLagThreshold  =0;
DGMGRL> Property "applylagthreshold" updated

DGMGRL>
DGMGRL> show configuration;

Configuration - test1

  Protection Mode: MaxPerformance
  Members:
  pearl - Primary database
    muthu - Physical standby database
      Warning: ORA-16809: multiple warnings detected for the member

Fast-Start Failover:  Disabled

Configuration Status:
WARNING   (status updated 46 seconds ago)

DGMGRL> show database pearl;

Database - pearl

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

Database Status:
SUCCESS

DGMGRL> show database muthu;

Database - muthu

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

  Database Warning(s):
    ORA-16857: member disconnected from redo source for longer than specified threshold

Database Status:
WARNING

Step 10. any threshold issue restart the standby check connections for primary and standby

DGMGRL> show database muthu;

Database - muthu

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      (unknown)
  Apply Lag:          (unknown)
  Average Apply Rate: (unknown)
  Real Time Query:    ON
  Instance(s):
    muthu

Database Status:
SUCCESS

DGMGRL> show configuration;

Configuration - test1

  Protection Mode: MaxPerformance
  Members:
  pearl - Primary database
    muthu - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 52 seconds ago

Comments

Post a Comment