Static Listener in Oracle

Back to: Oracle DBA Tutorials

Static Listener in Oracle with Examples

In this article, I am going to discuss Static Listener in Oracle with Examples. Please read our previous article where we discussed Dynamic Listener in Oracle with Examples. Until now, we have learned about dynamic listeners and connections using dynamic listeners. Let us try to learn about static listeners in oracle.

What is Static Listener in Oracle?

Static listener indicates manually added services. The static listener comes in handy when we want to remotely use our database. This is hardcoding the instance details in the listener.ora file.

In order to create a static listener, we have to follow the steps to create a static listener. We will be using the static listener for the pluggable databases that we created earlier. So, we will be adding the static listener for the pluggable databases.

Step 1: Create a new listener for the pluggable databases.

Open the listener.ora file and create a new entry for the pluggable databases. You can refer to the below document regarding how to create a new listener entry. Open the listener file and copy the old listener entry listener19c.

Once the entry is copied paste it above the LISTENER19c and make the listener name LISTENER_PDB as the new listener is used to connect to the pluggable databases. Change the port number to 1529.

Once all changes are done save the file.

Step 2: Enter the services in the listener.

We have to make entries to the services to the listener file manually. We have a term called SID_LIST which has the services list in it. We will be creating that entry. We will be using SID_LIST_LISTENER_PDB which contains all the services for the listener.

We have created SID_LIST_LISTENER_PDB and we have to open and close brackets for this variable. Inside this variable, we have to add another variable called SID_LIST. Open and close the brackets for SID_LIST as well. SID_LIST indicates all the database entries are stored in the SID_LIST. We have to create another entry called SID_DESC which is called SID Description which indicates the database details. SID_DESC is represented below.

In the SID_DESC we have three parameters named GLOBAL_NAME, SID_NAME, and ORACLE_HOME. The GLOBAL_NAME is extracted from the service name.

What is GLOBAL_NAME?

The global name is the database name that is concatenated with the domain name. To make sure know the database name and the domain name, let us connect to the database and check for the list of pluggable database names as we are making this entry for the pluggable database.

Query: select name, open_mode from v$pdbs;

We are using the pluggable database JAYAPDB so that will be the database name, and let’s go ahead and check the domain name. We have already discussed that the domain name of the database will be “com”. So, in that case, the GLOBAL_DBNAME will be JAYAPDB.com

We can get the global name using a query.

Query: select global_name from global_name;

Now the GLOBAL_DBNAME is JAYAPDB.com. Let’s go ahead and check SID_NAME. SID_NAME is the instance name. An instance can be obtained using the command “show parameter instance”

From the output, we can see the instance name is JAYADB. We will be considering the instance name as JAYADB. Let us go ahead and check the directory of ORACLE_HOME. It is a simple command to check the directory.

Command: echo $ORACLE_HOME

So, we have obtained all this information which fills the new listener SID_DESC entry. Applying all these terms will be resulting as below.

So, we have made changes to the new listener LISTENER_PDB. The listener file will be as below.

All the changes are done, now let us save the file.

Step 2: Start the Listener

Connect to the lsnrctl console and start the listener.

Command: start LISTENER_PDB

We can see the listener LISTENER_PDB is started. If we go to the services section, we can see that the status of jayapdb.com is unknown.

Generally, when we start the default listener using a dynamic listener the status will be ready but we have used the static listener, so the listener is displaying the status of service as unknown.

This is because the listener cannot guarantee that the listener is 100% that it is a valid service. This may take this as a mistake in service, so the status is unknown.

Step 3: Connection using the new listener.

Let us try to connect to the pluggable database using the new port number

Command: Sqlplus system/goodluck@oracletest.com:1529/jayapdb.com

The connection is successful without any issues.

Step 4: Add another database entry

If we need to add another database entry for example jayapdb1 is another pluggable database. Let us try to add that database to the listener.ora file. Open the listener.ora file.

Copy the SID_DESC part from the LISTENER_PDB section and paste the closed bracket of SID_DESC as below.

Just replace the old pluggable database with the new pluggable database name. In our case, we have added JAYAPDB1.com as GLOBAL_DBNAME and SID_NAME as jayadb. After making changes the listener.ora file will be appeared as below.

So, we have made all the necessary changes to the listener.ora file. Now, let us save the file.

Step 5: Connect to the pluggable database JAYAPDB1 using a new listener.

Connect to the database as “sqlplus / as sysdba”. Once connected make sure to open all the pluggable databases.

We have opened all the pluggable databases. Let us try to connect to the pluggable database jayapdb1 using port number 1529 by the easy connect method.

Command: connect system/goodluck@oracltest.com:1529/jayapdb1.com

We are getting an error because we have not restarted the listener after adding a new entry. Let us go ahead and connect to the lsnrctl console and restart the listener LISTENER_PDB.

The listener is stopped successfully. Let us go ahead and start the listener LISTENER_PDB again.

We can see the listener is started again. We can see there are two services associated with the listener LISTENER_PDB. Now, the listener is running again. Let us try to check the connection again. Connect to the database as “sqlplus / as sysdba”. Let us try to connect to the pluggable database jayapdb1 using port number 1529 by the easy connect method.

Command: connect system/goodluck@oracltest.com:1529/jayapdb1.com

We can see the database is connected now. In this article, we have learned about how to create a new listener entry and add the SID_DESC to the listener entry and connect to the database using the new port number.

In the next article, I am going to discuss Oracle Net Configuration Assistant with Examples. Here, in this article, I try to explain Static Listener in Oracle with Examples and I hope you enjoy this Static Listener in Oracle with Examples article.