How to connect with Oracle autonomous database?

[iAmBipinPaul]

Hi , I'm having issue to connect with Oracle autonomous database.

I'm trying to connect to Oracle free autonomous database from EF core 2.2 and i'm getting this erro.

2020-02-29 09:05:33.304378 ThreadID:1   (ERROR)   OracleDatabaseCreator.Exists() :  OracleException.Number: 12537
2020-02-29 09:05:33.307641 ThreadID:1   (ERROR)   OracleDatabaseCreator.Exists() :  Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-12537: Network Session: End of file
 ---> OracleInternal.Network.NetworkException (0x000030F9): ORA-12537: Network Session: End of file
   at OracleInternal.Network.OracleCommunication.DoConnect(String tnsDescriptor)
   at OracleInternal.Network.OracleCommunication.Connect(String tnsDescriptor, Boolean doNAHandshake, String IName, ConnectionOption CO)
   at OracleInternal.ServiceObjects.OracleConnectionImpl.Connect(ConnectionString cs, Boolean bOpenEndUserSession, OracleConnection connRefForCriteria, String instanceName)

Connection String format

 "ConnectionString": "User Id=ADMIN;Password=Password;Data Source=<adb....>.oraclecloud.com;"

so basically how to connect from these connection strings

Thank you :) !

[alexkeh]

Use the TNS Name instead of the partial connection string for the Data Source attribute value.

Here's some tutorials with a more complete walk through. Choose the tutorial that best fits your usage scenario.
Developing .NET Core Applications for Oracle Autonomous Database
Developing .NET Framework Applications for Oracle Autonomous Database
Deploying .NET Applications for Oracle Autonomous Database

[iAmBipinPaul]

Hi ,my connection string is like this but still get the same issue.

"ConnectionString": "User Id=admin;Password=FfYSRn7kduSnCR;Data Source=adb<............>oraclecloud.com:1522/<...........>_high.atp.oraclecloud.com"

[alexkeh]

You won't be able to use Easy Connect syntax to connect to to ADB since it uses TCPS. Use the TNS name.

[iAmBipinPaul]

can you please give me an example ?

[alexkeh]

You pasted a graphic in your original post that has the TNS Names on the left side. That's an example. I also cover end to end how to connect to ADB in those earlier three links I posted with the TNS name example" myadb_high".

[iAmBipinPaul]

Hi , I see only dot net core is mention, what about ADB with EF core ?

[alexkeh]

Ah, you want to use EF Core with ADB. The configuration scenario is different since EF Core doesn't provide access to the entire ODP.NET API surface itself. There are two sets of files an Oracle ADB connections needs: 1) tnsnames.ora and sqlnet. ora 2) wallet (cwallet.sso)

You can place 1) in the current working directory or in the TNS Admin directory location you set. You can place 2) in the wallet directory location you set in either in ODP.NET or sqlnet.ora.

For example, if you add the following namespace to your EF Core app:
using Oracle.ManagedDataAccess.Client;

Then add the following two lines of code before invoking any EF Core commands:

OracleConfiguration.TnsAdmin = @"<DIRECTORY>";
OracleConfiguration.WalletLocation = @"<DIRECTORY>";

Your app will then be able to find 1) and 2) when you set the directory where these files are located.

If you do not want to add these two property settings into your code, here's an alternative example that does not require code changes. You can place 1) in the current working directory or web root directory. For 2), you can modify the following sqlnet.ora file setting to where you r wallet is located:
WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="<my directory>")))

[iAmBipinPaul]

Hi , here is what I tried.
I download wallet and then extracted that in one folder.

protected override void OnConfiguring(DbContextOptionsBuilder options)
            => options.UseOracle("User Id=admin;Password=AdminPassword;Data Source=adb.ap<.......>.oraclecloud.com:1522/<..............>high.atp.oraclecloud.com;");

my code is like this now.

  Console.WriteLine("Inserting a new blog");
                db.Add(new Blog { Url = "http://blogs.msdn.com/adonet" });
                OracleConfiguration.TnsAdmin = @"C:\Users\user1\Downloads\Wallet_DB202002291754";
                OracleConfiguration.WalletLocation = @"C:\Users\user1\Downloads\Wallet_DB202002291754";
                db.SaveChanges();

but I'm getting the same error

so in the connection string which password do we have to use , the one which we created at the time of creating database or at the time of downloading wallet?

[alexkeh]

Your "Data Source" is still not using the TNS Name. Look at the column on the left side of this image you provided.

Use one of those values, such as "<...>_HIGH" or "<...>_MEDIUM". Do not use the column on the right for now despite it being called "Connection String"

The second thing you should do is move these two lines of code

OracleConfiguration.TnsAdmin = @"C:\Users\user1\Downloads\Wallet_DB202002291754";
OracleConfiguration.WalletLocation = @"C:\Users\user1\Downloads\Wallet_DB202002291754";

before invoking any EF Core commands, such as creating your DbContext. I forget exactly which command EF Core tries to initiate a connection. It's just a better practice to place these two commands at the very beginning.

[iAmBipinPaul]

Hi , I placed in the DbContext constructor and it works. after changing data source to TNS name

OracleConfiguration.TnsAdmin = @"C:\Users\user1\Downloads\Wallet_DB202002291754";
OracleConfiguration.WalletLocation = @"C:\Users\user1\Downloads\Wallet_DB202002291754";

Thank you :) !

Update

To me it looks like best place to add this is OnModelCreating. As Constructor will be called multiple times and it through exception when adding this in Constructor from second time on wards ,So in the case of asp.net core application we should add this in OnModelCreating.

 protected override void OnModelCreating(ModelBuilder builder)
        {                        
                OracleConfiguration.TnsAdmin = "C:\Users\user1\Downloads\Wallet_DB202002291754";
                OracleConfiguration.WalletLocation = "C:\Users\user1\Downloads\Wallet_DB202002291754";          
        }

[alexkeh]

No problem! Glad it worked out.

[alexkeh]

I have published the following sample code to demonstrate Oracle EF Core connectivity to Autonomous Database for anyone that stumbles across this thread and needs a sample.

https://github.com/oracle/dotnet-db-samples/tree/master/samples/dotnet-core/ef-core/autonomous-db

[aliaslam63]

I have published the following sample code to demonstrate Oracle EF Core connectivity to Autonomous Database for anyone that stumbles across this thread and needs a sample.

https://github.com/oracle/dotnet-db-samples/tree/master/samples/dotnet-core/ef-core/autonomous-db

This link is not working

[alexkeh]

@aliaslam63
The sample got moved to:
https://github.com/oracle/dotnet-db-samples/tree/master/samples/ef-core/autonomous-db

[aliaslam63]

@aliaslam63 The sample got moved to: https://github.com/oracle/dotnet-db-samples/tree/master/samples/ef-core/autonomous-db

Thanks, I have applied as you quoted above, but I am getting this error now
Oracle.ManagedDataAccess.Client.OracleException: 'ORA-12154: TNS:could not resolve the connect identifier specified'

[alexkeh]

ORA-12154 likely means the net service name provided, such as OracleADB_high, can't be resolved to the full connect descriptor in tnsnames.ora. The descriptor usually begins with "(description=...".

Did you set the OracleConfiguration.TnsAdmin property to point to the directory that contains your tnsnames.ora file?

[aliaslam63]

@alexkeh It was the issue with Data source name in the connection string. It is fixed.
Thanks

[sumanaik123]

ORA-12154 likely means the net service name provided, such as OracleADB_high, can't be resolved to the full connect descriptor in tnsnames.ora. The descriptor usually begins with "(description=...".

Did you set the OracleConfiguration.TnsAdmin property to point to the directory that contains your tnsnames.ora file?

Hi ,
I configured the oracle configuration. Tns admin property to the directory that contains tnsnames. Ora file. But still I am receiving the same error ORA-12154 in the report server. Also tried checking the connection through cmd prompt. tnsping abc_high . It is getting resolved. But when creating the data source in report server portal. Connection fails with ORA12154. Please help me yo get this resolve

[alexkeh]

@sumanaik123 Can you turn on ODP.NET tracing to figure out where ODP.NET is looking for its tnsnames.ora file and which one it uses? The trace file should help resolve why you see an ORA-12154. You can turn on tracing using the following settings for managed ODP.NET or ODP.NET Core.

            OracleConfiguration.TraceFileLocation = @"C:\traces";
            OracleConfiguration.TraceLevel = 7;