question

pdsqsql-8017 avatar image
0 Votes"
pdsqsql-8017 asked AlexanderIvanov-MSFT edited

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

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 @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' **';

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!








sql-server-generalsql-server-migration-assistant
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Shouldn't yo first talk with people in an Oracle forum to get the issue when you connect with Sqlplus resolved first before you starting dabbling with linked servers?

0 Votes 0 ·

Erland,
I agreed with you to check with ORacle expert but i thought this is more going with Sql Linked server so someone has more insight which can help me and thinking that when you are already working with sql server for many years and itm ight someone has already faced this kind of issue.

0 Votes 0 ·

Yeah, I worked with SQL Server for many years. But I have never worked with Oracle, not even linked servers to Oracle.

However, I spent quite a bit of time of troubleshooting, and I have learnt that it is best to try with errors with the least moving parts first. So troubleshoot all errors that does not involve SQL Server first.

0 Votes 0 ·
Show more comments
pdsqsql-8017 avatar image
0 Votes"
pdsqsql-8017 answered

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




5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

pdsqsql-8017 avatar image
0 Votes"
pdsqsql-8017 answered

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



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

DavidBrowne-msft avatar image
0 Votes"
DavidBrowne-msft answered pdsqsql-8017 commented

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';
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks David.
So if I connect through SSMA then I should use "ORCL"?

0 Votes 0 ·

David,
I need your help as i have already bypassed TNSNAMES.ORA and use as following like similar you have suggested, instead of Localhost, i have used FQDN with Port so Linked Server got created in Sql server but when i am trying to connect through SSMA, it's throwing an error ORA:-12541: TNS: NO Listener

My Linked Server script:
1. EXEC sys.sp_addlinkedserver @server = N'ORCL'
2. ,@srvproduct = N'Oracle'
3. ,@provider = N'OraOLEDB.Oracle'
4. ,@datasrc = N'//ORATEST.psi.psiorg.net:1521/orcl';

I am using following in SSMA:
Connect to Oracle

  1. Provider: OLEDB Provider

  2. Mode: Standard Mode ( there are also TNSNAMES mode and Connection String mode option)

  3. Server name: ORATest

  4. Server Port: 1521

  5. Oracle SID: ORCL

  6. User Name: OraUser

  7. Password: *
    8.

ORA:-12541: TNS: NO Listener











0 Votes 0 ·
MiaMiao-MSFT avatar image
0 Votes"
MiaMiao-MSFT answered MiaMiao-MSFT commented

Hi @pdsqsql-8017,

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



BR,
Mia


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


· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Mia,
Thanks for providing the Link, I have still issue.
The Link you have provided which i am unable to open it - http://configuring+connection+from+sql+server+to+oracle+using+linked+server/

0 Votes 0 ·

Hi @pdsqsql-8017,

Sorry.Please try it again: oracle-linked-server

BR,
Mia


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


0 Votes 0 ·
pdsqsql-8017 avatar image
0 Votes"
pdsqsql-8017 answered

Still having issue

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MiaMiao-MSFT avatar image
0 Votes"
MiaMiao-MSFT answered AlexanderIvanov-MSFT commented

Hi @pdsqsql-8017,

Still having issue

If the issue still exists after you have tested, please don't hesitate to ask professional engineers for help, and they will deal with your problem separately and confidentially: https://support.microsoft.com/en-us/assistedsupportproducts

BR,
Mia


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



· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello,
My Linked Server is working right now, I can Query the Oracle tables but having issue when i am trying to use with SSMA for my Migration project.

when I try to use SSMA for Migration and trying Connect to Oracle and entering parameters, getting Listener error.
Not sure, I am using something wrong in SSMA
Connection to Oracle Failed: ORA-12541: TNS: No Listener

I am using following:

  1. Provider: OLEDB Provider

  2. Mode: Standard

  3. Server name: ORatest

  4. Server Port: 1521

  5. Oracle SID: ORCL

  6. User name: ORaUser

  7. Password: **

Thanks for your help!

0 Votes 0 ·

Hi @pdsqsql-8017 ,

Is "ORCL" a SID or a service name? In standard mode in SSMA you can only provide SID, not a service name. I'd suggest you to try Connection String mode, where you can better express your environment configuration.

Regards,
Alex.

0 Votes 0 ·