I’m currently reviewing an environment to create a third standby. One of the challenges is that there isn’t any sort of consistent naming convention. One example is “db_name” and “db_unique_name.”
Firstly, don’t name db_unique_name using a number (for example, hawk1, hawk2, etc.), as this can create confusion in RAC environments.
Next, you might want to name local_listener. Use LISTENER_$(db_unique_name). Does this mean the listener name has to be LISTENER_$(db_unique_name)? If you are not using an alias, the answer is yes. If you are, then the answer is no.
However, you might ask, why not name the listener?
In my opinion, it’s easier to use an alias and lsnrct status as opposed to lsnrct status LISTENER_$(db_unique_name), which will be different depending on hosts. This creates a dependency which I typically like to avoid.
Primary database configuration:
[[email protected] ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Thu Apr 9 14:58:27 2020
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
OL7-121-DG1:([email protected]):PRIMARY> show parameter db_name
NAME TYPE VALUE
---------------------------------------- ----------- ------------------------------------------------------------
db_name string hawk
OL7-121-DG1:([email protected]):PRIMARY> show parameter db_unique_name
NAME TYPE VALUE
---------------------------------------- ----------- ------------------------------------------------------------
db_unique_name string hawka
OL7-121-DG1:([email protected]):PRIMARY> show parameter local_listener
NAME TYPE VALUE
---------------------------------------- ----------- ------------------------------------------------------------
local_listener string LISTENER_HAWKA
OL7-121-DG1:([email protected]):PRIMARY> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[[email protected] ~]$
Primary listener status:
[[email protected] ~]$ lsnrctl status
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 09-APR-2020 14:59:20
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ol7-121-dg1.localdomain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 08-APR-2020 16:16:17
Uptime 0 days 22 hr. 43 min. 3 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/ol7-121-dg1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ol7-121-dg1.localdomain)(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 "hawka" 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 "hawka.world" has 1 instance(s).
Instance "hawk", status UNKNOWN, has 1 handler(s) for this service...
Service "hawka_DGB" has 1 instance(s).
Instance "hawk", status READY, has 1 handler(s) for this service...
Service "hawka_DGMGRL.world" has 1 instance(s).
Instance "hawk", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[[email protected] ~]$
Primary sqlnet.ora:
[[email protected] ~]$ grep -i DOMAIN /u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/sqlnet.ora
NAMES.DEFAULT_DOMAIN=world
[[email protected] ~]$
Primary tnsnames.ora:
LISTENER_HAWKA2 does not have a hostname, as it will be localhost by default. The benefit is that it’s easy and can be set as such for local_listener. The drawback is that it you need to edit it for every host.
Basically, it’s possible to have all three static entries for all hosts, or two static entries and one dynamic entry.
[[email protected] ~]$ grep LISTEN /u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/tnsnames.ora
LISTENER_HAWKA2.world = (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 1521))
LISTENER_HAWKA.world = (ADDRESS = (PROTOCOL = TCP)(HOST = ol7-121-dg1.localdomain)(PORT = 1521))
LISTENER_HAWKB.world = (ADDRESS = (PROTOCOL = TCP)(HOST = ol7-121-dg2.localdomain)(PORT = 1521))
LISTENER_HAWKC.world = (ADDRESS = (PROTOCOL = TCP)(HOST = ol7-121-dg3.localdomain)(PORT = 1521))
[[email protected] ~]$
Let’s see if it works:
[[email protected] ~]$ tnsping LISTENER_HAWKA2
TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 09-APR-2020 15:29:05
Copyright (c) 1997, 2014, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (ADDRESS = (PROTOCOL = TCP)(HOST =)(PORT = 1521))
OK (0 msec)
[[email protected] ~]$ tnsping LISTENER_HAWKA
TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 09-APR-2020 15:29:08
Copyright (c) 1997, 2014, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (ADDRESS = (PROTOCOL = TCP)(HOST = ol7-121-dg1.localdomain)(PORT = 1521))
OK (0 msec)
[[email protected] ~]$ tnsping LISTENER_HAWKB
TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 09-APR-2020 15:29:11
Copyright (c) 1997, 2014, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (ADDRESS = (PROTOCOL = TCP)(HOST = ol7-121-dg2.localdomain)(PORT = 1521))
OK (0 msec)
[[email protected] ~]$ tnsping LISTENER_HAWKC
TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 09-APR-2020 15:29:14
Copyright (c) 1997, 2014, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (ADDRESS = (PROTOCOL = TCP)(HOST = ol7-121-dg3.localdomain)(PORT = 1521))
OK (0 msec)
[[email protected] ~]$
Hopefully, this post will help you plan and implement a consistent and easy-to-manage Data Guard environment.
No comments