Starting and Stopping Listeners in Oracle
In this article, I am going to discuss Starting and Stopping Listeners in Oracle. Please read our previous article where we discussed Shifting from Container to Pluggable Database. This topic is very important for the connectivity of the database. In the first step, we need to check where the listener is present inside the server.
Checking the Location of Listener Files in Oracle:
Open file manager. Click on the other locations directory and select Computer and go to /uo1 folder.
After opening 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 listener.ora, tnsnames.ora and sqlnet.ora files.
What is the listener.ora in the Oracle database?
First, open the listener.ora file. To open this file right click on the listener.ora and select open with and select the application “Text Editor”.
Once you click on Open with another application it shows the list of files. Select “Text Editor” and open the listener.ora file.
This is the listener file. This is the configuration of the listener. The listener.ora file consists of the addresses, hostname, and port number which are required for connections.
The listener’s name is LISTENER. This is the default listener. This listener will accept all the connections that point to the below addresses. Most of the time we will be using the first address as the connection protocol i.e TCP protocol.
Any Client trying to access the database need to have the address of TCP protocol and the hostname as oracletest.com and the port number as 1521. The port number 1521 is the default port number and the protocol TCP is the default protocol. If the connection satisfies all these address conditions, then the connection is a valid connection and the client will be able to connect. When you are using the default port number and default protocol the listener file is not much important.
Note: Even if you delete this file or rename this listener file the connection will work. But don’t try to delete this listener.ora file.
What is tnsnames.ora in Oracle?
This tnsnames.ora can exist in both the client system and the server system. This file makes the connections as client style in the server. Let us see more detail about the tnsnames.ora by opening the file.
This is the tnsnames.ora file. You can see there are entries for JAYADB and JAYAPDB and the listener LISTENER_JAYADB for the database jayadb. We call this a local listener.
Note: If you don’t have an entry for pluggable database (i.e jayapdb) kind of entry that is not an issue. If you have it, you can delete those entries or else leave those entries.
Now let us see the details of the entry to the JAYADB database.
We see the entry name as JAYADB so we can log in using the tnsname JAYADB. This entry is pointing to the address with protocol as TCP and host as oracletest.com and port number as 1521 which is the same as in the listener file. We have an extra name called service_name as jayadb.com which points to the database JAYADB. Now save the tnsnames.ora file and close the file.
Note: Whenever we start the Linux machine the listener starts up automatically. There is no need to start the listener every time you start the Linux machine.
How to stop the Listeners in Oracle?
In Linux machines, the listener can be stopped using commands. So, in order to execute the commands, you need to open the terminal.
Command: lsnrctl stop and press enter.
Once the process of stopping the listener is completed it will appear as below.
At the end you will see the command completed successfully, this indicates that the listener is stopped successfully. If the listener is down no user can connect to the database. Now, let’s test whether we can connect to the database or not.
Minimize the Linux machine. Don’t close the Linux machine. If you close the machine the server will be stopped and the database also will be stopped. Open SQL developer. Let us assume an oracle client and create a new connection to the database.
Click on Oracle connections and click the + icon. You will get the below image.
Now enter the username as “sys as sysdba” and enter the password for the system user. We have changed the password recently so enter the new password.
Click on the connection type we can connect using the basic type as well as the TNS type. The basic connection is very simple. You need to provide the hostname, port number, SID, or service name. So, we will be choosing a basic connection Type.
In the hostname column, we are entering the hostname “oracletest.com” and the I.P Address for this host is already present in the host file on the local computer. You can go through this document for further detail about the host file. Installing Putty Software and Testing Connections (dotnettutorials.net)
We will try to connect using the I.P Address as well once the connection with the hostname is successful. Enter the port number that is present in the listener file. The port number that we are using is 1521.After entering the port number the SID. Enter the SID “JAYADB”
If you don’t remember the instance name. Go to the virtual machine and open the terminal. Connect to the database as “sqlplus/ as sysdba“. If the database is not up and running start the database.
Only the sys user can connect to the database even if the listener is down. But the sys should use this method “sqlplus/as sysdba” in case the listener is down. After connecting to the database run the below query to check the instance name.
Query: select instance_name from v$instance;
We are using the select query to get the instance name from the data dictionary view v$instance. The instance name is the same as SID. You can copy the instance name and enter it in the SQL developer, and test the connection. You will be getting the following error.
Error: Failure- Test Failed: IO Error: The Network Adapter could not establish the connection. This error indicates that there is a problem in the network or that the listener is down. So, now we got to know that the listener is down. This is the way we stop the listener. We will try to learn how to start the listener.
How to Start the Listeners in Oracle?
Until now we have worked on how to stop the listener and tried connecting to the database. So, let’s try to start the listener and test the connection. We use the same way as the listener down.
Command: Isnrctl start and press enter.
You can see the image and states that The command was completed successfully. You can see the alias name as LISTENER and the start date and time of the listener and the security level and location of the login files of the listener. You can see the listener supports one service jayadb.com. This is because there is a background process that registers the listeners to the oracle database (LREG). So, the LREG background process registered the service to the listener.
You can check all the services using the below command. This command also shows us the status of the listener whether the listener is running or not and any issue with the listener.
Command: Isnrctl status
Now we can see the LREG process has registered all these services. We can services called jayapdb.com and jayadb.com and these are the global names for the databases JAYADB and JAYAPDB. When we create any database the oracle itself creates a service with the same name and with the domain. Currently, we have two services jayadb.com which are pointing to the main container database JAYADB, and jayapdb.com which is pointing to another pluggable database JAYAPDB and there are other services that are related to the JAYADB database. So, we have started the listener now let’s try again in the listener and try to connect to the database again.
Go to SQL developer and test the connection again. Click test you will see the status as success.
Now, let’s try with the service name. Enter the service name of the database. Click Test and we see success again.
Now let’s connect to the pluggable database JAYAPDB. Copy the service name of the pluggable database jayapdb.com and paste it into the service name box and click on Test. You can see the connection is a success.
Now let us try to connect with the I.P address of the host. You can get the I.P address of the Linux machine from the host file on your local computer. For any reference, you can go through this document Installing Putty Software and Testing Connections (dotnettutorials.net).
Copy the I.P address and paste it into the hostname box and click Test. The Connection is a success.
Note: This is the same I.P address for the virtual machine. To check this out Open the Linux virtual machine and open the terminal and enter the command ifconfig. There you can see the I.P address.
Note: You need to connect to the internet always. If you Turn off the wired connection then you will lose this I.P address because when we are setting up the virtual machine, we have selected the bridge adapter. This means the IP address will be taken always from our WIFI or any network connections.
In the next article, I am going to discuss Easy Connect Method to connecting with Oracle Database. Here, in this article, I try to explain Starting and Stopping Listeners in Oracle and I hope you enjoy this Starting and Stopping Listener in Oracle article.