Oracle Advanced Replication – wie funktioniert`s

Oracle Advanced Replication –
wie funktioniert’s ?
(Teil II – Updatable Materialized Views
oder Updatable Snapshot Replikation)
Autor: Stefan Menschel, ORACLE Deutschland GmbH
DOAGNews Q4_2004
Dieses Werk ist urheberrechtlich geschützt. Die dadurch begründeten Rechte, insbesondere die der Übersetzung, des Nachdrucks, des Vortrags, der Entnahme von Abbildungen
und Tabellen, der Funksendung, der Mikroverfilmung oder der Vervielfältigung auf anderen
Wegen und der Speicherung in Datenverarbeitungsanlagen, bleiben, bei auch nur
auszugsweiser Verwertung, vorbehalten. Eine Vervielfältigung dieses Werkes oder von
Teilen dieses Werkes ist auch im Einzelfall nur in den Grenzen der gesetzlichen
Bestimmungen des Urheberrechtes der Bundesrepublik Deutschland vom 9. September
1965 in der jeweils geltenden Fassung zulässig. Sie ist grundsätzlich vergütungspflichtig.
Zuwiderhandlungen unterliegen den Strafbestimmungen des Urheberrechtsgesetzes.
©2004
In diesem Artikel wird der Teil der symmetrischen Orale Realisation näher erläutern, welcher Update Anywhere DatenbankImplementierungen unterstützt. In der Entwicklung heutiger IT-Systeme wird immer öfter von Datenbankkonsolodierung und
zentralen Datenbanken gesprochen. Dennoch gibt es eine Reihe von Anwendungen, welche eine verteilt replizierte Datenbank
erfordern. Allein ein Aspekt der Security – Problematik für Internetdatenbankserver könnte mit Hilfe dieser Technologie ggf.
gelöst werden.
Was sind Updatable Materialized Views (UMV)?
Im ersten Kapitel wurde die einfache Replikation mit Hilfe der ReadOnly Materialized Views (RMV) erläutert. Auf der UMV
können nun DML-Operationen (INSERT/UPDATE/DELETE) im Gegensatz zur RMV ausgeführt werden. Die Grundlagen der
UMV stammen von den RMV. Dies bedeutet, dass die UMV ebenfalls wie die RMV die SQL*Net Konfiguration sowie
Datenbanklinks nutzt, um sich auf einem zentralen System (Masterdefinitionsite-MDS) zu registrieren. Zusätzlich sind nun
allerdings eine Reihe von Datenbankinternen Strukturen auf der MDS als auch auf der Datenbank der UMV erforderlich. Allein das
Thema „Transaktionskonflikt“ lässt erkennen, das hierfür zusätzliche Datenbankfunktionalitäten für ein solches System notwendig
sind.
In meinem Beispiel soll die Tabelle <DEPARTMENT> des User SCOTT über eine Updatable Materialized View auf die
Datenbanken DB_B.WORLD sowie DB_C.WORLD implementiert werden.
DEPARTMENT
DatabaseLink
DB_A.WORLD
MDS
DEPARTMENT
DatabaseLink
DEPARTMENT
DB_B.WORLD
UMV
DB_C.WORLD
UMV
Abb.1: Struktur Updatable Materialized Views
Voraussetzungen für die Implementierung der UMV auf der MDS
Alle im ersten Kapitel beschriebenen Komponenten gelten ebenfalls für die Implementierung der Updatable
Materialized Views. D.h. es müssen folgende Voraussetzungen geschaffen sein :
• SQL*Net Konfiguration, INIT*ORA Parameter
• Anlegen eines User REPADMIN auf allen Datenbanken, welcher intern die Replikation zwischen den
Datenbanksystemen vornimmt
• Anlegen von Materialized View Log für die Tabelle DEPARTMENT auf der Masterdefinitionsite im Schema
SCOTT (siehe auch Kapitel 1)
SQL>CONN scott/tiger@db_a.world
CREATE MATERIALIZED VIEW LOG ON department TABLESPACE snaplog;
Abb.2: SQL-Statement – Anlegen Materialized View Log
•
Festlegen des User REPADMIN als Propagator User und Vergabe folgender Grants
©2004
SQL>CONNECT SYS /PW@DB_A.WORLD AS SYSDBA;
EXEC DBMS_DEFER_SYS.REGISTER_PROPAGATOR('REPADMIN');
BEGIN
DBMS_REPCAT_ADMIN.REGISTER_USER_REPGROUP(
username
=>'REPADMIN’
privilege_type
=>'receiver'
list_of_gnames
=> NULL) ;
END;
/
EXEC DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_REPGROUP(userid=>’REPADMIN');
,
,
GRANT EXECUTE ON DBMS_DEFER
TO REPADMIN;
GRANT EXECUTE ON DBMS_DEFER_SYS
TO REPADMIN;
GRANT EXECUTE ON DBMS_REPCAT_UTL2
TO REPADMIN;
GRANT EXECUTE ON DBMS_REPCAT
TO REPADMIN;
GRANT EXECUTE ON DBMS_DEFER_QUERY
TO REPADMIN;
GRANT EXECUTE ANY PROCEDURE
TO REPADMIN;
GRANT EXECUTE ON DBMSOBJGWRAPPER
TO REPADMIN;
GRANT EXECUTE ON DBMS_REPCAT_RPC
TO REPADMIN;
GRANT EXECUTE ON DBMS_DEFER_INTERNAL_SYS
TO REPADMIN;
GRANT LOCK ANY TABLE
TO REPADMIN;
GRANT INSERT ANY TABLE
TO REPADMIN;
GRANT UPDATE ANY TABLE
TO REPADMIN;
GRANT DELETE ANY TABLE
TO REPADMIN;
GRANT SELECT ANY DICTIONARY
TO REPADMIN;
GRANT SELECT ON SYS.SNAP_REFOP$
TO REPADMIN;
GRANT SELECT ANY TABLE
TO REPADMIN;
GRANT CREATE PUBLIC SYNONYM
TO REPADMIN;
GRANT DELETE ON DBA_REPCATLOG
TO REPADMIN;
GRANT ALTER SYSTEM
TO REPADMIN;
GRANT DELETE ON REG_SNAP$
TO REPADMIN WITH GRANT OPTION;
GRANT DELETE ON SLOG$
TO REPADMIN WITH GRANT OPTION;
GRANT DELETE ON MLOG$
TO REPADMIN WITH GRANT OPTION;
GRANT SELECT ON DBA_REPGROUPED_COLUMN
TO REPADMIN WITH GRANT OPTION;
GRANT SELECT ON SLOG$
TO REPADMIN WITH GRANT OPTION;
GRANT SELECT ON MLOG$
TO REPADMIN WITH GRANT OPTION;
GRANT SELECT ON REG_SNAP$
TO REPADMIN WITH GRANT OPTION;
GRANT SELECT ON DBA_TAB_COLUMNS
TO REPADMIN WITH GRANT OPTION;
GRANT SELECT ON DEFTRAN
TO REPADMIN WITH GRANT OPTION;
GRANT SELECT ON DBA_REPCATLOG
TO REPADMIN WITH GRANT OPTION;
GRANT DELETE ON DBA_REPCATLOG
TO REPADMIN WITH GRANT OPTION;
GRANT SELECT ON DEFCALL
TO REPADMIN WITH GRANT OPTION;
GRANT SELECT ON DEFERROR
TO REPADMIN WITH GRANT OPTION;
GRANT SELECT ON DBA_DB_LINKS
TO REPADMIN WITH GRANT OPTION;
GRANT SELECT ON DBA_REPOBJECT
TO REPADMIN WITH GRANT OPTION;
GRANT SELECT ON DBA_SNAPSHOTS
TO REPADMIN WITH GRANT OPTION;
GRANT SELECT ON DBA_INDEXES
TO REPADMIN WITH GRANT OPTION;
GRANT SELECT ON DBA_SEGMENTS
TO REPADMIN WITH GRANT OPTION;
GRANT SELECT ON DBA_FREE_SPACE
TO REPADMIN WITH GRANT OPTION;
GRANT SELECT ON DBA_TABLES
TO REPADMIN WITH GRANT OPTION;
GRANT SELECT ON DBA_JOBS
TO REPADMIN WITH GRANT OPTION;
GRANT SELECT ON DBA_JOBS_RUNNING
TO REPADMIN WITH GRANT OPTION;
GRANT SELECT ON v_$SESSION
TO REPADMIN WITH GRANT OPTION;
EXEC DBMS_REPCAT_ADMIN.GRANT_ADMIN_REPGROUP(userid=>'SCOTT');
GRANT EXECUTE ON DBMS_DEFER
GRANT EXECUTE ON SYS.DBMS_REPCAT_UTL2
GRANT SELECT ON sys.def$_error
GRANT EXECUTE ANY PROCEDURE
GRANT CREATE SYNONYM
TO scott;
TO scott;
TO scott;
TO scott;
TO scott;
Abb.3: SQL-Statements – Vergabe der Rechte
Implementierung der UMV-Unterstützung auf der MDS
Da es sich bei der UMV um eine symmetrische Replikation handelt, müssen zunächst auf der MDS die
Replikationsobjekte zu der zu replizierenden Tabelle <DEPARTMENT> angelegt werden. Zunächst wird eine
Masterrepgroup angelegt. Diese Mastergroup ist eine Sammlung von zu replizierenden Datenbankobjekten. In der
Regel werden alle Tabellen, welche logisch abhängig sind, in diese Groups gebündelt. Der Groupname ist für 30
Zeichen frei wählbar. Mit dem Parameter drop_contents = TRUE werden die zu dieser Mastergroup gehörenden
Replikationsobjekte gelöscht. Da dies im Normalfall nicht gewünscht ist, ist dieser Parameter mit FALSE (=Default)
zu belegen. Das Erstellen einer Mastergroup erzeugt automatisch einen Datenbankjob für die Verarbeitung der
©2004
administrativen Aufgaben. Bei einer Materialized View Replikation kann dieser im Anschluß nach der erfolgreichen
Implementierung wieder gelöscht werden, wenn ausschließlich Tabellen repliziert werden.
SQL>CONN repadmin/repadmin@db_a.world
BEGIN
DBMS_REPCAT.DROP_MASTER_REPGROUP(
gname
=>’UMV_GROUP1’ ,
drop_contents
=> FALSE
,
all_sites
=>FALSE
);
END;
EXEC DBMS_REPCAT.CREATE_MASTER_REPGROUP(gname =>'UMV_GROUP1');
Abb.4: SQL-Statement – Löschen und Anlegen Mastergroup
Anschließend wird nun die Tabelle <DEPARTMENT> als Replikationsobjekt definiert. Auch hier kann über den
Parameter drop_objects=TRUE entschieden werden, das Objekt (Tabelle DEPARTMENT) zu löschen. Default =
FALSE. Der Parameter use_existing_object definiert ob das Objekt auf der Zieldatenbank angelegt werden soll.
In unserem Fall wird die Tabelle DEPARTMENT auf der Datenbank DB_B.WORLD anderweitig angelegt. Der
Parameter copy_rows gibt an, ob Tabelleninhalte der Tabelle DEPARTMENT bei der Initialisierung der Replikation
von der Datenbank DB_A.WORLD zur Datenbank DB_B.WORLD gesendet werden sollen. Im Parameter gname
wird nun die Tabelle DEPARTMENT in die Mastergroup UMV_GROUP1 eingehangen.
SQL>CONN repadmin/repadmin@db_a.world
BEGIN
DBMS_REPCAT.DROP_MASTER_REPOBJECT(
sname
=>'SCOTT'
oname
=>'"DEPARTEMENT"'
type
=>'TABLE'
drop_objects =>FALSE
END;
BEGIN
DBMS_REPCAT.CREATE_MASTER_REPOBJECT(
sname
=>'SCOTT'
oname
=>'DEPARTMENT'
type
=>'TABLE'
use_existing_object => FALSE ,
ddl_text
=> NULL
comment
=> NULL
retry
=> TRUE
copy_rows
=> TRUE
gname
=>'UMV_GROUP1'
END;
,
,
,
);
,
,
,
,
,
,
,
);
Abb.5: SQL-Statement – Löschen und Anlegen Masterrepobject
Nach der Erstellung der Replikationsobjekte müssen diese für die Replikationsunterstützung generiert werden. Damit
werden für diese alle internen Replikationsobjekte generiert. U.a. sind dann folgende neuen Packages sichtbar.
DEPARTMENT$RP – Package sowie DEPARTMENT$RP – Package Body. Diese (RemoteProcedureCall-Packages)
sind für die Abarbeitung der Transaktionen auf der MDS, welche auf der UMV per DML entstanden und von dort an
die MDS gesendet werden. Wichtig bei dem Aufruf dieser Statements in Abb.5 und Abb.6 ist, das immer der gleiche
NLS_LANG – Parameter auf den Systemen verwendet wird, weil sonst u.U. eine falsche Sortierung innerhalb der
..$RP Packages vorkommen kann. Dann stimmen die gelieferten Werte mit den RPC nicht überein.
SQL>CONN repadmin/repadmin@db_a.world
BEGIN
DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT(
sname
=>'SCOTT’
oname
=>'DEPARTMENT’
type
=>'TABLE'
gen_objs_owner
=>'REPADMIN'
END;
Abb.6: SQL-Statement – Generieren Replikationsunterstützung
©2004
,
,
,
);
Zum Abschluss ist nun die Mastergroup zu aktivieren und deren Status zu überprüfen.
SQL>CONN repadmin/repadmin@db_a.world
EXEC DBMS_REPCAT.RESUME_MASTER_ACTIVITY('UMV_GROUP1');
SELECT gname,status FROM dba_repcat;
GNAME
STATUS
-----------------------------------------------------------------UMV_GROUP1
NORMAL
Abb.7: SQL-Statement – Aktivieren Mastergroup
Implementierung der UMVs
Für die Nutzung einer UMV müssen auch auf den UMV-Datenbanken alle Rechte wie auf der MDS entsprechend
vergeben werden. (siehe Abb.3)
Nach dem Anlegen des Users Repadmin und der Vergabe der Rechte müssen nun für beide User Private Datenbank
Links in beide Richtungen angelegt werden.
•
Private Datenbank Links für die User SCOTT sowie REPADMIN in beide Richtungen
SQL>CONN scott/tiger@dba_a.world
CREATE DATABASE LINK DB_B.WORLD
CONNECT TO scott IDENTIFIED BY tiger
USING ’DB_B.WORLD’;
SQL>CONN scott/tiger@dba_b.world
CREATE DATABASE LINK DB_A.WORLD
CONNECT TO scott IDENTIFIED BY tiger
USING ’DB_A.WORLD’;
CONN repadmin/repadmin@db_a.world
CREATE DATABASE LINK DB_B.WORLD
CONNECT TO repadmin IDENTIFIED BY repadmin
USING ’DB_B.WORLD’;
CONN repadmin/repadmin@db_b.world
CREATE DATABASE LINK DB_A.WORLD
CONNECT TO repadmin IDENTIFIED BY repadmin
USING ’DB_A.WORLD’;
Abb.8: SQL-Statement – Anlegen Datenbank Link
Nun kann mit der Implementierung der UMV begonnen werden. Diese unterscheidet sich zunächst nur in einem
zusätzlichen Kommando gegenüber der RMV. Deshalb sind alle notwendigen Infos aus dem ersten Kapitel der RMV
auch hier zutreffend.
SQL>CONN scott/tiger@db_b.world;
CREATE MATERIALIZED VIEW department
ON PREBUILT TABLE
REFRESH FAST
WITH PRIMARY KEY
FOR UPDATE
AS SELECT * FROM department@db_a.world;
Abb.9: SQL-Statement – Anlegen Updatable Materialized View
Im Anschluss wird nun die Materialized View Group definiert. Diese ist das Gegenstück zur Mastergroup von der
MDS.
SQL>CONN repadmin/repadmin@db_b.world
BEGIN
DBMS_REPCAT.DROP_MVIEW_REPGROUP(
gname
=>'UMV_GROUP1'
drop_contents
=>FALSE)
END;
BEGIN
DBMS_REPCAT.CREATE_MVIEW_REPGROUP(
gname
=>'UMV_GROUP1'
master
=>'DB_A.WORLD'
propagation_mode
=>'ASYNCHRONOUS'
);
END;
Abb.10: SQL-Statement – Löschen und Anlegen Materialized View Group
©2004
,
;
,
,
Nun ist es noch erforderlich auf der Datenbank DB_B.WORLD die UMV als Replikationsobjekt zu erstellen. Der
Parameter min_communication ist auf TRUE zu setzen, um eine Reduzierung der Netzwerklast zu erreichen. In
diesem Fall werden ausschließlich geänderte Werte mit :old und :new sowie der Primary Key an die MDS gesendet.
SQL>CONN repadmin/repadmin@db_b.world
BEGIN
DBMS_REPCAT.CREATE_MVIEW_REPOBJECT(
sname
=>'SCOTT'
oname
=>'DEPARTMENT'
type
=>'MATERIALIZED VIEW'
gname
=>'UMV_GROUP1'
gen_objs_owner
=>'REPADMIN'
min_communication =>TRUE);
END;
,
,
,
,
,
Abb.11: SQL-Statement – Anlegen Materialized View Repobject
Es können verschiedene Tabellen in Refresh – Gruppen „gruppiert“ werden. Ratsam ist es, diese so zu gruppieren, wie
sie bereits in der Masterreplikationsgruppe (UMV_GROUP1) definiert werden. Wie diese Gruppierung vorgenommen
wird, ist im ersten Kapitel bei den RMV beschrieben. Ebenso ist im ersten Kapitel beschrieben, wie eine
Aktualisierung der UMV mit Daten von der MDS vorgenommen wird. Der Sinn einer UMV besteht aber nun darin,
dass auf dieser ebenfalls Änderungen möglich sind. Die UMV verhält sich also jetzt wie eine ganz normale Tabelle.
Alle DML-Transaktionen werden in der Tabelle SYSTEM.DEF$_AQCALL in der Datenbank DB_B.WORLD
gespeichert. Asynchrone Push/Refresh-Mechanismen – in der Regel Datenbankjobs – übernehmen nun die
Propagierung dieser Queue zur MDS (DB_A.WORLD).
Eine Transaktion kann ein Update auf 5000 Datensätze sein. Dann stehen 5000 Einträge in der Queue-Tabelle, denn es
wird jeder geänderte Datensatz als separater Call einer Transaction auf der MDS-nachgefahren. Dieses Transferieren
wird bei Ausführung des Commandos DBMS_REFRESH.REFRESH vorgenommen. Es kann jedoch auch ein zweiter
separater Datenbankjob diese Änderung in kürzeren Abständen zur MDS mit der Funktion
DBMS_DEFER_SYS.PUSH senden. Damit ist es möglich, in sehr kurzen Zeitabständen die DML-Transaktionen auf
einer UMV zur MDS zu propagieren. Wichtig ist im Gegensatz zu einer RMV, dass bei einer UMV in der Phase des
Refreshs ein LOCK Table ausgeführt wird. Wenn also 10 Tabellen in einer Refreshgruppe gruppiert wurden, so
werden bei Aufruf von DBMS_REFRESH.REFRESH alle 10 Tabellen in einen Table Lock versetzt. Dies kann u.U.
die Applikation behindern, wenn dieser Refresh in kurzen Intervallen ausgeführt wird.
Da es sich hierbei um eine asynchrone Replikation handelt, ist es immer möglich, dass Transaktionskonflikte
entstehen. Diese können enstehen, wenn der gleiche Datensatz von einem anderen Datenbanksystem (ebenfalls eine
UMV oder auf der MDS) bearbeitet wurde. Der Konflikt wird bei einer Materialized View Replikation immer auf der
MDS erkannt und muß dort aufgelöst werden. Wie wird dieser Konflikt erkannt? Die UMV propagiert immer die :old
und :new Werte der UMV + den Primary Key. Nun wird auf der MDS der Satz über den PK gesucht. Im Anschluß
wird der gefundene Satz mit allen Attributen mit den gelieferten :old Werten verglichen. Gibt es dabei eine
Abweichung, so kommt es zu einem Transaktionsfehler mit der Meldung ORA-01403 no_data_found. Für diese Fälle
ist vor der Implementierung einer Replikationsumgebung sehr intensiv zu analysieren, ob derartige Fehler auftreten
können, und wenn ja, wie damit umgegangen wird. Oracle liefert hierfür mehrere Konfliktlösungsroutinen gleich mit.
(TIMESTAMP,SITE PRIORITY, ADDITIVE,MINIMUM, MAXIMUM) Damit können automatische
Konfliktlösungen implementiert werden, welche diese Konflikte während des Auftretens sofort lösen. Für den Fall,
dass auf der MDS ein Satz gelöscht wurde gibt es jedoch keine automatische Lösung. Hier ist manuelle PL/SQLProgrammierung in eigenen Konfliktlösungen notwendig. Damit ist auch gesagt, dass DELETE-Transaktionen in
Replikationsumgebungen immer ein Problem darstellen. Deshalb sollten physikalische DELETE in logische DELETE
(Löschkennzeichen) umgewandelt werden.
Um die Replikation auf der Datenbank DB_C.WORLD aufzusetzen, ist genauso zu verfahren wie mit der Datenbank
DB_B.WORLD
©2004
Tipps und Tricks
Was wird nicht unterstützt für Replikation? geclusterte Tabellen, LONG+LONG RAW Datentyp, Sequences,
Replikation zwischen verschiedenen Schemas, es gibt keine Konfliktlösung für BLOB/CLOB – Datentypen
Was sind Connection-Qualifier ? Dies sind zusätzliche Namen bei der Definition des Datenbanklinks.
(z.B.DB_B.WORLD@UMV_GROUP1) So können über diese nun unter einem einzigen User mehrfache
Datenbanklinks zu einer Zieldatenbank genutzt werden, um ein paralleles Abarbeiten der Queue zu erreichen.
Wie ist das mit dem GLOBAL_NAME ? Der GLOBAL_NAME muß dem Namen des Datenbanklinks entsprechen
und muß in der TNS_NAMES.ORA gefunden werden. Zusätzlich ist der INIT*.ORA Parameter global_names=TRUE
zu setzen. Dies ist Voraussetzung für die Replikation.
Was passiert bei dem Refresh einer UMV ? Zunächst werden alle lokalen Änderungen aus der Queue
(SYSTEM.DEF$_AQCALL) an die MDS propagiert. Dort werden diese Änderungen auf der Tabelle nachgefahren.
Diese Änderung wiederum wird im Materialized View Log protokolliert, denn eine andere UMV soll diese Änderung
vielleicht ebenfalls bekommen. Im Anschluß wird auf der UMV diese eigene Transaktion nochmals ausgeführt. Der
Originalsatz wird zunächst gelöscht und danach wieder eingefügt. Dadurch entsteht bei den Materialized Views
höhere Last.
Wie ist das mit Triggern ? Bei allen Triggern auf replizierenden Tabellen sind Änderungen notwendig, weil der
Trigger erkennen muß, woher diese Transaktion kommt.
Dafür gibt es folgende Möglichkeit : IF NOT DBMS_REPUTIL.FROM_REMOTE THEN .... hier werden also keine
Replikationstransaktionen den Trigger aktivieren.
... und ForeignKeyConstraints ? Im Gegensatz zu früheren Oracle Releases können diese Constraints nun ab Oracle
Release 8.1 verwendet werden, sind jedoch als INITIALLY DEFERRED DEFERABLE zu deklarieren. Damit werden
die Constraints in der Refresh-Session für alle beteiligten Tabellen deaktiviert und anschließend wieder aktiviert.
Wie ist es mit Fragmentierungen ? UMV’s können ebenso wie die RMV’s horizontal und vertikal von der
Basistabelle aufgebaut werden. D.h. bestimmte Sätze und/oder nur bestimmte Columns können die Materialized View
bilden.
Wie ist es mit Export/Import ? Es ist dringend zu empfehlen folgende Tabellen aus dem Schema SYS zu exportieren
: SLOG$,MLOG$,REG_SNAP$, da diese bei einem Full-Datenbankexport nicht mit exportiert werden.
Benötigt die Materialized View Primary Key’s ? Jede Materialized View basiert auf einem Primary Key, dies ist
also unbedingt notwendig.
Kontakt:
[email protected]
©2004