ORA-12514: TNS: Listener has no knowledge about the service

[gpereira]

Good afternoon.

I am experiencing connection problems after removing domain database server.

I made changes to the network files, but there was no expected effect.

I tried some solutions presented in other topics, but I did not succeed.

Select all

SQL> conn sys/pppppppp@(DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = V000 
0857)(PORT = 1521))    (CONNECT_DATA = (SERVICE_NAME = orcl))  ) 
 
ORA-12514: TNS:listener nÒo tem conhecimento sobre o serviþo

The strangest is that I do tnscing successfully:

Select all

C:\Windows\system32>tnsping orcl 
 
TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 - Production on 11-OUT-2 
016 17:30:02 
 
Copyright (c) 1997, 2010, Oracle.  All rights reserved. 
 
Arquivos de parÔmetros usados: 
C:\app\x173576\product\11.2.0\dbhome_1\network\admin\sqlnet.ora 
 
 
Usado o adaptador TNSNAMES para resolver o apelido 
Tentativa de contatar (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = V0000857 
)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCL))) 
OK (10 ms) 

Below settings:

Listerner

Select all

 
# listener.ora Network Configuration File: C:\app\x173576\product\11.2.0\dbhome_1\network\admin\listener.ora 
# Generated by Oracle configuration tools. 
 
SID_LIST_LISTENER = 
  (SID_LIST = 
    (SID_DESC = 
      (SID_NAME = CLRExtProc) 
      (ORACLE_HOME = C:\app\x173576\product\11.2.0\dbhome_1) 
      (PROGRAM = extproc) 
      (ENVS = "EXTPROC_DLLS=ONLY:C:\app\x173576\product\11.2.0\dbhome_1\bin\oraclr11.dll") 
    ) 
  ) 
 
LISTENER = 
  (DESCRIPTION_LIST = 
    (DESCRIPTION = 
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) 
      (ADDRESS = (PROTOCOL = TCP)(HOST = V0000857)(PORT = 1521)) 
    ) 
  ) 
 
ADR_BASE_LISTENER = C:\app\x173576

TNSNames

Select all

 
# tnsnames.ora Network Configuration File: C:\app\x173576\product\11.2.0\dbhome_1\network\admin\tnsnames.ora 
# Generated by Oracle configuration tools. 
 
ORACLR_CONNECTION_DATA = 
  (DESCRIPTION = 
    (ADDRESS_LIST = 
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) 
    ) 
    (CONNECT_DATA = 
      (SID = CLRExtProc) 
      (PRESENTATION = RO) 
    ) 
  ) 
 
ORCL = 
  (DESCRIPTION = 
    (ADDRESS = (PROTOCOL = TCP)(HOST = V0000857)(PORT = 1521)) 
    (CONNECT_DATA = 
      (SERVER = DEDICATED) 
      (SERVICE_NAME = ORCL) 
    ) 
  ) 

SQLNET

Select all

 
# sqlnet.ora Network Configuration File: C:\app\x173576\product\11.2.0\dbhome_1\network\admin\sqlnet.ora 
# Generated by Oracle configuration tools. 
 
# This file is actually generated by netca. But if customers choose to  
# install "Software Only", this file wont exist and without the native  
# authentication, they will not be able to connect to the database on NT. 
 
SQLNET.AUTHENTICATION_SERVICES= (NONE) 
 
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

[adrianoturbo]

Brother, as is installed in Windows, verifies that the Listener is active in Windows services.
Point to Run and type: Services.msc and you can see if the listener is started and active.

[gpereira]

OPA ...

This started, even had already been tested to restart it. Unsuccessfully.

With the Listener service not started TnSping has no return.

[stcoutinho]

Hi,

Take a test with this option:

Select all

tnsping  (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=V0000857)(PORT=1521))(CONNECT_DATA=(SID=orcl)))) 

and SQLPlus:

Select all

SQL>conn sys/xxxxxx@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=V0000857)(PORT=1521))(CONNECT_DATA=(SID=orcl)))

99] at

Sergio

[gpereira]

Good afternoon.

In this case, he is not successfully successful with tnsping.

Select all

C:\Windows\system32>tnsping (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=V0000857)( 
PORT=1521))(CONNECT_DATA=(SID=orcl)))); 
 
TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 - Production on 19-OUT-2 
016 14:48:54 
 
Copyright (c) 1997, 2010, Oracle.  All rights reserved. 
 
Tentativa de contatar (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=V0000857)(PORT=1 
521))(CONNECT_DATA=(SID=orcl)))); 
TNS-12533: TNS:parÔmetros ADDRESS invßlidos 

Attempt to access with failure:

Select all

C:\Windows\system32>sqlplus sys/xxxx@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HO 
ST=V0000857)(PORT=1521))(CONNECT_DATA=(SID=orcl))) as sysdba 
 
SQL*Plus: Release 11.2.0.1.0 Production on Qua Out 19 14:49:02 2016 
 
Copyright (c) 1982, 2010, Oracle.  All rights reserved. 
 
ERROR: 
ORA-12505: TNS:listener nÒo tem conhecimento sobre o SID informado atualmente 
no descritor de conexÒo

[gpereira]

What intrigues me is to work tnsping and not operate access.


TNS Ping

Select all

C:\Windows\system32>tnsping orcl 
 
TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 - Production on 19-OUT-2 
016 15:01:24 
 
Copyright (c) 1997, 2010, Oracle.  All rights reserved. 
 
Arquivos de parÔmetros usados: 
C:\app\x173576\product\11.2.0\dbhome_1\network\admin\sqlnet.ora 
 
 
Usado o adaptador TNSNAMES para resolver o apelido 
Tentativa de contatar (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = V0000857 
)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl))) 
OK (20 ms) 

Attempt to access:

Select all

C:\Windows\system32>sqlplus sys/vector15@orcl as sysdba 
 
SQL*Plus: Release 11.2.0.1.0 Production on Qua Out 19 15:01:44 2016 
 
Copyright (c) 1982, 2010, Oracle.  All rights reserved. 
 
ERROR: 
ORA-12514: TNS:listener nÒo tem conhecimento sobre o serviþo solicitado 
atualmente no descritor de conexÒo 

[gpereira]

Even problem using easy connection:






Direct connection attempt:

Select all

C:\Windows\system32>sqlplus sys/XXXXXXX@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HO 
ST=V0000857)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORCL))) 
 
SQL*Plus: Release 11.2.0.1.0 Production on Qua Out 19 15:14:17 2016 
 
Copyright (c) 1982, 2010, Oracle.  All rights reserved. 
 
ERROR: 
ORA-12514: TNS:listener nÒo tem conhecimento sobre o serviþo solicitado 
atualmente no descritor de conexÒo

[fbifabio]

Have you tried running at the server command prompt the "lsnrctl status" command and saw if it returned the Orcl name in the list of services that this listener answers?