OraFAQ Forum: Networking and Gateways » ORA-12541: TNS:no listener". (.Net SqlClient Data Provider) (2 merged)

Home » RDBMS Server » Networking and Gateways » ORA-12541: TNS:no listener". (.Net SqlClient Data Provider) (2 merged) (Oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-12541: TNS:no listener". (.Net SqlClient Data Provider) (2 merged) [message #682083]

Sun, 04 October 2020 12:04

pdsQsql
Messages: 10
Registered: October 2020

Junior Member

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

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)

When checking Listener status for the service

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

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)

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)
)
)

My Linked Server script is:

USE master;

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

-- Configure the server for remote procedure calls
EXEC dbo.sp_serveroption @server = 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'**';

I am not a Oracle expert as i am working with Sql Server
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!