Back to: Oracle DBA Tutorials
Connecting to Oracle Database Using tnsnames.ora
In this article, I’m going to talk about about Connection to Oracle database using tnsnames.ora . Read our previous article where we discussed Easy connect method.
Connect to Oracle using tnsnames.ora
Let’s go to learn how connect using the tnsnames.ora file.
Step 1: Open the tnsnames.ora file.
Open the Linux virtual machine and log in as a user of Oracle. Open the file manager. Click on the other locations directory and select Computer and navigate to the /uo1 folder.
After opening the u01 folder, you will see many other different folders in the following path.
So, you are in the admin folder. This is the path where we can find the tnsnames.ora file.
Open the tnsnames.ora file. There you can see the entry for the JAYADB entry. This JAYADB is an alias name for the database. This entry points to the service named jayadb.com and this service points to the JAYADB database.
Step 2: Connect to the database using tnsnames entry.
Open terminal in the same path, ie $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 for these types of connections is to 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 the ‘@’ symbol, the pattern is considered as an entry of tnsnames. This method also requires the listener to be working correctly.
We are getting the same error as the previous time when we tried to connect using the easy method. We need to mention paper too. Connect sys/goodluck@jayadb as sysdba. Now, we can see that we have connected to the database using the tnsnames.ora entry.
Step 3: Connect as a system user.
Let’s try to connect using the system user. Remember that the system is the same as the sys user but with less privileges. If you remember the password of the system user, you can log in or change the password. Here we are changing the password for the system user.
Let’s try to connect using the system user via the tnsnames.ora method. There is no need to mention any role here. The syntax is connect system/goodluck@jayadb
We can connect to the system user.
Step 4: Creating the tns entry for the database connectable.
So far we tried to connect to the database using the tnsnames entry for the JAYADB container database. Now, let’s try to connect to the pluggable database. To connect to the pluggable database, we need to mention the pluggable database details 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 JAYAPDB pluggable database. We had already removed it because we need to understand how we should enter the tnsnames entry for any database. So, we’re creating it again for more practice. If you already have this entry, please 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 JAYAPDB pluggable database.
Now this JAYAPDB alias name points to the service name jayapdb.com and this service points to the pluggable database JAYAPDB. Save the file after making the changes.
Step 5: Connect to the pluggable database using tnsnames.ora.
So, we’ve made changes in the tnsnames.ora. Let’s try connecting to the pluggable database using the same syntax.Syntax: Connect system/goodluck@jayapdb.
You get an error saying “the database is not open”. If the database is not in the 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 following command.
Command: alter pluggable database JAYAPDB open;
Now try connecting to the pluggable database again using connect system/goodluck@jayapdb. It will connect without any problem. Furthermore, you can check the container name using the “show con_name” command.
Step 6: Creating tnsnames.ora in the local computer.
So far we connected from the server side and connected using the client style. So, let’s try to connect to the database from our local computer using the tnsnames.ora file. Open the listener.ora file and copy the alias description from the database ie. Description of Jayadb and jayapdb. Do not copy the description from LISTENER_JAYADB.
Go to your local computer and create a text document and paste it into the text document.
Rename the text file to tnsnames.ora. The file extension must be .ora file. So this is the same listener file that is present inside the server with slight modifications. This will help us connect to the database using the TNS method in SQL Developer.
Note: The file extension must be ora or else the SQL developer does not choose the file.
Step 7: Connect using tnsnames.ora from SQL developer on the local computer.
Open SQL Developer on local computer and click tools.
Click preferences.
Click on the base and select the advanced option.
The Advanced option is the place where we need to make changes to the tnsnames directory.
Click Browse and select the directory where you saved the tnsnames.ora file. I am selecting the desktop folder because I saved the file in the desktop folder.
After selecting the Directory, click OK to save these settings. Once you have changed the settings, click New Connection. Enter the username as system and enter the password for the system user.
Select the connection type as TNS.
There you can find the network alias names. Select JAYAPDB.
Click Test. You can see it’s a success.
So, the connection is successful. Now, let’s try playing with the tnsnames.ora file.
Step 8: Change the alias name and test the connection.
Open the tnsnames.ora file. using notepad.
Change the database alias name to some other name ie (dotnet) I am selecting Dotnet and saving the file.
Now this Dotnet alias name points to the service name jayadb.com which points to the JAYADB database. Open the SQL Developer and try to establish a new connection.
In the connection, type select TNS and it mentions the network alias that can be seen by dotnet.
Select that and test the connection.
We can see the connection is successful, so we can use any alias name in the tnsnames.ora file to to connect to the database.
In the next art article, I’m going to talk about 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 will enjoy this article Connecting to Oracle database using tnsnames.ora.
.