oracle rdbms connector

[Roderick Grau]

Scott,

Thank you for the answer.  I added a validationQuery and am seeing the same behavior.

Here is the errors in idp-process.log.  The IDP was working at the start of the log.  I removed several lines of the same error message.  The IDP appears to go offline while it is trying to reconnect to the DB.
The IDP starts working once the DB connect comes back.  I did not have to do anything to restart services.

14:05:21.049 - ERROR [edu.internet2.middleware.shibboleth.common.attribute.resolver.provider.dataConnector.RDBMSDataConnector:240] - RDBMS data connector [SUNYCARDDB, 66000, 12518] - Invalid connector configuration; SQL state: java.sql.SQLException: Listener refused the connection with the following error:
ORA-12518, TNS:listener could not hand off client connection
, SQL Code: {}
14:05:21.105 - ERROR [edu.internet2.middleware.shibboleth.common.attribute.resolver.provider.dataConnector.RDBMSDataConnector:240] - RDBMS data connector [SUNYCARDDB, 66000, 12518] - Invalid connector configuration; SQL state: java.sql.SQLException: Listener refused the connection with the following error:
ORA-12518, TNS:listener could not hand off client connection
, SQL Code: {}
14:05:36.022 - ERROR [edu.internet2.middleware.shibboleth.common.attribute.resolver.provider.dataConnector.RDBMSDataConnector:240] - RDBMS data connector [SUNYCARDDB, 66000, 12518] - Invalid connector configuration; SQL state: java.sql.SQLException: Listener refused the connection with the following error:
ORA-12518, TNS:listener could not hand off client connection
, SQL Code: {}
14:08:40.116 - ERROR [edu.internet2.middleware.shibboleth.common.attribute.resolver.provider.dataConnector.RDBMSDataConnector:240] - RDBMS data connector [SUNYCARDDB, 08006, 1033] - Invalid connector configuration; SQL state: java.sql.SQLRecoverableException: ORA-01033: ORACLE initialization or shutdown in progress
, SQL Code: {}
14:08:54.982 - INFO [Shibboleth-Access:74] - 20130521T180854Z|169.226.XX.XX|weblogindevel.itsli.albany.edu:443|/profile/SAML2/Redirect/SSO|
14:08:55.006 - INFO [Shibboleth-Access:74] - 20130521T180855Z|169.226.XX.XX|weblogindevel.itsli.albany.edu:443|/profile/SAML2/Redirect/SSO|

Here is my idp2.xml from tomcat.

<Context path="/idp2" docBase="/usr/local/shibboleth-idp/war/idp.war"
        privileged="true"
        antiResourceLocking="false"
        antiJARLocking="false"
        unpackWAR="false"
        swallowOutput="true" >

<!-- Dev connection -->
 <Resource name="jdbc/SUNYCARD"
    type="javax.sql.DataSource"
    driverClassName="oracle.jdbc.OracleDriver"
    url="jdbc:oracle:thin:@servername.albany.edu:1521:GOLDDEVL"
    username="SHIB_INTERFACE"
    password="**********"
    initialSize="1"
    maxActive="1"
    maxIdle="1"
    minIdle="1"
    validationQuery="select 1 from dual" />

</Context>

To summarize in case I did not explain it well enough.  The IDP is working and answering requests.  A DB connector goes offline and the IDP stops answering requests while it waits for the DB connector to come back.  Once the DB connector comes back the IDP again starts answering requests.

My comment about maxActive="0" was in reference to setting the pool to 0 and only initiating a DB connect when one was needed.  The tomcat/dbcp documentation say that "0" means no limit and not none.  The idea of setting a connection pool to 0 is backwards thinking and not what a connection pool was meant to be.

Any ideas?

Thanks,
-Rod

On May 20, 2013, at 4:36 PM, "Cantor, Scott" <cantor.2 at osu.edu> wrote:

> On 5/20/13 4:06 PM, "Roderick Grau" <rgrau at albany.edu> wrote:
> 
>> Our DBAs decided to do some patching this morning and brought our entire
>> Shib infrastructure down.  It looks like once the connection pool timed
>> out and could not be recreated, Tomcat "paused" the Shib web app until a
>> DB connection could be made.
> 
> Assuming your database was actually reachable at all, that would usually
> mean it thought one or more of the connections was still valid, but was
> hung because they weren't. Usually fixing that requires setting options to
> do validation queries in various ways.
> 
>> Other than the obvious, I need to tell this application owner that they
>> need another DB or patch at and outage, how do I fix this?  Is there some
>> connection pool options?
> 
> A container managed connection means it has nothing to do with Shibboleth
> and the options would depend on what you chose to use. I use dbcp, for
> example, but I haven't had remote DB connections for years now. At the
> time, I had pretty good luck with dbcp and Sybase.
> 
>> maxActive="0" and minIdle="0" does not do what you think it would.
> 
> That I guess depends what you think they mean, or what the documentation
> of whatever software you're using (which isn't Shibboleth) says they mean.
> 
> But a hang is rarely a pool size issue unless you have a hard cap on the
> pool size. It's a connection management problem. Usually a validation
> query option that will timeout quickly is a way to prevent a pool from
> reusing hung connections.
> 
> -- Scott
> 
> 
> --
> To unsubscribe from this list send an email to users-unsubscribe at shibboleth.net
> 

--
Roderick Grau
IT Systems Management and Operations
University at Albany
1400 Washington Avenue Ext.
Albany, New York 12222