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

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

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>

[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 ~]$
 

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>
 

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>
 

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>
 

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>
 

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>
 

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>
 

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>
 

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>
 

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>
 

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=hawk_DGMGRL
 SERVICE_NAME=hawk_stby_DGMGRL
 

[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 ~]$
 

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>