Verify remote database is up...

[THoey]

I was trying to think of a way to verify if a remote database (connected using database link)was up.

I thought about just trying to query from it and then handling any exceptions, but this didn't work:

CREATE OR REPLACE  PROCEDURE "EHGRIFF"."TEST_EXCEPTION"     IS 
   sql_msg VARCHAR2(1000) :=NULL;
BEGIN
   SELECT SYSDATE FROM DUAL@CFDEV.SBC.COM;
   -- If this works, perform process
EXCEPTION
   WHEN OTHERS THEN
   -- Do not perform process
END;

I ran this and got the following:

SQLWKS> execute test_exception;
ORA-02068: following severe error from CFDEV
ORA-01034: ORACLE not available
ORA-07429: smsgsg: shmget() failed to get segment.
IBM AIX RISC System/6000 Error: 2: No such file or directory

Is there a better way to test if an instance is up? Or is there another exception to handle that will catch this? Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[seagull69]

Hello,

I suggest you monitor the database from outside Oracle... With a perl script for example, or an open tool system like Karma.

HTH

Seagull69

 

[THoey]

Thanks for the suggestion, but I need to do this in a stored procedure, so that it knows whether or not to perform some DB maintenance. Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[seagull69]

Hi!
I guess you should first verify that the db link works and that you can successfully connect to the remote db.
Then grant select rights on the v$instance or v$database views to the user the db link connection is made for, and select these views.

HTH

Seagull69