I haven't create a video with commentary but here are a few dgmgrl commands.
In the output "RACDB" (or "racdb") is a 2-node RAC database (managed by ClusterWare) and "STDBY" (or "stdby") is a Single Node non-RAC database not using Oracle Restart. Both are 19c (19.23)
Initial Setup :
[oracle@srv1 ~]$ dgmgrl sys
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Sat Jul 27 02:31:53 2024
Version 19.23.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Password:
Connected to "RACDB"
Connected as SYSDBA.
DGMGRL> remove configuration;
Removed configuration
DGMGRL> create configuration racdb_dg as primary database is RACDB connect identifier is RACDB;
Configuration "racdb_dg" created with primary database "racdb"
DGMGRL> add database STDBY as connect identifier is STDBY maintained as physical;
Database "stdby" added
DGMGRL> show configuration;
Configuration - racdb_dg
Protection Mode: MaxPerformance
Members:
racdb - Primary database
stdby - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
DISABLED
DGMGRL> enable configuration;
Enabled.
DGMGRL> show configuration;
Configuration - racdb_dg
Protection Mode: MaxPerformance
Members:
racdb - Primary database
Warning: ORA-16905: The member was not enabled yet.
stdby - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
WARNING (status updated 177 seconds ago)
DGMGRL> show configuration ;
Configuration - racdb_dg
Protection Mode: MaxPerformance
Members:
racdb - Primary database
Warning: ORA-16905: The member was not enabled yet.
stdby - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
WARNING (status updated 182 seconds ago)
DGMGRL>
DGMGRL> show configuration ;
Configuration - racdb_dg
Protection Mode: MaxPerformance
Members:
racdb - Primary database
stdby - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 44 seconds ago)
DGMGRL>
DGMGRL> show configuration lag;
Configuration - racdb_dg
Protection Mode: MaxPerformance
Members:
racdb - Primary database
stdby - Physical standby database
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 23 seconds ago)
DGMGRL>
In the initial setup, it takes time to actually enable the databases so we see the "ORA-16095: the member was not enabled yet" warning messages.Later, it takes time for communication of the Transport and Apply lag information so I present the lag only some significant seconds later.
Note that DataGuard is configured as "Maximum Performance" mode (not Maximum Availability as I show in my previous video)
The VALIDATE commands :
GMGRL> validate database racdb;
Database Role: Primary database
Ready for Switchover: Yes
Managed by Clusterware:
racdb: YES
DGMGRL> validate database stdby;
Database Role: Physical standby database
Primary Database: racdb
Ready for Switchover: Yes
Ready for Failover: Yes (Primary Running)
Flashback Database Status:
racdb: On
stdby: Off
Capacity Information:
Database Instances Threads
racdb 2 2
stdby 1 2
Warning: the target standby has fewer instances than the
primary database, this may impact application performance
Managed by Clusterware:
racdb: YES
stdby: NO
Current Configuration Log File Sizes:
Thread # Smallest Online Redo Smallest Standby Redo
Log File Size Log File Size
(racdb) (stdby)
1 512 MBytes 100 MBytes
2 512 MBytes 100 MBytes
DGMGRL>
Here we see some information :(1) The configuration is Ready for Swtichover (from "racdb" as Primary to "stdb")
(2) Flashback Database is configured on "racdb" but not on "stdby"
(3) Although "racdb" has two instances (two redo threads), "stdby" has only 1 instance (and redo thread) {Thus we know it is possible to have a Single Instance Standby Database for a MultiNode RAC --- I won't go into a debate / discussion on such a configuration if comments appear in response to this blog post, suffice to say that IMHO, it can make sense to do so}
(4) "racdb" is managed by ClusterWare but "stdby" is not {Thus we know that the Standby does not have to be in ClusterWare / Oracle Restart configuration}
(5) Although "racdb" has Redo Log files of 512MB, "stdby" is, unfortunately, configured with some smaller Redo Logs
Some "Verbose" Configuration Information :
DGMGRL> show configuration verbose
Configuration - racdb_dg
Protection Mode: MaxPerformance
Members:
racdb - Primary database
stdby - Physical standby database
Properties:
FastStartFailoverThreshold = '30'
OperationTimeout = '30'
TraceLevel = 'USER'
FastStartFailoverLagLimit = '30'
CommunicationTimeout = '180'
ObserverReconnect = '0'
FastStartFailoverLagGraceTime = '0'
FastStartFailoverAutoReinstate = 'TRUE'
FastStartFailoverPmyShutdown = 'TRUE'
BystandersFollowRoleChange = 'ALL'
ObserverOverride = 'FALSE'
ExternalDestination1 = ''
ExternalDestination2 = ''
PrimaryLostWriteAction = 'CONTINUE'
ConfigurationWideServiceName = 'RACDB_CFG'
FastStartFailoverLagType = 'APPLY'
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS
DGMGRL>
DGMGRL> show database verbose racdb;
Database - racdb
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
RACDB1
RACDB2
Properties:
DGConnectIdentifier = 'racdb'
ObserverConnectIdentifier = ''
FastStartFailoverTarget = ''
PreferredObserverHosts = ''
LogShipping = 'ON'
RedoRoutes = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyLagThreshold = '30'
TransportLagThreshold = '30'
TransportDisconnectedThreshold = '30'
ApplyParallel = 'AUTO'
ApplyInstances = '0'
StandbyFileManagement = ''
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '0'
LogArchiveMinSucceedDest = '0'
DataGuardSyncLatency = '0'
LogArchiveTrace = '0'
LogArchiveFormat = ''
DbFileNameConvert = ''
LogFileNameConvert = ''
ArchiveLocation = ''
AlternateLocation = ''
StandbyArchiveLocation = ''
StandbyAlternateLocation = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
LogXptStatus = '(monitor)'
SendQEntries = '(monitor)'
RecvQEntries = '(monitor)'
HostName(*)
StaticConnectIdentifier(*)
TopWaitEvents(*)
SidName(*)
(*) - Please check specific instance for the property value
Log file locations(*):
(*) - Check specific instance for log file locations.
Database Status:
SUCCESS
DGMGRL>
DGMGRL> show instance verbose "RACDB1"
Instance 'RACDB1' of database 'racdb'
PFILE:
Properties:
HostName = 'srv1'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.91)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=RACDB_DGMGRL)(INSTANCE_NAME=RACDB1)(SERVER=DEDICATED)))'
TopWaitEvents = '(monitor)'
SidName = '(monitor)'
Log file locations:
Alert log : /u01/app/oracle/diag/rdbms/racdb/RACDB1/trace/alert_RACDB1.log
Data Guard Broker log : /u01/app/oracle/diag/rdbms/racdb/RACDB1/trace/drcRACDB1.log
Instance Status:
SUCCESS
DGMGRL>
Here we see some more information :(1) Timeouts (which I've left at defaults)
(2) Fast Start Failover and Observer information (which I am not using in this configuration)
(3) That Log Transport is Async, with 0 Delay and Compression Disabled.
(3) The Alert Log and DG Broker Log file location (for "racdb", I extract the information for only the first instance, "RACDB1")
Connect Identifiers :
DGMGRL> validate static connect identifier for racdb; Oracle Clusterware on database "racdb" is available for database restart. DGMGRL> validate static connect identifier for stdby; Oracle Clusterware is not configured on database "stdby". Connecting to database "stdby" using static connect identifier "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=stdby)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=STDBY_DGMGRL)(INSTANCE_NAME=STDBY)(SERVER=DEDICATED)(STATIC_SERVICE=TRUE)))" ... Succeeded. The static connect identifier allows for a connection to database "stdby".For "racdb" being managed by ClusterWare, I had specified "RACDB" {which is the SCAN address} as the Connect Identifier (with "primary database is RACDB connect identifier is RACDB" in the first configuration command) For "stdby", not being managed by ClusterWare or Oracle Restart, it DGMGRL entry for the Listener is required in this manner :
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = STDBY)
(SID_NAME = STDBY)
(ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
)
(SID_DESC =
(GLOBAL_DBNAME = STDBY_DGMGRL)
(SID_NAME = STDBY)
(ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
(ENVS="TNS_ADMIN=/u01/app/oracle/product/19.0.0/dbhome_1/network/admin")
)
)
So, if either your Primary or Standby or both are Not managed by ClusterWare or Oracle Restart, you need to have a DGMGRL entry for the Listener !