Home » RDBMS Server » Networking and Gateways » ORA-12541: TNS:no listener". (.Net SqlClient Data Provider) (2 merged) (Oracle 11g)
ORA-12541: TNS:no listener". (.Net SqlClient Data Provider) (2 merged) [message #682083] Sun, 04 October 2020 12:04 Go to next message
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!
Re: ORA-12541: TNS:no listener". (.Net SqlClient Data Provider) [message #682084 is a reply to message #682083] Sun, 04 October 2020 12:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.

1/
Quote:
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

I doubt your application service name is "orclXDB" which is what you have in tnsnames.ora; I think it is either "Oracle8" or "orcl" (from your lsnrctl services output)

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

Do you test it on the same server than you executed "lsnrctl" and "sqplus"?

If not then "localhost" is a bad configuration parameter value, you have to specify a real network name or address.

ORA-12541: TNS:no listener". (.Net SqlClient Data Provider) [message #682088 is a reply to message #682083] Sun, 04 October 2020 22:18 Go to previous messageGo to next message
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)


My Listener status also looks fine 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 have tried few things from the Google but didn't help, trying to follow the steps as other have provided.

Thanks for your help!

Re: ORA-12541: TNS:no listener". (.Net SqlClient Data Provider) [message #682090 is a reply to message #682088] Mon, 05 October 2020 00:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What is the purpose to repost the same topic 10 hours after I answered you?
You should investigate what I said instead.
(And read again How to use [code] tags and make your code easier to read, you failed to format in your new post.)

[Updated on: Mon, 05 October 2020 02:37]

Report message to a moderator

Re: ORA-12541: TNS:no listener". (.Net SqlClient Data Provider) [message #682099 is a reply to message #682084] Mon, 05 October 2020 09:07 Go to previous messageGo to next message
pdsQsql
Messages: 10
Registered: October 2020
Junior Member
Thanks Michel.
Appreciate your help!
Sorry for duplicating as when posted earlier, i couldn't see the post or anything under my profile but may be it was under approval process?
Actually I tried with all three ORacle8, orclXDB and ORCL but didn't work.
I also tried for host as IP or Fully Qualified Domain name but no luck.
I also tried Service name with full qualified name like ORCL.
If you show me step by step troubleshoot i will follow also if i am missing anything.

Thanks again for your kind support.

Re: ORA-12541: TNS:no listener". (.Net SqlClient Data Provider) [message #682100 is a reply to message #682099] Mon, 05 October 2020 09:12 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
ora-12514 ALWAYS only occurs due to a problem on DB Server system.
One cause of this problem is when the Oracle database is down & needs to be started.
A remote client send a request to the Listener asking to be connected to a specific service.
If/when the listener does not know anything about that service, the listener responds with ora-12514

post results from the following two OS commands:
lsnrctl status
lsnrctl service

Since every connection request to the listener gets logged, listener.log file will contain a line with 12514 as status code.

This line contains valuable debugging details. So post this line & surrounding lines.

for additional debugging suggestions read the URL below:
http://edstevensdba.wordpress.com/2011/03/19/ora-12514/
Re: ORA-12541: TNS:no listener". (.Net SqlClient Data Provider) [message #682101 is a reply to message #682100] Mon, 05 October 2020 09:48 Go to previous messageGo to next message
pdsQsql
Messages: 10
Registered: October 2020
Junior Member
Thanks BlackSwan.
When i was troubleshooting, I also checked the Oracle DB and it was up even though i have shutdown and startup the Oracle DB.

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

Somehow Listener.log is not configured when i was trying to check the log.

Thanks you for your help!
Re: ORA-12541: TNS:no listener". (.Net SqlClient Data Provider) [message #682102 is a reply to message #682101] Mon, 05 October 2020 10:05 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Listener Log File C:\app\oracle_user\tnslsnr\ORATEST\listener\alert\log.xml
Re: ORA-12541: TNS:no listener". (.Net SqlClient Data Provider) [message #682103 is a reply to message #682099] Mon, 05 October 2020 10:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
If you show me step by step troubleshoot i will follow also if i am missing anything.
The way is to show us (copy and paste) everything you tried clearly specifying from where you execute each command (you didn't answer my questions).

Re: ORA-12541: TNS:no listener". (.Net SqlClient Data Provider) [message #682104 is a reply to message #682103] Mon, 05 October 2020 10:21 Go to previous messageGo to next message
pdsQsql
Messages: 10
Registered: October 2020
Junior Member
Thanks Michel.
So you are asking to run again right? I will run all the commands again and copy here the results.
Re: ORA-12541: TNS:no listener". (.Net SqlClient Data Provider) [message #682105 is a reply to message #682104] Mon, 05 October 2020 10:39 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Yes, post, one shot, from the DB server:
set oracle
set path
set tns
lsnrctl services
tnsping orcl
type %ORACLE_HOME%\network\admin\sqlnet.ora
type %ORACLE_HOME%\network\admin\listener.ora
type %ORACLE_HOME%\network\admin\tnsnames.ora
sqlplus user/psw@orcl
And same thing from the Linked Server (but lsnrctl command) with the environment used by the script.

[Updated on: Mon, 05 October 2020 10:46]

Report message to a moderator

Re: ORA-12541: TNS:no listener". (.Net SqlClient Data Provider) [message #682114 is a reply to message #682105] Mon, 05 October 2020 18:52 Go to previous messageGo to next message
pdsQsql
Messages: 10
Registered: October 2020
Junior Member
Thanks Michel for sending step by step, looks like heading into right direction for troubleshooting.

I didn't understand the "And same thing from the Linked Server (but lsnrctl command) with the environment used by the script."
We have on same Server both Oracle and Sql Server.
I have posted also Linked Server script at bottom.

C:\>set oracle
ORACLE_HOME=D:\app\TestUser\product\11.2.0\dbhome_1

C:\>set path
 Path=D:\app\TestUser\product\11.2.0\dbhome_1\bin;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32\WindowsP owerShell\v1.0\;
     	C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\;C:\Program Files\Microsoft SQL Server\110\Tools\Binn\;
	C:\Program Files\Microsoft SQL Server\110\DTS\Binn\;C:\Program Files (x86)\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn\;
	C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\;C:\Program Files (x86)\Microsoft SQL Server\140\DTS\Binn\;
	C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\;C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Binn\;
	C:\Program Files\Microsoft SQL Server\130\Tools\Binn\;C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\;
	C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn\;C:\Program Files\Microsoft SQL Server\120\Tools\Binn\;
	C:\Program Files\Microsoft SQL Server\120\DTS\Binn\;C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn\;
	C:\Program Files (x86)\Microsoft SQL Server\120\DTS\Binn\;C:\Program Files (x86)\Microsoft SQL Server\130\DTS\Binn\;
	C:\Program Files (x86)\Microsoft SQL Server\150\DTS\Binn\;C:\Program Files\Microsoft SQL Server Migration Assistant for Oracle\bin\;
	C:\Users\TestUser\AppData\Local\Microsoft\WindowsApps;
PATHEXT=.COM;.EXE;.BAT;.CMD;.VBS;.VBE;.JS;.JSE;.WSF;.WSH;.MSC
C:\>set tns
TNS_ADMIN=D:\app\TestUser\product\11.2.0\dbhome_1\NETWORK\ADMIN
C:\>lsnrctl services

LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 05-OCT-2020 18:28:19

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.Psilg.PsiOrg.net" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:1617 refused:0 state:ready
         LOCAL SERVER
Service "orclXDB.Psilg.PsiOrg.net" 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.Psilg.PsiOrg.net)(PORT=58463))
The command completed successfully

C:\>tnsping orcl

TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 - Production on 05-OCT-2020 18:28:42

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

Used parameter files:
D:\app\TestUser\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)
C:\>TYPE %ORACLE_HOME%\network\admin\sqlnet.ora

#sqlnet.ora Network Configuration File: D:\app\TestUser\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= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
C:\>type %ORACLE_HOME%\network\admin\listener.ora


#listener.ora Network Configuration File: D:\app\TestUser\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 = D:\app\TestUser\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:D:\app\TestUser\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )

  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = D:\app\TestUser

C:\>type %ORACLE_HOME%\network\admin\tnsnames.ora

#tnsnames.ora Network Configuration File: D:\app\TestUser\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
#Generated by Oracle configuration tools.

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

C:\>sqlplus OraUser/******@orcl

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Linked Server:
USE master;


EXEC sys.sp_addlinkedserver @server = N'ORCL'
                           ,@srvproduct = N'Oracle'
                           ,@provider = N'OraOLEDB.Oracle'
			   ,@datasrc = N'//ORATEST.psilg.psiorg.net:1521/orcl';
                         

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

Thanks for your BIG Help!

[Updated on: Tue, 06 October 2020 00:23] by Moderator

Report message to a moderator

Re: ORA-12541: TNS:no listener". (.Net SqlClient Data Provider) [message #682117 is a reply to message #682114] Tue, 06 October 2020 00:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You incorrectly used the code tags.
Either you select the part of the text you want to format and then click on the "code" button (just before the "size" field); either you first click on this button and then put the text between the 2 generated tags ([code] and [/code]); either you manually put the [code] before the section you want to format and [/code] after it.
I fixed it in your post as well the other tags automatically generated.

Quote:
We have on same Server both Oracle and Sql Server.
Have you the same environment variables when the script is executed?


"lsnrctl services" shows "Service "orcl.Psilg.PsiOrg.net"". you have change the "tnsnames.ora" "ORCL" entry to reflect this:
SERVICE_NAME = orcl.Psilg.PsiOrg.net

[Updated on: Tue, 06 October 2020 01:19]

Report message to a moderator

Re: ORA-12541: TNS:no listener". (.Net SqlClient Data Provider) [message #682145 is a reply to message #682117] Tue, 06 October 2020 13:01 Go to previous messageGo to next message
pdsQsql
Messages: 10
Registered: October 2020
Junior Member
Thanks Michel.
I have tried to use SERVICE_NAME as fully qualified as I already tried with ORCL but didn't work.

What else you think I need to check or you can point out me what I am doing wrong?

Currently Linked Server is working but when I try to use SSMA for Migration and trying Connect to Oracle and entering parameters, getting Listener error.

Connection to Oracle Failed: ORA-12541: TNS: No Listener


I am using following in SSMA:
Provider: OLEDB Provider

Provider: OLEDB Provider
Mode: Standard
Server name: ORatest
Server Port: 1521
Oracle SID: ORCL
User name: ORaUser
Password: ******

Thanks for your help!
Re: ORA-12541: TNS:no listener". (.Net SqlClient Data Provider) [message #682146 is a reply to message #682145] Tue, 06 October 2020 13:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
I have tried to use SERVICE_NAME as fully qualified as I already tried with ORCL but didn't work.
Do not tell, show.
Same commands with new configuration.

You must FIRST make it work with SQL*Plus then we will see for other tools.

Re: ORA-12541: TNS:no listener". (.Net SqlClient Data Provider) [message #682147 is a reply to message #682114] Tue, 06 October 2020 13:24 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
YOur tnsping still indicates 'SERVICE_NAME=orclXDB". You need to modify the tnsnames.ora that exists on the SQL Server machine, and change that to SERVICE_NAME=orcl
Re: ORA-12541: TNS:no listener". (.Net SqlClient Data Provider) [message #682148 is a reply to message #682145] Tue, 06 October 2020 13:29 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
pdsQsql wrote on Tue, 06 October 2020 13:01
Thanks Michel.
I have tried to use SERVICE_NAME as fully qualified as I already tried with ORCL but didn't work.

What else you think I need to check or you can point out me what I am doing wrong?

Currently Linked Server is working but when I try to use SSMA for Migration and trying Connect to Oracle and entering parameters, getting Listener error.

Connection to Oracle Failed: ORA-12541: TNS: No Listener


That error means the request reached the specified host, but there was no oracle listener running on that destination. I also see that you report 'host=localhost'. That specification will just loop back to the requestor. If your MSSQL is on machine-A, and your database is on machine-O, then your HOST= (specified in machine-A's tnsnames.ora) must specify HOST=MACHINE-O. The listener is on the oracle database server.
Re: ORA-12541: TNS:no listener". (.Net SqlClient Data Provider) [message #682149 is a reply to message #682148] Tue, 06 October 2020 15:54 Go to previous messageGo to next message
pdsQsql
Messages: 10
Registered: October 2020
Junior Member
Thanks Ed for your help!
I have Oracle and Sql Server both on same machine that's why i have specified HOST as LocalHost and also I tried with IP and FQDN.
I have also Changed SERVICE_NAME=orcl but didn't work.
Re: ORA-12541: TNS:no listener". (.Net SqlClient Data Provider) [message #682150 is a reply to message #682149] Tue, 06 October 2020 16:13 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
I suspect WINDOWS is confused at OS level.
using CMD prompt commands using COPY & PASTE show us hostname & IP# of this system.
post content of "hosts" file.
Re: ORA-12541: TNS:no listener". (.Net SqlClient Data Provider) [message #682151 is a reply to message #682117] Tue, 06 October 2020 16:34 Go to previous messageGo to next message
pdsQsql
Messages: 10
Registered: October 2020
Junior Member
Thanks Michel for the great Help.

I have changed SERVICE_NAME = orcl.Psilg.Psiorg.net in TNSNAMES.ORA and Sql*Plus is now working

C:\Windows\system32>sqlplus OraUser@ORCL

SQL*Plus: Release 11.2.0.1.0 Production on Tue Oct 6 16:29:26 2020

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

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Re: ORA-12541: TNS:no listener". (.Net SqlClient Data Provider) [message #682155 is a reply to message #682151] Wed, 07 October 2020 00:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Great!
Now for your script, I don't this tool but I think you have to do the same thing in the following statement:
EXEC sys.sp_addlinkedserver @server = N'ORCL'
                           ,@srvproduct = N'Oracle'
                           ,@provider = N'OraOLEDB.Oracle'
			   ,@datasrc = N'//ORATEST.psilg.psiorg.net:1521/orcl';
replacing "orcl" by "orcl.Psilg.Psiorg.net".
Also are you sure "ORATEST.psilg.psiorg.net" is the correct server for you DB server?

Note I think this value of data source indicates the use of Easy Connect (EZCONNECT) and not tnsnames.ora (TNSNAMES) but to be sure you have to refer to the tool documentation.

Re: ORA-12541: TNS:no listener". (.Net SqlClient Data Provider) [message #682196 is a reply to message #682155] Thu, 08 October 2020 10:13 Go to previous message
pdsQsql
Messages: 10
Registered: October 2020
Junior Member
Michel,
Thanks for your very good help, looks like everything is working now after changing the SERVICE_NAME = orcl.Psilg.Psiorg.net in TNSNAMES.ORA.

I really appreciate your great help for step by step troubleshooting.

Thanks once again!
Previous Topic: Database link with proxy user
Next Topic: Oracle DBLink through Oracle Gateway connect to SAP HANA 2.0 Failed
Goto Forum:
  


Current Time: Thu Mar 28 13:23:23 CDT 2024