Connecting to Oracle using tnsnames.ora

Back to: Oracle DBA Tutorials

Connecting to Oracle Database using tnsnames.ora

In this article, I am going to discuss Connecting to Oracle Database using tnsnames.ora. Please read our previous article where we discussed Easy Connect Method.

Connecting to Oracle using tnsnames.ora

We are going to learn how to connect using the tnsnames.ora file.

Step 1: Opening tnsnames.ora file.

Open the Linux virtual machine and log in as oracle user. Open file manager. Click on the other locations directory and select Computer and go to /uo1 folder.

After opening into the u01 folder you will see a lot of different other folders go to the below path.

So, you are in the admin folder. This is the path where we can find the tnsnames.ora.

Open the tnsnames.ora file. There you can see the entry of the JAYADB entry. This JAYADB is an alias name for the database. This entry is pointing to the service called jayadb.com and this service is pointing to the database JAYADB.

Step 2: Connect to the database using tnsnames entry.

Open the terminal in the same path i.e $ORACLE_HOME/network/admin folder and connect to the database as “sqlplus / as sysdba”.

This is another method to connect to the database using the tns alias names. The syntax of these types of connections are connect username/password@database_aliasname. So, the database alias name is JAYADB.

Connect sys/goodluck@jayadb. By using this method oracle will understand that after ‘@’ symbol the pattern is considered as tnsnames entry. This method also requires the listener to be up and running fine.

We are getting the same error as the previous time we tried to connect using the easy method. We need to mention the role as well. Connect sys/goodluck@jayadb as sysdba. Now, we can see that we have connected to the database using the tnsnames.ora entry.

Step3: Connect as a system user.

Let’s try connecting using the system user. Remember that the system is the same as the sys user but with fewer privileges. If you remember the password for the system user then you can log in or else you can change the password. Here we are changing the password for the system user.

Let’s try to connect using system user through tnsnames.ora method. Here no need to mention any role. The syntax is connect system/goodluck@jayadb

We are able to connect to the system user.

Step4: Creating tns entry for the pluggable database.

Until now we tried to connect to the database using the tnsnames entry for the container database JAYADB. Now, let’s try to connect to the pluggable database. In order to connect to the pluggable database, we need to mention the details of the pluggable database in the tnsnames.ora file. Go to the admin folder and open the tnsnames.ora file.

Now we are creating the tns alias name for our pluggable database JAYAPDB. We had already removed it because we need to understand how we need to enter the tnsnames entry for any database. So, we are creating it again for further practice. If you already have this entry then check it and make changes if necessary. Copy the JAYADB entry and paste it below.

Change the entry name to JAYAPDB and the service name to jayapdb.com. So, the entries have changed to the pluggable database JAYAPDB.

Now, this JAYAPDB alias name is pointing to the service name jayapdb.com and this service is pointing to the pluggable database JAYAPDB. Save the file after making the changes.

Step 5: Connect to the pluggable database using tnsnames.ora.

So, we have made changes to the tnsnames.ora. Let’s try to connect to the pluggable database using the same syntax. Syntax: Connect system/goodluck@jayapdb.

You are getting an error saying “database is not open”. If the database is not in open state users cannot connect to the database. So, we need to open the database. Connect to the container database as “Sqlplus / as sysdba” and run the below command.

Command: alter pluggable database JAYAPDB open;

Now try to connect to the pluggable database again using connect system/goodluck@jayapdb. It will connect without any issues. Even further you can check the container name using the command “show con_name”.

Step 6: Creating tnsnames.ora in the local computer.

Until now we are connecting from the server side and we are connecting using the client style. So let us try to connect to the database from our local computer using the tnsnames.ora file. Open the listener.ora file and copy both the database alias description ie. Jayadb and jayapdb description. Don’t copy the LISTENER_JAYADB description.

Go to your local computer and create a text document and paste it in the text document.

Rename the text file to tnsnames.ora. The file extension should be .ora file. So, this is the same listener file that is present inside the server with slight modifications. This will help us to connect to the database using the TNS method in the SQL developer.

Note: The file extension should be ora or else the SQL developer doesn’t pick the file.

Step7: Connect using tnsnames.ora from SQL developer in the local computer.

Open the SQL developer in the local computer and click on tools.

Click on preferences.

Click on the database and select the advanced option.

The Advanced option is the place where we need to make changes to the tnsnames directory.

Click on Browse and select the directory where you have saved the tnsnames.ora file. I am selecting the desktop folder because I have saved the file in the desktop folder.

After selecting the Directory click ok to save these settings. Once the settings are changed click on New connection. Enter the username as system and enter the password for the system user.

Select connection type as TNS.

There you can find the Network Alias names. Select JAYAPDB.

Click Test. You can see it is a success.

So, the connection is successful. Now, let’s try to play with the tnsnames.ora file.

Step8: Changing the Alias name and trying the connection.

Open the tnsnames.ora file using notepad.

Change the alias name of the database to some other name i.e (dotnet) I am selecting Dotnet and saving the file.

Now, this alias name Dotnet is pointing to the service name jayadb.com which is pointing to the database JAYADB. Open the SQL developer and try to make a new connection.

In the connection, type select TNS and the network alias you can see dotnet is mentioned.

Select that and test the connection.

We can see the connection is a success. So, we can use any alias name in the tnsnames.ora file connects to the database.

In the next article, I am going to discuss Locking and Unlocking User Accounts in Oracle Database. Here, in this article, I try to explain Connecting to Oracle Database using tnsnames.ora, and I hope you enjoy this Connecting to Oracle Database using tnsnames.ora article.