-- |------------------------------------------------------------------------------------------------| -- | DATABASE : Oracle 10g, 11g, 12c | -- | FILE : check_dblinks.sql | -- | OUTPUT : ___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/[@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