How to Connect to Oracle Database

Back to: Oracle DBA Tutorials

How to Connect to Oracle Database

In this article, I am going to discuss How to Connect to Oracle Database i.e. going to discuss Oracle Listener and Listener.ora in detail. Please read our previous article where we discussed how to Shutdown Oracle Database.

Connecting to Oracle Database

Previously there is only one way to connect to the database being on the host and connect to the database. Now Oracle supports TCP/IP and Secure TCP/IP connections from clients. So, we can connect to the database in many other ways like connecting through command prompt and connecting using SQL developer.

How to Connect to the Oracle database?

To make a connection with oracle we need Oracle net services. These oracle net services are installed with the database. There is an important component that is associated with the oracle net services is the oracle net listener.

What is Oracle Net Listener?

Oracle net listener is a separate process that runs on the database server computer. It receives incoming client requests and manages the traffic of these requests to the database server. The purpose of the Oracle listener is to listen to the client connections. Oracle uses two main files for network configurations for connection between the client and the database. We will discuss these files in detail.

1. listener.ora
2. tnsnames.ora

Example:

We have an oracle client (client indicates any user or any application user who is connecting to the database) who is trying to connect to the database. In order to connect to a specific database, we need a network. Let us assume that there are six databases. A user needs to connect to database 1. He will connect to the database through the network using a protocol. We need some object to listen to the client connections. We call that object an Oracle listener. Oracle listener listens to the client’s requests.

What is Oracle Listener?

Oracle listener is a gateway to the actual oracle database. This is a separate component present in the server. The job of the Oracle listener is to wait and listen for the connections of the oracle database. Multiple Oracle instances running on the same server can use the same oracle listener as a gateway for all the connecting clients. This means all the oracle databases with the same version (ie. oracle 19c) running in the same server can use the same listener. In the above image, you can see there is only one listener that is managed for all the databases inside the server.

When a client needs to establish a connection to a specific oracle instance or database. The oracle client will use a combination of the hostname where the instance and the listener are running as well as the uniquely identifying service name for the desired instance. The oracle listener is a process running on the server and always listens to the client connections that are connecting to the database. We will understand the service name in detail. Oracle listener is the process that receives incoming client connections. There is always a file that is configured for the listener. We call the file name listener.ora

What is Listener.ora?

Listener.ora is a sql.net configuration file used to configure oracle database listeners (required to accept remote connection requests which were requested by the clients or users). The location of the listener file is $ORACLE_HOME/network/admin/. This is an example. I have gone through the path that is mentioned above and then opened the listener file.

From the above image, we can see the Listener name is mentioned as LISTENER. So, this listener is trying to accept the connection between the two addresses. Below are the two addresses where the connections are accepted from the client users. In the first address column, the protocol used is TCP protocol and the host’s name is oracletest.com and the port number is 1521. Port 1521 is the default port. The TCP protocol is the default. So, the users who are connecting to the database will have a valid connection parameter.

What is the difference between TCP/IP and IPC protocols?

TCP/IP is the standard communication protocol used for client/server communication over a network. This is for the client servers. The IPC protocol support can be used only when the client program and oracle database are installed on the same system. In most cases, we use TCP/IP protocol only.

When a client needs to establish a connection to a specific oracle instance or database. The oracle client will use a combination of the hostname where the instance and the listener are running as well as the uniquely identifying service name for the desired instance. Let us try to understand the meaning of the service name and how it is useful.

What is the service name?

When the client needs to connect to the database the client will use a combination of the hostname and the service name in order to connect to the database.

For example, there is a client. He needs to connect to the server and also needs to connect to the database inside the server. So, in order to connect to these, the client needs to know about the hostname, port number, protocol name (TCP), and also the service name to connect to the database.

Service name: An oracle database is represented to clients as a service name. It is just an alias for the database. A database can have one or multiple service names associated with it. SERVICE_NAME is the new feature that was launched from the 8i version of the oracle database in which the database can register itself with the listener.

Note: The Listener Registration Process (LREG) registers the information about the database instance and dispatcher processes with the Oracle Net Listener. The LREG process is one of the background processes that are important for running a database. This is called Dynamic Registration. When the instance is started this LREG process will register the instance and database information to the listener. This LREG background process was released from the oracle 12 version. Before the 12c version, the background process PMON is used to perform the listener registration. But from 12c the background process LREG is used for the registration of the database to the listener.

One Service for the Database:

We have a database called “jayadb” and this database is present inside a server. This database is associated with a service name called “jayadb.com”. So, the client will use the service name “jayadb.com” to connect to the database “jayadb”. The names of service names and database names can be the same. We call this One service for the database.

Multiple Services for one database:

We have a database called “dotnetdb” which is present inside a server. There are two more services associated with this database. The services are “usersdotnetdb.com” and “professorsdotnetdb.com“. These two services are pointing to the same database called “dotnetdb“. Actually, we have some situations where the services are of different regions with different times. We will discuss this later part.

What is tnsnames.ora?

When the oracle client is trying to connect to the database, he needs an oracle listener. The oracle client needs to know about the below information before connecting to the database.

1. Host name where the listener and database are running.
2. Port Number
3. Listener Protocol
4. Service name that the listener is handling

Instead of mentioning all these parameters every single time during the connection of the database, we use a client-side configuration file called tnsnames.ora. This file will be present on the client side. This file will be present on the server side in case if we want to connect as the client on the server. The purpose of this tnsnames.ora file is to make things simple. Below image is the entry of the tnsname.ora file.

We have tnsnames entry as “JAYADB” we can rename this if it is required. This entry contains two main parts Description and the Connection. The description part contains the Address where the important information is stored. We use a protocol called TCP protocol and the host’s name is mentioned as oracletest.com and the port number is 1521. So, whenever the user connection should match these parameters.

The other part is connected. We mentioned it as CONNECT_DATA. In this section, we specify the service name jayadb.com. The service jayadb.com is pointing to the database “jayadb”. And the other is Server is mentioned as Dedicated. Previously we have discussed the server process are of two types i.e. dedicated server process and shared server process. We are using a dedicated server process.

Connecting to the database:

Connect username/password@jayadb

In the above connection, we are providing the username, password, and the tns entry name of the database present in the tnsnames.ora file.

How do client connections work?

Now we will understand how tnsnames.ora and listener.ora coordinate to make connections for the client. The user is connecting to the database using username/password@jayadb. The “jayadb” is the entry name in the tnsnames.ora file. So, the oracle client will go to tnsnames.ora file and check for the meaning of the “jayadb”. The Oracle client check for the address where the hostname, port number, and protocol is present and checks for the service name. So, after checking the oracle client needs to connect to the jayadb.com service name which is present inside the local host. After checking this information will be sent to the oracle listener. The Oracle listener checks whether the connection is valid or not. If the connection is valid then the Oracle listener will create the server process and then connects to the database. Now, the client is connected to the database. He can make insert, update, and delete in the database.

Note: Once the client is connected to the database. There is no need for a listener. If the DBA stops the Oracle listener, then also you will be connected to the database. But if the client closes and opens his session again then the listener is required to make a new connection.

In the next article, I am going to discuss Installing Putty Software and Testing Connections. Here, in this article, I try to explain how to connect to Oracle Database and I hope you enjoy this Connecting to Oracle Database article.