create database link Test_remote connect to <monitoring_user> identified by <pwd> using 'tns_string';

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;

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

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

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>