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