cx-oracle-users

[cx-oracle-users] How to deal with lost connection to Oracle ?

[<prz...@po...>]

Hello,

(I am python and cx_oracle beginner so please excuse me ...)

I am going to write small python application which will fetch some small
data from many Oracles _once_ a minute for 24h/365.
These Oracles are backuped offline (shutdown, snapshot, startup).
Each Oracle has its own backup time. What I need is to
properly handle lost connections , e.g.:
minute 1: fetch data
minute 2: fetch data
minute 3: fetch data
minute 4: lost connection [1] (how to detect it ?) - backup started
minute 5: try to connect - failed [2] (how to detect it ?) - ongoing backup process
minute 6: try to connect - failed ...
minute 7: try to connect - failed ...
minute 8: try to connect - failed ...
minute 9: try to connect - successful ...
minute10: fetch data

How can I detect [1] and [2] ?


Regards
Przemyslaw Bak (przemol)





























----------------------------------------------------------------------
Duzi chlopcy lubia wampiry i krew.
http://linkint.pl/f2718

Re: [cx-oracle-users] How to deal with lost connection to Oracle ?

[Anthony T. <ant...@gm...>]

On Wed, Jul 21, 2010 at 7:14 AM,  <prz...@po...> wrote:
> Hello,
>
> (I am python and cx_oracle beginner so please excuse me ...)
>
> I am going to write small python application which will fetch some small
> data from many Oracles _once_ a minute for 24h/365.
> These Oracles are backuped offline (shutdown, snapshot, startup).

Hmm, any reason these aren't backed up online? That would make the
databases available 24/7. That doesn't eliminate the problem since
they could go down for other reasons but it eliminates most of the
problems.

> Each Oracle has its own backup time. What I need is to
> properly handle lost connections , e.g.:
> minute 1: fetch data
> minute 2: fetch data
> minute 3: fetch data
> minute 4: lost connection [1] (how to detect it ?) - backup started
> minute 5: try to connect - failed [2] (how to detect it ?) - ongoing backup process
> minute 6: try to connect - failed ...
> minute 7: try to connect - failed ...
> minute 8: try to connect - failed ...
> minute 9: try to connect - successful ...
> minute10: fetch data
>
> How can I detect [1] and [2] ?

It depends what version of Oracle you are using. For 10.2 and up you
can use connection.ping() which will attempt to contact the server and
raise an error if the connection has dropped.

try:
    connection.ping()
except cx_Oracle.DatabaseError:
    print "*** Connection dropped ***"

The same goes for attempting to connect

try:
    connection = cx_Oracle.Connection(connectString)
except cx_Oracle.DatabaseError:
    print "*** Connection failed to establish ***"

Note that those catch any database exception, not just "the database
is not available". If you need to be more careful about which
exceptions to catch then you need something like this:

try:
    connnection = cx_Oracle.Connection(connectString)
except cx_Oracle.DatabaseError, e:
    error, = e.args
    if error.code == 1017:
        print "*** Invalid username/password ***"
    else:
        print "*** Some other error:", error.message, "***"

Hope that helps.

Anthony

Re: [cx-oracle-users] How to deal with lost connection to Oracle ?

[<prz...@po...>]

On Wed, Jul 21, 2010 at 08:05:13AM -0600, Anthony Tuininga wrote:
> On Wed, Jul 21, 2010 at 7:14 AM,  <prz...@po...> wrote:
> > Hello,
> >
> > (I am python and cx_oracle beginner so please excuse me ...)
> >
> > I am going to write small python application which will fetch some small
> > data from many Oracles _once_ a minute for 24h/365.
> > These Oracles are backuped offline (shutdown, snapshot, startup).
> 
> Hmm, any reason these aren't backed up online? That would make the
> databases available 24/7. That doesn't eliminate the problem since
> they could go down for other reasons but it eliminates most of the
> problems.

I know. But this is beyond my ability - our DBAs didn't make decision to change it.

> > Each Oracle has its own backup time. What I need is to
> > properly handle lost connections , e.g.:
> > minute 1: fetch data
> > minute 2: fetch data
> > minute 3: fetch data
> > minute 4: lost connection [1] (how to detect it ?) - backup started
> > minute 5: try to connect - failed [2] (how to detect it ?) - ongoing backup process
> > minute 6: try to connect - failed ...
> > minute 7: try to connect - failed ...
> > minute 8: try to connect - failed ...
> > minute 9: try to connect - successful ...
> > minute10: fetch data
> >
> > How can I detect [1] and [2] ?
> 
> It depends what version of Oracle you are using. For 10.2 and up you
> can use connection.ping() which will attempt to contact the server and
> raise an error if the connection has dropped.
> 
> try:
>     connection.ping()
> except cx_Oracle.DatabaseError:
>     print "*** Connection dropped ***"
> 
> The same goes for attempting to connect
> 
> try:
>     connection = cx_Oracle.Connection(connectString)
> except cx_Oracle.DatabaseError:
>     print "*** Connection failed to establish ***"
> 
> Note that those catch any database exception, not just "the database
> is not available". If you need to be more careful about which
> exceptions to catch then you need something like this:
> 
> try:
>     connnection = cx_Oracle.Connection(connectString)
> except cx_Oracle.DatabaseError, e:
>     error, = e.args
>     if error.code == 1017:
>         print "*** Invalid username/password ***"
>     else:
>         print "*** Some other error:", error.message, "***"

I need just availability of the database. Which exception would be
the most useful ?


Regards
Przemyslaw Bak (przemol)





























----------------------------------------------------------------------
Wyprzedaz do -70%!!! Wejdz na Endo.pl
http://linkint.pl/f2791

Re: [cx-oracle-users] How to deal with lost connection to Oracle ?

[Anthony T. <ant...@gm...>]

On Wed, Jul 21, 2010 at 9:03 AM,  <prz...@po...> wrote:
> On Wed, Jul 21, 2010 at 08:05:13AM -0600, Anthony Tuininga wrote:
>> On Wed, Jul 21, 2010 at 7:14 AM,  <prz...@po...> wrote:
>> > Hello,
>> >
>> > (I am python and cx_oracle beginner so please excuse me ...)
>> >
>> > I am going to write small python application which will fetch some small
>> > data from many Oracles _once_ a minute for 24h/365.
>> > These Oracles are backuped offline (shutdown, snapshot, startup).
>>
>> Hmm, any reason these aren't backed up online? That would make the
>> databases available 24/7. That doesn't eliminate the problem since
>> they could go down for other reasons but it eliminates most of the
>> problems.
>
> I know. But this is beyond my ability - our DBAs didn't make decision to change it.

Ok. Such is life. :-)

I have a set of tools called cx_OracleDBATools that make performing
things like backups, restores whether online or offline very simple
and the same whether on Linux or Windows. I am hoping to release a new
version of those tools tomorrow.

>> > Each Oracle has its own backup time. What I need is to
>> > properly handle lost connections , e.g.:
>> > minute 1: fetch data
>> > minute 2: fetch data
>> > minute 3: fetch data
>> > minute 4: lost connection [1] (how to detect it ?) - backup started
>> > minute 5: try to connect - failed [2] (how to detect it ?) - ongoing backup process
>> > minute 6: try to connect - failed ...
>> > minute 7: try to connect - failed ...
>> > minute 8: try to connect - failed ...
>> > minute 9: try to connect - successful ...
>> > minute10: fetch data
>> >
>> > How can I detect [1] and [2] ?
>>
>> It depends what version of Oracle you are using. For 10.2 and up you
>> can use connection.ping() which will attempt to contact the server and
>> raise an error if the connection has dropped.
>>
>> try:
>>     connection.ping()
>> except cx_Oracle.DatabaseError:
>>     print "*** Connection dropped ***"
>>
>> The same goes for attempting to connect
>>
>> try:
>>     connection = cx_Oracle.Connection(connectString)
>> except cx_Oracle.DatabaseError:
>>     print "*** Connection failed to establish ***"
>>
>> Note that those catch any database exception, not just "the database
>> is not available". If you need to be more careful about which
>> exceptions to catch then you need something like this:
>>
>> try:
>>     connnection = cx_Oracle.Connection(connectString)
>> except cx_Oracle.DatabaseError, e:
>>     error, = e.args
>>     if error.code == 1017:
>>         print "*** Invalid username/password ***"
>>     else:
>>         print "*** Some other error:", error.message, "***"
>
> I need just availability of the database. Which exception would be
> the most useful ?

That's a good question. This is what I use in cx_OracleDBATools to
determine if a database is up.

def IsAvailable(self):
    try:
        cursor = self.connection.cursor()
        return True
    except self.driver.DatabaseError as errorInfo:
        errorInfo, = errorInfo.args
        if errorInfo.code in (1034, 12500):    # Oracle not started up
            return False
        elif errorInfo.code == 1031: # insufficient privileges
            return True
        elif errorInfo.code in (1089, 1090):  # shutdown in progress
            return True
        raise

self.driver is a particular flavor of cx_Oracle imported dynamically
in order to support different versions of Oracle at the same time.

Hope that helps.

Anthony

Re: [cx-oracle-users] How to deal with lost connection to Oracle ?

[Christopher J. <chr...@or...>]

On 07/21/2010 10:50 AM, Anthony Tuininga wrote:

>>> On Wed, Jul 21, 2010 at 7:14 AM,<prz...@po...>  wrote:

>>>> Each Oracle has its own backup time. What I need is to
>>>> properly handle lost connections , e.g.:
>>>> minute 1: fetch data
>>>> minute 2: fetch data
>>>> minute 3: fetch data
>>>> minute 4: lost connection [1] (how to detect it ?) - backup started
>>>> minute 5: try to connect - failed [2] (how to detect it ?) - ongoing backup process
>>>> minute 6: try to connect - failed ...
>>>> minute 7: try to connect - failed ...
>>>> minute 8: try to connect - failed ...
>>>> minute 9: try to connect - successful ...
>>>> minute10: fetch data
>>>>
>>>> How can I detect [1] and [2] ?
>>>
>>> It depends what version of Oracle you are using. For 10.2 and up you
>>> can use connection.ping() which will attempt to contact the server and
>>> raise an error if the connection has dropped.
>>>
>>> try:
>>>      connection.ping()

If you (i) can't use ping (ii) find its internal implementation
doesn't catch everything you classify as a connection error (iii) or
don't want to use it because it causes a roundtrip to the DB and hence
reduces scalability, then you could try explicitly testing error
codes.  A fairly good set of codes to identify if an error is a
connection error is given in the PHP_OCI_HANDLE_ERROR macro used by
PHP error checking.  See
http://svn.php.net/viewvc/php/php-src/branches/PHP_5_3/ext/oci8/php_oci8_int.h?view=markup
There's an OCI attribute check (also used in PHP_OCI_HANDLE_ERROR)
that does this test but cx_Oracle doesn't expose it.

Chris

PS.  Off topic (but of interest to Python & Oracle users) is a free,
online Oracle Tuxedo training event includes a hands-on Python lab.
Learn more at http://www.oracle.com/goto/otnvdd
The American-time zone lab is tomorrow.


-- 
Email: chr...@or...
Tel:  +1 650 506 8630
Blog:  http://blogs.oracle.com/opal/
Free PHP Book: http://tinyurl.com/ugpomhome

Re: [cx-oracle-users] How to deal with lost connection to Oracle ?

[Anthony T. <ant...@gm...>]

> If you (i) can't use ping (ii) find its internal implementation
> doesn't catch everything you classify as a connection error (iii) or
> don't want to use it because it causes a roundtrip to the DB and hence
> reduces scalability, then you could try explicitly testing error
> codes.  A fairly good set of codes to identify if an error is a
> connection error is given in the PHP_OCI_HANDLE_ERROR macro used by
> PHP error checking.  See
> http://svn.php.net/viewvc/php/php-src/branches/PHP_5_3/ext/oci8/php_oci8_int.h?view=markup
> There's an OCI attribute check (also used in PHP_OCI_HANDLE_ERROR)
> that does this test but cx_Oracle doesn't expose it.

That can be fixed. :-)

Just to be clear: this attribute is only set __after__ an attempt is
made to access the server. So at that point the typical solution would
be:

try:
    cursor.execute(some_sql)
except cx_Oracle.DatabaseError:
    if cursor.connection.connected:
        raise
    print "*** Server not connected: do something about it!"

The other option is to check this attribute when the error is being
created and store it on the error object instead -- but I suspect
exposing the object on the connection will be more flexible. Comments
anyone?

Anthony

Re: [cx-oracle-users] How to deal with lost connection to Oracle ?

[Weber, G. <Geo...@PA...>]

Sounds great to me!  I'd be in favor of exposing this as it's caused me headaches in the past, especially in Perl with DBD::Oracle.


>> If you (i) can't use ping (ii) find its internal implementation
>> doesn't catch everything you classify as a connection error (iii) or
>> don't want to use it because it causes a roundtrip to the DB and hence
>> reduces scalability, then you could try explicitly testing error
>> codes.  A fairly good set of codes to identify if an error is a
>> connection error is given in the PHP_OCI_HANDLE_ERROR macro used by
>> PHP error checking.  See
>> http://svn.php.net/viewvc/php/php-src/branches/PHP_5_3/ext/oci8/php_oci8_int.h?view=markup
>> There's an OCI attribute check (also used in PHP_OCI_HANDLE_ERROR)
>> that does this test but cx_Oracle doesn't expose it.

>That can be fixed. :-)

>Just to be clear: this attribute is only set __after__ an attempt is
>made to access the server. So at that point the typical solution would
>be:

>try:
>    cursor.execute(some_sql)
>except cx_Oracle.DatabaseError:
>    if cursor.connection.connected:
>        raise
>    print "*** Server not connected: do something about it!"

>The other option is to check this attribute when the error is being
>created and store it on the error object instead -- but I suspect
>exposing the object on the connection will be more flexible. Comments
>anyone?

I think you're right here, Anthony - having it be part of the connection object would probably make it more flexible, but since it's only set after the attempt to access the server, maybe using the error object still makes more sense?  I guess I can see it set in either location, but it might make more sense to understand the actual functionality better if it was part of the error object.  Or maybe you can expose it in both places?  I guess I'm not helping much, but those were my quick thoughts on the matter.  In the case of using the error object though, then would something like the following work?  Maybe it's too cumbersome...

try:
    cursor.execute(some_sql)
except (cx_Oracle.ConnectionError, cx_Oracle.DatabaseError) as e:
    if ( isinstance(e, cx_Oracle.ConnectionError) ):
        print '*** Server not connected: Do something about it! ***'
    else:
        # other error handling


Thanks!
   Geoff