Data Guard broker is a centralized framework to manage entire Data Guard configuration through a client connection to any database in the configuration
From PrimaryEnable Broker configuration parameters
Add entry for data guard broker on listener.ora
Reload listener to effective the changes
Makesure service for dataguard has been initiated
SQL> alter system set dg_broker_start=true;
SQL> alter system set local_listener='(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.196.210.135)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = smsprod_bos03)))';
[oracle@bo3upsmsxodb01 ~]$ vi $ORACLE_HOME/network/admin/listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = bo3upsmsxodb01.pearsoncmg.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = smsprod_bos03_DG)
(ORACLE_HOME = /data01/app/oracle/product/19.0.0/dbhome_1)
(SID_NAME = smsprod)
)
)
[oracle@bo3upsmsxodb01 ~]$ lsnrctl reload LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 22-JAN-2023 04:28:17 Copyright (c) 1991, 2022, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=bo3upsmsxodb01.pearsoncmg.com)(PORT=1521))) The command completed successfully
[oracle@bo3upsmsxodb01 ~]$ lsnrctl status LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 22-JAN-2023 04:28:22 Copyright (c) 1991, 2022, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=bo3upsmsxodb01.pearsoncmg.com)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production Start Date 21-JAN-2023 06:55:52 Uptime 0 days 21 hr. 32 min. 30 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /data01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora Listener Log File /data01/app/oracle/diag/tnslsnr/bo3upsmsxodb01/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=bo3upsmsxodb01.pearsoncmg.com)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "smsprodXDB" has 1 instance(s). Instance "smsprod", status READY, has 1 handler(s) for this service... Service "smsprod_bos03" has 1 instance(s). Instance "smsprod", status READY, has 1 handler(s) for this service... Service "smsprod_bos03_DG" has 1 instance(s). Instance "smsprod", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully
Enable Broker configuration parameters
Add entry for data guard broker on listener.ora
Reload listener to effective the changes
Makesure service for dataguard has been initiated
SQL> alter system set dg_broker_start=true;
SQL> ALTER SYSTEM SET local_listener='(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.196.210.38)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = smsprod_stby)))';
[oracle@bo3upsmsxdrs01 ahussmo]$ vi $ORACLE_HOME/network/admin/listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = bo3upsmsxdrs01.pearsoncmg.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = smsprod_stby_DG)
(ORACLE_HOME = /data01/app/oracle/product/19.0.0/dbhome_1)
(SID_NAME = smsprod)
)
)
[oracle@bo3upsmsxdrs01 ahussmo]$ lsnrctl reload LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 22-JAN-2023 04:33:14 Copyright (c) 1991, 2022, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=bo3upsmsxdrs01.pearsoncmg.com)(PORT=1521))) The command completed successfully
[oracle@bo3upsmsxdrs01 ahussmo]$ lsnrctl status LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 22-JAN-2023 04:33:18 Copyright (c) 1991, 2022, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=bo3upsmsxdrs01.pearsoncmg.com)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production Start Date 20-JAN-2023 22:33:24 Uptime 1 days 5 hr. 59 min. 54 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /data01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora Listener Log File /data01/app/oracle/diag/tnslsnr/bo3upsmsxdrs01/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=bo3upsmsxdrs01.pearsoncmg.com)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "smsprod_stby" has 1 instance(s). Instance "smsprod", status READY, has 1 handler(s) for this service... Service "smsprod_stby_DG" has 1 instance(s). Instance "smsprod", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully
Login to data guard command line interface via sys user
Create new configuration
Make sure primary configuration is available
Reset 'LOG_ARCHIVE_DEST_n' to avoid 'ORA-16698' Errors
Add standby database to dataguard broker
Make sure both primary and standby configurations are available
Reset 'LOG_ARCHIVE_DEST_n' to valid value
Enable configuration
Verify configuration status is 'SUCCESS'
[oracle@bo3upsmsxodb01 ~]$ dgmgrl sys/f1Nal#cak3
DGMGRL> create configuration 'smsprod_bos03_DG_config' as primary database is 'smsprod_bos03' connect identifier is smsprod; Configuration "smsprod_bos03_DG_config" created with primary database "smsprod_bos03"
DGMGRL> show configuration; Configuration - smsprod_bos03_DG_config Protection Mode: MaxPerformance Members: smsprod_bos03 - Primary database Fast-Start Failover: Disabled Configuration Status: DISABLED
[oracle@bo3upsmsxodb01 ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jan 22 05:25:38 2023 Version 19.15.0.0.0 Copyright (c) 1982, 2022, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.15.0.0.0
SQL> sho parameter log_archive_dest_2; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_2 string SERVICE=smsprod_stby NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=smsprod_stby
SQL> alter system set LOG_ARCHIVE_DEST_2='';
DGMGRL> Add database 'smsprod_stby' as connect identifier is smsprod_stby; Database "smsprod_stby" added
DGMGRL> show configuration;
Configuration - smsprod_bos03_DG_config
Protection Mode: MaxPerformance
Members:
smsprod_bos03 - Primary database
smsprod_stby - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
DISABLED
[oracle@bo3upsmsxodb01 ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jan 22 05:25:38 2023 Version 19.15.0.0.0 Copyright (c) 1982, 2022, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.15.0.0.0
SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=smsprod_stby NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=smsprod_stby';
SQL> sho parameter log_archive_dest_2; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_2 string SERVICE=smsprod_stby NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=smsprod_stby
DGMGRL> enable configuration; Enabled.
DGMGRL> show configuration
Configuration - smsprod_bos03_DG_config
Protection Mode: MaxPerformance
Members:
smsprod_bos03 - Primary database
smsprod_stby - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 38 seconds ago)
