Oracle TNS have always been a very confusing topic, specially for beginners. Therefore, I have decided to answer all your questions about Oracle TNS once and for all. And, I assure you that this tutorial is going to be your all-purpose guide to Oracle TNS.
What is TNS?
TNS stands for Transparent Network Substrate. It’s an Oracle’s proprietary networking technology. And is used for establishing a peer-to-peer communication between Server and Client.
What is tnsnames.ora file?
The tnsnames.ora is a client-side network configuration file. Moreover, in a server-client environment tnsnames.ora file is placed on the client machine.
What is the location of tnsnames.ora file?
The tnsnames.ora file is created during the installation by the Oracle Universal Installer (OUI). And by default it is placed in the %Oracle_Home% /network/admin directory in Linux/Unix system and %Oracle_Home% \network\admin directory on windows operating system.
Info Byte –
If you don’t know what is your %Oracle_Home% location, then simply paste the following command into your command prompt. It will show you the location of your Oracle Home directory.
REG QUERY HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDB19Home1 /v ORACLE_HOME
What does tnsnames.ora file contain?
The tnsnames.ora file contains all the names which points to the respective connect descriptor. These names are called local naming methods.
What is a connect descriptor?
A connect descriptor also known as a tns entry – is a special formatted description of destination (Oracle Database Server) for a network connection. Furthermore, it contains the service name of the database with which you want to connect as well as its network address.
This special formatted description looks something like this –
As you can see a connect descriptor is comprised of two main parts –
- Protocol address, and
- Connect Data
Later in this tutorial I will show you how to find out all this information along with the service name. So, just relax and don’t worry.
What is a protocol address in the connect descriptor?
If you have read the connect descriptor carefully in the above picture, then you must have come across a section with the name: “address” –
This address section in the connect descriptor is called protocol address. Furthermore this section is comprised mainly of three parameters –
- PROTOCOL: A network protocol the listener is using to communicate with the client.
- HOST: This parameter will hold the name or IP Address of the computer/server where you have installed and configured your Oracle Database Server.
- PORT: The port number which your Oracle Server is using for establishing the communication between Client and server. By default, Oracle Server uses the port number 1521.
What is connect_data in the connect descriptor?
Connect_Data is the second portion of the tnsnames.ora file. Additionally, this section of the tnsnames.ora specifies the name of the destination service/database to which the client wants to connect.
Though there are multiple parameters which can be used under connect_data but by default you will see only two parameters used. And, these are –
- SERVER, and
Server: This parameter is used to instruct the listener to connect the client to a specific type of service handler. Consequently, there are values for this parameter —
- Dedicated, and
Dedicated: This value specifies that client request be served by dedicated server
Shared: This specifies that client request be served by shared server.
Additionally, you can check out this Oracle Doc for other parameters of connect descriptor.
What is a service?
You must be wondering what is this service_name in the connect descriptor which I am mentioning repeatedly.
A service name is a logical representation of a database. A database is represented to clients as a service.
Furthermore, the database and its corresponding service shares the same name almost always. However, this is just a tactic adopted by the Oracle to reduce the confusion.
Also, it is recommended to name your net service after the name of the service for which you are creating the TNS entry. But it is not necessary.
Dummy connect descriptor of tnsnames.ora file
The default tns entry will look something like this –
ORCL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) ) )
So, this is a default entry created by OUI during the installation for ORCL service.
How To Create A TNS service In The tnsnames.ora file?
There are two ways of creating a TNS Service in tnsnames.ora file, these are –
- Manually, and
- By Using ‘Oracle Net Configuration Assistant’
Furthermore, creating a TNS service using both the above-mentioned ways are fairly simple. But, use of ‘Oracle Net Configuration Assistant’ is always recommended.
What is Oracle Net Configuration Assistant?
Oracle Net Configuration Assistant is a wizard-based network configuration tool which comes in bundle with Oracle Database software.
Consequently, it is a recommended tool for adding, deleting, renaming or reconfiguring a TNS service in Oracle Database.
How To Create TNS Service Using Oracle Net Configuration Assistant?
I have compiled an info packed PDF dedicated to the creation of TNS service using Oracle Net configuration Assistant. Which Can be Downloaded From Here
I hope this blog answers all your questions about Oracle TNS. Also, do check out the related links for a complete know-how. You can even like my Facebook page and connect with me there for more general info on the topic. Thanks & have a great day!