Home » RDBMS Server » Server Administration » Checking Remote database is up or not
Checking Remote database is up or not [message #276512] Thu, 25 October 2007 06:53 Go to next message
itramkumar
Messages: 1
Registered: October 2007
Junior Member
Hi,

I am using dblink to connect Biz database from Dev database.(Biz, Dev - database names)

Running dbms job using the dblink from the Dev database to update data on Biz database...

Problem is dbms job should run if and only if the Biz database is up for which i created the dblink...


How to make sure or implement the check to find whether the Biz is database is up or not in Pl/sql or sql...


pls suggest me how to find whether the database is up or not b4 running the dbms job?

Using- Oracle 10g

Thanks,
Ram
Re: Checking Remote database is up or not [message #276515 is a reply to message #276512] Thu, 25 October 2007 06:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

am using dblink to connect Biz database from Dev database

Argh!

Quote:

using the dblink from the Dev database to update data on Biz database...

Aaaaargh!

In short: you can't.

Regards
Michel
Re: Checking Remote database is up or not [message #276541 is a reply to message #276512] Thu, 25 October 2007 08:07 Go to previous messageGo to next message
girish.rohini
Messages: 744
Registered: April 2005
Location: Delhi (India)
Senior Member
HI

You can check the availability of remote database using the following code:

Step 1:

Create DB link from dev (Monitoring database) to biz (monitored database):
create database link Test_remote connect to <monitoring_user> identified by <pwd> using 'tns_string';



Step 2:

Check the remote database (biz) availability using the following code:

set serveroutput on
declare
  err_msg varchar2(100);
  begin
   execute immediate 'select sysdate from dual@test_remote';
   exception
    when others then
         err_msg := SUBSTR(SQLERRM, 1, 100);
         begin
		dbms_output.put_line('Error encountered: ' || err_msg);
	 end;
  end;


I hope this does resolve your query.

--Girish

[Updated on: Thu, 25 October 2007 08:08]

Report message to a moderator

Re: Checking Remote database is up or not [message #276542 is a reply to message #276541] Thu, 25 October 2007 08:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If the remote database is not up, this will just hang.
No difference with the same access in OP job.

Regards
Michel
Re: Checking Remote database is up or not [message #276567 is a reply to message #276542] Thu, 25 October 2007 09:04 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:

If the remote database is not up, this will just hang.


Are you shure? Usually when the remote db for an dblink is not there we get something along the lines of

ORA-02068: following severe error from XXXXX
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
IBM AIX RISC System/6000 Error: 2: No such file or directory


or

ORA-02068: following severe error from XXXXX
ORA-03113: end-of-file on communication channel


IF it was up recently.

That would throw Girishs exception handler.

Thomas
Re: Checking Remote database is up or not [message #276585 is a reply to message #276567] Thu, 25 October 2007 09:57 Go to previous messageGo to next message
girish.rohini
Messages: 744
Registered: April 2005
Location: Delhi (India)
Senior Member
@Michel

The following scenario is alongwith output. The program runs fine, as expected.

SQL> select name from v$database;

NAME
---------
TESTRMAN       -- MOnitoring Database

SQL> create database link test_remote connect to gz107 identified by gz107_gz using 'Girish.WORLD';

Database link created.   -- Link created to Remote database


SQL> select sysdate from dual@test_remote;

SYSDATE
---------
25-OCT-07

SQL> set serveroutput on
SQL> declare
  2    err_msg varchar2(100);
  3    begin
  4     execute immediate 'select sysdate from dual@test_remote';
  5     exception
  6      when others then
  7           err_msg := SUBSTR(SQLERRM, 1, 100);
  8           begin
  9             dbms_output.put_line('Error encountered: ' || err_msg);
 10      end;
 11    end;
 12  /

PL/SQL procedure successfully completed.

----- At this point shutdown remote database

SQL> /
Error encountered: ORA-02068: following severe error from TEST_REMOTE
ORA-03113:
end-of-file on communication channel

PL/SQL procedure successfully completed.

SQL> conn gz107@Girish.world    -- Check for remote database
Enter password:
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist


Warning: You are no longer connected to ORACLE.
SQL> conn / as sysdba   --- Connected to Monitoring database
Connected.
SQL> set serveroutput on
SQL> declare
  2    err_msg varchar2(100);
  3    begin
  4     execute immediate 'select sysdate from dual@test_remote';
  5     exception
  6      when others then
  7           err_msg := SUBSTR(SQLERRM, 1, 100);
  8           begin
  9             dbms_output.put_line('Error encountered: ' || err_msg);
 10      end;
 11    end;
 12  /
Error encountered: ORA-02068: following severe error from TEST_REMOTE
ORA-01034:
ORACLE not available
ORA-27101: shared

PL/SQL procedure successfully completed.

----Brought the remote database up.

SQL> set serveroutput on
SQL> declare
  2    err_msg varchar2(100);
  3    begin
  4     execute immediate 'select sysdate from dual@test_remote';
  5     exception
  6      when others then
  7           err_msg := SUBSTR(SQLERRM, 1, 100);
  8           begin
  9             dbms_output.put_line('Error encountered: ' || err_msg);
 10      end;
 11    end;
 12  /

PL/SQL procedure successfully completed.

SQL> 


--Girish
Re: Checking Remote database is up or not [message #276601 is a reply to message #276585] Thu, 25 October 2007 10:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I badly express myself. I meant inside a job not in a interactive session, this is OP question.
This was my experience, maybe not valid for all Oracle versions.

Anyway, trapping the exception inside this function that should call the function inside the job or directly trapping it in the job is the same thing, doesn't it?

Regards
Michel

[Updated on: Thu, 25 October 2007 10:38]

Report message to a moderator

Re: Checking Remote database is up or not [message #276619 is a reply to message #276601] Thu, 25 October 2007 11:16 Go to previous message
girish.rohini
Messages: 744
Registered: April 2005
Location: Delhi (India)
Senior Member
@Michel

Quote:

This was my experience, maybe not valid for all Oracle versions.


Yes, true this might be version specific. I used 10g (10.2.0.3 to be precise for testing).


Quote:

Anyway, trapping the exception inside this function that should call the function inside the job or directly trapping it in the job is the same thing, doesn't it?


yes, this should work both ways, but my feeling... trapping the exception in a functions gives better readability to code.

--Girish
Previous Topic: PGA Aggregate Target File Location
Next Topic: statspack
Goto Forum:
  


Current Time: Mon Dec 05 11:18:55 CST 2016

Total time taken to generate the page: 0.05310 seconds