DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

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

Hybrid View

  1. #1
    Join Date
    Oct 2020
    Posts
    1

    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


    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)


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

    Thanks for your help!

  2. #2
    Join Date
    Dec 2002
    Posts
    74
    Did you reboot SQL Server after you installed Oracle client and created linked server?. I have faced this many times and reboot fixes it "magically"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width