OracleConnection.Open is throwing ORA-12541 TNS no listener

Question

So I am connecting to an external server through C#. I just installed Oracle 11g client on my machine from here: http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html (255MB one).

After reading many blogs/questions I found this article to be useful:

http://dbaspot.com/oracle-faq/444787-ora-12541-tns-no-listener.html

So is this correct? I cannot do anything. The DBA has to edit the LISTENER.ORA file?

My tnsnames.ora looks like this:

  TestingConnect=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = TestHostName.us.local)(PORT = 1523))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TEST)
    )
  )

It is throwing me the err at:

Oracle.DataAccess.Client.OracleConnection connection = new Oracle.DataAccess.Client.OracleConnection();

connection.ConnectionString = "Data Source=TestHostName.us.local;Persist Security Info=True;" + "User ID=tesName;Password=test";

connection.Open() //Throwing ERR!!!

What should I do? I appreciate any comments. Thanks!

Billi

Are you sure that your hostname "TestHostName.us.local" is correct? Did you check with "tnsping TestingConnect" from the command line whether the database is reachable? – Bun

There are many reasons for this error. If you want to eliminate having to have a TNSNames.ora entry you could try using the TNS-less connection string. see ... connectionstrings.com/oracle just to see if this works. This may not fix the problem but it would eliminate the TNSNames.ora as the problem. – Cytoplast

Answer 1

Thanks for all your input. I changed my connection string and it worked. Here its what looks like:

 private static string GetConnectionString()
    {
        return "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=TestHostName.us.local)(PORT=1523) ) )" +
               "(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=CCDB)));User id=UserName; Password=Password; enlist=false; pooling=false;";
    }

Billi

Answer 2

You can do this a couple of ways: Using your TNSNames file the data source should specify the TNSHosts entry name (the bit before the first "=" from the tnsnames.ora), not the host name:

connection.ConnectionString = "Data Source=TestingConnect;Persist Security Info=True;" + "User ID=tesName;Password=test"; 

Or you can put the entire TNS entry in the connection string like so:

connection.ConnectionString = "Data Source=(DESCRIPTION = " +
    "(ADDRESS = (PROTOCOL = TCP)(HOST = TestHostName.us.local)(PORT = 1523))" +
    "(CONNECT_DATA =" + 
    "(SERVER = DEDICATED)" + 
    "(SERVICE_NAME = TEST))" + 
    ");Persist Security Info=True;User ID=tesName;Password=test"; 

Ardeliaardelis

Answer 3

At my case, this error occured when Drive C is full, and the impact is Oracle Service run unstable..

Oracle.ManagedDataAccess.Client.OracleException (0x80004005): 
ORA-12541: TNS: No listener 
---> OracleInternal.Network.NetworkException (0x80004005): 
ORA-12541: TNS: No listener 
---> System.Net.Sockets.SocketException (0x80004005): 
No connection could be made because the target machine actively refused it {ServerIP}:1521 
at System.Net.Sockets.Socket.InternalEndConnect(IAsyncResult asyncResult) 
at System.Net.Sockets.Socket.EndConnect(IAsyncResult asyncResult) 
at System.Net.Sockets.TcpClient.EndConnect(IAsyncResult asyncResult) 
at OracleInternal.Network.TcpTransportAdapter.Connect(ConnectionOption conOption) 
at OracleInternal.Network.OracleCommunication.DoConnect(String tnsDescriptor) 
at OracleInternal.ServiceObjects.OracleConnectionImpl.Connect(ConnectionString cs, Boolean bOpenEndUserSession, String instanceName) 
at OracleInternal.ConnectionPool.PoolManager`3.CreateNewPR(Int32 reqCount, Boolean bForPoolPopulation, ConnectionString csWithDiffOrNewPwd, String instanceName) 
at OracleInternal.ConnectionPool.PoolManager`3.Get(ConnectionString csWithDiffOrNewPwd, Boolean bGetForApp, String affinityInstanceName, Boolean bForceMatch) 
at OracleInternal.ConnectionPool.OraclePoolManager.Get(ConnectionString csWithNewPassword, Boolean bGetForApp, String affinityInstanceName, Boolean bForceMatch) 
at OracleInternal.ConnectionPool.OracleConnectionDispenser`3.Get(ConnectionString cs, PM conPM, ConnectionString pmCS, SecureString securedPassword, SecureString securedProxyPassword) 
at Oracle.ManagedDataAccess.Client.OracleConnection.Open() 

Try to check it, maybe your C drive is full too.

After that, do restart for some oracle services using Task Manager Check Mikael Holmgren's answer

Wyckoff