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

Posted in: Technical Track

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.

email

Authors

Interested in working with Michael? Schedule a tech call.

No comments

Leave a Reply

Your email address will not be published. Required fields are marked *