Oracle reboot gives error 03114

[gtswim]

We noticed that some applications would get Oracle error 03114 "Can not connect to Oracle" after a database server reboot.

Here's what I found - let me know if any of these findings are off base.

* DOA Session objects that do NOT use connection pooling are OK after a reboot.

* If the DOA session objects use the "spInternal" pooling, they will get the 03114 error after the reboot.

Someone had mentioned setting ThreadSafe property to True might help, I tried that but it had no affect.

I also tried using the restore connection method "CheckConnection" which did return a value of "ccReconnect" but then the ensuing query would still receive the 03114 error.

Once I shut down our COM+ component, the next time it was used everything would work fine. This makes sense since the connection pooling is reset after the COM+ component is shut down

 

[Marco Kalter]

In version 4.0 you will have much more control over the pool. We'll add some functionality to remove killed sessions from the pool.

------------------
Marco Kalter
Allround Automations

 

[krome]

Marco,

This thread was originally posted by a co-worker of mine, and the problem was subsequently handed off to me to correct.

I am not sure how you have taken care of this in version 4, but I thought I should share the patch we came up with for 3.4.6. (this issue was too important to us to warrant waiting for version 4)

As you probably know already, the problem is that when using internal pooling, once a session is added to the pool and connected to the server, it is never again verified. If a pooled connection fails at any point (due to db server reboot or other network anomaly), it will still issue the invalid pooled session object to future session requests. We fixed it simply by adding a small bit of verification code to the pool reservation code:

in TOracleSessionPoolEntry.Reserve, just before the line that reads:

  if PooledSession = nil then

add the following code:

  if PooledSession <> nil then
  begin
    // Verify the pooled connection hasnt been nuked!
    ccResult := PooledSession.CheckConnection(True);

    if ccResult = ccError then
    begin
      // Wasnt able to reconnect, so recreate the session from scratch
      try
        PooledSession.Free;
      except
      end;
      PooledSession := nil;
    end;
  end;

also, you need to declare the new variable used in this procedure:

  var
    ccResult: TCheckConnectionResult;

While perhaps not the best solution, it ensures a solid connection is being returned to requests for pooled connections by attempting to first reconnect, and then secondly it forces the pooled session to be recreated if it could not be reconnected.

Keith

 

[Marco Kalter]

Thanks

------------------
Marco Kalter
Allround Automations