Oracle
Data Guard
Broker

Assumptions



In addition to Data Guard config, also check any COST or VNCR configuration.
NOTE: Do NOT do the StaticConnectIdentifier Wallet steps yet, they will not work until the Broker is enabled.

Enable

Standby

sqlplus / as sysdba

ALTER SYSTEM SET dg_broker_config_file1='/u01/app/oracle/dr1ORCL2.dat' SCOPE=both;

ALTER SYSTEM SET dg_broker_config_file2='/u02/app/oracle/dr2ORCL2.dat' SCOPE=both;

NOTE: In the example above the filename includes the DB_UNIQUE_NAME of the database instance.NOTE: Best practice is for these files to exist outside of the ${ORACLE_HOME} directory structure on separate filesystems (ideally separate physical storage) to each other.

ALTER SYSTEM SET log_archive_dest_2='' SCOPE=BOTH;

NOTE: For databases before 12c, this step should be skipped.

ALTER SYSTEM SET dg_broker_start=true SCOPE=BOTH;

exit

Primary

sqlplus / as sysdba

ALTER SYSTEM SET dg_broker_config_file1='/u01/app/oracle/dr1ORCL1.dat' SCOPE=both;

ALTER SYSTEM SET dg_broker_config_file2='/u02/app/oracle/dr2ORCL1.dat' SCOPE=both;

NOTE: In the example above the filename includes the DB_UNIQUE_NAME of the database instance.NOTE: Best practice is for these files to exist outside of the ${ORACLE_HOME} directory structure on separate filesystems (ideally separate physical storage) to each other.

ALTER SYSTEM SET log_archive_dest_2='' SCOPE=BOTH;

NOTE: For databases before 12c, this step should be skipped.

ALTER SYSTEM SET dg_broker_start=true SCOPE=BOTH;

exit

Start the Broker CLI and create Configuration

dgmgrl /

CREATE CONFIGURATION orcl_dg_config AS PRIMARY DATABASE IS ORCL1 CONNECT IDENTIFIER IS ORCL1;

ADD DATABASE ORCL2 AS CONNECT IDENTIFIER IS ORCL2 MAINTAINED AS PHYSICAL;

ENABLE CONFIGURATION;

(You may need to wait a minute for configuration to show up correctly)

SHOW CONFIGURATION;

exit

Primary

Add the StaticConnectIdentifers

Standby

Add the StaticConnectIdentifers

Disable

NOTE: This assumes that LOG_ARCHIVE_DEST_2 was set to NULL when Broker was enabled, but that all other DG variable have not been manually altered (e.g. FAL_CLIENT etc).

Primary

ALTER SYSTEM SET DG_BROKER_START=FALSE SCOPE=BOTH;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=ORCL2 LGWR ASYNC NOAFFIRM DELAY=0 NET_TIMEOUT=30 REOPEN=300 DB_UNIQUE_NAME=ORCL2 VALID_FOR=(PRIMARY_ROLE,ALL_LOGFILES)' SCOPE=BOTH;

Standby

ALTER SYSTEM SET DG_BROKER_START=FALSE SCOPE=BOTH;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=ORCL1 LGWR ASYNC NOAFFIRM DELAY=0 NET_TIMEOUT=30 REOPEN=300 DB_UNIQUE_NAME=ORCL1 VALID_FOR=(PRIMARY_ROLE,ALL_LOGFILES)' SCOPE=BOTH;

Configuration Management

Create Configuration

CREATE CONFIGURATION orcl_dg_config AS PRIMARY DATABASE IS ORCL1 CONNECT IDENTIFIER IS ORCL1;

ADD DATABASE ORCL2 AS CONNECT IDENTIFIER IS ORCL2 MAINTAINED AS PHYSICAL;

ENABLE CONFIGURATION;

(You may need to wait a few minutes for configuration to show up correctly)

Show Configuration

dgmgrl /@${ORACLE_SID}

SHOW CONFIGURATION;

Configuration - orcl_dg_config
  Protection Mode: MaxPerformance  Members:  orcl1 - Primary database    orcl2 - Physical standby database 
Fast-Start Failover: DISABLED
Configuration Status:SUCCESS   (status updated 1 second ago)

Disable Configuration

dgmgrl /@${ORACLE_SID}

DISABLE CONFIGURATION;

Delete Configuration

dgmgrl /@${ORACLE_SID}

REMOVE CONFIGURATION;

Show Database

dgmgrl /@${ORACLE_SID}

SHOW DATABASE ORCL1;

Database - orcl1
  Role:               PRIMARY  Intended State:     TRANSPORT-ON  Instance(s):    ORCL1
Database Status:SUCCESS

SHOW DATABASE ORCL2;

Database - orcl2
  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: 7.00 KByte/s  Real Time Query:    OFF  Instance(s):    ORCL2
Database Status:SUCCESS
Note that (PROTOCOL=ipc) in StaticConnectIdentifier will impact Switchover/Failover/Snapshot Standby operations (i.e. to perform database restarts you will need to run dgmgrl on the machine hosting the database to be restarted). The safest way to work around this is to change LOCAL_LISTENER to use TCP instead of IPC and drop/recreate the Broker Configuration (and add TCP based DGMGRL Wallet entries). Using IPC for LOCAL_LISTENER is normally done to enforce listener registrations from the local server. This tends to be for 11.2.0.3 and earlier. From 11.2.0.4 you should use VNCR (Valid Node Checking for Registration) instead.

dgmgrl /@${ORACLE_SID}

SHOW DATABASE VERBOSE ORCL2;

Database - orcl2
  Role:               SNAPSHOT STANDBY  Transport Lag:      0 seconds (computed 1 second ago)  Apply Lag:          7 minutes 9 seconds (computed 1 second ago)  Instance(s):    ORCL2
  Properties:    DGConnectIdentifier             = 'orcl2'    ObserverConnectIdentifier       = ''    LogXptMode                      = 'ASYNC'    RedoRoutes                      = ''    DelayMins                       = '0'    Binding                         = 'optional'    MaxFailure                      = '0'    MaxConnections                  = '1'    ReopenSecs                      = '300'    NetTimeout                      = '30'    RedoCompression                 = 'DISABLE'    LogShipping                     = 'ON'    PreferredApplyInstance          = ''    ApplyInstanceTimeout            = '0'    ApplyLagThreshold               = '30'    TransportLagThreshold           = '30'    TransportDisconnectedThreshold  = '30'    ApplyParallel                   = 'AUTO'    ApplyInstances                  = '0'    StandbyFileManagement           = 'AUTO'    ArchiveLagTarget                = '0'    LogArchiveMaxProcesses          = '4'    LogArchiveMinSucceedDest        = '1'    DataGuardSyncLatency            = '0'    DbFileNameConvert               = ''    LogFileNameConvert              = ''    FastStartFailoverTarget         = ''    InconsistentProperties          = '(monitor)'    InconsistentLogXptProps         = '(monitor)'    SendQEntries                    = '(monitor)'    LogXptStatus                    = '(monitor)'    RecvQEntries                    = '(monitor)'    PreferredObserverHosts          = ''    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522))(CONNECT_DATA=(SERVICE_NAME=ORCL2_DGMGRL)(INSTANCE_NAME=ORCL2)(SERVER=DEDICATED)))'    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'    AlternateLocation               = ''    LogArchiveTrace                 = '0'    LogArchiveFormat                = '%t_%s_%r.dbf'    TopWaitEvents                   = '(monitor)'
  Log file locations:    Alert log               : /u01/app/oracle/diag/rdbms/orcl2/ORCL2/trace/alert_ORCL2.log    Data Guard Broker log   : /u01/app/oracle/diag/rdbms/orcl2/ORCL2/trace/drcORCL2.log
Database Status:SUCCESS

SHOW DATABASE orcl2 'RecvQEntries';

STANDBY_RECEIVE_QUEUE              STATUS     RESETLOGS_ID           THREAD              LOG_SEQ       TIME_GENERATED       TIME_COMPLETED    FIRST_CHANGE#     NEXT_CHANGE#       SIZE (KBs)         NOT_APPLIED        698842858                1                89337  10/04/2022 16:56:10  10/04/2022 17:01:28   11076961290297   11076964813454             3958

Edit Database

Redo Apply

For example, to stop redo apply on ORCL2...

EDIT DATABASE orcl2 SET STATE=APPLY-OFF;

To restart redo apply on ORCL2...

EDIT DATABASE orcl2 SET STATE=APPLY-ON;

Log Shipping

EDIT DATABASE orcl1 SET STATE=TRANSPORT-OFF;

EDIT DATABASE orcl1 SET STATE=TRANSPORT-ON;

Validate Database

VALIDATE DATABASE orcl1;

  Database Role:    Primary database
  Ready for Switchover:  Yes

VALIDATE DATABASE orcl2;

  Database Role:     Physical standby database  Primary Database:  orcl1
  Ready for Switchover:  Yes  Ready for Failover:    Yes (Primary Running)
  Flashback Database Status:    orcl1:  Off    orcl2:  Off
  Log Files Cleared:    orcl1 Standby Redo Log Files:  Not Cleared    orcl2 Online Redo Log Files:   Cleared    orcl2 Standby Redo Log Files:  Available

Troubleshooting

  Database Role:     Physical standby database  Primary Database:  orcl1
  Ready for Switchover:  No  Ready for Failover:    Yes (Primary Running)
  Flashback Database Status:    orcl1:  Off    orcl2:  Off
  Standby Apply-Related Information:    Apply State:      Not Running    Apply Lag:        5 minutes 37 seconds (computed 1 second ago)    Apply Delay:      0 minutes
  Log Files Cleared:    orcl1 Standby Redo Log Files:  Not Cleared    orcl2 Online Redo Log Files:   Cleared    orcl2 Standby Redo Log Files:  Available
  Future Log File Groups Configuration:    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status              (orcl2 )                (orcl1)    1         4                       0                       Insufficient SRLs    Warning: standby redo logs not configured for thread 1 on orcl1
Insufficent SRLsCheck Thread# is set correctly for all SRLs on Primary and Standby
Apply State: Not Runningedit database 'orcl2' set STATE='APPLY-ON';

Monitoring

Assuming you are running dgmgrl with the ORACLE_SID set to the instance you want to monitor, cut & paste the following as necessary:

echo "'SHOW DATABASE ${ORACLE_SID} InconsistentLogXptProps;'" | xargs dgmgrl /@${ORACLE_SID}

echo "'SHOW DATABASE ${ORACLE_SID} InconsistentProperties ;'" | xargs dgmgrl /@${ORACLE_SID}

echo "'SHOW DATABASE ${ORACLE_SID} LogXptStatus;'" | xargs dgmgrl /@${ORACLE_SID}

echo "'SHOW DATABASE ${ORACLE_SID} LsbyFailedTxnInfo;'" | xargs dgmgrl /@${ORACLE_SID}

echo "'SHOW DATABASE ${ORACLE_SID} LsbyParameters;'" | xargs dgmgrl /@${ORACLE_SID}

echo "'SHOW DATABASE ${ORACLE_SID} LsbySkipTable;'" | xargs dgmgrl /@${ORACLE_SID}

echo "'SHOW DATABASE ${ORACLE_SID} LsbySkipTxnTable;'" | xargs dgmgrl /@${ORACLE_SID}

echo "'SHOW DATABASE ${ORACLE_SID} RecvQEntries;'" | xargs dgmgrl /@${ORACLE_SID}

echo "'SHOW DATABASE ${ORACLE_SID} SendQEntries;'" | xargs dgmgrl /@${ORACLE_SID}

echo "'SHOW DATABASE ${ORACLE_SID} TopWaitEvents;'" | xargs dgmgrl /@${ORACLE_SID}

Licensing

You can install the broker command line interface DGMGRL and run the observer software on computer systems that are separate from the Oracle Data Guard primary and standby systems. It is not necessary to obtain a separate license for a system hosting the observer.

Oracle® Database Licensing Information 11g Release 2 (11.2)

Troubleshooting

DRC Log

Review the DRC log in your diag location...

tail -f /u01/app/oracle/diag/rdbms/$(echo ${ORACLE_SID} | tr '[:upper:]' '[:lower:]')/${ORACLE_SID}/trace/drc${ORACLE_SID}.log

    cat /u01/app/oracle/diag/rdbms/$(echo ${ORACLE_SID} | tr '[:upper:]' '[:lower:]')/${ORACLE_SID}/trace/drc${ORACLE_SID}.log

10/13/2022 16:20:16Connection to database orcl2 returns ORA-1017.Please check if database orcl2 is using a remote password file,its remote_login_passwordfile is set to SHARED or EXCLUSIVE,and the SYS or SYSDG password is the same as this database.Failed to connect to remote database orcl2. Error is ORA-01017Failed to send message to site orcl2. Error code is ORA-01017.Data Guard Broker Status Summary:  Type                        Name                             Severity  Status  Configuration               orcl_dg_config                    Warning  ORA-16607  Primary Database            orcl1                               Error  ORA-16778  Physical Standby Database   orcl2                               Error  ORA-01017
Steps to fix:
  1. Check ALL wallet entries
  2. Recopy $ORACLE_HOME/dbs/orapwORCL1 from the Primary to the Standby and rename to $ORACLE_HOME/dbs/orapwORCL2

Validate

dgmgrl /

validate database orcl2;

  Database Role:     Physical standby database  Primary Database:  orcl1
  Ready for Switchover:  Yes  Ready for Failover:    Yes (Primary Running)
  Flashback Database Status:    athena:  Off    pallas:  Off
  Log Files Cleared:    athena Standby Redo Log Files:  Not Cleared    pallas Online Redo Log Files:   Cleared    pallas Standby Redo Log Files:  Available
  Current Log File Groups Configuration:    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status              (orcl1)                 (orcl2)    1         4                       3                       Insufficient SRLs
  Future Log File Groups Configuration:    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status              (pallas)                (athena)    1         4                       0                       Insufficient SRLs    Warning: standby redo logs not configured for thread 1 on athena

Debug

dgmgrl -debug /@${ORACLE_SID}

ORA-16778: redo transport error for one or more databases

DGMGRL> show configuration;
Configuration - orcl_dg_config
  Protection Mode: MaxPerformance  Members:  orcl1 - Primary database    Error: ORA-16778: redo transport error for one or more databases
    orcl2 - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:ERROR   (status updated 22 seconds ago)
DGMGRL> show database orcl1
Database - orcl1
  Role:               PRIMARY  Intended State:     TRANSPORT-ON  Instance(s):    ORCL1      Error: ORA-16737: the redo transport service for standby database "orcl2" has an error
Database Status:ERROR
Common causes:
  1. It takes a few minutes for a new configuration to begin to work properly. If this is a new configuration, check again in 10 minutes.
  2. This issue can also occur if there is a mismatch between password files
DGMGRL> show database orcl1 'LogXptStatus'LOG TRANSPORT STATUSPRIMARY_INSTANCE_NAME STANDBY_DATABASE_NAME               STATUS              ORCL1               orcl2 ORA-07286: sksagdi: cannot obtain device information.

ORA-16797: database is not using a server parameter file

DGMGRL> show configuration
Configuration - orcl_dg_config
  Protection Mode: MaxPerformance  Databases:    zeus  - Primary database    atlas - Physical standby database      Error: ORA-16797: database is not using a server parameter file
Fast-Start Failover: DISABLED
Configuration Status:ERROR
Common causes:
  1. Database is NOT using a SPFILE. Check using SHOW PARAMETER SPFILE; Fix using: CREATE SPFILE FROM PFILE;
  2. If SHOW PARAMETER SPFILE shows a SPFILE that definitely exists with the correct permissions: try restarting the database instance.

Bibliography