Problem with multiple servers in config

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
Miscode
Posts: 5
Joined: Fri 13 Oct 2017 08:44

Problem with multiple servers in config

Post by Miscode » Fri 13 Oct 2017 08:54

Hi all,

Currently I'm trying to connect to an Oracle database via Direct mode.
Everything works fine when I have the next entry in the application-config:

User Id=secret;Password=supersecret;Direct=true;Sid=production;Server=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=prd001.internalcorp.net)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=production_prim.internalcorp.net)));

Unfortunately when adding a second server, I get a "failed on open" exception.
User Id=secret;Password=supersecret;Direct=true;Sid=production;Server=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=prd001.internalcorp.net)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=prd002.internalcorp.net)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=production_prim.internalcorp.net)));

Is something wrong with the connectionstring? Or do I need to change some settings in Devart?
When working with TOAD/SqlDeveloper the same identical entry in the tnsnames works fine.

Regards,
Miscode

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Problem with multiple servers in config

Post by Shalex » Fri 13 Oct 2017 17:20

We cannot reproduce the issue in our environment at the moment.

1. Please remove the SID parameter from your connection string.

2. Specify the full stack trace of the "failed on open" exception.

Miscode
Posts: 5
Joined: Fri 13 Oct 2017 08:44

Re: Problem with multiple servers in config

Post by Miscode » Mon 16 Oct 2017 08:43

Hi Shalex, thank you for your reply.

I've removed the sid to no avail unfortunately.

The stacktrace:
  • bij System.Data.Entity.Core.EntityClient.EntityConnection.Open()
    bij System.Data.Entity.Core.Objects.ObjectContext.EnsureConnection(Boolean shouldMonitorTransactions)
    bij System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
    bij System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClass7.<GetResults>b__5()
    bij System.Data.Entity.Core.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
    bij System.Data.Entity.Core.Objects.ObjectQuery`1.<System.Collections.Generic.IEnumerable<T>.GetEnumerator>b__0()
    bij System.Data.Entity.Internal.LazyEnumerator`1.MoveNext()
    bij System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
    bij System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
    bij OracleRepro.Api.Services.DbRepository`1.FindBy(Expression`1 predicate) in C:\dev\projects\OracleRepro\Db\Repositories\DbCountryRepository.cs:regel 25
    bij OracleRepro.Api.Services.DbService.GetAllCountries() in C:\dev\projects\OracleRepro\Db\Services\DbService.cs:regel 406
    bij OracleRepro.Api.Controllers.CountriesController.Get() in C:\dev\projects\OracleRepro\Api\Controllers\CountriesController.cs:regel 24

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Problem with multiple servers in config

Post by Shalex » Tue 17 Oct 2017 18:03

There should be the inner exception with provider specific error and its stack trace.

JIC:
* select Common Language Runtime Exceptions in Debug > Windows > Exception Settings
* when an exception is thrown by your code in the debug mode, press Copy Details and specify this information

Miscode
Posts: 5
Joined: Fri 13 Oct 2017 08:44

Re: Problem with multiple servers in config

Post by Miscode » Wed 18 Oct 2017 09:57

Devart.Data.Oracle.OracleException occurred
Code=-1
ErrorCode=-2147467259
HResult=-2147467259
IsRecoverable=false
Message=NET: Invalid Service Name
Offset=0
Source=Devart.Data.Oracle
StackTrace:
bij Devart.Data.Oracle.c1.b()
InnerException:

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Problem with multiple servers in config

Post by Shalex » Thu 19 Oct 2017 09:55

Miscode wrote:Devart.Data.Oracle.OracleException occurred
Message=NET: Invalid Service Name
bij Devart.Data.Oracle.c1.b()
That is an exception of ADO.NET level. You should be able to reproduce it in a simple console application:

Code: Select all

var conn = new OracleConnection(connString);
conn.Open();
Please test these 3 connection strings and notify us about the result:

Code: Select all

string connString1 = "User Id=secret;Password=supersecret;Direct=true;Server=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=prd001.internalcorp.net)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=production_prim.internalcorp.net)));";

Code: Select all

string connString2 = "User Id=secret;Password=supersecret;Direct=true;Server=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=prd002.internalcorp.net)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=production_prim.internalcorp.net)));";

Code: Select all

string connString3 = "User Id=secret;Password=supersecret;Direct=true;Server=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=prd001.internalcorp.net)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=prd002.internalcorp.net)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=production_prim.internalcorp.net)));";

Miscode
Posts: 5
Joined: Fri 13 Oct 2017 08:44

Re: Problem with multiple servers in config

Post by Miscode » Thu 19 Oct 2017 11:42

Hi,

I've tried the three options you asked for.

Case 1: works fine
Case 2: invalid service name
Case 3: invalid service name

Case 2 and 3 are throwing the exact same error as the exception from our application mentioned before.

Regards,
Miscode

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Problem with multiple servers in config

Post by Shalex » Thu 19 Oct 2017 12:10

Miscode wrote:Case 2: invalid service name
Looks like the "production_prim.internalcorp.net" service is not running at socket "prd002.internalcorp.net:1521".

Miscode
Posts: 5
Joined: Fri 13 Oct 2017 08:44

Re: Problem with multiple servers in config

Post by Miscode » Thu 19 Oct 2017 13:48

I've double checked with the DBA's and they are confirming that the production_prim is not configured, but it seems that this is common practice.
Our secondary (standby) servers are not available during normal operations. When in need of recovery, the secondary server will be primary.

Why is Devart trying to connect to the secondary? Shouldn't it try to connect to the secondary only when the primary is down? Is that also the reason why accessing the db via direct-mode=false and thus using the installed oracle-client, works?

Regards,
Miscode

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Problem with multiple servers in config

Post by Shalex » Fri 20 Oct 2017 12:31

1. There are no primary and secondary servers in the list. The provider connects to nodes randomly not to overload the only (first in the list) node.

2. We have reproduced the issue when the provider throws error instead of switching to another node. We will investigate it and notify you about the result.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Problem with multiple servers in config

Post by Shalex » Fri 30 Mar 2018 16:59

RAC connection in the Direct mode is improved: viewtopic.php?f=1&t=36966.

Post Reply