Blog

Oracle Listener


The Databases/services needs to be registered with listener in order to establish connection.There are two types of registration Namely


 1.Dynamic Registration

 2.Static Registration 


Dynamic registration :

As the name suggests,dynamic means the Database/Service registration is done Automatic. As soon as the listener is started Automatically the dynamic registration happens which is TRUE .but it will not happen immediately at least it takes 60 seconds to identify which are all databases are running and needs to be registered ,so as soon as we start the listener it gives the message the listener supports no services .

Sample:-


and after 60 seconds if we check the listener status it will show the list of databases registered .


Sample:



 if you’re using a port number 1521, And the name of your listener is listener only the dynamic registration will happen.


Sample:-



Static Registration:-

Static Registration will take place firstly whenever the name and the port number of the listener are different. And secondly whenever the database is in mount stage or not open this happens in dataguard environment where primary is open stage and the standby is in mount stage.since the database is in mount stage it cannot be registered with the listener so that time we are using static registration .



How to do static registration happens we will open the listener configuration file which is listener.ora file in that we mention the names of the databases which needs to be registered ,

Sample:-



The status of the service is shown as unknown in static registration and ready in case of dynamic registration.



Whenever we are making changes to listener.ora files listener needs to be restarted so that changes takes place .


Listener and databases are independent of each other Starting and stopping the listener does not impact the database .the existing users can perform there update and select transactions only the new users connections are impacted for a minute .


However listener should not be stopped for a long time and appropriate approval has to be taken from the management if we need to stop any services on the database in a live production environment.

Different utilities to connect to databases through listeners .

For a connection to be successful the listener should be up and running and database should be registered with the listener.

Using SQL developer :

To create a database connection:

In the Connections navigator in SQL Developer, right-click the Connections node and select New Connection.
The New / Select Database Connection dialog box appears, with the Oracle tab displayed.

Enter the following information:

  • In the Connection Name field, enter the name to use for this database connection.

  • In the Username field, enter the name of the user for whom this database connection is being created.

  • In the Password field, enter the password for the user.

  • In the Connection Type field, select the database connection type.
    The connection types are:

    • Basic

    • TNS

    • LDAP

    • Advanced

    • Local/Bequeath

  • When you choose a connection type, the fields below will change to be appropriate for the selected connection type. This example describes the fields for the Basic connection type.

  • In the Role field, select Default or SYSDBA, based on the role assigned to the user.

  • In the Hostname field, enter the name of the host where the database is located.

  • In the Port field, enter the port for the database.

  • In the SID field, enter the SID for the database (when the database connection is for a non-CDB user or for a multitenant container database (CDB) user):

When a database connection to a non-CDB or CDB is created for an administrative user such as SYS, SYSDBA is typically specified in the Role field for the connection.


In the Service name field, enter the service name for the pluggable database (PDB), including the domain name (when the database connection is for a PDB use

When a database connection to a PDB is created for an administrative user such as SYS, SYSDBA is typically specified in the Role field for the connection.


Optionally, click Test to test that the data you provided will allow the specified user to connect to the database.


When you are finished, click Connect to connect using the database connection, or click Save to save the database creation.


Using oracle client :-


Oracle Client is part of Oracle Database software. It is not a database, but, it helps talk with an Oracle database.


Oracle client will behave like putty it will have command prompt .so in order to make connection to database which is on server side that  oracle client need one more file called tnsnames.ora file and on server level we have a file called listener.ora file .there is also  tnsnames file on server and it is used for testing purpose 

Sample tnsnames.ora file :-


Scenario 1:


If a user called u1 wants to connect to our database through oracle client .he gets some error so he sends the screen shot to DBA so the DBA has either has an option to take control over his system and fix the issue or he can test from his side by using tnsnames.ora file .and executing the command called called tnsping 


After he executes the command he identifies the problem that listener is down so he starts the listener .and again executes the tnsping command and he gets the output as ok .

Secerio 2:


If we tnsping using the wrong database name which is not part of tnsnames.ora file  the tnsping command gets hanged and there could be either no output or it will show  an error message .so in order to have a connection name of the database must be correct and there should be entry in the file.


The oracle client connection happens in this way 

Sqlplus pdbuser/testpassword@pdbprim


Location of tnsnames.ora file on client machine 


There are two ways we can change the name of the listener.


1.editing the listener.ora which is located in  

   $ORACLE_HOME/network/admin.

2.or using utilities like netca and netmgr.

How to create listener using netca ?

Purpose:


#

Description

Command / Details / Screenshots

1

Putty to server as oracle

(MAC Users: Do the following steps in XQuartz)

2

Verify ORACLE_BASE is set

echo $ORACLE_BASE

3

Verify ORACLE_HOME is set

echo $ORACLE_HOME

4

Verify TNS_ADMIN is set

echo $TNS_ADMIN

5

Type “netca” to start the Network Configuration Assistance that will help us create the listener called LISTENER

6

7

8

9

10

11

12

13

14

Verify listener is up

ps -ef |grep lsnr

15

Verify the status of the listener

lsnrctl status listener

How to create a connection string using NETCA?


Task:

Task is to create a connection string to connect to Batch33 database. Assumption is you already have oracle client installed on your laptop. If not, you will want to install it first. 


Solutions:


#

Description

Command / Details / Screenshots

1

  1. Click on magnifier on windows 10.

  2. Type cmd

  3. Right click on cmd

  4. Click on Run as Administrator

2

Type netca in command prompt to start “network configuration assistant”

3

Click on LOCAL NET SERVICE NAME CONFIGURATION

Click on ADD

4

Add the service name you want to add.

SERVICE NAME: Batch33

Similarly, if your database name is called “MarsDB” then the service name you want to put in as “MarsDB”.

5

Use “TCP” as the protocol. Click NEXT.

6

Give the IP Address or the Hostname where database is hosted.

If you are my student, use the following:

Hostname: ssh.**********.com

Port: 1521

Note-1: If you do not know the hostname, send me an email and I will provide you the hostname.

7

Click on “No, do not test” and click Next.

8

Provide your database name for “Net Service Name” and click Next.

9

Since we do not want to configure another new service name, lets select NO and click NEXT

10

Click NEXT to proceed.

11

Click FINISH.

12

Verify the connection string is created in TNSNames.ora file.

Tnsnames.ora file resides in:

C:\u01\app\oracle\product\12.2.0\client_1

13

Change permission of tnsnames.ora file so that you can edit it manually.

Right click on tnsnames.ora file and click “Properties.

14

Click on SECURITY

Click the AUTHENTICATED USERS

Click EDIT

15

Click on checkbox of FULL CONTROl 

Click APPLY

Additionally, you may repeat the same for all users. 

16

Double click on tnsnames.ora file to open it.

17

Verify the connection string is created. You want to verify the following:

  1. Hostname: sql.*********.com

  2. Port: 1521

  3. Protocol: TCP

  4. SERVICE_NAME: Batch33

Follow me

Contact us for Training/ Job Support

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.