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:

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: