Oracle Dataguard - DGMGRL

Oracle Data Guard Broker is a management and monitoring tool provided by Oracle for Oracle Data Guard, a high-availability and disaster recovery solution for Oracle databases. Data Guard Broker simplifies the configuration, management, and monitoring of a Data Guard environment, making it easier to set up and maintain standby databases and to automate failover and switchover operations.

	alter system set dg_broker_start=true;
	ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='' scope=both;

	alter database recover managed standby database cancel;
	alter system set dg_broker_start=true;
	ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='' scope=both;

If you look at the listener configuration file, there is a dedicated service we have to create for DGMGRL. This is required in order enable Data Guard Broker. If this is not set, add below entry (proddb_DGMGRL and it has to be exactly in the same format < SID >_DGMGRL) and restart listener on both primary and standby.

	su - grid
	cd $ORACLE_HOME/network/admin

	cat listener.ora

	LISTENER =
	  (DESCRIPTION_LIST =
		(DESCRIPTION =
		  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.16)(PORT = 1521))
		  (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
		)
	  )

	SID_LIST_LISTENER =
	  (SID_LIST =
		(SID_DESC =
		 (SID_NAME = tesprim1)
		 (ORACLE_HOME = /u01/app/oracle/product/12.2.0/dbhome_1/)
		)
		(SID_DESC =
		 (SID_NAME = tesprim1_DGMGRL)
		 (ORACLE_HOME = /u01/app/oracle/product/12.2.0/dbhome_1/)
		)

	  (SID_DESC =
		 (SID_NAME = tesstand)
		 (ORACLE_HOME = /u01/app/oracle/product/12.2.0/dbhome_1/)
		)

	)

	[oracle@tesdb ~]$ dgmgrl sys/abcd#123@tesprim1
	DGMGRL for Linux: Release 12.2.0.1.0 - Production on Tue
	Oct 31 16:00:09 2023

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

	Welcome to DGMGRL, type "help" for information.
	Connected to "tesprim1"
	Connected as SYSDBA.

	DGMGRL> create configuration prim1 as primary database is tesprim1
	connect identifier is tesprim1;

	Configuration "prim1" created with primary database "tesprim1"
	DGMGRL> show configuration;

Register standby with broker :

	DGMGRL> add database tesstand as connect identifier is tesstand;
	DGMGRL> show configuration;

	DGMGRL> show database tesprim1;

	Database - tesprim1

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

	Database Status:
	SUCCESS

	DGMGRL> show database tesstand

	Database - tesstand

	  Role:               PHYSICAL STANDBY
	  Intended State:     APPLY-ON
	  Transport Lag:      59 seconds (computed 0 seconds ago)
	  Apply Lag:          31 minutes 34 seconds (computed 0 seconds ago)
	  Average Apply Rate: 1.14 MByte/s
	  Real Time Query:    ON
	  Instance(s):
		tesstand

	  Database Warning(s):
		ORA-16853: apply lag has exceeded specified threshold
		ORA-16855: transport lag has exceeded specified threshold

	Database Status:
	WARNING

	dgmgrl sys/abcd#123@tesprim1
	DGMGRL> show configuration;
	DGMGRL> show database tesstand;
	DGMGRL> edit database tesstand set state=APPLY-ON;
	DGMGRL> show database tesstand;

	dgmgrl sys/abcd#123@tesprim1
	DGMGRL> show configuration;
	DGMGRL> show database tesstand;
	DGMGRL> edit database tesstand set state=APPLY-OFF;
	DGMGRL> show database tesstand;

How we can manually enable log shipping from primary to standby, the same way we can use broker to enable log shipping

	dgmgrl sys/sys@tesprim1
	DGMGRL> show configuration;
	DGMGRL> show database tesprim1;
	DGMGRL> edit database tesprim1 set state=TRANSPORT-OFF;
	DGMGRL> show database tesprim1;
	1

	dgmgrl sys/sys@tesprim1
	DGMGRL> show configuration;
	DGMGRL> show database tesprim1;
	DGMGRL> edit database tesprim1 set state=TRANSPORT-ON;
	DGMGRL> show database tesprim1;