Oracle
Data Guard
Broker

Databases

Oracle

Oracle Availability

Oracle Data Guard

Assumptions

The Listener is running
The LISTENER.ORA includes Data Guard Config
The Database parameter LOCAL_LISTENER is set
There is a Primary database and at least one Standby configured and running
A Wallet has been created containing SYS passwords for Primary and all Standby database instances

Listener

In addition to Data Guard config, also check any COST or VNCR configuration.

Physical Standby

Wallet

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

Oracle Wallet

Add the StaticConnectIdentifers

Standby

Oracle Wallet

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

ODG 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

Oracle Flashback

ODG Standby Redo Logs

Insufficent SRLsCheck Thread# is set correctly for all SRLs on Primary and Standby

Apply State: Not Runningedit database 'orcl2' set STATE='APPLY-ON';

Monitoring

InconsistentLogXptProps (Inconsistent Redo Transport Properties)
InconsistentProperties (Inconsistent Database Properties)
LogXptStatus (Redo Transport Status)
LsbyFailedTxnInfo (Logical Standby Failed Transaction Information)
LsbyParameters (Logical Standby Parameters)
LsbySkipTable (Logical Standby Skip Table)
LsbySkipTxnTable (SQL Apply Skip Transaction Table)
RecvQEntries (Receive Queue Entries)
SendQEntries (Send Queue Entries)
TopWaitEvents

12.1

12.2

18

19

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}

Fast-Start Failover

https://support.dbagenesis.com/knowledge-base/enable-fast-start-failover-data-guard-broker/

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

ODG 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:

Check ALL wallet entries
Recopy $ORACLE_HOME/dbs/orapwORCL1 from the Primary to the Standby and rename to $ORACLE_HOME/dbs/orapwORCL2

Oracle Wallet

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:

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.
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:

Database is NOT using a SPFILE. Check using SHOW PARAMETER SPFILE; Fix using: CREATE SPFILE FROM PFILE;
If SHOW PARAMETER SPFILE shows a SPFILE that definitely exists with the correct permissions: try restarting the database instance.

Bibliography

Configurable Database Properties (11.2)Monitorable Database Properties (11.2)https://blog.dbi-services.com/oracle-12cr2-dataguard-switchover-with-oracle-wallets/ https://minimalistic-oracle.blogspot.com/2019/07/how-do-identify-transport-error-in-data.htmlhttps://www.support.dbagenesis.com/post/oracle-data-guard-broker-configuration#viewer-13d0lhttps://ulfet.blogspot.com/2014/02/data-guard-broker-configuration.html
https://docs.oracle.com/en/database/oracle/oracle-database/19/dgbkr/using-data-guard-broker-to-manage-switchovers-failovers.html

OracleData GuardBroker

Assumptions

Enable

Standby

Primary

Primary

Standby

Disable

Primary

Standby

Configuration Management

Create Configuration

Show Configuration

Disable Configuration

Delete Configuration

Show Database

Edit Database

Redo Apply

Log Shipping

Validate Database

Troubleshooting

Monitoring

Fast-Start Failover

Licensing

Troubleshooting

DRC Log

Validate

Debug

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

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

Bibliography

Oracle
Data Guard
Broker