Listener Configuration ,TNS Configuration in DGMGRL.
TYPE 1:
On Standby & primary Database.
[oraprd@prd1 admin]$ cat listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENERPRMDB =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = LAPPRIMDB_DGMGRL.hana.com)
[oraprd@prd1 admin]$ cat listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENERPRMDB =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = LAPPRIMDB_DGMGRL.hana.com)
(ORACLE_HOME = /oracle/PRD/19)
(SID_NAME = LAPPRIMDB)
)
)
ADR_BASE_LISTENERPRMDB = /oracle/PRD
LISTENERPRMDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.18)(PORT = 1526))
)
(SID_NAME = LAPPRIMDB)
)
)
ADR_BASE_LISTENERPRMDB = /oracle/PRD
LISTENERPRMDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.18)(PORT = 1526))
)
On Standby
[oraprd@prd2 admin]$ cat listener.ora
# listener.ora Network Configuration File: /oracle/PRD/19/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENERDRDB =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = LAPDRDB_DGMGRL.hana.com)
(ORACLE_HOME = /oracle/PRD/19)
(SID_NAME = LAPDRDB)
)
)
LISTENERDRDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.19)(PORT = 1526))
)
[oraprd@prd2 STANDBY]$ lsnrctl status LISTENERDRDB
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 31-MAY-2021 23:25:13
Copyright (c) 1991, 2020, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.19)(PORT=1526)))
STATUS of the LISTENER
------------------------
Alias LISTENERDRDB
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 31-MAY-2021 23:22:14
Uptime 0 days 0 hr. 2 min. 58 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/PRD/19/network/admin/listener.ora
Listener Log File /oracle/PRD/diag/tnslsnr/prd2/listenerdrdb/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.19)(PORT=1526)))
Services Summary...
Service "LAPDRDB.hana.com" has 1 instance(s).===> Its come from Service_name parameter from the database
Instance "LAPDRDB", status READY, has 1 handler(s) for this service...
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 31-MAY-2021 23:25:13
Copyright (c) 1991, 2020, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.19)(PORT=1526)))
STATUS of the LISTENER
------------------------
Alias LISTENERDRDB
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 31-MAY-2021 23:22:14
Uptime 0 days 0 hr. 2 min. 58 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/PRD/19/network/admin/listener.ora
Listener Log File /oracle/PRD/diag/tnslsnr/prd2/listenerdrdb/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.19)(PORT=1526)))
Services Summary...
Service "LAPDRDB.hana.com" has 1 instance(s).===> Its come from Service_name parameter from the database
Instance "LAPDRDB", status READY, has 1 handler(s) for this service...
==================================================================
Service "LAPDRDB_DGMGRL.hana.com" has 1 instance(s).===> Its defined with GLOBAL_DB_NAME parameter in Listener for static. and for DGMGRL
DB_UNIQUE_NAME.DGMGRL.hana.com
Instance "LAPDRDB", status UNKNOWN, has 1 handler(s) for this
Instance "LAPDRDB", status UNKNOWN, has 1 handler(s) for this
===================================================================
service...
Service "LAP_CFG.hana.com" has 1 instance(s). ====>its Come automatic with DR Setup
Instance "LAPDRDB", status READY, has 1 handler(s) for this service...
The command completed successfully
Service "LAP_CFG.hana.com" has 1 instance(s). ====>its Come automatic with DR Setup
Instance "LAPDRDB", status READY, has 1 handler(s) for this service...
The command completed successfully
=======================================================================
On Primary Dataabase.
[oraprd@prd1 STANDBY]$ lsnrctl status LISTENERPRMDB
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 31-MAY-2021 23:23:38
Copyright (c) 1991, 2020, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.18)(PORT=1526)))
STATUS of the LISTENER
------------------------
Alias LISTENERPRMDB
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 31-MAY-2021 23:22:24
Uptime 0 days 0 hr. 1 min. 14 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/PRD/19/network/admin/listener.ora
Listener Log File /oracle/PRD/diag/tnslsnr/prd1/listenerprmdb/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.18)(PORT=1526)))
Services Summary...
Service "LAP.hana.com" has 1 instance(s).
Instance "LAPPRIMDB", status READY, has 1 handler(s) for this service... its coming from Service_name Parameter
===============================================================
Service "LAPPRIMDB.hana.com" has 1 instance(s).
Instance "LAPPRIMDB", status READY, has 1 handler(s) for this service... its local_listener were set.
==================================================================
Service "LAPPRIMDB_DGMGRL.hana.com" has 1 instance(s).
Instance "LAPPRIMDB", status UNKNOWN, has 1 handler(s) for this service...=====> This is the Static setting we have done on the Listener file. DB_UNIQUE_NAME_DGMGRL.hana.com defined in Lisetner.ora
=================================================================
Service "LAPXDB.hana.com" has 1 instance(s).
Instance "LAPPRIMDB", status READY, has 1 handler(s) for this service...Also Database automatic Service
===================================================================
Service "LAP_CFG.hana.com" has 1 instance(s).
Instance "LAPPRIMDB", status READY, has 1 handler(s) for this service... Its by automatic by oracle
The command completed successfully
[oraprd@prd1 STANDBY]$
[oraprd@prd1 STANDBY]$ cat /oracle/PRD/19/network/admin/tnsnames.ora
LAPDRDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.19)(PORT = 1526))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = LAPDRDB_DGMGRL.hana.com) ===> this Service Name is in Listener Configuration on Dr side
)
)
LAPPRIMDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.18)(PORT = 1526))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = LAPPRIMDB_DGMGRL.hana.com)===>this Service Name is in Listener Configuration on primary side.
)
)
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.18)(PORT=1526))(CONNECT_DATA=(SERVICE_NAME=LAPPRIMDB_DGMGRL.hana.com)(INSTANCE_NAME=LAPPRIMDB)(SERVER=DEDICATED)))'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.19)(PORT=1526))(CONNECT_DATA=(SERVICE_NAME=LAPDRDB_DGMGRL.hana.com)(INSTANCE_NAME=LAPDRDB)(SERVER=DEDICATED)))'
Here the SERVICE_NAME=LAPDRDB_DGMGRL.hana.com is same Everywhere so there is no problem for DR Configuration and switchover Configuration also.
[oraprd@prd1 STANDBY]$ ./validate_dg.sh
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Mon May 31 23:45:28 2021
Version 19.8.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> Connected to "LAPPRIMDB"
Connected as SYSDBA.
DGMGRL>
Configuration - DRENV
Protection Mode: MaxPerformance
Members:
LAPPRIMDB - Primary database
lapdrdb - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 60 seconds ago)
DGMGRL>
Database Role: Primary database
Ready for Switchover: Yes
Flashback Database Status:
LAPPRIMDB: Off
Managed by Clusterware:
LAPPRIMDB: NO
Validating static connect identifier for the primary database LAPPRIMDB...
The static connect identifier allows for a connection to database "LAPPRIMDB".
DGMGRL> DGMGRL> [oraprd@prd1 STANDBY]$
[oraprd@prd1 STANDBY]$
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Mon May 31 23:45:28 2021
Version 19.8.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> Connected to "LAPPRIMDB"
Connected as SYSDBA.
DGMGRL>
Configuration - DRENV
Protection Mode: MaxPerformance
Members:
LAPPRIMDB - Primary database
lapdrdb - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 60 seconds ago)
DGMGRL>
Database Role: Primary database
Ready for Switchover: Yes
Flashback Database Status:
LAPPRIMDB: Off
Managed by Clusterware:
LAPPRIMDB: NO
Validating static connect identifier for the primary database LAPPRIMDB...
The static connect identifier allows for a connection to database "LAPPRIMDB".
DGMGRL> DGMGRL> [oraprd@prd1 STANDBY]$
[oraprd@prd1 STANDBY]$
Note: Here pfile,Spfile and orapassword file having name like DB_UNIQUE_NAME. for eg. spfileLAPPRIMDB.ora, orapwLAPPRIMDB
TYPE 2:
On primary:-
[oraprd@prd1 STANDBY]$ lsnrctl status LISTENERPRMDB
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 01-JUN-2021 00:20:10
Copyright (c) 1991, 2020, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.18)(PORT=1526)))
STATUS of the LISTENER
------------------------
Alias LISTENERPRMDB
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 31-MAY-2021 23:22:24
Uptime 0 days 0 hr. 57 min. 45 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/PRD/19/network/admin/listener.ora
Listener Log File /oracle/PRD/diag/tnslsnr/prd1/listenerprmdb/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.18)(PORT=1526)))
Services Summary...
Service "LAP.hana.com" has 1 instance(s).
Instance "LAPPRIMDB", status READY, has 1 handler(s) for this service...
================================================================
Service "LAPPRIMDB.hana.com" has 1 instance(s).
Instance "LAPPRIMDB", status READY, has 1 handler(s) for this service...
================================================================
Service "LAPPRIMDB_DGMGRL" has 1 instance(s).
Instance "LAPPRIMDB", status UNKNOWN, has 1 handler(s) for this service...===> This is Static Entry in Listener configuration.
DB_UNIQUE_DGMGRL value we set.we removed domain name
==============================================================
Service "LAPXDB.hana.com" has 1 instance(s).
Instance "LAPPRIMDB", status READY, has 1 handler(s) for this service...
================================================================
Service "LAP_CFG.hana.com" has 1 instance(s).
Instance "LAPPRIMDB", status READY, has 1 handler(s) for this service...
=================================================================
The command completed successfully
[oraprd@prd1 STANDBY]$
On STandby Database
[oraprd@prd2 STANDBY]$ lsnrctl status LISTENERDRDB
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 01-JUN-2021 00:11:42
Copyright (c) 1991, 2020, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.19)(PORT=1526)))
STATUS of the LISTENER
------------------------
Alias LISTENERDRDB
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 31-MAY-2021 23:22:14
Uptime 0 days 0 hr. 49 min. 28 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/PRD/19/network/admin/listener.ora
Listener Log File /oracle/PRD/diag/tnslsnr/prd2/listenerdrdb/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.19)(PORT=1526)))
Services Summary...
Service "LAPDRDB.hana.com" has 1 instance(s).
Instance "LAPDRDB", status READY, has 1 handler(s) for this service...
================================================================
Service "LAPDRDB_DGMGRL" has 1 instance(s).
Instance "LAPDRDB", status UNKNOWN, has 1 handler(s) for this service... This is Static Entry we are using in Listener. DB_UNIQUE_DGMGRl.
===============================================================
Service "LAP_CFG.hana.com" has 1 instance(s).
Instance "LAPDRDB", status READY, has 1 handler(s) for this service...
===============================================================
The command completed successfully
But
StaticConnectIdentifier= '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.18)(PORT=1526))(CONNECT_DATA=(SERVICE_NAME=LAPPRIMDB_DGMGRL.hana.com)(INSTANCE_NAME=LAPPRIMDB)(SERVER=DEDICATED)))'
StaticConnectIdentifier= '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.19)(PORT=1526))(CONNECT_DATA=(SERVICE_NAME=LAPDRDB_DGMGRL.hana.com)(INSTANCE_NAME=LAPDRDB)(SERVER=DEDICATED)))'
But Service_name in StaticConnectIdentifier is SERVICE_NAME=LAPDRDB_DGMGRL.hana.com & But in tnsname & listener.ora file is SERVICE_NAME=LAPPRIMDB_DGMGRL
LAPPRIMDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.18)(PORT = 1526))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME=LAPPRIMDB_DGMGRL)===>
)
)
LAPDRDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.19)(PORT = 1526))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = LAPDRDB_DGMGRL)=====>
)
)
Here the Service name is different at StaticConnectIdentifier, still let see the Validate dg.
Validating static connect identifier for the primary database LAPPRIMDB...
Unable to connect to database using (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.18)(PORT=1526))(CONNECT_DATA=(SERVICE_NAME=LAPPRIMDB_DGMGRL.hana.com)(INSTANCE_NAME=LAPPRIMDB)(SERVER=DEDICATED)(STATIC_SERVICE=TRUE)))
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Here We got the error .. Now Here is two Solution, First Used the Case 1 solution or either update the staticConnectIdentifier.
edit database lapprimdb set property staticconnectidentifier='(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.18)(PORT=1526))(CONNECT_DATA=(SERVICE_NAME=LAPPRIMDB_DGMGRL)(INSTANCE_NAME=LAPPRIMDB)(SERVER=DEDICATED)))'
edit database lapdrdb set property staticconnectidentifier='(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.18)(PORT=1526))(CONNECT_DATA=(SERVICE_NAME=LAPDRDB_DGMGRL)(INSTANCE_NAME=LAPDRDB)(SERVER=DEDICATED)))'
Look Validate Command Work. So This way we can resolve the issue.
[oraprd@prd1 STANDBY]$ ./validate_dg.sh
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Tue Jun 1 00:47:55 2021
Version 19.8.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> Connected to "LAPPRIMDB"
Connected as SYSDBA.
DGMGRL>
Configuration - DRENV
Protection Mode: MaxPerformance
Members:
LAPPRIMDB - Primary database
lapdrdb - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 12 seconds ago)
DGMGRL>
Database Role: Primary database
Ready for Switchover: Yes
Flashback Database Status:
LAPPRIMDB: Off
Managed by Clusterware:
LAPPRIMDB: NO
Validating static connect identifier for the primary database LAPPRIMDB...
The static connect identifier allows for a connection to database "LAPPRIMDB".
DGMGRL> DGMGRL> [oraprd@prd1 STANDBY]$
[oraprd@prd1 STANDBY]$
[oraprd@prd1 STANDBY]$
Till Now we have used Service_name in Tns name. So in Third Case lets Use only Sid
Note: Here pfile,Spfile and orapassword file having name like DB_UNIQUE_NAME. for eg. spfileLAPPRIMDB.ora, orapwLAPPRIMDB
TYPE 3:
[oraprd@prd1 admin]$ cat listener.ora
# listener.ora Network Configuration File: /oracle/PRD/19/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENERPRMDB =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = LAPPRIMDB_DGMGRL)===>Removed Domain_name
(ORACLE_HOME = /oracle/PRD/19)
(SID_NAME = LAPPRIMDB)
)
)
ADR_BASE_LISTENERPRMDB = /oracle/PRD
LISTENERPRMDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.18)(PORT = 1526))
)
ADR_BASE_LISTENERPRD = /oracle/PRD
[oraprd@prd2 admin]$ cat listener.ora
# listener.ora Network Configuration File: /oracle/PRD/19/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENERDRDB =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = LAPDRDB_DGMGRL)===>Removed Domain_name
(ORACLE_HOME = /oracle/PRD/19)
(SID_NAME = LAPDRDB)
)
)
LISTENERDRDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.19)(PORT = 1526))
)
ADR_BASE_LISTENERDRDB = /oracle/PRD
LAPPRIMDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.18)(PORT = 1526))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID=LAPPRIMDB)===> I Used SID instead of Service_name
)
)
LAPDRDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.19)(PORT = 1526))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = LAPDRDB)===> I Used SID instead of Servie_name
)
)
Check Listener Status on Primary and Standby Node.
[oraprd@prd2 admin]$ lsnrctl status LISTENERDRDB
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 01-JUN-2021 08:05:29
Copyright (c) 1991, 2020, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.19)(PORT=1526)))
STATUS of the LISTENER
------------------------
Alias LISTENERDRDB
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 01-JUN-2021 07:54:51
Uptime 0 days 0 hr. 10 min. 37 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/PRD/19/network/admin/listener.ora
Listener Log File /oracle/PRD/diag/tnslsnr/prd2/listenerdrdb/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.19)(PORT=1526)))
Services Summary...
Service "LAPDRDB.hana.com" has 1 instance(s).
Instance "LAPDRDB", status READY, has 1 handler(s) for this service...
=====================================================================
Service "LAPDRDB_DGMGRL" has 1 instance(s).
Instance "LAPDRDB", status UNKNOWN, has 1 handler(s) for this service...====> SID used to connect.
===============================================================
Service "LAP_CFG.hana.com" has 1 instance(s).
Instance "LAPDRDB", status READY, has 1 handler(s) for this service...
===========================================================
The command completed successfully
[oraprd@prd2 admin]$
Now Lets Check Static Connector in DG.
StaticConnectIdentifier= '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.18)(PORT=1526))(CONNECT_DATA=(SERVICE_NAME=LAPPRIMDB_DGMGRL.hana.com)(INSTANCE_NAME=LAPPRIMDB)(SERVER=DEDICATED)))'
StaticConnectIdentifier= '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.19)(PORT=1526))(CONNECT_DATA=(SERVICE_NAME=LAPDRDB_DGMGRL.hana.com)(INSTANCE_NAME=LAPDRDB)(SERVER=DEDICATED)))'
Here SERVICE_NAME=LAPPRIMDB_DGMGRL.hana.com so will get error during the Switchover , Lets verify it.
[oraprd@prd2 STANDBY]$ sh validate_dg.sh
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Tue Jun 1 08:08:54 2021
Version 19.8.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> Connected to "LAPDRDB"
Connected as SYSDBA.
DGMGRL>
Configuration - DRENV
Protection Mode: MaxPerformance
Members:
LAPPRIMDB - Primary database
lapdrdb - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 30 seconds ago)
DGMGRL>
Database Role: Physical standby database
Primary Database: LAPPRIMDB
Ready for Switchover: Yes
Ready for Failover: Yes (Primary Running)
Flashback Database Status:
LAPPRIMDB: Off
lapdrdb : Off
Managed by Clusterware:
LAPPRIMDB: NO
lapdrdb : NO
Validating static connect identifier for the primary database LAPPRIMDB...
Unable to connect to database using (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.18)(PORT=1526))(CONNECT_DATA=(SERVICE_NAME=LAPPRIMDB_DGMGRL.hana.com)(INSTANCE_NAME=LAPPRIMDB)(SERVER=DEDICATED)(STATIC_SERVICE=TRUE)))
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
So To resolve this issue we have to update the StaticConnectIdentifier in DG.
[oraprd@prd2 STANDBY]$ cat updat_staticConnection.sh
upd()
{
dgmgrl <<eof
connect sys/abc#123@lapdrdb
edit database lapprimdb set property StaticConnectIdentifier='lapprimdb';
edit database lapdrdb set property StaticConnectIdentifier='lapdrdb';
eof
}
upd1()
{
dgmgrl <<eof
connect sys/abc#123@lapprimdb
edit database lapdrdb set property StaticConnectIdentifier='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=prd2.hana.com)(PORT=1526))(CONNECT_DATA=(SERVICE_NAME=LAPDRDB_DGMGRL)(INSTANCE_NAME=LAPDRDB)(SERVER=DEDICATED)))';
edit database lapprimdb set property StaticConnectIdentifier='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=prd1.hana.com)(PORT=1526))(CONNECT_DATA=(SERVICE_NAME=LAPPRIMDB_DGMGRL)(INSTANCE_NAME=LAPPRIMDB)(SERVER=DEDICATED)))';
eof
}
upd1
upd
You can use the upd or upd1 shell script to update the staticConnection.
DGMGRL> validate database lapprimdb;
Database Role: Primary database
Ready for Switchover: Yes ====> Ready for Switchover
Flashback Database Status:
LAPPRIMDB: Off
Managed by Clusterware:
LAPPRIMDB: NO
Validating static connect identifier for the primary database LAPPRIMDB...
The static connect identifier allows for a connection to database "LAPPRIMDB".
DGMGRL> validate database lapdrdb;
Database Role: Physical standby database
Primary Database: LAPPRIMDB
Ready for Switchover: Yes
Ready for Failover: Yes (Primary Running)====.Ready for Switchover
Flashback Database Status:
LAPPRIMDB: Off
lapdrdb : Off
Managed by Clusterware:
LAPPRIMDB: NO
lapdrdb : NO
Validating static connect identifier for the primary database LAPPRIMDB...
The static connect identifier allows for a connection to database "LAPPRIMDB".
Current Log File Groups Configuration:
Thread # Online Redo Log Groups Standby Redo Log Groups Status
(LAPPRIMDB) (lapdrdb)
1 3 3 Insufficient SRLs
Future Log File Groups Configuration:
Thread # Online Redo Log Groups Standby Redo Log Groups Status
(lapdrdb) (LAPPRIMDB)
1 3 2 Insufficient SRLs
DGMGRL>
So here we have setting like
StaticConnector.
SERVICE_NAME=LAPDRDB_DGMGRL
SERVICE_NAME=LAPPRIMDB_DGMGRL
TnsName:-
(SID = LAPDRDB)
(SID = LAPPRIMDB)
listener.ora
(GLOBAL_DBNAME = LAPPRIMDB_DGMGRL)
(ORACLE_HOME = /oracle/PRD/19)
(SID_NAME = LAPPRIMDB)
Let do Switchover.
DGMGRL> switchover to lapdrdb;
Performing switchover NOW, please wait...
Operation requires a connection to database "lapdrdb"
Connecting ...
Connected to "LAPDRDB"
Connected as SYSDBA.
New primary database "lapdrdb" is opening...
Operation requires start up of instance "LAPPRIMDB" on database "LAPPRIMDB"
Starting instance "LAPPRIMDB"...
Connected to an idle instance.
ORACLE instance started.
Connected to "LAPPRIMDB"
Database mounted.
Connected to "LAPPRIMDB"
Switchover succeeded, new primary is "lapdrdb"
DGMGRL> show configuration;
Configuration - DRENV
Protection Mode: MaxPerformance
Members:
lapdrdb - Primary database
LAPPRIMDB - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 60 seconds ago)
Error:-Sometime in this scenario if we do validate from Dr side then we are getting error ,
ORA-12545: Connect failed because target host or object does not exist
DGMGRL> validate database lapprimdb;
Database Role: Primary database
Ready for Switchover: Yes
Flashback Database Status:
LAPPRIMDB: Off
Managed by Clusterware:
LAPPRIMDB: NO
Validating static connect identifier for the primary database LAPPRIMDB...
Unable to connect to database using (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=prd1.hana.com)(PORT=1526))(CONNECT_DATA=(SERVICE_NAME=LAPPRIMDB_DGMGRL)(INSTANCE_NAME=LAPPRIMDB)(SERVER=DEDICATED)(STATIC_SERVICE=TRUE)))
ORA-12545: Connect failed because target host or object does not exist
Failed.
Warning: Ensure primary database's StaticConnectIdentifier property
is configured properly so that the primary database can be restarted
by DGMGRL after switchover
Error. If Validate command run on DR side you will get below Error.
ORA-12545: Connect failed because target host or object does not exist, Because its trying to connect on local server with Primary database Service and that Service does not exist.
SERVICE_NAME=LAPPRIMDB_DGMGRL
DGMGRL> validate database lapdrdb;
Database Role: Physical standby database
Primary Database: LAPPRIMDB
Ready for Switchover: Yes
Ready for Failover: Yes (Primary Running)
Flashback Database Status:
LAPPRIMDB: Off
lapdrdb : Off
Managed by Clusterware:
LAPPRIMDB: NO
lapdrdb : NO
Validating static connect identifier for the primary database LAPPRIMDB...
Unable to connect to database using (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=prd1.hana.com)(PORT=1526))(CONNECT_DATA=(SERVICE_NAME=LAPPRIMDB_DGMGRL)(INSTANCE_NAME=LAPPRIMDB)(SERVER=DEDICATED)(STATIC_SERVICE=TRUE)))
ORA-12545: Connect failed because target host or object does not exist
Failed.
Warning: Ensure primary database's StaticConnectIdentifier property
is configured properly so that the primary database can be restarted
by DGMGRL after switchover
Current Log File Groups Configuration:
Thread # Online Redo Log Groups Standby Redo Log Groups Status
(LAPPRIMDB) (lapdrdb)
1 3 3 Insufficient SRLs
Future Log File Groups Configuration:
Thread # Online Redo Log Groups Standby Redo Log Groups Status
(lapdrdb) (LAPPRIMDB)
1 3 2 Insufficient SRLs
Note: Here pfile,Spfile and orapassword file having name like DB_UNIQUE_NAME. for eg. spfileLAPPRIMDB.ora, orapwLAPPRIMDB
TYPE 4:-
Also Pfile name and passwordfile name will be DB_NAME.
on both node.
We are using SID, rather than the SERVICE_NAME in the entries. This is important as the broker will need to connect to the databases when they are down, so the services will not be present.
SID_NAME=DB_NAME in the tnsname.ora & Listener.ora
GLOBAL_DB_NAME=DB_UNIQUE_NAME_DGMGRL
Primary Database Listener.ora and tnsentry
[oraprd@prd1 admin]$ cat listener.ora
# listener.ora Network Configuration File: /oracle/PRD/19/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENERPRMDB =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = LAPPRIMDB_DGMGRL)
(ORACLE_HOME = /oracle/PRD/19)
(SID_NAME = LAP)
)
)
ADR_BASE_LISTENERPRMDB = /oracle/PRD
LISTENERPRMDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.18)(PORT = 1526))
)
ADR_BASE_LISTENERPRD = /oracle/PRD
TNSENTRY=====>
LAPDRDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.19)(PORT = 1526))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = LAP)
)
)
LAPPRIMDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.18)(PORT = 1526))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = LAP)
)
)
On Standby Database Server.
[oraprd@prd2 admin]$ cat listener.ora
# listener.ora Network Configuration File: /oracle/PRD/19/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENERDRDB =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = LAPDRDB_DGMGRL)
(ORACLE_HOME = /oracle/PRD/19)
(SID_NAME = LAP)
)
)
LISTENERDRDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.19)(PORT = 1526))
)
TNSENTRY===>
LAPPRIMDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.18)(PORT = 1526))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID=LAP)
)
)
LAPDRDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.19)(PORT = 1526))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = LAP)
)
)
[oraprd@prd1 STANDBY]$ lsnrctl status LISTENERPRMDB
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 02-JUN-2021 00:08:19
Copyright (c) 1991, 2020, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.18)(PORT=1526)))
STATUS of the LISTENER
------------------------
Alias LISTENERPRMDB
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 01-JUN-2021 23:14:39
Uptime 0 days 0 hr. 53 min. 39 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/PRD/19/network/admin/listener.ora
Listener Log File /oracle/PRD/diag/tnslsnr/prd1/listenerprmdb/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.18)(PORT=1526)))
Services Summary...
Service "LAP.hana.com" has 1 instance(s).
Instance "LAP", status READY, has 1 handler(s) for this service...
===================================================================
Service "LAPPRIMDB.hana.com" has 1 instance(s).
Instance "LAP", status READY, has 1 handler(s) for this service...
===============================================================
Service "LAPPRIMDB_DGMGRL" has 1 instance(s).
Instance "LAP", status UNKNOWN, has 1 handler(s) for this service...
This is entry we need. DBUNIQUE_NAME_DGMGRL we used here.
===============================================================
Service "LAPXDB.hana.com" has 1 instance(s).
Instance "LAP", status READY, has 1 handler(s) for this service...
The command completed successfully
===============================================================
Instance Name ======================>
Standby Node
[oraprd@prd2 STANDBY]$ ps -ef|grep pmon
oraprd 841 1 0 23:53 ? 00:00:00 ora_pmon_LAP ====> Here We have to used Actual DB_name as not DB_UNIQUE name.
oraprd 8542 10166 0 23:55 pts/2 00:00:00 grep --color=auto pmon
[oraprd@prd2 STANDBY]$
Production Node.
[oraprd@prd1 admin]$ ps -ef|grep pmon
oraprd 4960 1 0 21:59 ? 00:00:00 ora_pmon_LAP
oraprd 8870 7151 0 23:56 pts/2 00:00:00 grep --color=auto pmon
[oraprd@prd1 admin]$
[oraprd@prd1 STANDBY]$ ./show_dg.sh
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Wed Jun 2 00:15:52 2021
Version 19.8.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> Connected to "LAPPRIMDB"
Connected as SYSDBA.
DGMGRL>
Configuration - DRENV
Protection Mode: MaxPerformance
Members:
LAPPRIMDB - Primary database
lapdrdb - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 26 seconds ago)
But StaticConnectIdentifier====>
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.18)(PORT=1526))(CONNECT_DATA=(SERVICE_NAME=LAPPRIMDB_DGMGRL.hana.com)(INSTANCE_NAME=LAP)(SERVER=DEDICATED)))'
But here SERVICE_NAME=LAPPRIMDB_DGMGRL.hana.com so will get the error for Switchover.
[oraprd@prd1 STANDBY]$ ./validate_dg.sh
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Wed Jun 2 00:19:40 2021
Version 19.8.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> Connected to "LAPPRIMDB"
Connected as SYSDBA.
DGMGRL>
Configuration - DRENV
Protection Mode: MaxPerformance
Members:
LAPPRIMDB - Primary database
lapdrdb - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 53 seconds ago)
DGMGRL>
Database Role: Primary database
Ready for Switchover: Yes
Flashback Database Status:
LAPPRIMDB: Off
Managed by Clusterware:
LAPPRIMDB: NO
Validating static connect identifier for the primary database LAPPRIMDB...
Unable to connect to database using (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.18)(PORT=1526))(CONNECT_DATA=(SERVICE_NAME=LAPPRIMDB_DGMGRL.hana.com)(INSTANCE_NAME=LAP)(SERVER=DEDICATED)(STATIC_SERVICE=TRUE)))
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Failed.
Warning: Ensure primary database's StaticConnectIdentifier property
is configured properly so that the primary database can be restarted
by DGMGRL after switchover
Now to Resolve this issue, Please update the StaticConnectIdentifier
[oraprd@prd1 STANDBY]$ dgmgrl /
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Wed Jun 2 00:33:03 2021
Version 19.8.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected to "LAPPRIMDB"
Connected as SYSDG.
DGMGRL> connect sys/abc#123@lapprimdb
Connected to "LAPPRIMDB"
Connected as SYSDBA.
DGMGRL> edit database lapprimdb set property staticconnectidentifier='lapprimdb';
Property "staticconnectidentifier" updated
DGMGRL> edit database lapdrdb set property staticconnectidentifier='lapdrdb';
Property "staticconnectidentifier" updated
DGMGRL> show database verbose lapdrdb;
or remove the entry (INSTANCE_NAME=LAP)
edit database lapdrdb set property staticconnectidentifier='(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.19)(PORT=1526))(CONNECT_DATA=(SID=LAP) (SERVER=DEDICATED)))';
edit database lapprimdb set property staticconnectidentifier='(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.18)(PORT=1526))(CONNECT_DATA=(SID=LAP)(SERVER=DEDICATED)))';
Now Do validate
DGMGRL> validate database lapprimdb;
Database Role: Primary database
Ready for Switchover: Yes
Flashback Database Status:
LAPPRIMDB: Off
Managed by Clusterware:
LAPPRIMDB: NO
Validating static connect identifier for the primary database LAPPRIMDB...
The static connect identifier allows for a connection to database "LAPPRIMDB".
Look Great. Now do switchover
DGMGRL> switchover to lapdrdb;
Performing switchover NOW, please wait...
Operation requires a connection to database "lapdrdb"
Connecting ...
Connected to "LAPDRDB"
Connected as SYSDBA.
New primary database "lapdrdb" is opening...
Operation requires start up of instance "LAP" on database "LAPPRIMDB"
Starting instance "LAP"...
Connected to an idle instance.
ORACLE instance started.
Connected to "LAPPRIMDB"
Database mounted.
Connected to "LAPPRIMDB"
Switchover succeeded, new primary is "lapdrdb"
DGMGRL>
Its working fine.
*****************************
EOD
