C:\Users\Administrator>dgmgrl /@cdb1
DGMGRL for 64-bit Windows: Release 19.0.0.0.0 - Production on Tue Mar 28 02:38:55 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "cdb1"
Connected as SYSDG.
DGMGRL> show configuration verbose
Configuration - cdb1_dg_conf
Protection Mode: MaxPerformance
Members:
cdb1 - Primary database
cdb1s - Physical standby database
Properties:
FastStartFailoverThreshold = '30'
OperationTimeout = '30'
TraceLevel = 'USER'
FastStartFailoverLagLimit = '30'
CommunicationTimeout = '180'
ObserverReconnect = '0'
FastStartFailoverAutoReinstate = 'TRUE'
FastStartFailoverPmyShutdown = 'TRUE'
BystandersFollowRoleChange = 'ALL'
ObserverOverride = 'FALSE'
ExternalDestination1 = ''
ExternalDestination2 = ''
PrimaryLostWriteAction = 'CONTINUE'
ConfigurationWideServiceName = 'cdb1_CFG'
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS
DGMGRL> validate database cdb1
Database Role: Primary database
Ready for Switchover: Yes
Flashback Database Status:
cdb1: Off
Managed by Clusterware:
cdb1: NO
Validating static connect identifier for the primary database cdb1...
The static connect identifier allows for a connection to database "cdb1".
DGMGRL> validate database cdb1s
Database Role: Physical standby database
Primary Database: cdb1
Ready for Switchover: Yes
Ready for Failover: Yes (Primary Running)
Flashback Database Status:
cdb1 : Off
cdb1s: Off
Managed by Clusterware:
cdb1 : NO
cdb1s: NO
Validating static connect identifier for the primary database cdb1...
The static connect identifier allows for a connection to database "cdb1".
DGMGRL> validate static connect identifier for all
Oracle Clusterware is not configured on database "cdb1".
Connecting to database "cdb1" using static connect identifier "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.50.100)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=CDB1_DGMGRL)(INSTANCE_NAME=CDB1)(SERVER=DEDICATED)(STATIC_SERVICE=TRUE)))" ...
Succeeded.
The static connect identifier allows for a connection to database "cdb1".
Oracle Clusterware is not configured on database "cdb1s".
Connecting to database "cdb1s" using static connect identifier "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.50.200)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=CDB1S_DGMGRL)(INSTANCE_NAME=CDB1S)(SERVER=DEDICATED)(STATIC_SERVICE=TRUE)))" ...
Unable to connect to database using (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.50.200)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=CDB1S_DGMGRL)(INSTANCE_NAME=CDB1S)(SERVER=DEDICATED)(STATIC_SERVICE=TRUE)))
ORA-12518: TNS:listener could not hand off client connection
Failed.
DGMGRL>
strange behavior observed:
- switchover to cdb1s was ok, but
- switchover to cdb1 give the TNS:listener could not hand off client connection!
I have already the static entries for both of my listener.ora
----pri node listener.ora entries
# listener.ora Network Configuration File: C:\oracle\RDBMS\19.0.0\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER_CDB1=
(SID_LIST=
(SID_DESC=
(SID_NAME=CDB1)
(GLOBAL_DBNAME=CDB1_DGMGRL)
(ORACLE_HOME=C:\oracle\RDBMS\19.0.0)
)
)
LISTENER_CDB1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.50.100)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
------- standby node listener status
C:\Users\Administrator>lsnrctl status listener_cdb1
LSNRCTL for 64-bit Windows: Version 19.0.0.0.0 - Production on 28-MAR-2023 02:12:08
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.50.200)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias listener_cdb1
Version TNSLSNR for 64-bit Windows: Version 19.0.0.0.0 - Production
Start Date 28-MAR-2023 01:18:47
Uptime 0 days 0 hr. 53 min. 21 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File C:\oracle\RDBMS\19.0.0\network\admin\listener.ora
Listener Log File C:\oracle\diag\tnslsnr\cdb02\listener_cdb1\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.50.200)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
Services Summary...
Service "CDB1S" has 1 instance(s).
Instance "cdb1", status READY, has 1 handler(s) for this service...
Service "CDB1S_DGMGRL" has 1 instance(s).
Instance "CDB1S", status UNKNOWN, has 1 handler(s) for this service...
Service "cdb1_CFG" has 1 instance(s).
Instance "cdb1", status READY, has 1 handler(s) for this service...
The command completed successfully
--- standby node listener.ora entries
# listener.ora Network Configuration File: C:\oracle\RDBMS\19.0.0\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER_CDB1=
(SID_LIST=
(SID_DESC=
(SID_NAME=CDB1S)
(GLOBAL_DBNAME=CDB1S_DGMGRL)
(ORACLE_HOME=C:\oracle\RDBMS\19.0.0)
)
)
--- pri node tnsnames.ora entries
# tnsnames.ora Network Configuration File: C:\oracle\RDBMS\19.0.0\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.
LISTENER_CDB1 =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.50.100)(PORT = 1521))
CDB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.50.100)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = cdb1)
)
)
PDB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.50.100)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdb1)
)
)
CDB1S =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.50.200)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = cdb1s)
)
)
---- standby node tnsname.ora entries
# tnsnames.ora Network Configuration File: C:\oracle\RDBMS\19.0.0\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.
LISTENER_CDB1 =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.50.200)(PORT = 1521))
CDB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.50.100)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME=cdb1)
)
)
PDB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.50.200)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME=pdb1)
)
)
CDB1S =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.50.200)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME=cdb1s)
)
)
--- standby dg infor for its staticconnectidentifier
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.50.100)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=CDB1_DGMGRL)(INSTANCE_NAME=CDB1)(SERVER=DEDICATED)))'
-- pri dg infor for its staticconnectidentifier
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.50.100)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=CDB1_DGMGRL)(INSTANCE_NAME=CDB1)(SERVER=DEDICATED)))'
I believed I have input the required static entries for the dgmgrl for both Pri/ standby but when need to switchover back from standby node (the now Pri node) to Pri node (the previous old Pri Node) ,somehow the dgmrl (broker cannot connect to the standby node to mount it ……. .
I am aware that the standby node will be shutdown but the static _dgmgrl should be able to connect to it without having standby to be mounted.
this is the status of the listener_cdb1 on standby when the database is shut:
C:\Users\Administrator>lsnrctl status listener_cdb1
LSNRCTL for 64-bit Windows: Version 19.0.0.0.0 - Production on 28-MAR-2023 02:37:19
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.50.200)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias listener_cdb1
Version TNSLSNR for 64-bit Windows: Version 19.0.0.0.0 - Production
Start Date 28-MAR-2023 02:28:17
Uptime 0 days 0 hr. 9 min. 3 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File C:\oracle\RDBMS\19.0.0\network\admin\listener.ora
Listener Log File C:\oracle\diag\tnslsnr\cdb02\listener_cdb1\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.50.200)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
Services Summary...
Service "CDB1S_DGMGRL" has 1 instance(s).
Instance "CDB1S", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
so CDB1S_DGMRL is there to connect to the shutdown standby and bring it up?