LISTENER TNSNAMES Configuration Files
listener.ora - Server side configuration file
tnsnames.ora - Client side configuration file
1. Listener and tnsnames configuration
listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oraclelab1.localdomain.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
DEVDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = oraclelab1.localdomain.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = DEVDB)
)
)
DEVDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = oraclelab1.localdomain.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = SHARED)
(SERVICE_NAME = DEVDB)
)
)
DEVDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = oraclelab1.localdomain.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DEVDB)
)
)
DEVDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = oraclelab1.localdomain.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = POOLED)
(SERVICE_NAME = DEVDB)
)
)
IPC: InterProcess communication
IPC is only relevent if you have a client process on the server.
Example an application server running on the same server where the rdbms server is running and making requests to it.
In this case IPC should be more efficient than TCP (Fewer layers in the OSI stack)
2. Listener and tnsnames configuration with TCP/IP and SSL enabled
listener.ora configuration with TCP/IP with SSL
# listener.ora Network Configuration File: /u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCPS)(HOST = oraclelab1.localdomain.com)(PORT = 1521))
)
ADR_BASE_LISTENER = /u01/app/oracle
tnsnames.ora configuration with TCP/IP with SSL
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
DEVDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCPS)(HOST = oraclelab1.localdomain.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = DEVDB)
)
)
3. Listener & tnsnames configuration with multiple address
# listener.ora Network Configuration File: /u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER_1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oraclelab1.localdomain.com)(PORT = 1521))
)
ADR_BASE_LISTENER_1 = /u01/app/oracle
LISTENER_2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oraclelab1.localdomain.com)(PORT = 1522))
)
ADR_BASE_LISTENER_2 = /u01/app/oracle
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
DEVDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = oraclelab1.localdomain.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = oraclelab1.localdomain.com)(PORT = 1522))
)
(CONNECT_DATA =
(SERVICE_NAME = DEVDB)
)
)
4. Use of local_listener parameter along woth listener and tnsnames
[oracle@oraclelab1 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER_DEVDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oraclelab1.localdomain.com)(PORT = 1522))
)
ADR_BASE_LISTENER = /u01/app/oracle
[oracle@oraclelab1 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
DEVDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = oraclelab1.localdomain.com)(PORT = 1522))
)
(CONNECT_DATA =
(SERVICE_NAME = DEVDB)
)
)
MALLIK_DEVDB =
(ADDRESS = (PROTOCOL = TCP)(HOST = oraclelab1.localdomain.com)(PORT = 1522))
[oracle@oraclelab1 admin]$ lsnrctl status LISTENER_DEVDB
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 02-MAR-2023 01:08:49
Copyright (c) 1991, 2022, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oraclelab1.localdomain.com)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias LISTENER_DEVDB
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 02-MAR-2023 01:05:21
Uptime 0 days 0 hr. 3 min. 27 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/oraclelab1/listener_devdb/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraclelab1.localdomain.com)(PORT=1522)))
Services Summary...
Service "DEVDB" has 1 instance(s).
Instance "DEVDB", status READY, has 1 handler(s) for this service...
Service "DEVDBXDB" has 1 instance(s).
Instance "DEVDB", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@oraclelab1 admin]$
[oracle@oraclelab1 admin]$ tnsping MALLIK_DEVDB
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 02-MAR-2023 01:09:31
Copyright (c) 1997, 2022, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (ADDRESS = (PROTOCOL = TCP)(HOST = oraclelab1.localdomain.com)(PORT = 1522))
OK (0 msec)
[oracle@oraclelab1 admin]$
[oracle@oraclelab1 admin]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 2 01:09:43 2023
Version 19.17.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.17.0.0.0
SQL> show parameter local_listener
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string MALLIK_DEVDB
SQL>
Important Step to set local_listener
Option 1:
SQL> alter system set local_listener=MALLIK_DEVDB scope=both;
System altered.
SQL>
[oracle@oraclelab1 admin]$ sqlplus mallik/mallik@DEVDB
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 2 01:12:15 2023
Version 19.17.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Last Successful login time: Wed Mar 01 2023 22:11:38 +05:30
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.17.0.0.0
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.17.0.0.0
[oracle@oraclelab1 admin]$
Option 2:
SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=oraclelab1.localdomain.com)(PORT=1522))))' scope=both;
System altered.
SQL> show parameter local_listener
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string (DESCRIPTION=(ADDRESS_LIST=(AD
DRESS=(PROTOCOL=TCP)(HOST=orac
lelab1.localdomain.com)(PORT=1
522))))
SQL>
[oracle@oraclelab1 admin]$ sqlplus mallik/mallik@DEVDB
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 2 01:14:37 2023
Version 19.17.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Last Successful login time: Thu Mar 02 2023 01:13:51 +05:30
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.17.0.0.0
SQL>
Regards,
Mallik
No comments:
Post a Comment