10 Replies Latest reply: Aug 29, 2014 2:50 PM by user10835044 RSS

    Oracle.ManagedDataAccess.Client deployment without Oracle client

    Ken.Saraf

      Hi,

      We're trying to deploy our application on a server which does not have Oracle client installed. We're using the managed dataAccess dll with entity framework 5. Our web.config file contains the following:

      <connectionStrings>

          <add name="CroEntities" connectionString="metadata=res://*/CroModel.csdl|res://*/CroModel.ssdl|res://*/CroModel.msl;provider=Oracle.ManagedDataAccess.Client;provider connection string=&quot;DATA SOURCE=oracle;PASSWORD=password;PERSIST SECURITY INFO=True;USER ID=user&quot;" providerName="System.Data.EntityClient" />

        </connectionStrings>

       

      <Oracle.ManagedDataAccess.Client>

          <version number="4.121.1.0">

        <dataSources>

                    <dataSource alias="oracle" descriptor="(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = myhost.bc.ca)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = serviceName.bc.ca)))" />

             </dataSources>

          </version>

        </Oracle.ManagedDataAccess.Client>

       

      Oracle.ManagedDataAccess.dll is in our Bin folder.

      When running, we get the following error: ORA-12541: TNS: No listener.

      The listener is working fine. My thinking is that something is missing in our configuration. When we add debug information for the value of context.Database.Connection.Database we get empty string which make me think that the the information in the config file is not making it to the application at run time.

       

      Any thought on what could be missing?

      Thank you,

      Ken

        • 1. Re: Oracle.ManagedDataAccess.Client deployment without Oracle client
          Alex.Keh .Product.Manager-Oracle

          I've seen customers report a problem before where the web.config in the root interferes with the web.config in a virtual directory. It would be a case where the machine Oracle is contacting does not have a listener running on it.

           

          Without knowing your complete setup, it's hard to pinpoint what the issue is. I would start simplifying the stack to see what is likely to be the issue.

           

          There's a couple ways to proceed.

           

          1) Have ODP.NET generate a trace (see Chapter 2) to see if it's connecting to the right server.

           

          2) Simplify the app setup until you find the problematic area.

           

          For example, create a simple managed ODP.NET console app and deploy to your web server that just connects to Oracle without using a config file. The connection string would look like "DATA SOURCE=(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = myhost.bc.ca)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = serviceName.bc.ca)));PASSWORD=password;USER ID=user"

           

          From there, put the data source into an app,config.

           

          Then create an EF app.

           

          At some point, you'll hit the error again and know the problem area.

          • 3. Re: Oracle.ManagedDataAccess.Client deployment without Oracle client
            Ken.Saraf

            Hi Alex and thank you.

            I tried to add the trace as you suggested so my managedDataAccess section now looks like this:

             

            <Oracle.ManagedDataAccess.Client>

                <version number="4.121.1.0">

                   <dataSources>

                          <dataSource alias="oracle" descriptor="(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = edmbchcmtst.bchydro.bc.ca)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = CROTEST.bchydro.bc.ca)))" />

                   </dataSources>

                   <settings>

                     <setting name="TraceLevel" value="7" />

                     <setting name="TraceOption" value="1"/>

                    <setting name="TraceFileLocation" value="D:\CROServices\oraTrace.trc"/>

                  </settings>

                </version>

              </Oracle.ManagedDataAccess.Client>

             

            But I get no trace file created (I gave permission to Everyone to write to this folder so I don't think it's a permission issue). This make me think that my managedDataAccess.dll is not configured correctly. All I did was to place Oracle.ManafedDataAcces.dll in the Bin folder of my application. I didn't registered it in the GAC or added any settings to the machine.config (all my settings are in the web.config).

            The documentation specified that I should ran the configure.bat but, if possible, I would like to keep the machine "clean". SO my questions now are:

            1. Must I registered the dll in the GAC?

            2. Must I add some sections to the machine.config or could it all be done in the web.config?

            3. Is there a way to specify in the config file where the dll is located or should I place it in a specific location?

            4. How does the application "know" to use the managed driver and to read the managedDataAccess section in the config file?

            Thank you,

            Ken

            • 4. Re: Oracle.ManagedDataAccess.Client deployment without Oracle client
              Alex.Keh .Product.Manager-Oracle

              TraceFileLocation needs to be a directory,not a file name. That was a change between unmanaged and managed ODP.NET.

               

              IIRC, you don't have to register ODP.NET in the GAC for run-time. The bin directory should be fine. At design-time, Visual Studio does require that ADO.NET assemblies to be GAC'ed in order to interact with the tooling.

               

              All .NET config can be added to either the machine.config and/or web.config in any combination. With that said, EF does ADO,NET providers to make changes to one of these .NET config files. When you run the Oracle installer, whether using the Xcopy batch file or the Oracle automated installer, these changes will be made. Perhaps that is what you are missing in terms of setup.

               

              It's these setup steps that tell EF to use ODP.NET.

               

              In general, I would recommend on following the ODP.NET install steps if you're not familiar with how EF interacts with data providers. If you do want to configure manually, I would recommend spending some time following how the ODP.NET xcopy install works. It's a plain text batch file. That will detail all the steps you need to enable ODP.NET in a run-time scenario.

              • 5. Re: Re: Oracle.ManagedDataAccess.Client deployment without Oracle client
                Ken.Saraf

                Hi Alex,

                Thank you. I tried your advise with using a simple application to test the issue. What I found is that when I use:

                <connectionStrings>

                  <add name="MyDB" connectionString="DATA SOURCE=MyDB_in_TNSNames;PASSWORD=password;PERSIST SECURITY INFO=True;USER ID=usr" providerName="System.Data.SqlClient" />

                  </connectionStrings>


                Everything is fine. But, when I replaces it with:

                <connectionStrings>

                  <add name="MyDB" connectionString="DATA SOURCE=oracle;PASSWORD=password;PERSIST SECURITY INFO=True;USER ID=usr" providerName="System.Data.SqlClient" />

                  </connectionStrings>

                <Oracle.ManagedDataAccess.Client>

                  <version number="4.121.1.0">

                     <dataSources>

                        <dataSource alias="oracle" descriptor="(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = host.network.bc.ca)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = service.network.bchydro.bc.ca)))" />

                       </dataSources>

                  </version>

                </Oracle.ManagedDataAccess.Client>

                 

                I get "ORA 12541: TNS: No listener" on the development machine which I only use the xcopy installation on it. On my machine, which have the full ODAC installed, I get "ORA-12154: TNS:could not resolve the connect identifier specified"

                 

                The code is super simple:

                 

                using System;

                using Oracle.ManagedDataAccess.Client;

                using Oracle.ManagedDataAccess.Types;

                using System.Configuration;

                 

                namespace Connect

                   class Program

                {

                   static void Main(string[] args)

                   {

                        try

                       {

                             OracleConnection con = new OracleConnection();

                            con.ConnectionString = ConfigurationManager.ConnectionStrings["MyDB"].ConnectionString;

                             Console.WriteLine("Connected to Oracle Database {0}", con.ServerVersion);

                             Console.WriteLine("Press RETURN to exit.");

                             Console.ReadLine();

                         }

                         catch (Exception ex)

                         {

                             Console.WriteLine("Error : {0}", ex);

                             Console.ReadLine();

                          }

                     }

                   }

                }

                 

                So it seem like using the alias is what's causing the problem. Any ideas what is missing to get it to work?

                 

                Thank you,

                Ken

                • 6. Re: Oracle.ManagedDataAccess.Client deployment without Oracle client
                  Alex.Keh .Product.Manager-Oracle

                  I noticed is that your provider name is referencing SqlClient (System.Data.SqlClient), not ODP.NET (Oracle.ManagedDataAccess.Client).

                  • 7. Re: Re: Oracle.ManagedDataAccess.Client deployment without Oracle client
                    Ken.Saraf

                    Hi Alex,

                    Thank you. I changed the provider name to the one you provided above and I still get the same errors on both environments. Anything else you can think of? It seems like the application is ignoring the <Oracle.ManagedDataAccess.Client> configuration section and try to read the "oracle" alias as is? The fact that I'm not able to get tracing working as well, support this as well.


                    Thank you,

                    Ken

                     

                    PS> here's the entire config file:

                    <?xml version="1.0" encoding="utf-8" ?>

                     

                    <configuration>

                      <configSections>

                           <section name="Oracle.ManagedDataAccess.Client" type="OracleInternal.Common.ODPMSectionHandler, Oracle.ManagedDataAccess" />

                      </configSections>

                      <startup>

                      <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />

                      </startup>

                    <connectionStrings>

                           <add name="CroEntities" connectionString="DATA SOURCE=oracle;PASSWORD=password;PERSIST SECURITY INFO=True;USER ID=usr" providerName="Oracle.ManagedDataAccess.Client" />

                      </connectionStrings>

                      <Oracle.ManagedDataAccess.Client>

                      <version number="4.121.1.0">

                           <dataSources>

                                <dataSource alias="oracle" descriptor="(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = host.network.bc.ca)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = service.bchydro.bc.ca)))" />

                           </dataSources>

                      </version>

                      <version number="*">

                      <settings>

                      <setting name="TraceLevel" value="7" />

                      <setting name="TraceOption" value="1"/>

                      <setting name="TraceFileLocation" value="C:\Users\ksaraf\logs\"/>

                      </settings>

                      </version>

                      </Oracle.ManagedDataAccess.Client>

                     

                    </configuration>

                    • 8. Re: Oracle.ManagedDataAccess.Client deployment without Oracle client
                      Alex.Keh .Product.Manager-Oracle

                      The XML tag should be  <oracle.manageddataaccess.client>. All lower case. In your config file, you've capitalized some of the letters in the tag. That's likely why ODP.NET can't read that XML section.

                      • 9. Re: Re: Oracle.ManagedDataAccess.Client deployment without Oracle client
                        Ken.Saraf

                        Thank you so much - This was the issue indeed.

                        • 10. Re: Oracle.ManagedDataAccess.Client deployment without Oracle client
                          user10835044

                          Finally I got the solution as below.

                          I too have the similar problem.

                          I am using .NET4.5.1 x64 bit, Oracle.ManagedDataAccess.

                          In the C# Project settings, there is a flag Prefer32Bit which is checked by default, that's causing the problem. After I removed Prefer32Bit, everything is success.