http://qs321.pair.com?node_id=11112321

perldigious has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks,

A problem I'm running in to as I try to write scripts to connect to my employer's Oracle database via DBD::Oracle is that it intermittently fails to connect...

DBI connect(XXX,XXX,XXX) failed: ORA-12170: TNS:Connect timeout occurred (DBD ERROR: OCIServerAttach) at lov_lookup.pl line 31.

Or it fails to disconnect...

DBD::Oracle::db disconnect failed: ORA-25408: can not safely replay call (DBD ERROR: OCISessionEnd) at lov_lookup.pl line 58.

After talking to some coworkers who have more database experience than I do, they thought the issue might be that I'm connecting/disconnecting to the DB too often (I'm attempting to get in touch with our Oracle DBA team to ask them as well). My coworkers and I have several different tools they use for DB access, and in all cases those tools will establish a connection and then keep that connection open through however many successive queries are done, only disconnecting after the tool is closed or after a very long timeout period (not sure on exact duration, but from my own experience at least an hour).

With this in mind, I've gone down the path of trying to figure out how I can keep the DB connection alive and reusable between runs of any script I write. The scripts will be run from a local machine Windows environment as either a ".pl" or possibly in the future packaged in to an ".exe". I modified my code to remove the explicit disconnect command, but as I understand it auto calls this when the script finishes anyway (which I don't want). I've also changed the code that establishes the connection to the following based on a similar suggestion in another thread.

my $dsn = "dbi:Oracle:DWAPRD";
my @connection = ($dsn, $user, $pass);
my $dbh = DBI->connect_cached(@connection) or die "Couldn't connect to
+ database: " . DBI->errstr;

[download]

These changes resulted in the previous disconnect error changing to...

DBD::Oracle::db DESTROY failed: ORA-25408: can not safely replay call (DBD ERROR: OCISessionEnd) during global destruction.

It would be convenient if any of the scripts I write could establish the necessary DB connection, but only if necessary, and that all of them could use the existing connection that was left open if still available. I don't know if that's possible... perhaps there is a way to have each script check a file that points to the connection, and if the script has to establish the connection have it update that file? Again, I'm not sure what's possible or not.

I'm hoping that explains where I'm at well enough and the Monks can either give me suggestions on how to accomplish what I'm after, or otherwise explain to me that I'm trying to solve the problem the wrong way and make another suggestion. Thank you.

-------------------------------------------------

UPDATE - Resolved: See update at bottom of post here.

Just another Perl hooker - My clients appreciate that I keep my code clean but my comments dirty.

Replies are listed 'Best First'.
Re: Keep a Database Connection Active and Reuse it Between Script Executions?
by davido (Cardinal) on Feb 03, 2020 at 19:07 UTC

    The failures are a greater concern aren't they? If the database fails to accept a connection, that's a visible failure. Is it also failing to take input/output on an existing connection or dropping connections in a less visible manner? What could the underlying issue be; network, server load, ...there has to be something.

    For keeping a connection opened, I think your scripts would need to be running within a persistent process. That may require a complete change of how the scripts are written. You wouldn't even want them to be forked subprocesses (whatever Windows actually does for forks) because then children are sharing a connection, and that's "bad".

    I'd probably try to use a resilient connector such as DBIx::Connector to manage the connection. But at least, could you retry on connection failure? I like DBIx::Connector because it handles reconnecting when a connection falls over. It should be able to handle a failure to connect in the first place too, but if not you could retry on failure after a very brief delay.


    Dave

[reply]

      Thanks, davido. This sent me down what seems to be a working path. I didn't go so far as to try DBIx::Connector yet, but you got me poking around and it seems just adding the InactiveDestroy => 1 attribute to my connection string along with my other changes has remedied the immediate issue.

      This is something I would expect to be an issue for me during code development work, where I may be running a script repeatedly in a very short order of time for debugging purposes and testing different things, which is what I was doing and the repeated connection issues were slowly driving me insane as they slowed me down.

      I do tend to agree that the failures are possibly a greater concern, but I know very little about databases; I do this stuff on the side to make my regular job more efficient. I have put in an issue ticket with my Oracle DBA team to contact me and look at the issue with me, but they can be a bit slow to get around to minor issue tickets that only impact 1 person... also, sometimes they can end up hurting more than helping if they decide they don't like what someone is doing to their database. I try to sympathize, and I'm happy to play by their rules as long as they help me... I just want easy access to the data I need to do certain job tasks of mine more efficiently.

      Just another Perl hooker - My clients appreciate that I keep my code clean but my comments dirty.
[reply]
[d/l]

        An advantage of DBIx::Connector is that if a handle falls over (the connection drops) the connector will re-establish a connection automatically, so you don't have to solve that problem yourself. Eventually you're going to solve this problem one way or the other. DBIx::Connector allows you to not have to solve it with code that you will then have to maintain yourself.

        But it's not right for every use case. Sometimes you really may have a good reason for solving it with your own code.


        Dave

[reply]
Re: Keep a Database Connection Active and Reuse it Between Script Executions?
by talexb (Chancellor) on Feb 04, 2020 at 19:38 UTC
      After talking to some coworkers who have more database experience than I do, they thought the issue might be that I'm connecting/disconnecting to the DB too often (I'm attempting to get in touch with our Oracle DBA team to ask them as well).

    This is hilarious -- it anthropomorphizes the database into a being that will get frustrated with your rapid connection/disconnection behaviour, and stop answering. I'm pretty sure there's no database setting that limits the number of connections made in a time period. :)

    Alex / talexb / Toronto

    Thanks PJ. We owe you so much. Groklaw -- RIP -- 2003 to 2013.

[reply]
A reply falls below the community's threshold of quality. You may see it by logging in.

Back to Seekers of Perl Wisdom