-- |------------------------------------------------------------------------------------------------|
-- | DATABASE : Oracle 10g, 11g, 12c                                                                | 
-- | FILE     : check_dblinks.sql                                                                   |
-- | OUTPUT   : <instance_name>_<host_name>_<date>_dblink_report.txt                                |         
-- | PURPOSE  : Dieses Script erzeugt einen detailierten Report der in der Datenbank vorhandenen    |
-- |            Datenbanklinks und testet diese.                                                    |
-- | INFO     : Es werden nur lesende Operationen auf das Dictionary ausgefuehrt.                   |
-- | HAFTUNG:	Es wird keine Haftung fuer Schaeden jeglicher Art, die durch das Originalscript     |
-- |            oder durch eine angepasste Version dieses Scripts entstanden sind oder entstehen    |
-- |            werden. Die Ausfuehrung dieses Scripts erfolgt auf eigene Verantwortung.            | 
-- | VERSION  : 1.0                                                                                 |
-- | DATE     : 05.10.2017                                                                          |
-- | USAGE    : Das Script sollte vorwiegend als User mit SYSDBA-Rolle, z.B. SYS, ausgefuehrt       |
-- |            werden, um auf alle Dictionary-Views Zuriff zu haben. Steht ein SYSDBA-User nicht   |
-- |            zur Verfuegung, sollte der User mindestens die SELECT_CATALOG ROlle und die         |
-- |            Execute Berechtigung auf das Package SYS.DBMS_SYS_SQL besitzen.                     |   
-- |            sqlplus sys/<password>[@db_connect_identifier] as sysdba @check_dblinks.sql         |
-- | AUTHOR   : Frank Gerasch                                                                       |
-- +------------------------------------------------------------------------------------------------+

SET PAGESIZE 500
SET LINESIZE 120 FEEDBACK OFF
SET SERVEROUTPUT ON FORMAT WRAPPED SIZE 1000000

ALTER SESSION SET NLS_TERRITORY=GERMANY;

column spool_name new_value S
set termout off
select lower(instance_name)||'_'||lower(host_name)||'_'||TO_CHAR(SYSDATE,'DD.MM.YYYY')||'_dblink_report.txt' spool_name
from v$instance
where rownum < 2;
set termout on
spool &S


DECLARE
 v1 VARCHAR(200);
BEGIN
 select name into v1 from v$database;
 DBMS_OUTPUT.PUT_LINE(' ');
 DBMS_OUTPUT.PUT_LINE(' ');
 DBMS_OUTPUT.PUT_LINE('---------------------------------------------------------------------------------------------------');
 DBMS_OUTPUT.PUT_LINE('ORACLE DATABASE LINKS '||v1 );
END;
/

declare
  cursor public_links is select l.name db_link, u.name owner, l.ctime created, host, userid username, decode(flag,0,'Connected User',1,'Current User',2,'Fixed User') user_type from sys.link$ l, sys.user$ u where  user# = owner# and u.name = 'PUBLIC' order by u.name, l.name;
  cursor private_links is select l.name db_link, u.name owner, l.ctime created, host, userid username, decode(flag,0,'Connected User',1,'Current User',2,'Fixed User') user_type, user# from sys.link$ l, sys.user$ u where  user# = owner# and u.name != 'PUBLIC' order by u.name, l.name;
  v_status number default null;
  v_cursor integer;
  v_dbname varchar2 (100);
  v_user_type varchar2 (100);
  v_hostname varchar2 (100);
  v_platform varchar2 (100);
  v_instance varchar2 (100);
  v_dblink varchar2 (100);
  v_dbversion varchar2 (100);
  v_connect_identifier varchar2 (1000);
  v_connect_identifier_1 varchar2 (1000);
  v_connect_identifier_2 varchar2 (1000);
  v_connect_identifier_length varchar2 (100);
  v_remote_user varchar2 (100);
  no_table_942 exception;
  connect_timeout_12170 exception;
  no_listener_12541 exception;
  not_resolvable_12154 exception;
  no_valid_password_user_1017 exception;
  connect_failed_12545 exception;
  account_locked_28000 exception;
  pragma exception_init(no_table_942, -00942);
  pragma exception_init(connect_timeout_12170, -012170);
  pragma exception_init(not_resolvable_12154, -012154);
  pragma exception_init(connect_failed_12545, -12545);
  pragma exception_init(no_listener_12541, -012541);
  pragma exception_init(no_valid_password_user_1017, -01017);
  pragma exception_init(account_locked_28000, -28000);

-- Check public links
begin
  dbms_output.put_line('---------------------------------------------------------------------------------------------------');
  for c1 in public_links 
  loop
    v_connect_identifier := c1.host;
    v_remote_user := c1.username;
    dbms_output.put_line('Database Link Name:   '|| c1.db_link) ;
    dbms_output.put_line('Database Link Type:   PUBLIC') ;
    dbms_output.put_line('Database User Type:   '|| c1.user_type) ;
    dbms_output.put_line('Remote User:          '|| c1.username) ;
    select length(v_connect_identifier) into v_connect_identifier_length from dual;
    if ( v_connect_identifier_length > 30 )
    then
      select substr(v_connect_identifier,1,78) into v_connect_identifier_1 from dual;
      select substr(v_connect_identifier,79) into v_connect_identifier_2  from dual;
      dbms_output.put_line('Connect Identifier:   '|| v_connect_identifier_1);
      dbms_output.put_line('                      '|| v_connect_identifier_2);
    else
      dbms_output.put_line('Connect Identifier:   '|| v_connect_identifier ||' (tnsnames alias)');
    end if;
    begin
    if c1.user_type = 'Fixed User' 
    then
      execute immediate 'select global_name from global_name@"'||c1.db_link||'"' into v_dbname;
      execute immediate 'select banner from v$version@"'||c1.db_link||'" where banner like ''Oracle%''' into v_dbversion;
      execute immediate 'select instance_name from v$instance@"'||c1.db_link||'"' into v_instance;
      execute immediate 'select host_name from v$instance@"'||c1.db_link||'"' into v_hostname;
      execute immediate 'select platform_name from v$database@"'||c1.db_link||'"' into v_platform;
      dbms_output.put_line('Connect Status:       SUCCESSFUL');
      dbms_output.put_line('Remote DB Name:       '|| v_dbname);
      dbms_output.put_line('Remote Version:       '|| v_dbversion);
      dbms_output.put_line('Remote Instance:      '|| v_instance);
      dbms_output.put_line('Remote Host:          '|| v_hostname);
      dbms_output.put_line('Remote Platform:      '|| v_platform);
      commit; 
      execute immediate 'alter session close database link "'||c1.db_link||'"';
    else
        dbms_output.put_line('Connect Status:       UNKNOWN - Check not possible with user type '||c1.user_type);  
        dbms_output.put_line('                      To verify the database link, login as remote user '||c1.username||' and do');  
        dbms_output.put_line('                      "select * from dual@'||c1.db_link||';".');
    end if;
      exception
      when no_table_942 then
	  	dbms_output.put_line('Connect Status:       SUCCESSFUL');
        dbms_output.put_line('Remark:               Not possible to get more information about remote database due to');
        dbms_output.put_line('                      missing credentials on remote dictionary for remote user '||v_remote_user);
      when connect_timeout_12170 then
        dbms_output.put_line('Connect Status:       FAILED ');
        dbms_output.put_line('                      ORA'||sqlcode||': Cannot reach remote host - connect timeout');
      when not_resolvable_12154 then
        dbms_output.put_line('Connect Status:       FAILED ');
        dbms_output.put_line('                      ORA'||sqlcode||': Connect identifier '||v_connect_identifier||' not resolvable');
        dbms_output.put_line('                      Probably no tnsnames.ora entry present for connect identifier '||v_connect_identifier);
      when no_listener_12541 then
        dbms_output.put_line('Connect Status:       FAILED ');
        dbms_output.put_line('                      ORA'||sqlcode||': Cannot reach listener at remote host '); 
      when connect_failed_12545 then
        dbms_output.put_line('Connect Status:       FAILED ');
        dbms_output.put_line('                      ORA'||sqlcode||': Connect failed because target host or object does not exist');		
      when no_valid_password_user_1017 then
        dbms_output.put_line('Connect Status:       FAILED ');
        dbms_output.put_line('                      ORA'||sqlcode||': Invalid username/password for remote user '||v_remote_user);
      when account_locked_28000 then
        dbms_output.put_line('Connect Status:       FAILED ');
        dbms_output.put_line('                      ORA'||sqlcode||': Remote Account '||v_remote_user||' locked'); 
      when others then
        dbms_output.put_line('Connect Status:       FAILED ');
        dbms_output.put_line('                      ORA'||sqlcode);
        dbms_output.put_line('                      '||sqlerrm);
    end;
  dbms_output.put_line('---------------------------------------------------------------------------------------------------');
  end loop;

  execute immediate ('ALTER SESSION SET GLOBAL_NAMES=FALSE');

-- Check private links

  for c2 in private_links 
  loop
  begin
    select length(v_connect_identifier) into v_connect_identifier_length from dual;
    dbms_output.put_line('Database Link Name:   ' || c2.db_link) ;
    dbms_output.put_line('Database Link Type:   PRIVATE') ;
    dbms_output.put_line('Database Link Owner:  ' || c2.owner) ;
    dbms_output.put_line('Database User Type:   '|| c2.user_type) ;
    if c2.user_type = 'Fixed User'
    then
      dbms_output.put_line('Remote User:          ' || c2.username) ;
      v_remote_user := c2.username;
    else
      dbms_output.put_line('Remote User:          ' || c2.owner) ;
      v_remote_user :=  c2.owner;
    end if;
    if c2.user_type = 'Fixed User'
    then
    v_connect_identifier := c2.host;
    v_dblink := c2.db_link;
    select length(v_connect_identifier) into v_connect_identifier_length from dual;
    if ( v_connect_identifier_length > 30 )
    then
      select substr(v_connect_identifier,1,78) into v_connect_identifier_1 from dual;
      select substr(v_connect_identifier,79) into v_connect_identifier_2  from dual;
      dbms_output.put_line('Connect Identifier:   '|| v_connect_identifier_1);
      dbms_output.put_line('                      '|| v_connect_identifier_2);
    else
      dbms_output.put_line('Connect Identifier:   '|| v_connect_identifier ||' (tnsnames alias)');
    end if;
    v_cursor:=sys.dbms_sys_sql.open_cursor();
    sys.dbms_sys_sql.parse_as_user(v_cursor,'select * from global_name@'||c2.db_link,dbms_sql.native,c2.user#);
    sys.dbms_sys_sql.define_column( v_cursor,1,v_dbname,1000);
    v_status:=sys.dbms_sys_sql.execute(v_cursor);

    if ( dbms_sys_sql.fetch_rows(v_cursor) > 0 )
    then
      dbms_sys_sql.column_value(v_cursor, 1,v_dbname );
    end if;
   
    dbms_output.put_line('Connect Status:       SUCCESSFUL');
    dbms_output.put_line('Remote DB Name:       '||v_dbname);
	commit;
    sys.dbms_sys_sql.close_cursor(v_cursor);

    v_cursor:=sys.dbms_sys_sql.open_cursor();
    sys.dbms_sys_sql.parse_as_user(v_cursor,'select banner from v$version@'||c2.db_link||' where rownum = 1',dbms_sql.native,c2.user#);
    sys.dbms_sys_sql.define_column( v_cursor,1,v_dbversion,100);
    v_status:=sys.dbms_sys_sql.execute(v_cursor);
    if ( dbms_sys_sql.fetch_rows(v_cursor) > 0 )
    then
      dbms_sys_sql.column_value(v_cursor, 1,v_dbversion );
      dbms_output.put_line('Remote Version:       '||v_dbversion);
    end if;
	commit;
    sys.dbms_sys_sql.close_cursor(v_cursor);

    v_cursor:=sys.dbms_sys_sql.open_cursor();
    sys.dbms_sys_sql.parse_as_user(v_cursor,'select instance_name from v$instance@'||c2.db_link,dbms_sql.native,c2.user#);
    sys.dbms_sys_sql.define_column( v_cursor,1,v_instance,100);
    v_status:=sys.dbms_sys_sql.execute(v_cursor);
    if ( dbms_sys_sql.fetch_rows(v_cursor) > 0 )
    then
      dbms_sys_sql.column_value(v_cursor, 1,v_instance );
      dbms_output.put_line('Remote Instance:      '||v_instance);
    end if;
	commit;
    sys.dbms_sys_sql.close_cursor(v_cursor);

    v_cursor:=sys.dbms_sys_sql.open_cursor();
    sys.dbms_sys_sql.parse_as_user(v_cursor,'select host_name from v$instance@'||c2.db_link,dbms_sql.native,c2.user#);
    sys.dbms_sys_sql.define_column( v_cursor,1,v_hostname,100);
    v_status:=sys.dbms_sys_sql.execute(v_cursor);
    if ( dbms_sys_sql.fetch_rows(v_cursor) > 0 )
    then
      dbms_sys_sql.column_value(v_cursor, 1,v_hostname );
      dbms_output.put_line('Remote Host:          '||v_hostname);
    end if;
	commit;
    sys.dbms_sys_sql.close_cursor(v_cursor);

    v_cursor:=sys.dbms_sys_sql.open_cursor();
    sys.dbms_sys_sql.parse_as_user(v_cursor,'select platform_name from v$database@'||c2.db_link,dbms_sql.native,c2.user#);
    sys.dbms_sys_sql.define_column(v_cursor,1,v_platform,100);
    v_status:=sys.dbms_sys_sql.execute(v_cursor);
    if ( dbms_sys_sql.fetch_rows(v_cursor) > 0 )
    then
      dbms_sys_sql.column_value(v_cursor, 1,v_platform );
    end if;
    dbms_output.put_line('Remote Platform:      '||v_platform);
	commit;
    sys.dbms_sys_sql.close_cursor(v_cursor);
  else
     dbms_output.put_line('Connect Status:       UNKNOWN - Check not possible with user type '||c2.user_type);  
     dbms_output.put_line('                      To verify the database link, login as remote user '||c2.username||' and do');  
     dbms_output.put_line('                      "select * from dual@'||c2.db_link||';".');
  end if;
  exception
    when no_table_942 then
	    dbms_output.put_line('Connect Status:       SUCCESSFUL');
      dbms_output.put_line('Remark:               Not possible to get more information about remote database due to');
      dbms_output.put_line('                      missing credentials on remote dictionary for remote user '||v_remote_user);
    when connect_timeout_12170 then
      dbms_output.put_line('Connect Status:       FAILED ');
      dbms_output.put_line('                      ORA'||sqlcode||': Cannot reach remote host - connect timeout');
    when not_resolvable_12154 then
      dbms_output.put_line('Connect Status:       FAILED ');
      dbms_output.put_line('                      ORA'||sqlcode||': Connect identifier '||v_connect_identifier||' not resolvable');
      dbms_output.put_line('                      Probably no tnsnames.ora entry present for connect identifier '||v_connect_identifier);
    when no_listener_12541 then
      dbms_output.put_line('Connect Status:       FAILED ');
      dbms_output.put_line('                      ORA'||sqlcode||': Cannot reach listener at remote host ');
    when connect_failed_12545 then
      dbms_output.put_line('Connect Status:       FAILED ');
      dbms_output.put_line('                      ORA'||sqlcode||': Connect failed because target host or object does not exist');
    when no_valid_password_user_1017 then
      dbms_output.put_line('Connect Status:       FAILED ');
      dbms_output.put_line('                      ORA'||sqlcode||': Invalid username/password for remote user '||v_remote_user);
    when account_locked_28000 then
      dbms_output.put_line('Connect Status:       FAILED ');
      dbms_output.put_line('                      ORA'||sqlcode||': Remote Account '||v_remote_user||' locked'); 
    when others then
      dbms_output.put_line('Connect Status:       FAILED ');
      dbms_output.put_line('                      ORA'||sqlcode);
      dbms_output.put_line('                      '||sqlerrm);
end;
dbms_output.put_line('---------------------------------------------------------------------------------------------------');
end loop;

end ;
/


spool off
exit
