ORA-12541: TNS:no listener". (.Net SqlClient Data Provider)

Question

Hello,
I am having issue with connecting oracle from Sql Server using Linked Server.
I have installed Oracle client and Sql Server on both the same server.
I have tested TNSPING ORCL coming fine

Blockquote
C:\Windows\system32>TNSPING orcl

TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 - Production on 02-OCT-2020 13:00:03
Copyright (c) 1997, 2010, Oracle. All rights reserved.
Used parameter files:
C:\app\oracle_user\product\11.2.0\dbhome_1\NETWORK\ADMIN\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME=orclXDB)))
OK (0 msec)

Blockquote

My Listener status also looks fine for the service

Blockquote
C:\Windows\system32>lsnrctl status

LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 02-OCT-2020 13:00:24

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER

---

Alias LISTENER
Version TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production
Start Date 01-OCT-2020 23:30:11
Uptime 0 days 13 hr. 30 min. 55 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File C:\app\oracle_user\product\11.2.0\dbhome_1\NETWORK\ADMIN\listener.ora
Listener Log File C:\app\oracle_user\tnslsnr\ORATEST\listener\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\.\pipe\EXTPROC1521ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 3 handler(s) for this service...
Service "Oracle8" has 1 instance(s).
Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully

Blockquote

When I run sqlplus OraUser@ORCL, it's throwing an error:

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

When I try to create the linked server and test it, getting following error:
ORA-12541: TNS:no listener". (.Net SqlClient Data Provider)

My Linked Server script is:

Blockquote

SE master;

EXEC sys.sp_addlinkedserver @Testta = N'ORCL'
,@srvproduct = N'Oracle'
,@provider = N'OraOLEDB.Oracle'
,@datasrc = N'orcl';

-- Configure the server for remote procedure calls
EXEC dbo.sp_serveroption @Testta = N'ORCL' -- sysname
,@optname = 'rpc out' -- varchar(35)
,@optvalue = N'true' -- nvarchar(128)

EXEC sys.sp_addlinkedsrvlogin @rmtsrvname = N'ORCL'
,@useself = 'false'
,@locallogin = NULL
,@rmtuser = N'OraUser'
,@rmtpassword = N'******';

Blockquote

I have tried few things from the Google but didn't help, trying to follow the steps as other have provided.

Thanks for your help!

Answer 1

Please also see the following Sqlnet, tnsnames and listener.ora files

Sqlnet.Ora

SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

TNSNAMES.ORA

LISTENER_ORCL =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(CONNECT_DATA =
(SID = CLRExtProc)
(PRESENTATION = RO)
)
)

ORCL=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME=orclXDB)
)
)

LISTENER.ORA

LISTENER_ORCL =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(CONNECT_DATA =
(SID = CLRExtProc)
(PRESENTATION = RO)
)
)

ORCL=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME=orclXDB)
)
)

Answer 2

Also please see the result for C:\Windows\system32>lsnrctl services

LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 02-OCT-2020 15:34:51

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 3 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
"ORACLE SERVER" established:0 refused:0 current:0 max:25 state:ready
CLRExtProc
(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\.\pipe\NTN_B18_4AA6BBE0.ORA))
"ORACLE SERVER" established:0 refused:0 current:0 max:25 state:ready
CLRExtProc
(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\.\pipe\NTN_B18_4AA6BBDE.ORA))
Service "orcl" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:137 refused:0 state:ready
LOCAL SERVER
Service "orclXDB" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: ORATEST, pid: 9728>
(ADDRESS=(PROTOCOL=tcp)(HOST=ORATEST)(PORT=58463))
The command completed successfully

Answer 3

Try bypassing the TNSNAMES.ORA file by using EazyConnect naming. eg

EXEC sys.sp_addlinkedserver @server = N'ORCL'
,@srvproduct = N'Oracle'
,@provider = N'OraOLEDB.Oracle'
,@datasrc = N'//localhost/orcl';

Answer 4

Hi @pdsqsql ,

Do you still have the error?
If yes,please troubleshoot as this: how-to-resolve-ora-12514-tns-listener-does-not-currently-know-of-service-requested-in-connect-descriptor
Follow steps as this : oracle-linked-server

2:

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 5

Still having issue