Mir Sayeed Hassan – Oracle Blog

How to Configure TNSNAMES.ORA in Oracle on Linux, Windows, and using SQL Developer

The TNSNAMES.ORA file is a key Oracle configuration file that maps service names to connection details, allowing clients to connect to database servers. Proper configuration is essential for DBAs, developers, and administrators.

Here are some alternative other locations where the TNSNAMES.ORA file can be stored.

Client Machine

$ORACLE_HOME\network\admin in client machine as this file is located in both the server and client.

Environment variable as TNS_ADMIN

The environment variable called TNS_ADMIN, which specifies a directory where the TNSNAMES.ORA file can also be located.

How to Find ORACLE_HOME and the TNSNAMES.ORA File Location in Unix

$env | grep ORACLE_HOME or $echo $ORACLE_HOME

How to Find ORACLE_HOME and the TNSNAMES.ORA File Location in Windows and to locate the ORACLE_HOME directory in Windows, you can check several locations.

To check the environment variable locate to the control panel., if its not there check the registry.

control panel -> open system -> advanced system setting -> left side - > advanced tab or - > environment variable.

Check the user variable session - > system variable section for variable called - > ORACLE_HOME., - > you will find the value in your ORACLE_HOME location.

If it is not present, you will need to check the Windows Registry. Since it is not listed in the Environment Variables, the next step is to verify it in the registry.

Use RUN Command or type regedit - > enter - > left - > navigate to this location by expanding the folders.

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE

C:\oracleexe\app\oracle\product\19.3.0\server

After this Copy the value and paste it into Windows Explorer.

Navigate to the Oracle folder in the left pane of the Registry Editor. Under this, you will find entries such as KEY_XE (for Oracle Express Edition) or KEY_OraDb19c, depending on your installation.

Hence to view the path, double-click on the entry labeled ORACLE_HOME; this will display the Oracle Home directory location.

Setup in TNSNAMES.ORA File in Linux?

TNSNAMES.ORA file uses a structured format to define network service names (aliases) and their corresponding connection details (connect descriptors).

Example:  Default location : $ORACLE_HOME/network/admin

MIRDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = hostname/IP address)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = mirdb)
)
)

Components overview:

• ALIAS_NAME → The name used to connect to the database., ex: ORCL_DB

• DESCRIPTION → Container for connection details

• ADDRESS → Specifies how to reach the database
  PROTOCOL → Communication protocol (usually TCP)
  HOST → host name or IP address
  PORT → Listener port (default is 1521) or non-default port.

• CONNECT_DATA → Database-specific details
  SERVICE_NAME → Name of the database service., ex: mirdb

Can the tnsnames.ora file be modify in database.

To modify the TNSNAMES.ORA file, locate it in the Oracle network configuration directory $ORACLE_HOME/network/admin or a path set by TNS_ADMIN. Open the file using a text editor, then add, update, or remove database connection entries as needed and test the configuration using tools like tnsping or by attempting a database connection.

Note: It is recommended to backup the file before making any modifications to avoid configuration issues.

Configure the TNSNAMES.ORA in windows.

Creating a TNSNAMES.ORA file in Windows involves defining database connection details so Oracle clients can connect using an alias instead of a full connection string.

Navigate to the default location or Or check the directory specified by the TNS_ADMIN environment variable.

C:\oracle\product\19c\dbhome_1\network\admin

Create the File

• Right-click inside the folder

• Select New → Text Document

• Rename it to:
  
  tnsnames.ora

Make sure it is not saved as .txt

Add the Connection Entry

Open the file in Notepad and add the following:

MIRDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = hostname/IP Address)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = mirdb)
    )
  )

Port: 1521 is default or you can use the non-default port as per your requirement.

Save and close the file after adding the required entries.

Test the configuration by Opening the Command Prompt and run:

tnsping MIRDB

Therefore if the configuration is correct you will receive the successful output.

Does Oracle SQL Developer Use the TNSNAMES.ORA File?

The answer is Yes ofcourse, As the Oracle SQL Developer can use the TNSNAMES.ORA file to connect to databases using predefined aliases, though it also supports database connection methods.

To connect with SQL Developer tools

Open the SQL Developer tool -> Preferences -> database section -> advanced -> tnsnames directory option -> add the location of tnsnames.ora file -> this file will be ORACLE_HOME\network\admin. - > click ok.

Therefore you can create a new connection by using the tnsnames., navigate to the green + sign on sql developer - > connection drop-down - > select TNS -> finally test the connection with the above setting.

=====Hence tested and verified in our test env=====