This discussion is archived
11 Replies Latest reply: Nov 7, 2012 9:57 AM by 963110 RSS

ODP.NET connection string does not resolve TNS entry ORA-12154

972028 Newbie
Currently Being Moderated
I'm trying to connect to oracle 11g db using Oracle.DataAccess.dll through XCOPY on a server. I've copied the 5 required dlls.
Connection string is defined as
Data Source=tnsnamehere;User Id=abc;Password=abc;
When tried to connect, hit the following error
ORA-12154: TNS:could not resolve the connect identifier specified
When tns-less connection is used connection on the server works as well.

Please let me know why is the issue and how to resolve?

Notes:
1. Same when tried on the development machine works
2. 3. oci.dll,Oracle.DataAccess.dll,orannzsbb11.dll,OraOps11w.dll,oraociei11.dll,OraOps11w.dll are deployed in the app bin directory
3. This is a .net windows service application
4. Version used:ODAC 11.2 Release 5 (11.2.0.3.20)
5. Verified that required tns entries exists on the server and when System.Data.OracleClient provider is used tns connection works as well.
  • 1. Re: ODP.NET connection string does not resolve TNS entry ORA-12154
    Mark Williams-Oracle Employee ACE
    Currently Being Moderated
    969025 wrote:
    I'm trying to connect to oracle 11g db using Oracle.DataAccess.dll through XCOPY on a server. I've copied the 5 required dlls.
    Connection string is defined as
    Data Source=tnsnamehere;User Id=abc;Password=abc;
    When tried to connect, hit the following error
    ORA-12154: TNS:could not resolve the connect identifier specified
    When tns-less connection is used connection on the server works as well.

    Please let me know why is the issue and how to resolve?

    Notes:
    1. Same when tried on the development machine works
    2. 3. oci.dll,Oracle.DataAccess.dll,orannzsbb11.dll,OraOps11w.dll,oraociei11.dll,OraOps11w.dll are deployed in the app bin directory
    3. This is a .net windows service application
    4. Version used:ODAC 11.2 Release 5 (11.2.0.3.20)
    5. Verified that required tns entries exists on the server and when System.Data.OracleClient provider is used tns connection works as well.
    Where's the tnsnames.ora file located on the machine?

    Are you familiar with the Microsoft SysInternals Process Monitor tool?

    You could use that to capture where the service/client components are looking for the file as a starting point to confirm the correct file is being used.
  • 2. Re: ODP.NET connection string does not resolve TNS entry ORA-12154
    972028 Newbie
    Currently Being Moderated
    Thanks for reply. As the server is a black box, I donot have access.

    tns name resolves when
    1. Remove all the XCOPY ODP.NET dlls (5 of them) from app run directory
    2. Switch the provider from 'Oracle.DataAccess.Client' to Microsoft 'System.Data.OracleClient'

    So with ODP.NET, tnsnames.ora is definitely not picked.
  • 3. Re: ODP.NET connection string does not resolve TNS entry ORA-12154
    Mark Williams-Oracle Employee ACE
    Currently Being Moderated
    You must have another Oracle Home on the machine then and the MS client is getting the tnsnames.ora file from the default location under that Home (as would ODP.NET without the XCopy deployment).

    If you want to use an XCopy deployment with a tnsnames.ora file you will need to make that file available to the XCopy deployment (i.e. put it in the default location, which is what I would recommend, or do something like use TNS_ADMIN to point to an existing tnsnames.ora file in another accessible location).
  • 4. Re: ODP.NET connection string does not resolve TNS entry ORA-12154
    963110 Newbie
    Currently Being Moderated
    Did you know there is a way to enter all 5 values and bypass the tns file altogether?
    var connString = string.Format("Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST={0})(PORT={1})))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME={2})));User Id={3};Password={4};", HostName, Port, ServiceName, UserName, Password);
    HostName = computer name, ServiceName = database name, Port usually 1521(do not use the SDE port).
    We use this in a troubleshooting tool to bypass anything that could be corrupted by a customer.
  • 5. Re: ODP.NET connection string does not resolve TNS entry ORA-12154
    Mark Williams-Oracle Employee ACE
    Currently Being Moderated
    960107 wrote:
    Did you know there is a way to enter all 5 values and bypass the tns file altogether?
    var connString = string.Format("Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST={0})(PORT={1})))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME={2})));User Id={3};Password={4};", HostName, Port, ServiceName, UserName, Password);
    HostName = computer name, ServiceName = database name, Port usually 1521(do not use the SDE port).
    We use this in a troubleshooting tool to bypass anything that could be corrupted by a customer.
    Yes I know that. Can also use EZCONNECT format as well.
  • 6. Re: ODP.NET connection string does not resolve TNS entry ORA-12154
    963110 Newbie
    Currently Being Moderated
    Hey, since your an Oracle person...
    In the initial thread, the porter said he dropped the required dlls into his folder to create an Oracle client.
    I had to do this same thing to get our database troubleshooting tool working.
    The tool works great (using the long 5 input connection string).
    Now that we are getting ready to deploy this tool to our customers, I am being told that to just drop the 5 dlls into the executable's folder to not legal by Oracle standards. Something about we can't break apart a third party install.
    I have seen several places in these forums where people are doing just that to get their projects working, and nothing is being said about the legality of doing it.
    Can you shed some light on this, or direct to someone who can?
    tks.
  • 7. Re: ODP.NET connection string does not resolve TNS entry ORA-12154
    Mark Williams-Oracle Employee ACE
    Currently Being Moderated
    I do not know the official answer to this.

    I will ask someone internally to take a peek at this thread and see if he can provide some input/direction on this question.
  • 8. Re: ODP.NET connection string does not resolve TNS entry ORA-12154
    Alex_Keh - Oracle_Product_Manager Expert
    Currently Being Moderated
    The problem is not legality. Whether it's legal or not depends on the license agreement your organization signed with Oracle. It's doubtful your Oracle contract has any language that makes removing DLLs for the purposes of reducing install size a violation. I don't see it in the OTN download agreement myself. Then again, I'm not a lawyer either. In any case, from a business standpoint, Oracle is not going to sue anyone nor revoke their licenses for removing DLLs if they just want to make the install size smaller.

    From a supportability and functionality standpoint, ODP.NET is tested and certified with all the DLLs bundled with it. Officially, Oracle doesn't support a component where you remove DLLs from the component directory or one of its dependent directories. You may remove DLLs and components from non-dependent directories. For example, if you don't use OLE DB or OraMTS, you can remove those two component directories from your install.

    However, practically speaking, if you try to use some ODP.NET functionality that requires a missing DLL, the solution is to include that DLL again. When you contact Oracle Support, you should be able to reproduce the bug with all the dependent DLLs present. If you can't, then the obvious problem is the missing DLL(s). The error message should tell you what DLL your app can't find.

    If you can live with adding back DLLs to your Oracle Client when needed, for all intents and purposes, you can use this method for your xcopy ODAC deployments. Just make sure you test functionality well to ensure you're not missing any dependencies.
  • 9. Re: ODP.NET connection string does not resolve TNS entry ORA-12154
    Tridus Journeyer
    Currently Being Moderated
    960107 wrote:
    Hey, since your an Oracle person...
    In the initial thread, the porter said he dropped the required dlls into his folder to create an Oracle client.
    I had to do this same thing to get our database troubleshooting tool working.
    The tool works great (using the long 5 input connection string).
    Now that we are getting ready to deploy this tool to our customers, I am being told that to just drop the 5 dlls into the executable's folder to not legal by Oracle standards. Something about we can't break apart a third party install.
    I have seen several places in these forums where people are doing just that to get their projects working, and nothing is being said about the legality of doing it.
    Can you shed some light on this, or direct to someone who can?
    tks.
    Unless you're using something it doesn't support, it's going to be infinitely easier to use the new managed client. That requires one assembly included as a reference in your application (with Copy Local = true), and that's it. No fussing around with all these other files, xcopy, etc.

    There's a beta available, I suggest giving it a look. :)
  • 10. Re: ODP.NET connection string does not resolve TNS entry ORA-12154
    963110 Newbie
    Currently Being Moderated
    It requires us to upgrade from .NET 3.5 to .NET 4.0, which another piece of our product required us to do this morning.
    Looks like it should do exactly what I need.
    thanks!
  • 11. Re: ODP.NET connection string does not resolve TNS entry ORA-12154
    963110 Newbie
    Currently Being Moderated
    Just moved over to the Oracle.ManagedDataAccess.dll (requires .NET 4) and it works great!
    Happy Camper!

Legend

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