This discussion is archived
6 Replies Latest reply: Oct 23, 2012 1:49 PM by 970249 RSS

ORA-12541 TNS:no listener on Oracle.DataAccess connection open

970249 Newbie
Currently Being Moderated
My WCF service uses Oracle.DataAccess.Client to connect to oracle db. It has been running successfully for a year or so using the following connectionString: OracleConnection conn = new OracleConnection(String.Format("USER ID=XXX;PASSWORD=XXX;DATA SOURCE={0}", db));

I recently got a new pc and had to re-install the Oracle client. I tried to add a new operationContract to my existing service and had to re-reference the newly-added ODAC. Suddenly, it won't connect anymore. It fails on the connection.open();

My TNSnames.ora file is in the same spot that it ever was. I even tried copying it to the project's bin directory just for grins.

TOAD works. I can tnsping my database successfully from OraHome. I can access the database using System.Data.OracleClient, but I don't want to rewrite everything in order to use a deprecated dll.

If the tnsnames is there, the paths are identified in my env variables, and the listener is running, what else should be checked? I did notice that the HKEY_LOCAL_MACHINE key for Oracle was pointing to the wrong OraHome path, but even when I modified the path it still wouldn't connect, so I put it back, afraid that I would make TOAD stop working too.

Any suggestions?
  • 1. Re: ORA-12541 TNS:no listener on Oracle.DataAccess connection open
    636190 Explorer
    Currently Being Moderated
    How about your TNS_ADMIN environmental var?
    r,
    dennis
  • 2. Re: ORA-12541 TNS:no listener on Oracle.DataAccess connection open
    970249 Newbie
    Currently Being Moderated
    I just created that env var and pointed it to the network/admin folder where the tnsnames file is (same place TOAD is looking at) No joy.
  • 3. Re: ORA-12541 TNS:no listener on Oracle.DataAccess connection open
    orafad Oracle ACE
    Currently Being Moderated
    967246 wrote:
    ... new OracleConnection(String.Format("USER ID=XXX;PASSWORD=XXX;DATA SOURCE={0}", db));
    What is the value of db?

    If you verify the connection properties, with sqlplus user@'hostname:port/servicename' (replace with specifics from your "db" connection entry), what result do you get?

    If you are using tns naming, did you trace with ProcessMonitor which tnsnames.ora file is actually used in resolving the connect identifier?
  • 4. Re: ORA-12541 TNS:no listener on Oracle.DataAccess connection open
    970249 Newbie
    Currently Being Moderated
    db refers to a specific instance within the TNSnames.ora file (DEV, PTCH, or PROD, etc.):

    DEV =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = pind21-vip.xxx.com)(PORT = 1541))
    (ADDRESS = (PROTOCOL = TCP)(HOST = pind22-vip.xxx.com)(PORT = 1541))
    (LOAD_BALANCE = yes)
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = DEV)
    (FAILOVER_MODE =
    (TYPE = SELECT)
    (METHOD = BASIC)
    (RETRIES = 180)
    (DELAY = 5)
    )
    )
    )

    PTCH =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = pind21-vip.xxx.com)(PORT = 1571))
    (ADDRESS = (PROTOCOL = TCP)(HOST = pind22-vip.xxx.com)(PORT = 1571))
    (LOAD_BALANCE = yes)
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = PTCH)
    (FAILOVER_MODE =
    (TYPE = SELECT)
    (METHOD = BASIC)
    (RETRIES = 180)
    (DELAY = 5)
    )
    )
    )

    PROD =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = pind41-vip.xxx.com)(PORT = 1531))
    (ADDRESS = (PROTOCOL = TCP)(HOST = pind42-vip.xxx.com)(PORT = 1531))
    (LOAD_BALANCE = yes)
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = PROD)
    (FAILOVER_MODE =
    (TYPE = SELECT)
    (METHOD = BASIC)
    (RETRIES = 180)
    (DELAY = 5)
    )
    )
    )

    don't know how to use sqlPlus or ProcessMonitor....but can connect through other .net provider (system.data.oracleclient) using current state and locations of tnsnames, which leads me to believe I need to hunt for something specific to the odp provider I just installed. I just don't know what to look for.
  • 5. Re: ORA-12541 TNS:no listener on Oracle.DataAccess connection open
    970249 Newbie
    Currently Being Moderated
    ok, sqlPlus cnxn successful:

    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
    With the Partitioning, Real Application Clusters, OLAP, Data Mining
    and Real Application Testing options
  • 6. Re: ORA-12541 TNS:no listener on Oracle.DataAccess connection open
    970249 Newbie
    Currently Being Moderated
    Thanks orafad. The sqlPlus thing gave me the hint I needed to fix it. I deleted all of the excess OraHome paths from my path environment variable (even though I had the TNS_ADMIN path set to the correct one) and now it's connecting to the 32-bit version so everything is working again.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points