Listener

How to Resolve The listener supports no services

The Listener Supports Services

The listener supports no services

I newly created a database called SMALLDB, but I can’t see any service registered with the new LISTENER2 which is listening to incoming connections from port 1522. The listener showed “The listener supports no services”.

In fact, this message tells us that the listener is working fine, but no dynamic service registers with the listener. We had better check our database configuration closely.

Let’s see the status of listener which is LISTENER2 in our case.

[oracle@primary01 ~]$ export ORACLE_SID=SMALLDB
[oracle@primary01 ~]$ lsnrctl status listener2
...
Listener Parameter File   /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/primary01/listener2/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=primary01)(PORT=1522)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522)))
The listener supports no services
The command completed successfully

For database users, even though the listener is up and running, they may receive ORA-12514 in their connection tools. I talked about some causes and solutions to ORA-12514 in another post.

By the way, the formal error code of “The listener supports no services” is TNS-01030.

TNS-01030 The listener supports no services
TNS-01030 The listener supports no services

Rationale

In most cases, the message indicates that the database instance is closed, no any database services are available. You can just startup your database and make sure the service names are correct.

If you’re pretty sure that your database instance is up and running, please check the parameter of local listener.

SQL> show parameter local_listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string       (ADDRESS=(PROTOCOL=TCP)(HOST=
                                                 192.168.0.111)(PORT=1521))

Usually, we don’t have to set LOCAL_LISTENER parameter explicitly, because the database instance will look for an existing and appropriate local listener to register its services after startup. Once the listener accepts dynamic service registration, it facilitates connections to get database service subsequently.

Let’s continue reading this post to see how we register the service in the listener.

Solution

In our case, we want it go to port 1522. Apparently, it went to the wrong port. Therefore, we corrected the setting by this:

SQL> alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.111)(PORT=1522))';

System altered.

SQL> show parameter local_listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string      (ADDRESS=(PROTOCOL=TCP)(HOST=1
                                                 92.168.0.111)(PORT=1522))

Please note that, if the instance was startup with a SPFILE, it implied SCOPE=BOTH in the above statement. Otherwise, it implied SCOPE=MEMORY.

After setting the parameter, a background process called listener registration (LREG) will discover the target listener in 60 seconds and send information such as the service name, instance names, and workload information to the listeners. Therefore, you don’t have to register it by yourself.

Of course, you can override the 60-second delay of LREG by this SQL statement below: SQL> ALTER SYSTEM REGISTER;
The above statement forces LREG to register with the listener in order to support the database service immediately.

After registration, the listener starts to deal with connections that are meant for the database. Service handlers, either dispatchers or dedicated server processes, will be used to maintain the connections. Once the connections between clients and database established, the listener stepped aside and continued to listen to new connections.

Will it show The listener supports no services this time? Let’s check the listener again.

[oracle@primary01 admin]$ lsnrctl status listener2
...
Listener Parameter File   /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/primary01/listener2/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=primary01)(PORT=1522)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522)))
Services Summary...
Service "SMALLDB" has 1 instance(s).
  Instance "SMALLDB", status READY, has 1 handler(s) for this service...
Service "SMALLDBXDB" has 1 instance(s).
  Instance "SMALLDB", status READY, has 1 handler(s) for this service...
The command completed successfully

It’s back. The listener is holding the services now.

By the way, the service name is usually the same as DB_UNIQUE_NAME in a RAC environment by default. For a single-instance database, the service name is usually the instance name, i.e. $ORACLE_SID.

Static Services

Whereas dynamic services have to be registered with the listener only when the database is up and running, static service registration can be done very early when the listener startup, no matter the instance does exist or not.

That is to say, static services in listener don’t care the instance. You can connect to an idle database as SYS from any remote clients via static services in order to maintain the database. This can be a workaround to The listener supports no services. Because there already have services upheld by the listener.

The common jobs that need to connect an idle or nomount database, for examples, could be starting up a database remotely or duplicating an auxiliary database, say a standby database from a target database.

To take advantages of static service registration, you have to add an entry called SID_LIST_LISTENER to the configuration file of listener which is usually at $ORACLE_HOME/network/admin/listener.ora. Static services will take effect after you restart the listener.

More Considerations

Whenever in doubt, use tnsping and then sqlplus to test all situations of listener or service problems.

If you still have troubles on adding a new listener to a grid infrastructure (RAC), you can refer to my tutorials as below:

They might give you some clues to solve The listener supports no services from different angles.

4 thoughts on “How to Resolve The listener supports no services

Leave a Reply

Your email address will not be published. Required fields are marked *