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