What is TNSNAMES.ORA and how to set it

NYAN CAT with horns

1. TNSNAMES.ORA?

TNSNAMES.ORA is a configuration file that Oracle database use. It allows users and applications to connect to Oracle Databases by matching a connection name with all of the relevant details.

2. Where is the TNSNAMES.ORA located?

$ORACLE_HOME\network\admin\

• $ORACLE_HOME : the location that the Oracle database is installed in.

3. How to find ORACLE_HOME and the TNSNAMES.ORA Location in Unix

env | grep ORACLE_HOME

echo $ORACLE_HOME

4. Syntax of the TNSNAMES.ORA File

net_service_name =
  (DESCRIPTION=
    (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.com)(PORT = 1521)
  )
  (CONNECT_DATA =
    (SERVICE_NAME=service_name)
  )
)

• net_service_name: This is the name that you use for a connection string later. You can choose what this is. It’s like a name you give to this set of connection details.
• host: The IP address or server name where the database lives or that you want to connect to.
• port: The port that is required for the connection. In most cases the default port of 1521 will be fine.
• service_name: This is the name of the database you want to connect to.

5. How can I create / modify the TNSNAMES.ORA File?

1. MODIFY
   To add an entry into the file, you can either copy the format from above, or copy and paste an existing entry from the file. Then, make changes.
2. CREATE

   net_service_name =
     (DESCRIPTION=
       (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.com)(PORT = 1521)
     )
     (CONNECT_DATA =
       (SERVICE_NAME=service_name)
     )
   )
   

   You can also create one if you don’t have it in your ORACLE_HOME directory. To create the file, open a new text file. Save the file with the name TNSNAMES.ORA and save it into your ORACLE_HOME location. Below is the template