Problem with multiple servers in config

Problem with multiple servers in config

Postby 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
Miscode
 
Posts: 5
Joined: Fri 13 Oct 2017 08:44

Re: Problem with multiple servers in config

Postby 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.
Shalex
Devart Team
 
Posts: 7789
Joined: Thu 14 Aug 2008 12:44

Re: Problem with multiple servers in config

Postby 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
Miscode
 
Posts: 5
Joined: Fri 13 Oct 2017 08:44

Re: Problem with multiple servers in config

Postby 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
Shalex
Devart Team
 
Posts: 7789
Joined: Thu 14 Aug 2008 12:44

Re: Problem with multiple servers in config

Postby 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:
Miscode
 
Posts: 5
Joined: Fri 13 Oct 2017 08:44

Re: Problem with multiple servers in config

Postby 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)));";
Shalex
Devart Team
 
Posts: 7789
Joined: Thu 14 Aug 2008 12:44

Re: Problem with multiple servers in config

Postby 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
Miscode
 
Posts: 5
Joined: Fri 13 Oct 2017 08:44

Re: Problem with multiple servers in config

Postby 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".
Shalex
Devart Team
 
Posts: 7789
Joined: Thu 14 Aug 2008 12:44

Re: Problem with multiple servers in config

Postby 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
Miscode
 
Posts: 5
Joined: Fri 13 Oct 2017 08:44

Re: Problem with multiple servers in config

Postby 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
Devart Team
 
Posts: 7789
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle