Management and Troubleshooting Features: Data Guard Command-Line Interface (DGMGRL) 19c

by Michael Dinh on Oct 1, 2020 12:00:00 AM

With Oracle Data Guard promising high availability, data protection and disaster recovery for enterprise data, it's useful to understand the Data Guard command-line interface (DGMGRL) which enables you to control and monitor a Data Guard configuration from the DGMGRL prompt or within scripts. DGMGRL offers the following capabilities:

Using DGMGRL commands to manage and monitor databases in the configuration.
Creating an observer process to continuously monitor primary and target standby databases.
Evaluating whether failover is necessary and initiating a fast-start failover when warranted.

Following the release of Oracle Database 19c I have been experimenting with 19c Oracle Data Guard command-line interface (DGMGRL) and have come up with some interesting findings for Linux: Release 19.0.0.0.0 - Version 19.3.0.0.0. Below are examples of features which may simplify management and troubleshooting: Export Data Guard broker configuration.

DGMGRL> show configuration
 
 Configuration - my_dg_config
 
  Protection Mode: MaxPerformance
  Members:
  hawk - Primary database
  hawk_stby - Physical standby database
 
 Fast-Start Failover: Disabled
 
 Configuration Status:
 SUCCESS (status updated 50 seconds ago)
 
 DGMGRL> export configuration to my_dg_config.txt
 Succeeded.
 DGMGRL>

my_dg_config.txt located at $ORACLE_BASE/diag/rdbms/$ORACLE_UNQNAME/$ORACLE_SID/trace/my_dg_config.txt

[oracle@ol7-112-dg2 ~]$ ls -l $ORACLE_BASE/diag/rdbms/$ORACLE_UNQNAME/$ORACLE_SID/trace/my_dg_config.txt
 -rw-r--r--. 1 oracle oinstall 6813 Sep 24 20:44 /u01/app/oracle/diag/rdbms/hawk_stby/hawk/trace/my_dg_config.txt
 [oracle@ol7-112-dg2 ~]$

Show lag for Data Guard configuration.

DGMGRL> show configuration lag
 
 Configuration - my_dg_config
 
  Protection Mode: MaxPerformance
  Members:
  hawk - Primary database
  hawk_stby - Physical standby database
  Transport Lag: 0 seconds (computed 3 seconds ago)
  Apply Lag: 0 seconds (computed 3 seconds ago)
 
 Fast-Start Failover: Disabled
 
 Configuration Status:
 SUCCESS (status updated 29 seconds ago)
 
 DGMGRL>

Show configuration for database role change — this will be useful with multiple standby databases.

DGMGRL> show configuration when primary is hawk_stby
 
 Configuration when hawk_stby is primary - my_dg_config
 
  Members:
  hawk_stby - Primary database
  hawk - Physical standby database
 
 DGMGRL>

Validate database spfile (don't use OS authentication).

DGMGRL> connect /
 Connected to "hawk_stby"
 Connected as SYSDG.
 
 DGMGRL> show configuration
 
 Configuration - my_dg_config
 
  Protection Mode: MaxPerformance
  Members:
  hawk_stby - Primary database
  hawk - Physical standby database
 
 Fast-Start Failover: Disabled
 
 Configuration Status:
 SUCCESS (status updated 16 seconds ago)
 
 DGMGRL> validate database hawk spfile
 Command requires a connection that uses database or external credentials.
 
 DGMGRL> connect sys@hawk
 Password:
 Connected to "hawk"
 Connected as SYSDBA.
 DGMGRL> validate database hawk spfile
 Connecting to "hawk_stby".
 Connected to "hawk_stby"
 
 Connecting to "hawk".
 Connected to "hawk"
 
 *** Parameter settings with different values: *** 
 log_archive_trace:
 hawk_stby (PRIMARY) : 0
 hawk : NOT SPECIFIED
 
 DGMGRL> validate database hawk_stby spfile
 This command cannot be used for the primary database.
 
 DGMGRL> validate database verbose hawk spfile
 Connecting to "hawk_stby".
 Connected to "hawk_stby"
 
 Connecting to "hawk".
 Connected to "hawk"
 
 Parameter Settings:
 audit_file_dest:
 hawk_stby (PRIMARY) : /u01/app/oracle/admin/hawk/adump
 hawk : /u01/app/oracle/admin/hawk/adump
 
 audit_trail:
 hawk_stby (PRIMARY) : DB
 hawk : db
 
 compatible:
 hawk_stby (PRIMARY) : 11.2.0.4.0
 hawk : 11.2.0.4.0
 
 db_block_size:
 hawk_stby (PRIMARY) : 8192
 hawk : 8192
 
 db_name:
 hawk_stby (PRIMARY) : hawk
 hawk : hawk
 
 diagnostic_dest:
 hawk_stby (PRIMARY) : /u01/app/oracle
 hawk : /u01/app/oracle
 
 dispatchers:
 hawk_stby (PRIMARY) : (PROTOCOL=TCP) (SERVICE=hawkXDB)
 hawk : (PROTOCOL=TCP) (SERVICE=hawkXDB)
 
 log_archive_trace:
 hawk_stby (PRIMARY) : 0
 hawk : NOT SPECIFIED
 
 open_cursors:
 hawk_stby (PRIMARY) : 300
 hawk : 300
 
 pga_aggregate_target:
 hawk_stby (PRIMARY) : 536870912
 hawk : 536870912
 
 processes:
 hawk_stby (PRIMARY) : 300
 hawk : 300
 
 remote_login_passwordfile:
 hawk_stby (PRIMARY) : EXCLUSIVE
 hawk : EXCLUSIVE
 
 sga_target:
 hawk_stby (PRIMARY) : 1610612736
 hawk : 1610612736
 
 undo_tablespace:
 hawk_stby (PRIMARY) : UNDOTBS1
 hawk : UNDOTBS1
 
 DGMGRL>

Validate static connect identifier.

DGMGRL> validate static connect identifier for all
 
 Oracle Clusterware is not configured on database "hawk".
 Connecting to database "hawk" using static connect identifier "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ol7-112-dg1.local)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=hawk_DGMGRL)(INSTANCE_NAME=hawk)(SERVER=DEDICATED)(STATIC_SERVICE=TRUE)))" ...
 Succeeded.
 The static connect identifier allows for a connection to database "hawk".
 
 Oracle Clusterware is not configured on database "hawk_stby".
 Connecting to database "hawk_stby" using static connect identifier "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ol7-112-dg2.local)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=hawk_stby_DGMGRL)(INSTANCE_NAME=hawk)(SERVER=DEDICATED)(STATIC_SERVICE=TRUE)))" ...
 
 Succeeded.
 The static connect identifier allows for a connection to database "hawk_stby".
 
 DGMGRL>

Validate network configuration FAILED and SUCCEEDED. This may be due to database being upgraded from 11.2 to 19.3.

DGMGRL> validate network configuration for all
 
 Connecting to instance "hawk" on database "hawk" ...
 Unable to connect to database using (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=ol7-112-dg1.local)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SID=hawk)(CID=(PROGRAM=dgmgrl)(HOST=ol7-112-dg2.local)(USER=oracle))(INSTANCE_NAME=hawk)))
 ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
 
 Failed.
 Warning: Cannot connect to instance "hawk" on database "hawk".
 
 Connecting to instance "hawk" on database "hawk_stby" ...
 Unable to connect to database using (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=ol7-112-dg2.local)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SID=hawk)(CID=(PROGRAM=dgmgrl)(HOST=ol7-112-dg2.local)(USER=oracle))(INSTANCE_NAME=hawk)))
 ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
 
 Failed.
 Warning: Cannot connect to instance "hawk" on database "hawk_stby".
 
 =======================================================
 
 Oracle Clusterware is not configured on database "hawk".
 Connecting to database "hawk" using static connect identifier "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ol7-112-dg1.local)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=hawk_DGMGRL)(INSTANCE_NAME=hawk)(SERVER=DEDICATED)(STATIC_SERVICE=TRUE)))" ...
 
 Succeeded.
 The static connect identifier allows for a connection to database "hawk".
 
 Oracle Clusterware is not configured on database "hawk_stby".
 Connecting to database "hawk_stby" using static connect identifier "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ol7-112-dg2.local)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=hawk_stby_DGMGRL)(INSTANCE_NAME=hawk)(SERVER=DEDICATED)(STATIC_SERVICE=TRUE)))" ...
 
 Succeeded.
 The static connect identifier allows for a connection to database "hawk_stby".
 
 DGMGRL>

Validate primary and standby databases.

DGMGRL> connect sys@hawk_stby
 Password:
 Connected to "hawk_stby"
 Connected as SYSDBA.
 
 DGMGRL> validate database hawk
 
  Database Role: Primary database
 
  Ready for Switchover: Yes
 
  Managed by Clusterware:
  hawk: NO
  Validating static connect identifier for the primary database hawk...
  The static connect identifier allows for a connection to database "hawk".
 
 DGMGRL> validate database hawk_stby
 
  Database Role: Physical standby database
  Primary Database: hawk
 
  Ready for Switchover: Yes
  Ready for Failover: Yes (Primary Running)
 
  Managed by Clusterware:
  hawk : NO
  hawk_stby: NO
  Validating static connect identifier for the primary database hawk...
  The static connect identifier allows for a connection to database "hawk".
 
 DGMGRL>

Perform switchover completed successfully even with failed network configuration.

DGMGRL> switchover to hawk_stby
 Performing switchover NOW, please wait...
 New primary database "hawk_stby" is opening...
 Operation requires start up of instance "hawk" on database "hawk"
 Starting instance "hawk"...
 Connected to an idle instance.
 ORACLE instance started.
 Connected to "hawk"
 Database mounted.
 Connected to "hawk"
 Switchover succeeded, new primary is "hawk_stby"
 DGMGRL>

Review configuration.

DGMGRL> show configuration lag
 
 Configuration - my_dg_config
 
  Protection Mode: MaxPerformance
  Members:
  hawk_stby - Primary database
  hawk - Physical standby database
  Error: ORA-1034: ORACLE not available
  Transport Lag: 0 seconds (computed 15 seconds ago)
  Apply Lag: (unknown)
 
 Fast-Start Failover: Disabled
 
 Configuration Status:
 ERROR (status updated 268980 seconds ago)
 
 DGMGRL> show configuration
 
 Configuration - my_dg_config
 
  Protection Mode: MaxPerformance
  Members:
  hawk_stby - Primary database
  hawk - Physical standby database
  Warning: ORA-16854: apply lag could not be determined
 
 Fast-Start Failover: Disabled
 
 Configuration Status:
 WARNING (status updated 19 seconds ago)
 
 DGMGRL> /
 
 Configuration - my_dg_config
 
  Protection Mode: MaxPerformance
  Members:
  hawk_stby - Primary database
  hawk - Physical standby database
  Warning: ORA-16854: apply lag could not be determined
 
 Fast-Start Failover: Disabled
 
 Configuration Status:
 WARNING (status updated 41 seconds ago)
 
 DGMGRL> /
 
 Configuration - my_dg_config
 
  Protection Mode: MaxPerformance
  Members:
  hawk_stby - Primary database
  hawk - Physical standby database
 
 Fast-Start Failover: Disabled
 
 Configuration Status:
 SUCCESS (status updated 19 seconds ago)
 
 DGMGRL>

Avoid using OS authentication when there is configuration or database role change to avoid ORA-01017.

DGMGRL> connect /
 Connected to "hawk_stby"
 Connected as SYSDG.
 DGMGRL> validate database hawk
 
  Database Role: Physical standby database
  Primary Database: hawk_stby
 
  Ready for Switchover: Yes
  Ready for Failover: Yes (Primary Running)
 
  Managed by Clusterware:
  hawk_stby: NO
  hawk : NO
  Validating static connect identifier for the primary database hawk_stby...
 
 *** ORA-01017: invalid username/password; logon denied ***
 
  Warning: Ensure primary database's StaticConnectIdentifier property
  is configured properly so that the primary database can be restarted
  by DGMGRL after switchover
 
  Log Files Cleared:
  hawk_stby Standby Redo Log Files: Cleared
  hawk Online Redo Log Files: Not Cleared
  hawk Standby Redo Log Files: Available
 
 DGMGRL> validate database hawk_stby
 
  Database Role: Primary database
 
  Ready for Switchover: Yes
 
  Managed by Clusterware:
  hawk_stby: NO
  Validating static connect identifier for the primary database hawk_stby...
 
 *** ORA-01017: invalid username/password; logon denied ***
 
  Warning: Ensure primary database's StaticConnectIdentifier property
  is configured properly so that the primary database can be restarted
  by DGMGRL after switchover
 
 DGMGRL>

Alternative for manual validation of StaticConnectIdentifier. Find StaticConnectIdentifier for databases.

DGMGRL> show instance hawk StaticConnectIdentifier
 Two or more instances have the name "hawk"
 
 DGMGRL> show instance hawk on database hawk
 
 Instance 'hawk' of database 'hawk'
 
 
 Instance Status:
 SUCCESS
 
 DGMGRL> show instance hawk on database hawk_stby
 
 Instance 'hawk' of database 'hawk_stby'
 
 
 Instance Status:
 SUCCESS
 
 DGMGRL> show instance hawk StaticConnectIdentifier on database hawk
  StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ol7-112-dg1.local)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=hawk_DGMGRL)(INSTANCE_NAME=hawk)(SERVER=DEDICATED)))'
 
 DGMGRL> show instance hawk StaticConnectIdentifier on database hawk_stby
  StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ol7-112-dg2.local)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=hawk_stby_DGMGRL)(INSTANCE_NAME=hawk)(SERVER=DEDICATED)))'
 DGMGRL>

SERVICE_NAME has _DGMGRL since database was upgraded from 11.2.

SERVICE_NAME=hawk_DGMGRL
 SERVICE_NAME=hawk_stby_DGMGRL

Check listener and service hawk_stby_DGMGRL is registered.

[oracle@ol7-112-dg2 ~]$ lsnrctl status
 
 LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 24-SEP-2020 21:41:09
 
 Copyright (c) 1991, 2019, Oracle. All rights reserved.
 
 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ol7-112-dg2.local)(PORT=1521)))
 STATUS of the LISTENER
 ------------------------
 Alias LISTENER
 Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
 Start Date 21-SEP-2020 18:36:16
 Uptime 3 days 3 hr. 4 min. 53 sec
 Trace Level off
 Security ON: Local OS Authentication
 SNMP OFF
 Listener Parameter File /u01/app/oracle/product/19.3.0.0/dbhome_1/network/admin/listener.ora
 Listener Log File /u01/app/oracle/diag/tnslsnr/ol7-112-dg2/listener/alert/log.xml
 Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ol7-112-dg2.local)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
 Services Summary...
 Service "hawkXDB" has 1 instance(s).
  Instance "hawk", status READY, has 1 handler(s) for this service...
 Service "hawk_stby" has 2 instance(s).
  Instance "hawk", status UNKNOWN, has 1 handler(s) for this service...
  Instance "hawk", status READY, has 1 handler(s) for this service...
 Service "hawk_stby_DGMGRL" has 1 instance(s).
  Instance "hawk", status UNKNOWN, has 1 handler(s) for this service...
 The command completed successfully
 [oracle@ol7-112-dg2 ~]$

Test connectivity using info from StaticConnectIdentifier completed successfully.

SQL> connect sys@'(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ol7-112-dg1.local)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=hawk_DGMGRL)(INSTANCE_NAME=hawk)(SERVER=DEDICATED)))' as sysdba
 Enter password:
 Connected.
 
 SQL> select name,database_role from v$database;
 
 NAME DATABASE_ROLE
 --------- ----------------
 HAWK PHYSICAL STANDBY
 
 SQL>
 
 SQL> connect sys@'(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ol7-112-dg2.local)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=hawk_stby_DGMGRL)(INSTANCE_NAME=hawk)(SERVER=DEDICATED)))' as sysdba
 Enter password:
 Connected.
 SQL> select name,database_role from v$database;
 
 NAME DATABASE_ROLE
 --------- ----------------
 HAWK PRIMARY
 
 SQL>

I hope you find this information useful to let you experiment on your own. I'd also love to hear about any features or tips you've found, and I'd be happy to answer any of your questions.

Topics: Automation Site Reliability Engineering Technical Track Infrastructure Graphite

No Comments Yet

Let us know what you think

Share this

Management and Troubleshooting Features: Data Guard Command-Line Interface (DGMGRL) 19c

Share this

No Comments Yet

Subscribe by email