Using Oracle's DRCP

[NigelW]

I've been evaluating dotConnect for Oracle for an existing software product as we would like to have the data provider in managed code.

I've also been looking at using Oracle's DRCP, specifically setting a connection class value so that connections are pooled between specific groups of users with the same Oracle user id.

Actually setting the connection class via C# and ODP.NET seems impossible and so I was wondering whether dotConnect for Oracle supports this and if, as I suspect, not whether this is a feature that you are planning to provide in the future.

Many thanks

[Shalex]

NigelW, thank you for your suggestion. We are planning to support the DRCP feature. But there is no timeframe at the moment. It can take several months or so. We will post in this thread about the results of implementing this feature.

[Shalex]

We have supported DRCP. I will post here when the corresponding build of dotConnect for Oracle is available.

[Shalex]

New version of dotConnect for Oracle 6.10 is released!
It can be downloaded from http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with valid subscription only): http://secure.devart.com/ .
For more information, please refer to http://www.devart.com/forums/viewtopic.php?t=20115 .

[NigelW]

This is great news, thank you.

I'm running through the DRCP tests again and I'm having trouble. I want to connect directly without using the Oracle client, however I need to pass the equivalent of SERVER=POOLED, which would have appeared in tnsnames.ora if using the Oracle client.

My understanding is that this is needed with connection class to use DRCP and connecting via dotConnect for Oracle doesn't appear to create a connection pool on Oracle.

Sorry if I've missed something obvious, and any advice will be gratefully received.

Many thanks

[Shalex]

DRCP is a feature of Oracle Client. You need to set also OCI Session Polling = true for using DRCP. But this feature is not available in the Direct mode.

[NigelW]

Thanks for the info, however I'm still unable to get it to work; I'm not seeing a connection in v$cpool_cc_stats.

My connection string builder is:

OracleConnectionStringBuilder oraCSB = new OracleConnectionStringBuilder();
oraCSB.Direct = false;
oraCSB.Server = "nigel";
oraCSB.UserId = "xxx";
oraCSB.Password = "yyy";
oraCSB.OciSessionPooling = true;
oraCSB.Home = "OraDb11g_home1";
oraCSB.ConnectionClass = "Branston";

Which generates the connection string:

User Id=xxx;Password=yyy;Server=nigel;Home=OraDb11g_home1;Direct=False;Oci Session Pooling=True;Connection Class=Branston

The TNSNames.ora is:
NIGEL=
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=nigel-vistanew)
(PORT=1521)
)
(CONNECT_DATA=
(SERVICE_NAME=orcl)
(SERVER=POOLED)
)
)

Am I doing anything wrong?

Thanks

[Shalex]

DRCP needs to be started before clients can request for connections. Have you executed the following command to start the Broker, which registers itself with the database listener?

Code: Select all

SQL> execute dbms_connection_pool.start_pool;

If this is not the case, please tell us:
1) the exact versions of your dotConnect for Oracle (x.xx.xxx), Oracle client, and Oracle server;
2) your pool configuration (select * from dba_cpool_info).

[NigelW]

DRCP has been started using start_pool

dotConnect for Oracle version is 6.10.121
Oracle client is 11.1.0.7.0
Oracle server is 11.0.7.0

Pool configuration is:

Code: Select all

CONNECTION_POOL,STATUS,MINSIZE,MAXSIZE,INCRSIZE,SESSION_CACHED_CURSORS,INACTIVITY_TIMEOUT,
MAX_THINK_TIME,MAX_USE_SESSION,MAX_LIFETIME_SESSION

SYS_DEFAULT_CONNECTION_POOL,ACTIVE,4,40,2,20,3600,3600,500000,86400

Thanks

[NigelW]

I just wanted to clarify what I need to achieve and whether DRCP is the correct way to do this. I have previously built a test rig using OCI.dll to create the DRCP sessions successfully, however it is unstable and cannot be used in production, which is why I want to use Devart.

I have a client/server application with multiple host applications, on different servers, communicating with one Oracle database. Multiple hosts allow for load balancing and for a host to disappear and the clients attached to that host able to switch to another host without the user of the client having to log in again. I would like to maintain session state in Oracle such that when the host disappears the client switches to another host and picks up where it left off in Oracle, rather than having to create Oracle session data again.

With DRCP I was expecting to have a connection class per client connected to our host software so when a different host reconnects on behalf of the client, the same connection class is used and therefore the same session data. We never have more than one client with the same connection class.

With my test rig I have been able to see this working, switching between different machines when closing sessions and environments cleanly, however when the host application is closed or stops the Oracle session data is lost.

I would be interested in whether this is your experience of using DRCP.

Many thanks

[Shalex]

1. We cannot reproduce the problem in our environment with dotConnect for Oracle v 6.10.121
when the connection is not listed in v$cpool_cc_stats with any of the following scenarios:
a) Oracle client v 11.2 + Oracle server v 11.2;
b) Oracle client v 11.1 + Oracle server v 11.1.

2. Our DRCP implementation is based on OCI. The behaviour of DRCP via dotConnect for Oracle
should be the same as you have got when using OCI.dll.

[NigelW]

I've built a new test rig and have DRCP working via Devart for 11.1 and 11.2 Oracle installations. It is so close to doing what I need for our application.

When the Oracle connection is closed and disposed, there is a connection shown in v$cpool_conn_info until the application is terminated (I believe this is described as a 'transparent' connection to the connection broker).

With the first application still running, but having closed the Oracle connection, a second identical application running on another server is blocked from reusing the same session data created by the first application.

Once the first application has terminated, running the second application picks up the session data created by the first.

What I need to achieve is being able to pick up the session data created in the first application by the second application, where the first application has closed/disposed the Oracle connection, but has not terminated. This is to provide load balancing, manual transfer of our clients to another host application and to provide failover, where our client swaps to a new host and continues to work with as little delay as possible.

Is there anything I can do to achieve this?

Many thanks

[Shalex]

Please try disabling pooling (the "Pooling=false;" connection string parameter).

[NigelW]

Pooling is set to false.

Connection string is:
User Id=xxx;Password=yyy;Server=ORA112X64TEST;Pooling=False;Direct=False;Oci Session Pooling=True;Oci Session Pool Min Size=1;Oci Session Pool Increment=1;Oci Session Pool Max Size=10;Oci Session Pool Connection Lifetime=1;Oci Session Pool Allow Waiting=False;Connection Class=Branston;Service Name=ORCL;Port=1521

Thanks

[Shalex]

Seems like the session data cannot be reused until OCI is unloaded from the application domain of the first application. Please contact the Oracle support for details.