Connection Pooling Questions

2845609

Hi there,

I was previously using the jferner/node-oracle module with the "generic-pool" (https://github.com/coopernurse/node-pool) module for connection pooling. 

I'm trying out a setup with connection pooling with node-oracledb and have a few questions:

* If an execute call fails with a connection i've retrieved from the pool, and I want to destroy that connection and remove it from the pool, how do I do that?  Is it done implicitly for me? 

* Is there any way to validate a connection before it's used?  Or again, is this done implicitly?  Is there a way to toggle it on and off for perf tuning?

* Is there any way to tune how frequently Oracle checks for idle connections?  (In generic-pool this was called reapIntervalMillis)

* Is there any way to turn on any logging of how the connection pool behaves for development debugging?  I just want to make sure my setup is behaving as I think it should be.

I'm making some good headway on getting the module working and it wasn't too difficult a conversion from node-oracle, either, that's good!

-Matt

Christopher Jones-Oracle

You should release() bad connections to the pool so the pool can replace them.

Validating connections is generally not worth it: between validation & use there could be a failure, so your executions need to handle errors anyway.  Why reduce scalability and performance by doing an extra "round trip" for validation? Also you can use FAN which can proactively clean up idle sessions in the session pool that are affected by the DB instance disappearing (due to network glitches etc).

The client-side pool is handled by Oracle session pooling, so the algorithms are opaque.

2845609

Thank you, I missed that the release method applies to connection pools as well.

Point taken about the validation, thanks!

Matt M.

Hi there, I'm resurrecting this thread because we're seeing some inconsistent behavior.  From the previous responses, it sounds as though as long as we're calling release() after we execute a statement, the OCI sesion pooling should identify a bad connection and clean it up.

We're seeing that our oracle db sessions are being killed (which is another story), and after the session is killed we're seeing lots of this:

ORA-01012: not logged on\nProcess ID: 25345\nSession ID: 1297 Serial number: 31263

We've done quite a bit of work to ensure that release() is being called, so is there something else we need to be doing to get this bad connection released?  Do we need to terminate the pool?

Christopher Jones-Oracle

Can you post more details about how the sessions are being killed and what the actual behaviour is - do the errors go away after you release the connection and get a new one?  Session pool cleanup will handle fan events. And do you even have FAN enabled - you would need to do this in and oraaccess.xml file.

Matt M.

Sure, and thank you for responding!

Every time we execute a query we're acquiring a session from our pool using getConnection().  We execute the query, then we're using some functionality from https://github.com/petkaantonov/bluebird to release the connection using release().  We're using promises so this works nicely.  The release() will happen regardless of success or failure of the query.

When the connection throws the errors about due to ORA-01012, the next query run will have the same error.  I would have expected that the connection would get released automatically.

What we did find is that we had our poolMin option set to 2, if we set it to 0, then the bad connection would eventually get evicted assuming that the use of the connection was low.  This is our (very bad) workaround for now.

As for FAN: I'm was not familiar with that feature so you'll have to pardon my ignorance.  I doubt we have it enabled unless it gets defaulted somewhere, which I'm guessing from the response that it's not.

So, in order for the OCI connection pools to handle an event like the ORA-01012 killed session event we need to explicitly turn FAN on then, is that right?

Would this be a sufficient enough oraaccess.xml file to turn FAN on and keep other defaults? (Updated 8/14 11:27am ET):

<?xml version="1.0" encoding="ASCII" ?>

<oraaccess xmlns="http://xmlns.oracle.com/oci/oraaccess"

    xmlns:oci="http://xmlns.oracle.com/oci/oraaccess"

    schemaLocation="http://xmlns.oracle.com/oci/oraaccess

    http://xmlns.oracle.com/oci/oraaccess.xsd">

    <default_parameters>

          <fan>

              <!--only possible values are "trace" or "error" -->

                <subscription_failure_action>errorssss</subscription_failure_action>

          </fan>

          <ons>

                <subscription_wait_timeout>5</subscription_wait_timeout>

                <auto_config>true</auto_config>

          </ons>

        <events>true</events>

    </default_parameters>

</oraaccess>

What else might I need?

Finally, to get this file working with node-oracle, I want to confirm I'm understanding I have this right from reading various docs:

I would place the xml file in ${ORACLE_HOME}/network/admin, then set ${TNS_ADMIN}=${ORACLE_HOME}/network/admin.  From there, the instantclient should pick it up?

I'm getting errors when I try to use the file, really ANY file (i've tried with numerous different example files):

terminate called after throwing an instance of 'ExceptionImpl'

  what():  ORA-24296: error in processing the XML configuration file %s

Aborted (core dumped)

I looked around for other documentation but couldn't find anything at a level high enough that I could make sense of.  Also, if you'd be amenable to it, I could submit a Pull Request for the INSTALL.md   to highlight how to use the oraaccess.xml stuff a little more once I get it all working.

Christopher Jones-Oracle

The key piece of info we need is how the session are being killed.  You may need TAF not FAN.  What command(s) are being issued that kill sessions or severs?

To use the oraaccess.xml file you would create the file in any directory and then set TNS_ADMIN to point to that directory (not to the file).

What version of node-oracledb and platform are you using?  I am seeing errors about the misspelling of 'errorsss' :

terminate called after throwing an instance of 'ExceptionImpl'
  what():  ORA-24296: error in processing the XML configuration file /home/cjones/n/oraaccess.xml
Line No: 9
Error No: 290
LSX-00290: invalid enumeration choice "errorssss"

I'd be more than happy to have some contributions for the doc!  You would need to follow https://github.com/oracle/node-oracledb/blob/master/CONTRIBUTING.md

Matt M.

This is node-oracle 0.7.0 running on Ubuntu with the 12.1.0.2.0-1 instant client.

According to the DBA's who support this particular database, it's a stored procedure simply running 'ALTER SYSTEM KILL SESSION 'SID,SER#'' commands.  Would FAN allow the connections to be cleaned up?

I have TNS_ADMIN set to the directory (not the file) and the oraaccess.xml file is in the directory, but what's odd is that I'm not seeing the file location and name output in the error, it's just the '%s'.  I'll do a little more experimenting, but if you have any other suggestions that would be great.

Matt M.

I figured out the issue... Apparently the parsing of oraaccess.xml requires access to the xsd file through the internet to validate.  Once I set my corporate proxy through the HTTP_PROXY environment variable, things started working fine.

This is an internal app and we can't have our proxy set on these servers.  Is there any way to turn off this validation?  Or, at the very least, is there a way to set the proxy so that only OCI has access to it?  I don't want to set the HTTP_PROXY variable for any process to have access to.

As for FAN vs TAF, let me know which one applies to the KILL SESSION command.

Thanks so, so much for the help!

Matt M.

Unfortunately, I still haven't found a way around the schema validation problem

Is there really no way to turn off the internet-needed schema validation in OCI?  I can't find a thing on Google.

Lacking that:  Will the Node.js driver pick up settings from a sqlnet.ora file?  We could use that too to turn on FAN or TAF, correct?

Finally, I still need to know if FAN or TAF is needed for the KILL SESSION command.

Many thanks, again!

Christopher Jones-Oracle

The namespace URIs are just tokens which need to match those the Oracle client code expects.

Those URIs don't even exist as files and I don't see any network calls for them when I use an oraaccess.xml file.

To recover a transaction after an ORA-1012, you would need to use TAF, and to change the KILL command to:

ALTER SYSTEM DISCONNECT SESSION ‘sid, serial#’ POST_TRANSACTION

However I suspect you just want a usable connection? Either way we need to improve node-oracledb to handle ORA-1012 better.

We will probably enhance it to drop (instead of putting back in the pool) a session when connection.release() is called on a session that is giving ORA-1012.