Oracle Flashback without Connection Pooling Issues

Oracle Flashback without Connection Pooling Issues

Postby Oktane » Wed 01 Jun 2011 09:56

We have been trying to use the Oracle flashback technology but are running into a few issues. Essentially we want to use flashback to roll our DB back to a previous state, retrieve data and then disable flashback. However as we have a multi user environment, when we use Connection pooling, and 2 users try enable flashback at the same time on the same connection we get an error. Our solution was to prevent connection pooling on the flashback queries, however this is when we run into problems.

Our steps are quite simple
1.) Create a new Context
2.) Execute manually on the context an ExecuteCommand
"begin dbms_flashback.enable_at_time(
to_timestamp(
'31-5-2011 23:1:00',
'DD-MM-YYYY HH24:MI:SS')
)
; end;"

This enables the flashback feature
3.) On the context perform our DB operations (ie retrieve data)
4.) Finally disable Flashback using
"begin dbms_flashback.disable;end;"

We are using a Direct Connection to an Oracle 11g DB. Our Flashback queries work if we use Pooled connections (ie the connection starts, Flashback rolls the db back to a previous state, we call our EF Queries, which retrieves the historic data, we disable flashback). If we disable pooling, even though I can see (using EF Profiler) all the queries running correctly (in the correct order and on the same Context), it seems that the queries are being run on different DB Connections. As a result my EF Queries execute between the start/end of the flashback on the current Context, do not seem to share the same DB scope (ie a new connection/query) and do not run within the Flashback enabled scope.

Anybody got any ideas?
Oktane
 
Posts: 8
Joined: Wed 22 Sep 2010 14:16

Postby Oktane » Wed 01 Jun 2011 10:46

Actually, Have just run DBMonitor and confirmed that if you are using Non Pooled connections, each call has an open connection / close connection, even though the Context has not been recreated.

Anyone know if this behaviour is normal? Surely it should hold the connection till it goes out of scope?[/img]
Oktane
 
Posts: 8
Joined: Wed 22 Sep 2010 14:16

Postby AndreyR » Wed 01 Jun 2011 11:18

This behaviour is designed. See the Managing Connections and Transactions article for details.
I recommend that you execute the following code in the OnContextCreated method:
Code: Select all
OracleConnection conn = (Connection as EntityConnection).StoreConnection as OracleConnection;
conn.Open();

This code guarantees that the same StoreConnection will be used during context lifetime.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby Oktane » Wed 01 Jun 2011 11:38

AndreyR wrote:This behaviour is designed. See the Managing Connections and Transactions article for details.
I recommend that you execute the following code in the OnContextCreated method:
Code: Select all
OracleConnection conn = (Connection as EntityConnection).StoreConnection as OracleConnection;
conn.Open();

This code guarantees that the same StoreConnection will be used during context lifetime.


Hi thanks so much, I had just found the article and was giving it a go. Seems to have resolved my problem.
Oktane
 
Posts: 8
Joined: Wed 22 Sep 2010 14:16


Return to dotConnect for Oracle