Can not connect ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Question

I want to connect my PLSQL developer tools into database but fail to connect due to error below. ORA-12514: TNS: listener does not currently know of service requested in connect descriptor

Check configuration below. How to configure to make it works?

SQL> select value from v$parameter where name='service_names';
VALUE
orcl

SQL> select instance from v$thread;
INSTANCE
orcl
SQL> select host_name,instance_name,version from v$instance;
HOST_NAME
INSTANCE_NAME    VERSION
ex-cs-b
orcl             11.2.0.1.0

SQL> select global_name from global_name;

GLOBAL_NAME
ORCL
SQL>

lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 23-OCT-2017 16:49:51

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                23-OCT-2017 16:49:40
Uptime                    0 days 0 hr. 0 min. 10 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /app/oracle/diag/tnslsnr/ex-cs-b/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ex-cs-b)(PORT=1521)))
The listener supports no services
The command completed successfully

lsnrctl services

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 23-OCT-2017 16:58:18

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully



more tnsnames.ora
# tnsnames.ora Network Configuration File: /app/oracle/product/11.2.0/dbhome_1/network
/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ex-cs-b)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

more listener.ora
# listener.ora Network Configuration File: /app/oracle/product/11.2.0/dbhome_1/network
/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = ex-cs-b)(PORT = 1521))
    )
  )
ADR_BASE_LISTENER = /app/oracle

Solution

Your database hasn't been registered with the listener. lsnrctl status returned "The listener supports no services" and your listener.ora file doesn't have a SID_LIST.

Connect to your database instance as a SYSDBA and run:

 ALTER SYSTEM SET LOCAL_LISTENER='';

If that doesn't resolve your problem then you mostly likely have several listeners and you'll need to follow the registration process for the listener that you want.

All of this complexity is to allow an operating system to run multiple listeners, which can be useful.

Do note that depending on how you started your instance, you may need to specify SCOPE on the ALTER SYSTEM command or your configuration will disappear on restart of the instance.

Comments

[snowman0805]

more sqlnet.ora # sqlnet.ora Network Configuration File: /app/oracle/product/11.2.0/dbhome_1/net work/admin/sqlnet.ora # Generated by Oracle configuration tools. NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT) ADR_BASE = /app/oracle

[snowman0805]

I have try to add the SID list in the listener.ora. But found error below. more listener.ora # listener.ora Network Configuration File: /app/oracle/product/11.2.0/dbhome_1/network /admin/listener.ora # Generated by Oracle configuration tools. LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = ex-cs-b)(PORT = 1521)) ) )

[snowman0805]

SID_LIST_LISTENER= (SID_LIST= (SID_DESC= (GLOBAL_DBNAME=ORCL) (ORACLE_HOME=/app/oracle/product/11.2.0/dbhome_1) (SID_NAME=orcl)) (SID_DESC= (SID_NAME=orcl) (ORACLE_HOME=/app/oracle/product/11.2.0/dbhome_1) (PROGRAM=EXTPROC1521))) ADR_BASE_LISTENER = /app/oracle

[snowman0805]

lsnrctl status LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 23-OCT-2017 16:47:49 Copyright (c) 1991, 2009, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521))) TNS-12541: TNS:no listener TNS-12560: TNS:protocol adapter error TNS-00511: No listener Linux Error: 2: No such file or directory Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ex-cs-b)(PORT=1521‌​))) TNS-12541: TNS:no listener TNS-12560: TNS:protocol adapter error TNS-00511: No listener Linux Error: 111: Connection refused

[Steven Ensslen]

Was the listener running when you ran the commands in these comments? Try lsnrctl start as described in this Oracle community thread