Data Guard Broker Configuration in oracle
Listener configuration on primary
(SID_DESC =
(GLOBAL_DBNAME = ddb_dgmgrl)
(ORACLE_HOME = /u01/app/oracle/product/19.0/dbhome1)
(SID_NAME = ddb)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.31.19.40)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ddb)
(ORACLE_HOME = /u01/app/oracle/product/19.0/dbhome1)
(SID_NAME = ddb)
)
(SID_DESC =
(GLOBAL_DBNAME = ddb_dgmgrl)
(ORACLE_HOME = /u01/app/oracle/product/19.0/dbhome1)
(SID_NAME = ddb)
)
)
Listener configuration on standby
(SID_DESC =
(GLOBAL_DBNAME = ddb_dr_dgmgrl)
(ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
(SID_NAME = ddb_dr)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.13.0.4)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ddb_dr)
(ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
(SID_NAME = ddb_dr)
)
(SID_DESC =
(GLOBAL_DBNAME = ddb_dr_dgmgrl)
(ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
(SID_NAME = ddb_dr)
)
)
tnsnames.ora on primary and standby
DDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = wes-4-92-00-112.compute-1.amazonaws.com )(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ddb)
)
)
DDB_DR =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 92.100.100.922 )(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ddb_dr)
)
)
On standby database:
SQL> alter database recover managed standby database cancel;
SQL> alter system set LOG_ARCHIVE_DEST_2='' SCOPE=BOTH sid='*';
On primary database:
SQL> alter system set LOG_ARCHIVE_DEST_2='' SCOPE=BOTH sid='*';
Enable broker on primary and standby database
alter system set dg_broker_start=true;
show parameter dg_broker_start
Register primary database with broker
create configuration prod_ddb as primary database is ddb connect identifier is ddb;
[oracle@10.22.3333.2222 admin]$
[oracle@10.22.3333.2222 admin]$
[oracle@10.22.3333.2222 admin]$ dgmgrl sys/Rdx1234567890@ddb
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Sat Jan 14 15:02:21 2023
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.
Connected to "ddb"
Connected as SYSDBA.
DGMGRL>
DGMGRL>
DGMGRL> create configuration prod_ddb as primary database is ddb connect identifier is ddb;
Configuration "prod_ddb" created with primary database "ddb"
DGMGRL>
DGMGRL> show configuration;
Configuration - prod_ddb
Protection Mode: MaxPerformance
Members:
ddb - Primary database
Fast-Start Failover: Disabled
Configuration Status:
DISABLED
DGMGRL>
Register standby database with broker
add database ddb_dr as connect identifier is ddb_dr;
DGMGRL> add database ddb_dr as connect identifier is ddb_dr;
Database "ddb_dr" added
DGMGRL>
DGMGRL> show configuration;
Configuration - prod_ddb
Protection Mode: MaxPerformance
Members:
ddb - Primary database
ddb_dr - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
DISABLED
DGMGRL>
Enable Data Guard broker
DGMGRL>
DGMGRL>
DGMGRL> enable configuration;
Enabled.
DGMGRL>
DGMGRL> show configuration;
Configuration - prod_ddb
Protection Mode: MaxPerformance
Members:
ddb - Primary database
ddb_dr - Physical standby database
Warning: ORA-16853: apply lag has exceeded specified threshold
Fast-Start Failover: Disabled
Configuration Status:
WARNING (status updated 16 seconds ago)
DGMGRL>
DGMGRL>
DGMGRL>
DGMGRL>
DGMGRL> show database ddb
Database - ddb
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
ddb
Database Status:
SUCCESS
DGMGRL> show database ddb_dr
Database - ddb_dr
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: 55.00 KByte/s
Real Time Query: OFF
Instance(s):
ddb_dr
Database Status:
SUCCESS
DGMGRL>
DGMGRL>
DGMGRL>
