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
[o[email protected] ~]$ 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 [[email protected] ~]$
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 [email protected]
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 [email protected]_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.
[[email protected] ~]$ 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 [[email protected] ~]$
Test connectivity using info from StaticConnectIdentifier completed successfully.
SQL> connect [email protected]'(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 [email protected]'(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.
No comments