Oracle Advanced Replication – wie funktioniert`s ?

Oracle Advanced Replication –
wie funktioniert’s ?
(Teil I – ReadOnly Materialized Views
oder Snapshot Replikation)
Autor: Stefan Menschel , ORACLE Deutschland GmbH
DOAGNews Q3_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
Einleitung
Mit der Einführung des Release 7.1.6 des Oracle Database Server wurde erstmalig die Advanced Replication Option zur
Verfügung gestellt.
Mittlerweile hat sich diese Option zu einer mächtigen Umgebung entwickelt, welche alle Anforderungen an eine verteilt
replizierte Datenverarbeitung erfüllt. Oracle bietet ab dem Release 9.2 und der Option Oracle Streams neue weitere und andere
Funktionalitäten für derartige Systeme. Die Oracle Advanced Replication Option bietet die Möglichkeit, weltweit lokal verteilte
Datenbanken zu einer globalen Datenbank zu integrieren. Weiterhin können mit dieser Option hochverfügbare Systeme erstellt
und verwaltet werden. Ich möchte mit diesem und den folgenden Artikeln ( Updatable Materialized View, Multimasterreplikation ) allen Anwendern darlegen, was es bei der Implementierung einer komplexen verteilt replizierten Datenbank mit
Hilfe der Advanced Replication Option zu beachten gibt.
Begriffe
Ab dem Release 8.1 hielt der Begriff Materialized View (MV) in der Datenbank Einzug. Dies kam mit den neuen
Funktionalitäten für Data Warehouse Systeme. Oracle ermöglichte ab diesem Release in einer lokalen Datenbank,
voraggregierte Views abzubilden, welche einen schnelleren Zugriff auf Daten eines Warehouses ermöglichen. Mit der
Einführung dieser Funktionalität wurde der bis dahin bekannte Begriff Snapshot aus der Advanced Replication Option ersetzt.
Was ist Advanced Replication ?
Oracle Advanced Replication wird primär genutzt, um Oracle Tabellen oder Subsets von Tabellen auf anderen Datenbank –
Servern lokal nutzbar für Lese- und /oder Schreibzugriffe zur Verfügung zu stellen. Zusätzlich zu den Oracle – Tabellen
können Proceduren, Packages, Trigger und Indizes repliziert werden. Die Replikation von Oracle – Tabellen ist Gegenstand
dieser Artikelreihe. Oracle liefert mit der Advanced Replication Option zwei Möglichkeiten eine verteilt replizierte Datenbank
zu implementieren. Zum einen ist es das Verfahren mit Materialized Views und zum anderen die Multimaster Replikation. Im
ersten Teil dieses Artikels möchte ich die Funktionalität der Replikation über ReadOnly Materialized Views beschreiben.
Allgemeine Grundlagen
Für die Nutzung der Advanced Replication Option sind folgende allgemeine Grundlagen unbedingt notwendig :
•
•
•
•
•
•
•
TCP/IP – Verbindung zwischen den Datenbank Servern
SHARED_POOL_SIZE mindestens 25 Mbyte
JOB_QUEUE_PROCESSES = 2 (oder mehr)
GLOBAL_NAMES = TRUE
Replikation kann nicht zwischen unterschiedlichen Schemas (außer ReadOnly MV’s) ausgeführt werden
eine Replikation von Tabellen mit LONG oder LONG RAW Datentypen wird nicht unterstützt
das Skript $ORACLE_HOME/rdbms/admin/catrep ist als Schema SYSTEM in die Datenbank zu
installieren (ab Release 9.0 automatisch)
Bei einem replizierten Datenbanksystem ist immer eine Datenbank als Masterdefinitionsite und die redundanten
Datenbanksysteme als Mastersites zu definieren. Diese Entscheidung ist für die Implementierung der Replikationsfunktionalität
erforderlich. In meinem Beispiel ist die Datenbank DB_A.WORLD die Masterdefinitionsite.
Voraussetzung für eine Implementierung einer Replikation ist eine SQL*Net Verbindung zwischen den zu replizierenden
Datenbanksystemen. Die beteiligten Datenbanksysteme werden über ihren globalen Datenbanknamen im Gesamtsystem
unterschieden, d.h. der GLOBAL_NAME der Datenbanken muss Unique sein. Der Instance-Name spielt keine Rolle, d.h. alle
beteiligten Datenbanken können den gleichen Instance-Namen haben.
©2004
Oracle Listener
TNSNAMES.ORA
Datenbank Server A
DB_A.WORLD=
.....
DB_B.WORLD=
...
DB_C.WORLD
...
TCP/IP und SQL*Net
Datenbank Server C
Datenbank Server B
Oracle Listener
TNSNAMES.ORA
Oracle Listener
TNSNAMES.ORA
DB_B.WORLD
DB_C.WORLD=
.....
DB_A.WORLD=
...
=
.....
DB_A.WORLD
=
Abb.1: SQL*Net Konfiguration
Auf dieser funktionierenden SQL*Net Verbindung werden private Datenbank-Links zwischen den Datenbanken für
das zu replizierende Schema (z.B. SCOTT) erstellt. Die Erstellung von Public Database Links wird aus
Sicherheitsgründen nicht empfohlen.
SQL>CREATE DATABASE LINK DB_B.WORLD
CREATE DATABASE LINK DB_A.WORLD
CONNECT TO scott IDENTIFIED BY tiger
CONNECT TO scott IDENTIFIED BY tiger
USING ’DB_B.WORLD’;
USING ’DB_A.WORLD’;
Abb.2: SQL-Statement CREATE DATABASE LINK
Die Verbindung über den Datenbank Link kann nun mit folgendem Statement getestet werden :
SQL>SELECT SYSDATE FROM DUAL@db_a.world;
Abb.3: SQL-Statement Check Database Links
Replikation mit Materialized Views
Oracle bietet die Möglichkeit eine Replikation über Materialized Views (MV) für zwei unterschiedliche Verwendungszwecke zu implementieren. Diese sind die ReadOnly – oder die Updatable MV’s. ReadOnly MV’s werden
ausschließlich für lesenden Zugriff implementiert, während Updatable MV’s durch lokale DML-Operationen
(INSERT, UPDATE , DELETE) veränderbar sind.
Die Aktualisierung der MV’s sollte immer nur die geänderten Datensätze betrachten.
ReadOnly Materialized Views
Masterdefinitionsite
Der Aufbau der ReadOnly MV’s ist simpel zu implementieren. Auf der Masterdefinitionsite sind für jede zu
replizierende Tabelle Materialized View Logs anzulegen. Dabei ist zu beachten, dass hierfür möglichst ein eigenes
local managed Tablespace vorher anzulegen ist.
©2004
SQL>CREATE MATERIALIZED VIEW LOG ON department TABLESPACE snaplog;
Abb.4 SQL-Statement Anlegen Materialized View Log
In der Datenbank DB_A.WORLD wird durch dieses DDL-Statement automatisch eine Tabelle MLOG$_DEPARTMENT sowie ein interner Trigger auf der Tabelle DEPARTMENT angelegt, welcher bei jedem DML-Statement auf
der Tabelle DEPARTMENT einen entsprechenden Eintrag in diesem Log vornimmt.
Dabei wird der Primary Key , das DML-Type (I=INSERT,D=DELETE,U=UPDATE) sowie ein Timestamp in der
Column SNAPTIME$$ eingetragen. Der Timestamp hat immer den Wert „01.01.4000“ bei Eintrag in diese Tabelle.
Sollten mehrere Mastersites diese Daten der Tabelle DEPARTMENT redundant halten, wird empfohlen einen Index
auf den MV Log auf die Columns SNAPTIME$$,DMLTYPE$$ anzulegen, da hiermit ein schnellerer Zugriff auf dem
Materialized View Log während der Aktualisierung erreicht wird.
Mastersite
Die Materialized View wird nun auf der Mastersite implementiert. Hierzu wird der bereits installierte Datenbanklink
für die Verbindung zur Masterdefinitionsite genutzt. Eine Materialized View kann ab dem Release 8.1 in einem neuen
Modus implementiert werden. Dieser Modus gestattet die Erstellung einer Materialized View auf eine existierende
Tabelle, welche vorher durch Export/Import – Verfahren angelegt wurde. Diese Vorgehensweise ist bei sehr großen
MV’s sicher sehr nützlich und bringt auch bei DDL-Befehlen (ALTER TABLE ADD ...) erhebliche Zeiteinsparungen, da durch das DROP MATERIALIZED VIEW Kommando die Materialized View/Tabelle physikalisch
nicht gelöscht wird.
Die ReadOnly Materialized View wird mit folgendem Kommando angelegt.
SQL>CREATE MATERIALIZED VIEW department
ON PREBUILT TABLE
REFRESH FAST
AS SELECT * FROM department@db_a.world;
WITH PRIMARY KEY
Abb.5: SQL-Statement Anlegen Materialized View
Der Schalter <ON PREBUILT TABLE > setzt voraus, dass die Tabelle DEPARTMENT mit einem Primary Key
auf der Datenbank DB_B.WORLD bereits vorhanden ist. Nach Ausführung wird im Dictionary der Datenbank
DB_B.WORLD vermerkt, dass diese Tabelle DEPARTMENT eine Materialized VIEW von der Tabelle
DEPARTEMENT der Datenbank DB_A.WORLD ist. Wenn dieser Schalter nicht genutzt wird, so wird bei
Ausführung dieses Kommandos eine Tabelle DEPARTMENT mit dem entsprechenden Primary Key auf der
Datenbank DB_B.WORLD über den Database Link aufgebaut.
Der Schalter REFRESH FAST definiert den Refresh – Modus dieser Materialized View. Oracle unterstützt hier die
Modis COMPLETE – FAST und FORCE. Der Complete Refresh würde diese MV bei jeder Aktualisierung
zunächst komplett löschen und dann alle Daten von der Datenbank DB_A.WORLD neu laden. In einem produktiven
System ist dies sicher nicht angebracht, und wird ausschließlich für eine komplette Neusynchronisation mit der
Mastertabelle genutzt. Der Schalter FAST definiert, dass die MV ausschließlich alle Änderungen seit der letzten
Aktualisierung von der Mastertabelle nachzieht. Die internen Mechanismen hierzu werden folgend beschrieben. Der
Schalter FORCE definiert, dass die MV zunächst einen FAST – Refresh ausführen soll, sollte dieser wegen Fehler in
den Timestamps nicht ausgeführt werden können, so führt die MV sofort einen COMPLETE – Refresh aus. Wir
empfehlen deshalb alle MV’s mit dem Schalter FAST zu definieren. Materialized Views können fragmentiert
aufgebaut werden, d.h. die SELECT Anweisung kann individuell nur bestimmte Columns beinhalten und/oder durch
eine WHERE – Clause bestimmte Sätze der Mastertabelle abbilden. Wurde eine Tabelle komplett durch
EXPORT/IMPORT auf der Datenbank DB_B.WORLD erstellt, die MV jedoch Fragmentierung beinhalten, so werden
alle Datensätze die nicht der SELECT – Clause entsprechen bei dem ersten Refresh aus der MV gelöscht.
Was geschieht im Dictionary der Datenbanken nach der Erstellung der MV ?
©2004
Auf der Masterdefinitionsite (DB_A.WORLD) gibt es drei wichtige Tabellen für die MV-Replikation. Diese sind im
Schema SYS die Tabellen SLOG$, MLOG$ sowie REG_SNAP$. Diese drei Tabellen steuern den FAST-Refresh jeder
Materialized View. Die Tabelle SLOG$ hat für jede erstellt MV einen Eintrag.
MOWNER MASTER
SNAPID SNAPTIME
--------------------------------------- ------------------------------------------ ---------- -----------------------------------SCOTT
DEPARTMENT
1111
16-JAN-2004 13:03:34
SCOTT
DEPARTMENT
1112
16-JAN-2004 14:05:45
Abb.6: Tabelle SYS.SLOG
In der Tabelle MLOG$ steht für jede erstellte MV eine Zeile mit dem Zeitstempel für den ältesten und den jüngsten
Refresh.
MOWNER MASTER
OLDEST_PK
YOUNGEST
--------------------------------------- -------------------- ---------- ------------------------------------------------------------------SCOTT
DEPARTMENT
16-JAN-2004 13:03:34
16-JAN-2004 14:05:45
Abb.7: Tabelle SYS.MLOG
In der Tabelle REG_SNAP$ steht für eine bessere Identifizierung der MV auf der Masterdefinitionsite die entsprechende
Registrierung des globalen Datenbanknamen, wo die MV angelegt wurde sowie der SQL-Text der Materialized View.
SOWNER
SNAPNAME
SNAPSITE
SNAPSHOT_ID
QUERY_TXT
--------------------------------------- ------------------------------------------------------------------------------- ---------- -------------------------------------SCOTT
DEPARTMENT
DB_B.WORLD
1111
SELECT “DEPARTMENT_ID“,...
SCOTT
DEPARTMENT
DB_C.WORLD
1112
SELECT “DEPARTMENT_ID“,...
Abb.8: Tabelle SYS.REG_SNAP
Auf den Mastersites DB_B.WORLD sowie DB_C.WORLD gibt es die Tabelle SNAP$ im Schema SYS in welcher alle
angelegten Materialized Views der Datenbank registriert werden.
SOWNER MASTER
MLINK
SNAPSHOT
SNAPTIME
SNAPID
--------------------------------------- ------------------------------------------------------------------------------- ---------- ------------------------------------SCOTT
DEPARTMENT DB_A.WORLD
01-JAN-1950 12:00:00
01-JAN-1950 12:00:00
1111
Abb.9: Tabelle SYS.SNAP$
In der Column Flag der Tabelle SYS.SNAP$ steht der Identifier, ob es sich bei dieser MV um eine MV mit der ON
PREBUILT OPTION handelt. Dies kann über folgendes SQL-Statement ausgelesen werden.
SQL>SELECT master, DECODE(BITAND(s.flag,131072), 0, 'NO', 'YES') FROM sys.snap$;
Abb.10: SQL-Query für ON PREBUILT MV
Einfacher geht die Abfrage über die View DBA_SNAPSHOTS zu regeln.
SQL>SELECT owner,master,prebuilt FROM dba_snapshots;
Abb.11: SQL-Query für DBA_SNAPSHOTS
Was geschieht nun bei der Aktualisierung einer Materialized View mit einem vorherigen DML auf der Tabelle
DEPARTMENT ?
Masterdefinitionsite:
©2004
In der Tabelle DEPARTMENT wird ein Satz geändert. Durch den internen Trigger wird dieses DML in der MV-Log
protokolliert.
DEPARTMENT_ID
SNAPTIME$$
DMLTYPE$$
--------------------------------------- ---------------------------------------------4711
01.01.4000 00:00:00
U
Abb.12: Materialized View Log nach UPDATE
Nun wird von der ersten Mastersite eine Aktualisierung der Materialized View aktiviert. Dies kann über einen automatischen
Datenbankjob oder manuell veranlasst werden.
SQL> EXEC DBMS_SNAPHOT.REFRESH(’DEPARTMENT’,’F’);
Abb.13: manuelle Aktualisierung einer MV mittels FAST Refresh
Nach erfolgreicher Ausführung am 20.01.2004 um 8:00 Uhr stehen nun folgende Informationen im Dictionary der
Masterdefinitionsite.
DEPARTMENT_ID
SNAPTIME$$
DMLTYPE$$
--------------------------------------- ---------------------------------------------------4711
20.01.2004 08:00:00
U
Abb.14: Materialized View Log nach Aktualisierung durch die erste Mastersite
MOWNER
MASTER
SNAPID
SNAPTIME
--------------------------------------- ------------------------------------------ ---------- -------------------------------------SCOTT
DEPARTMENT
1111
20-JAN-2004 08:00:00
SCOTT
DEPARTMENT
1112
16-JAN-2004 14:05:45
Abb.15: Tabelle SYS.SLOG
MOWNER
MASTER
OLDEST_PK
YOUNGEST
--------------------------------------- -------------------- ---------- ---------------------------------------------------------------------SCOTT
DEPARTMENT
16-JAN-2004 14:05:45
20-JAN-2004 08:00:00
Abb.16: Tabelle SYS.MLOG
Es ist zu erkennen, dass der Zeitstempel in dem MV-Log auf den Zeitstempel der jetzigen Aktualisierung geändert
wurde. Gleichzeitig wurden die Zeitstempel in den Tabellen SYS.SLOG$ sowie SYS.MLOG$ geändert. Die
Aktualisierung hat jetzt also ausschließlich das eine UPDATE-Statement auf die Tabelle DEPARTMENT
nachgezogen. Im MV-Log steht nun noch der Eintrag für die zweite zu aktualisierende MV.
Nach dem Ausführen dieser Aktualisierung am 20.01.2004 um 09:00 Uhr erhalten wir folgende Informationen aus
dem Dictionary. Der Eintrag aus dem MV-Log wurde gelöscht. In den SYS – Tabellen stehen nun folgende
Informationen.
MOWNER
MASTER
SNAPID
SNAPTIME
--------------------------------------- ------------------------------------------ -------------- ----------------------------------SCOTT
DEPARTMENT
1111
20-JAN-2004 08:00:00
SCOTT
DEPARTMENT
1112
20-JAN-2004 09:00:00
Abb.17: Tabelle SYS.SLOG
©2004
MOWNER
MASTER
OLDEST_PK
YOUNGEST
--------------------------------------- -------------------- ---------- --------------------------------------------------------------------SCOTT
DEPARTMENT
20-JAN-2004 08:00:00
20-JAN-2004 09:00:00
Abb.18: Tabelle SYS.MLOG
Warum wurde der Eintrag aus dem MV-Log gelöscht ? Nach der ersten Aktualisierung wurde der Zeitstempel in dem
MV-Log geändert. Durch die Informationen aus der Tabelle SYS.MLOG$ sowie SYS.SLOG$ hat die Aktualisierung
der zweiten Datenbank erkannt, dass diese die letzte zu replizierende Datenbank des Gesamtsystems ist. Der
OLDEST_PK Eintrag ist älter als der Eintrag im MV-Log, somit musste dieser Satz noch aktualisiert werden, einen
älteren Eintrag gibt es nicht, nach der erfolgreichen Aktualisierung wurde dann der Eintrag aus dem MV-Log gelöscht.
Probleme mit dem MV-Log
Wenn eine MV nicht richtig deregistriert wurde, z.B. Ausführung DROP SNASPHOT ohne Verbindung zur
Masterdefinitionsite oder Crash der Mastersite, so werden alle DML-Transaktionen für diese MV weiter protokolliert.
Dies hat zur Folge, dass die MV-Log – Tabelle unbegrenzt anwächst. Deshalb ist darauf zu achten, dass die
Registrierung im Dictionary immer mit der tatsächlichen Umgebung übereinstimmt.
Eine nachträgliche Deregistrierung kann ausschließlich manuell erfolgen, indem die Tabellen SLOG$, MLOG$ sowie
REG_SNAP$ manuell aktualisiert werden. Der Aufruf des Packages
DBMS_SNAPSHOT.UNREGISTER_SNAPSHOT reicht hierfür nicht aus, da hiermit lediglich die REG_SNAP$
aktualisiert wird.
Gruppieren von MV’s
Sehr oft sind Tabellen über die Applikation voneinander abhängig. In der Oracle Datenbank werden diese Abhängigkeiten über ForeignKey Constraints geregelt. In einer replizierten Datenbank ist es nun ebenso wichtig diese
Abhängigkeiten während des Replikationsvoranges zu berücksichtigen. Dies wird erreicht, indem mehrere abhängige
MV’s in einer Refreshgruppe gebündelt werden. Eine Aktualisierung erfolgt nun nicht mehr über eine einzelne MV,
sondern über alle in dieser Gruppe zusammengehörenden MV’s.
SQL>EXEC DBMS_REFRESH.MAKE(’GROUP1’,’DEPARTMENT’.....);
EXEC DBMS_REFRESH.ADD(’GROUP1’,’PRODUCT’);
Abb.19: Gruppieren von mehreren MV’s
Nach der Gruppierung kann nun eine Aktualisierung aller MV’s über diese Refreshgruppe erfolgen.
SQL>EXEC DBMS_REFRESH.REFRESH(’GROUP1’);
Abb.20: Aktualisierung aller MV’s einer Gruppe
Sollte nun innerhalb der Aktualisierung ein Problem mit einer MV auftreten, so wird die bis dahin
erfolgte Aktualisierung aller MV’s durch die Datenbank mit dem Rollback Mechanismus zurückgefahren. Wenn die Refresh-Modis auf FORCE eingestellt sind, und die Datenbank versucht automatisch ein COMPLETE – Refresh auszuführen, so wird intern zunächst ein DELETE auf die MV
vorgenommen. Dieses Verhalten ist anders als bei dem DBMS_SNAPHOT.REFRESH, da hier ein
TRUNCATE auf die MV ausgeführt wird. Bei großen MV’s kann dies zu sehr langen Refresh –
Laufzeiten führen. Aus diesem Grund ist die Empfehlung zum FAST – Refresh zu verstehen.
Denn wenn eine einzelne MV ein Problem hat, kann diese besser über den DBMS_SNAPSHOT.REFRESH aktualisiert werden.
Mit dieser beschriebenen Vorgehensweise ist es nun möglich eine ReadOnly Materialized View
Replikation problemlos zu implementieren und zu kontrollieren.
©2004
Kontakt:
Stefan Menschel
[email protected]
©2004