This post details how to resolve listener connection issues when DB & listener are started but still connect fails with following errors:-

ORA-12505 TNS:listener could not resolve SID given in connect descriptor
TNS-12505 TNS:listener could not resolve SID given in connect descriptor

ORA-12514 TNS:listener could not resolve SERVICE_NAME given in connect descriptor
TNS-12514 TNS:listener could not resolve SERVICE_NAME given in connect descriptor

The issue is confirmed when you issue “lsnrctl services ” and notice “listener supports no services” .

Following is the output of “lsnrctl status ”

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 26-JUN-2016 18:42:30

Copyright (c) 1991, 2004, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=test1.yourdomain.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     listener
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                26-JUN-2016 17:36:52
Uptime                    0 days 1 hr. 5 min. 37 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/app/dbhome_1/network/admin/listener.ora
Listener Log File         /opt/oracle/app/dbhome_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.144.11)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

As you can notice from the above output that the listener is binding to a specific IP instead of all the interfaces.

Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.144.11)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))

This suggests that the listener is configured on a hostname which does not match the system’s hostname so it would bind to the associated IP.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=test1.yourdomain.com)(PORT=1521)))

Now what will happen in this case, PMON would try to register its service against the listener using the hostname, which will fail since the hostname would not match the system’s hostname. And in this case if no static entries exist for this service in listener.ora, the listener would not be registered with any service and hence the “listener supports no services” message, thus any new client connection would fail because the SERVICE_NAME or SID specified in the tns would not be registered with the listener.

Solution:-

Try one or more of the solutions mentioned below:-

1) Reconfigure listener to bind on system hostname:-

First check the /etc/hosts file for system hostname and update the listener.ora with the correct hostname. Then restart the listener and issue an “ALTER SYSTEM REGISTER” via SQL prompt.

The service should have been registered by now, check the same with lsnrctl services command.

2) Reconfigure the database instance local_listener parameter:-

Check that you have a TNS name which points to the listener (same hostname and port) and use it as a value for the local_listener database parameter, by issuing the following DDL:-

ALTER SYSTEM SET local_listener="(address=(protocol=tcp)(host=<your_host>)(port=<your_port>))" scope=both sid='<INSTANCE_NAME>';

3) Create a static service registration:-

Edit the listener.ora to configure a static registration entry in the listener which will point to the database.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = <instance_name>)
      (GLOBAL_DBNAME=<service_name>)
      (ORACLE_HOME = </path/to/oracle/home>)
    )
  )

And then finally reload the listener by issuing “lsnrctl reload ” in order for the changes to take effect.

By then you should have resolved the issue, as in my experience following one or more of the above solutions have resolved the issue but in case it didn’t please comment below and I’ll try to work with you in resolving the issue.