Oracle Database Links sind eine hervorragende Möglichkeit, um Schnittstellen zwischen zwei Oracle Datenbanken aufzubauen oder Daten mit Hilfe von Materialized Views zwischen Datenbanken zu replizieren. Davon wird in Unternehmen mit verteilten Anwendung und verteilter Datenhaltung häufig Gebrauch gemacht. In diesem Artikel werde ich einen Überblick über die unterschiedlichen Datenbanklink-Typen geben und einige nützliche SQL-Abfragen präsentieren, mit denen man sich ein genaues Bild machen kann welche Database Links in einer Datenbank vorhanden sind und wie diese genutzt werden.
Oracle Database Link
Ein Oracle Database Link ist ein Objekt in der Datenbank, welches eine unidirektionale Verbindung zu einer anderen Oracle Datenbank ermöglicht. Die Definition des Database Links enthält mindestens den Namen des Database Links, des Eigentümers und Verbindungsinformationen zum Ziels. Je nach Typ, ist auch der Name und das Passwort des Users abgespeichert, zu dem eine Verbindung hergestellt werden soll. Die Information zum Ziel (Net Service Namen) wird üblicherweise als TNS-Aliasnamen angegeben, der dann in der SQL*Net Konfigurationsdatei tnsnames.ora in einen vollständigen Connect-String zur Zieldatenbank aufgelöst wird. Alternativ kann man aber auch den Connect-String selber im „Easy Connect“-Format bei der Erstellung des Database Links nutzen, um sich unabhängig von einer tnsnames.ora zu machen.
Der große Vorteil von Database Links ist, dass er Benutzern erlaubt auf Objekte anderer User in einer entfernten Datenbank mit den Privilegien der Objekteigentümers zuzugreifen. Mit anderen Worten, ein lokaler Benutzer kann auf eine entfernte Datenbank zugreifen, als wäre er der Eigentümer der Objekte in der entfernten Datenbank.
Beispiel Erstellung eines Database Links mit Tnsnames-Aliasname:
create database link <DB LINK NAME> connect to <REMOTE USER> identified by <PASSWORD> using <Tnsnames-Aliasname>;
Beispiel Erstellung eines Database Links mit Easy Connect
create database link <DB LINK NAME> connect to <REMOTE USER> identified by <PASSWORD> using '//<server>:<port>/<service_name>';
Beispiel Zugriff über einen Database Link OE_ORCL2 auf die Tabelle DEPT im Shema OE der entfernten Datenbank ORCL2
create database link oe_orcl2 connect to oe identified by mysecretpassword using '//dbserver2:1521/orcl2'; select deptno,dname from dept@oe_orcl2;
Public und Private Database Links
Ein Database Link ist üblicherweise vom Typ „Public“ oder „Private“. Es gibt noch den Typ „Global“, der einen Directory Server erfordert und für das ganze Netzwerk gilt, aber eher selten genutzt wird. Ein Database Link vom Typ Public gehört dem User Public, gilt datenbankweit und, wie bei allen Public-Objekten, steht dieser allen Benutzern und PL/SQL Programmen zur Verfügung. Daraus folgt, dass jeder Datenbankbenutzer damit auf die Objekte des definierten Users in der entfernten Datenbank zugreifen kann! Aus Sicherheitsgründen wird daher empfohlen diesen Typ nur in Ausnahmefällen zu nutzen und lieber Database Links vom Typ „Private“ zu erstellen.
Ein Database Link vom Typ Private gehört einem bestimmten User und berechtigt auch nur diesen den Database Link zu nutzen. Sprich, nur der Eigentümer des Database Links kann auf die Objekte in der entfernten Datenbank zugreifen.
Indentitäts-Typen
Bei der Erstellung eines Database Links wird anhand der Syntax festgelegt mit welcher Identität und welcher Authentifizierungsmethode sich an die entfernte Datenbank angemeldet wird. Oracle unterscheidet zwischen drei verschiedenen Kategorien.
1. Fixed-User Database Link
Dieser am häufigsten verwendete Database Link beinhaltet einen Usernamen und ein Passwort. Wenn ein solcher Database Link genutzt wird, so wird sich mit dem bei der Erstellung angegebenen Benutzer und Passwort an die entfernte Datenbank verbunden. Da dieser Datenbanklink-Typ auch das Passwort enthält, wirken sich Passwortänderungen in der entfernten Datenbank unmittelbar auf die Funktionsfähigkeit des Database Links aus. Wenn das Passwort in der entfernten Datenbank geändert wird, so muss der Database Link mit dem neuen Passwort neu erstellt werden. Ein großer Vorteil und sicher auch der Grund für die häufige Verbreitung dieses Typs ist, dass man einem Benutzer mit einfachen Mitteln Zugriff auf sämtliche Objekte eines anderen Benutzers in dessen Context erteilen kann.
Beispiel Private:
CREATE DATABASE LINK ORCL2 CONNECT TO OE IDENTIFIED BY secretpassword USING 'ORCL2';
Beispiel Public:
CREATE PUBLIC DATABASE LINK ORCL2_OE CONNECT TO OE IDENTIFIED BY secretpassword USING 'ORCL2';
2. Connected-User Database Link
Dies sind Database Links, die bezüglich des Namens und Passworts einen gleichen User in der entfernten Datenbank erfordern. Bei der Erstellung des Database Links wird kein bestimmter Usernamen oder Passwort festgelegt. Die Verbindung zur entfernten Datenbank wird mit dem User hergestellt, der den Database Link aufruft. Wenn z.B. der User SYSTEM auf einen Public Database Link zugreift, dann ist der Connected-User SYSTEM und die Verbindung wird zum Schema SYSTEM der entfernten Datenbank hergestellt. Ein Connected-User ist jeder User, der den Database Link aufrufen kann. Passwortänderungen des Nutzers müssen immer auf beiden involvierten Datenbanken identisch und zeitgleich erfolgen, um die Funktionsfähiglkeit zu erhalten.
Beispiel Private:
CREATE DATABASE LINK ORCL2_OE USING 'ORCL2';
Beispiel Public:
CREATE PUBLIC DATABASE LINK ORCL2_OE USING 'ORCL2';
3. Current-User Datenbase Link
Ein solcher Database Link erlaubt es Enterprise Users (Global Users) auf Objekte einer entfernten Datenbank zuzugreifen, ohne dass Authentifizierungsinformationen beim Aufruf des Database Links übergeben werden. Bei der Erstellung wird ebenfalls kein Usernamen und Passwort festgelegt. Voraussetzung sind SSL- oder Passwort-authentifizierte Enterprise-User, die eine entsprechend eingerichtete Infrastruktur erwarten und die in beiden involvierten Datenbanken angelegt sein müssen. Current-User Database Links dürfen aufgrund der notwendigen „Enterprise“-Authentifizierung nur mit der Oracle Advanced Security Option verwendet werden. Wenn der Aufruf des Database Links übrigens in einer Prozedur, die dem Global User gehört, erfolgt, dann muss der User, der den Database Link über die Ausführung Prozedur nutzt, nicht der Eigentümer und auch auch kein Enterprise User sein.
Beispiel Private:
CREATE DATABASE LINK ORCL2_OE CONNECT TO CURRENT_USER USING 'ORCL2';
Nützliche SQL-Abfragen zu Database Links
Folgende Abfragen helfen dem DBA Informationen zu den in einer Datenbank vorhandenen Database Links zu erhalten.
1. Anzeige der vorhandenen Database Links
set pagesize 200
set linesize 110
col "remote user" FOR a15
col "connect identifier" FOR a19
col created format a11
col owner format a15
col db_link FOR a25
SELECT owner,
db_link,
To_char(created, 'dd.mm.yyyy') created,
host "CONNECT IDENTIFIER",
username "REMOTE USER"
FROM dba_db_links
ORDER BY owner,
db_link;
OWNER DB_LINK CREATED CONNECT IDENTIFIER REMOTE USER --------------- ------------------------- ----------- ------------------- --------------- PUBLIC PH_ORCL2.EXAMPLE.DE 04.09.2007 ORCL2.EXAMPLE.DE PH HR ORCL2 04.09.2007 ORCL2.EXAMPLE.DE SCOTT PM HR_HRDB_LINK 04.05.2017 HRDB.EXAMPLE.DE HR SCOTT OE_ERPDB 27.10.2013 ERPDB OE OE SALESLINK 13.10.2016 SALESDB SH
Die gleiche Abfrage mit Information zum User-Type (Anmeldung als SYS erforderlich).
set pagesize 200
set linesize 110
col "remote user" FOR a15
col "connect identifier" FOR a19
col created format a11
col owner format a15
col db_link FOR a25
SELECT owner,
db_link,
To_char(created, 'dd.mm.yyyy') created,
host "CONNECT IDENTIFIER",
username "REMOTE USER"
FROM dba_db_links
ORDER BY owner,
db_link;
OWNER DB_LINK CREATED CONNECT IDENTIFIER REMOTE USER USER TYPE ---------- -------------------- ----------- ------------------- ------------ --------------- PUBLIC PH_ORCL2.EXAMPLE.DE 04.09.2007 ORCL2.EXAMPLE.DE PH Fixed User HR ORCL2 04.09.2007 ORCL2.EXAMPLE.DE Connected User PM HR_HRDB_LINK 04.05.2012 HRDB.EXAMPLE.DE HR Fixed User SCOTT SALESLINK 13.10.2016 SALESDB SH Fixed User OE OE_ERPDB 27.10.2013 ERPDB Connected User
2. Database Link relevante Parameter
set linesize 110
col parameter FOR a24
col value FOR a6
col isdefault FOR a9
col description FOR a61
SELECT name parameter,
display_value value,
isdefault,
description
FROM v$parameter
WHERE name IN ( 'open_links', 'open_links_per_instance', 'global_names' );
PARAMETER VALUE ISDEFAULT DESCRIPTION ------------------------ ------ --------- --------------------------------- global_names FALSE TRUE enforce that database links have same name as remote database open_links 4 TRUE max # open links per session open_links_per_instance 4 TRUE max # open links per instance
3. Nutzungs-Statistiken zu Database Links
(Seit Instancestart)
set linesize 110
col name FOR a40 head sysstat
col value FOR 9999999999999
SELECT name,
value
FROM v$sysstat
WHERE name IN ( 'bytes sent via SQL*Net to dblink',
'bytes received via SQL*Net from dblink',
'SQL*Net roundtrips to/from dblink' );
SYSSTAT VALUE ---------------------------------------- -------------- bytes sent via SQL*Net to dblink 2423247583 bytes received via SQL*Net from dblink 6705612557 SQL*Net roundtrips to/from dblink 9567350
4. Derzeit geöffnete Database Links
set pagesize 200
set linesize 110
col "db link" FOR a26
col "owner" FOR a15
col userid FOR a15 heading "remote user"
col logged_on FOR a5 heading "logon"
col protocol FOR a8
col host FOR a22 heading "connect identifier"
SELECT s.name "db link",
u.name "owner",
d.host,
userid,
logged_on
FROM v$dblink v,
sys.link$ s,
dba_db_links d,
sys.user$ u
WHERE owner# = user#
AND v.db_link = d.db_link
AND logged_on = 'YES'
ORDER BY v.db_link,
owner;
(Anmeldung als SYS erforderlich)
5. Database Links, die von Synonymen referenziert werden
set pagesize 200
set linesize 110
col synonym_owner FOR a20
col synonym_name FOR a30
col db_link FOR a40
SELECT db_link,
owner synonym_owner,
synonym_name
FROM dba_synonyms
WHERE db_link IS NOT NULL
GROUP BY db_link,
owner,
synonym_name,
db_link
ORDER BY owner,
db_link,
synonym_name;
DB_LINK SYNONYM_OWNER SYNONYM_NAME ---------------------------------------- -------------------- ------------------------------ PH_ORCL2.EXAMPLE.DE PM PH.DEPT ORCL2 SCOTT MGR.COMMENT_TXT HR_HRDB_LINK PUBLIC EMP SALESLINK PUBLIC CUSTOMER OE_ERPDB PUBLIC SALES
6. Database Links, die von MViews referenziert werden
set pagesize 200
set linesize 110
col owner FOR a20
col mview_name FOR a30
col master_link FOR a26
col refresh_mode FOR a12
col refresh_method FOR a14
SELECT master_link,
mview_name,
owner,
refresh_method
FROM dba_mviews
WHERE master_link IS NOT NULL
ORDER BY owner,
master_link;
MASTER_LINK MVIEW_NAME OWNER REFRESH_METHOD -------------------------- ------------------------------ -------------------- -------------- @PH_ORCL2.EXAMPLE.DE MV_DEVICES MGR FAST @SALESLINK MV_CUSTOMER SCOTT FAST @HR_HRDB_LINK EMP HR FAST @HR_HRDB_LINK DEPT PH FAST @OE_ERPDB RESULTS OE FAST
7. Objekte im Objekt-Cache, auf die über einen Database Link zugegriffen wird
set pagesize 200
set linesize 110
col db_link FOR a20
col name FOR a20
col owner FOR a20
col TYPE FOR a15
SELECT db_link,
owner,
name,
TYPE
FROM v$db_object_cache
WHERE db_link IS NOT NULL
ORDER BY owner,
db_link;
DB_LINK OWNER NAME TYPE -------------------- -------------------- -------------------- --------------- SALESLINK SH MAG_PCKGE PACKAGE SALESLINK SH SALES_TAB TABLE OE_ERPDB OE CALC NON-EXISTENT HR_HRDB_LINK HR V_DEPT VIEW HR_HRDB_LINK HR V_EMP_ATTRIBUTES VIEW
8. User, die aktuell ein Database Link nutzen (Incoming and Outcoming)
col origin FOR a16
col gtxid FOR a28
col lsession FOR a10
col username FOR a15
col status FOR a8
SELECT /*+ ORDERED */ Substr(s.ksusemnm, 1, 10) ||'-'|| Substr(s.ksusepid, 1, 10) "ORIGIN",
Substr(g.k2gtitid_ora, 1, 35) "GTXID",
Substr(s.indx, 1, 4)||'.'|| Substr(s.ksuseser, 1, 5)"LSESSION",
s2.username,
Substr(Decode(Bitand(ksuseidl, 11), 1, 'ACTIVE',0,Decode(Bitand(ksuseflg, 4096), 0,
'INACTIVE','CACHED'), 2, 'SNIPED', 3, 'SNIPED','KILLED'), 1, 8) "STATUS"
FROM x$k2gte g,
x$ktcxb t,
x$ksuse s,
v$session_wait w,
v$session s2
WHERE g.k2gtdxcb = t.ktcxbxba
AND g.k2gtdses = t.ktcxbses
AND s.addr = g.k2gtdses
AND w.sid = s.indx
AND s2.sid = w.sid;
(Anmeldung als SYS erforderlich)
ORIGIN GTXID LSESSION USERNAME STATUS --------------------- ---------------------------- ---------- --------------- -------- PC_SCOTT ORCL2.d90ada8a.10.29.676282 245.21421 SCOTT INACTIVE DBSERVER1 ORCL2.d90ada8a.15.25.490887 168.11363 SYS INACTIVE
9. Von Stored Objects referenzierte Database Links
set linesize 130 set pagesize 500 col object for a50 col DB_LINK for a30 head "CONTAINED DATATABASE LINK" SELECT DISTINCT s.TYPE||' '||s.owner||'.'||s.name OBJECT, l.db_link FROM dba_source s JOIN dba_db_links l ON Upper(s.text) LIKE '%@'||Upper(l.db_link)||'%' ORDER BY object;
OBJECT DATATABASE LINK ---------------------------------- ----------------------------- PACKAGE BODY PDA.DBP029 SYNC_SCOTT_OSC.WORLD PACKAGE BODY PDA.DBPCOPYSERVP SYNC_SCOTT_OSC.WORLD PACKAGE BODY PDA.DBPPLZDMAG DBSERV_DB8I PACKAGE BODY PDA.DBPPLZDMAG DBSERV_DB8I.US.ORACLE.COM PACKAGE BODY PDA.DBPPSST001 DBSERV_DB8I PACKAGE BODY PDA.DBPVFB4TOOL SYNC_SCOTT_OSC.WORLD PACKAGE BODY PDA.DBPVFB9TOOL SYNC_SCOTT_OSC.WORLD PACKAGE BODY PDA.DBPXPOBS001 XPLINK PACKAGE BODY PDA.DBPXPOBS006 XPLINK PACKAGE PDA.DBPEGK004 SYNC_SCOTT_OSC.WORLD TRIGGER PDA.PDA_ANZBUI_LOG DBSERV_DB8I
10. Von Views referenzierte Database Links
set serveroutput on
DECLARE
V_CHAR VARCHAR2(32767);
V_DBLINK_COUNT NUMBER := 0;
BEGIN
select count(*) into V_DBLINK_COUNT from dba_db_links;
IF V_DBLINK_COUNT> 0 THEN
FOR REC1 IN (select OWNER, DB_LINK DB_LINK from dba_db_links)
LOOP
FOR REC IN (SELECT owner, view_name, text FROM ALL_VIEWS WHERE TEXT_LENGTH <32767)
LOOP
V_CHAR := UPPER(REC.TEXT);
IF (INSTR(V_CHAR, '@'||REC1.DB_LINK)> 0) THEN
DBMS_OUTPUT.PUT_LINE('VIEW '||REC.OWNER||'.'||REC.VIEW_NAME||' is using '||REC1.DB_LINK);
END IF;
END LOOP;
END LOOP;
ELSE
DBMS_OUTPUT.PUT_LINE('No database links exists in database');
END IF;
END;
/
Einschränkung: Es werden nur Views mit maximal 32776 Zeichen berücksichtigt
VIEW PDA.Z_XPPAAUF is using DBSERV_DB8I VIEW PDA.Z_VERKBELEG is using DBSERV_DB8I VIEW PDA.ADR_EXT_AP is using DBSERV_DB8I VIEW PDA.ADR_EXT is using DBSERV_DB8I VIEW PDA.LEI_EXT is using DBSERV_DB8I VIEW PDA.Z_XPPAAUF is using DBSERV_DB8I.US.ORACLE.COM VIEW PDA.Z_VERKBELEG is using DBSERV_DB8I.US.ORACLE.COM VIEW PDA.ADR_EXT_AP is using DBSERV_DB8I.US.ORACLE.COM VIEW PDA.ADR_EXT is using DBSERV_DB8I.US.ORACLE.COM VIEW PDA.LEI_EXT is using DBSERV_DB8I.US.ORACLE.COM