Pythian Blog: Technical Track

Data Guard Listener Networking

by Michael Dinh on Apr 16, 2020 12:00:00 AM

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: [oracle@ol7-121-dg1 ~]$ 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:(SYS@hawk):PRIMARY> show parameter db_name NAME TYPE VALUE ---------------------------------------- ----------- ------------------------------------------------------------ db_name string hawk OL7-121-DG1:(SYS@hawk):PRIMARY> show parameter db_unique_name NAME TYPE VALUE ---------------------------------------- ----------- ------------------------------------------------------------ db_unique_name string hawka OL7-121-DG1:(SYS@hawk):PRIMARY> show parameter local_listener NAME TYPE VALUE ---------------------------------------- ----------- ------------------------------------------------------------ local_listener string LISTENER_HAWKA OL7-121-DG1:(SYS@hawk):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 [oracle@ol7-121-dg1 ~]$ Primary listener status: [oracle@ol7-121-dg1 ~]$ 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 [oracle@ol7-121-dg1 ~]$ Primary sqlnet.ora: [oracle@ol7-121-dg1 ~]$ grep -i DOMAIN /u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/sqlnet.ora NAMES.DEFAULT_DOMAIN=world [oracle@ol7-121-dg1 ~]$ 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. [oracle@ol7-121-dg1 ~]$ 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)) [oracle@ol7-121-dg1 ~]$ Let's see if it works: [oracle@ol7-121-dg1 ~]$ 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) [oracle@ol7-121-dg1 ~]$ 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) [oracle@ol7-121-dg1 ~]$ 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) [oracle@ol7-121-dg1 ~]$ 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) [oracle@ol7-121-dg1 ~]$ Hopefully, this post will help you plan and implement a consistent and easy-to-manage Data Guard environment.
Topics: MySQL Technical Track Open Source Mariadb

No Comments Yet

Let us know what you think

See all

See all

Subscribe by email
Follow us
Love Your Data
Pythian Logo

© 2024 All rights reserved.