RE: Checking if remote database is up

From: Mercadante, Thomas F (LABOR) <thomas.mercadante_at_labor.state.ny.us>
Date: Tue, 26 Apr 2005 09:55:43 -0400
Message-ID: <C9995D8C5E0DDA4A8FF9D68EE666CE0704AC04BB@exchsen0a1ma>

I do it from both.

I first do a tnsping.
If that is ok, I run a sqlplus script attempting to make a connection.

If either one fails, I mark a record in the local database indicating that the remote database is unavailable.

-----Original Message-----
From: Gene Sais [mailto:Gsais_at_co.palm-beach.fl.us] Sent: Tuesday, April 26, 2005 9:33 AM
To: niall.litchfield_at_gmail.com; stephenbooth.uk_at_gmail.com; thomas.mercadante_at_labor.state.ny.us
Cc: oracle-l_at_freelists.org
Subject: RE: Checking if remote database is up

I prefer to do the check outside the db from a ksh script. It's not going to help when the db doing the check is down :). >>> "Mercadante, Thomas F (LABOR)" <thomas.mercadante_at_labor.state.ny.us> 04/26/05 9:24 AM >>>
This is an even easier approach if all his queries go through stored procs as you mentioned.

-----Original Message-----
From: Niall Litchfield [mailto:niall.litchfield_at_gmail.com] Sent: Tuesday, April 26, 2005 9:18 AM
To: stephenbooth.uk_at_gmail.com
Cc: Oracle-L (E-mail)
Subject: Re: Checking if remote database is up

Would a quick hack to code the procedure as a version of=20

declare

    l_alive number;
begin

     select 1 into l_alive from dual_at_remotedb;
     if l_alive = 1 then
        proc_name;
     end if

      exception
          when others then
                null;

end;
/

make sense?

This might avoid ocella upgrades breaking your check, or dealing with the politics :).

Alternatively you might wish to add error handling for what ever errors you get when the ocella db is down and add that to your functions and procedures...

On 4/26/05, stephen booth <stephenbooth.uk_at_gmail.com> wrote:
> One of our new systems (Documentum from EMC) uses a database link to a
> remote database (Ocella from Ocella) for some processes. Due to
> organisation politics the people managing the Ocella database don't
> tell the people managing Documentum when they're taking their system
> down (the joys of working in the public sector). Documentum can do
> most of it's functions when Ocella is down, it just can't do certain
> transactions, unfortunately it's currently not very good at dealing
> with situations where the Ocella database is down.

>=20

> We're looking at some way of checking if the Ocella database is up
> before trying a transaction that needs it then reporting back to the
> user if it's down. What we're currently thinking of is putting an
> empty table in the Ocella database then querying that from a PL/SQL
> function over the link and trapping the error. If we get data or 'No
> Rows Returned' then we know that the database is up and the link is
> working. If we get an ORA-03113 then we know that the database is
> down or the link isn't working for some other reason (e.g. Network
> broken again). the function returns either TRUE or FALSE depending on
> whether the remote dtabase is up or not.

>=20

> Does anyone have experience of a similar situation? Is there a more
> elegant/reliable method? Anything I've failed to consider that will
> make this all blow up?

>=20

> Thanks

>=20

> Stephen

>=20

> --=3D20
> It's better to ask a silly question than to make a silly assumption.
> --
> http://www.freelists.org/webpage/oracle-l
>=20

--=20
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com

--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l



--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l

Received on Tue Apr 26 2005 - 10:00:05 CDT