Configure DG BROKER

 

Database Roles,
Primary - apxtest

Standby - apxdr

Complete the configuration of Physical Standby and then proceed with DG Broker setup.

Create and Configure DG Broker Service

	dgmgrl sys@apxtest

	DGMGRL> create configuration my_dg as primary database is apxtest connect identifier is apxtest;

	DGMGRL> show configuration;

	DGMGRL> add database apxdr as connect identifier is apxdr;

	DGMGRL> show configuration;

	DGMGRL> ENABLE CONFIGURATION;

	DGMGRL> show configuration;

The broker log file is created in the same directory as the alert log and is named drc<$ORACLE_SID>. log .

Stop log apply:
	dgmgrl sys@apxtest
	DGMGRL> show configuration;
	DGMGRL> show database apxdr;
	DGMGRL> edit database apxdr set state=APPLY-OFF;
	DGMGRL> show database apxdr;

Start log apply:
	DGMGRL> edit database apxdr set state=APPLY-ON;
	DGMGRL> show database apxdr;

Example -

dgmgrl sys@apxtest
            DGMGRL for 64-bit Windows: Release 19.0.0.0.0 - Production 
            Version 19.24.0.0.0
            Copyright (c) 1982, 2024, Oracle and/or its affiliates.  All rights reserved.
            Welcome to DGMGRL, type "help" for information.
            Password:
            Connected to "apxtest"
          

DGMGRL> create configuration my_dg as primary database is apxtest connect identifier is apxtest;   
            Configuration "my_dg" created with primary database "apxtest"

DGMGRL> show configuration;
      Configuration - my_dg
        Protection Mode             :      MaxPerformance
        Members                         :      apxtest - Primary database
        Fast-Start Failover         :      Disabled
    Configuration Status      :     DISABLED

DGMGRL> add database apxdr as connect identifier is apxdr;
     Database "apxdr" added
    
DGMGRL> show configuration;
	        DGMGRL> show configuration;
        Configuration - my_dg
        Protection Mode             :      MaxPerformance
        Members                         :      apxtest - Primary database
                                                   apxdr   - Physical standby database
        Fast-Start Failover         :      Disabled
   Configuration Status      :     DISABLED


DGMGRL> ENABLE CONFIGURATION;            
  Enabled.

DGMGRL> show configuration;

Configuration - my_dg
        Protection Mode             :      MaxPerformance
        Members                         :      apxtest - Primary database
                                                   apxdr   - Physical standby database
        Fast-Start Failover         :      Disabled
    Configuration Status      :     SUCCESS   (status updated 0 seconds ago)


Stop log apply:
============
dgmgrl sys@apxtest

DGMGRL> show database apxdr;
   Database - apxdr
          Role                             :          PHYSICAL STANDBY
          Intended State             :          APPLY-ON
          Transport Lag             :          0 seconds (computed 1 second ago)
          Apply Lag                   :          0 seconds (computed 1 second ago)
          Average Apply Rate    :         3.00 KByte/s
          Real Time Query        :       OFF
          Instance(s)                 :       apxdr
        Database Status:        :        SUCCESS
       

DGMGRL> edit database apxdr set state=APPLY-OFF;
    Succeeded.

AlertLog on DR database
-------------------------------------------------------------------------------
    ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
    PR00 (PID:2540): MRP0: Background Media Recovery cancelled with status 16037
    Errors in file E:\ORACLE\APP\diag\rdbms\apxdr\apxdr\trace\apxdr_pr00_2540.trc:
    ORA-16037: user requested cancel of managed recovery operation
    PR00 (PID:2540): Managed Standby Recovery not using Real Time Apply
    Recovery interrupted!

Perform some Log switch on Primary to create a difference on DR

    Thread         Last Sequence     Received Last Sequence Applied     Difference
---------------------------------------------------------------------------------------------------
         1                     47                                44                                          3


Start log apply:
============
DGMGRL> edit database apxdr set state=APPLY-ON;
Succeeded.

AlertLog on DR database
-----------------------------------------------
PR00 (PID:1624): Managed Standby Recovery starting Real Time Apply
Parallel Media Recovery started with 8 slaves
Stopping change tracking
PR00 (PID:1624): Media Recovery Log C:\BACKUP\XXTEST\APXTEST\ARCHIVES\APX_ARC_0000000045_1179826869_0001.ARC
PR00 (PID:1624): Media Recovery Log C:\BACKUP\XXTEST\APXTEST\ARCHIVES\APX_ARC_0000000046_1179826869_0001.ARC
PR00 (PID:1624): Media Recovery Log C:\BACKUP\XXTEST\APXTEST\ARCHIVES\APX_ARC_0000000047_1179826869_0001.ARC
PR00 (PID:1624): Media Recovery Waiting for T-1.S-48 (in transit)
Recovery of Online Redo Log: Thread 1 Group 4 Seq 48 Reading mem 0
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT  NODELAY

DGMGRL> show database apxdr
  Database - apxdr
          Role                             :          PHYSICAL STANDBY
          Intended State             :          APPLY-ON
          Transport Lag             :          0 seconds (computed 1 second ago)
          Apply Lag                   :          5 minutes 14 seconds (computed 1 second ago)
          Average Apply Rate    :         3.00 KByte/s
          Real Time Query        :       OFF
          Instance(s)                 :       apxdr
         Database Status     : WARNING
        Database Warning(s):   ORA-16853: apply lag has exceeded specified threshold.
        
This is because of the DIFFERENCE we had on DR server.
Check again, the MRP process gets active and starts applying archives.

DGMGRL>  show database apxdr
 Database - apxdr
          Role                             :      PHYSICAL STANDBY
          Intended State             :      APPLY-ON
          Transport Lag             :      0 seconds (computed 1 second ago)
          Apply Lag                   :       5 minutes 14 seconds (computed 1 second ago)
          Average Apply Rate    :       3.00 KByte/s
          Real Time Query        :       OFF
          Instance(s)                 :       apxdr
          Database Status        :        SUCCESS


    Thread     Last Sequence Received         Last Sequence Applied     Difference
-----------------------------------------------------------------------------------------------
         1                     47                                            47                                  0