Transaktionsverwaltung mit Undo-Tablespaces - EDV

77
7
Transaktionsverwaltung
mit Undo-Tablespaces
In Kapitel 6 befassten wir uns kurz mit der Verwaltung des Speicherplatzes in einem
Undo-Tablespace und untersuchten Views wie V$UNDOSTAT, die den DBA beim
Überwachen und Dimensionieren des Undo-Tablespace unterstützen. In diesem
Kapitel untersuchen wir die Konfigurierung und das Managen des Undo-Tablespace, und wie man widersprüchlichen Anforderungen gerecht wird; beispielsweise
muss eine ausreichende Anzahl von Undos für die Lesekonsistenz zur Verfügung
stehen, und gleichzeitig ist dafür zu sorgen, dass DML-Anweisungen keinesfalls
scheitern, nur weil der Wert für den Retention-Parameter zu hoch ist.
Zur Einführung beschäftigen wir uns kurz mit den Transaktionen aus Benutzersicht.
Diese Ausführungen sollen Ihnen zeigen, wie man durch korrekt dimensionierte
Undo-Tablespaces die Benutzertransaktionen unterstützt. Danach geben wir eine
Übersicht über das Anlegen eines Undo-Tablespace, entweder beim Einrichten der
Datenbank oder zu einem späteren Zeitpunkt mit dem bekannten create tablespaceBefehl. Undo-Segmente erfüllen für Datenbankbenutzer zahlreiche Anforderungen,
die wir anschließend etwas genauer unter die Lupe nehmen.
Oracle verfügt über verschiedene Möglichkeiten zur Überwachung und korrekten Dimensionierung von Undo-Tablespaces. Mit dem dbms_advisor-Package lässt sich die
Nutzung des Undo-Tablespace überwachen (siehe Kapitel 6). Dieses Package werden
wir etwas genauer untersuchen und zeigen, wie diese Analysen von der Oracle Enterprise Manager Database Control unterstützt werden.
Der letzte große Abschnitt beschäftigt sich mit den verschiedenen Flashback-Optionen, die auf adäquat dimensionierten Undo-Tablespaces aufsetzen, und dafür sorgen, dass man aus verschiedenen Fehlersituationen zurücksetzen kann. Hier stellen
wir alle wichtigen Flashback-Optionen auf Abfrage-, Tabellen- oder Transaktionsebene vor: Flashback Database wird in Kapitel 15 besprochen.
272
7
Transaktionsverwaltung mit Undo-Tablespaces
Die Rollback-Segmente aus älteren Oracle-Versionen ließen sich nur schwer verwalten
und wurden von den meisten DBAs entweder zu groß oder zu klein dimensioniert.
Oracle empfiehlt deshalb bei allen neuen Datenbanken den Einsatz des automatischen
Undo Managements, und dass nach einem Upgrade unbedingt auf diese Option konvertiert werden sollte. Das manuelle Undo-Management ist nicht Gegenstand dieses
Kapitels, allerdings gehen wir kurz darauf ein, wie man von Rollback-Segmenten auf
automatische Undos migriert.
7.1
Transaktionen – Grundlagen
Eine Transaktion ist eine Sammlung von SQL DML-Anweisungen, die als logische
Einheit behandelt werden; das Scheitern einer Anweisung in der Transaktion impliziert, dass keine der übrigen Änderungen an der Datenbank permanent festgeschrieben wird. Falls die DML-Anweisungen in der Transaktion erfolgreich ausgeführt wurden, schreiben die Applikationen oder SQL*Plus-Benutzer die Änderungen mit einem
commit fest. Beim Überweisen einer Summe zwischen zwei Konten ist die Transaktion
nur dann erfolgreich, wenn sowohl die Soll-Seite des einen Kontos als auch die HabenSeite des anderen Kontos aktualisiert wurde. Scheitert eine oder beide Transaktionen,
wird die gesamte Transaktion ungültig. Falls nur eine der Transaktionen erfolgreich
ist, und die Applikation oder ein SQL*Plus-Benutzer gibt ein commit aus, hätte die
Bank ein paar sehr unzufriedene Kunden!
Eine Transaktion wird implizit initiiert. Nachdem das commit einer vorherigen
Transaktion abgeschlossen ist und zumindest eine Zeile einer Tabelle eingefügt, aktualisiert oder gelöscht hat, wird implizit eine neue Transaktion angelegt. Auch alle
DML-Befehle wie create table und alter index schreiben eine aktive Transaktion fest
und beginnen eine neue. Eine Transaktion lässt sich mit dem set transaction … name
'transaction_name'-Befehl benennen; obwohl die Applikation davon keinen direkten
Nutzen hat, wird der zugewiesene Name in der dynamischen Performance View
V$TRANSACTION protokolliert und erlaubt dem DBA das Überwachen lang laufender Transaktionen. Der set transaction-Befehl muss, falls eingesetzt, der erste Befehl
innerhalb der Transaktion sein.
Innerhalb einer gegebenen Transaktion kann man einen Savepoint definieren. Ein Savepoint ermöglicht das Partitionieren der DML-Befehlssequenzen innerhalb der
Transaktion. Damit kann man DML-Befehle nach dem Savepoint mit einem Rollback
zurücksetzen, nachfolgend weitere DML-Befehle eingeben oder DML-Befehle festschreiben, die vor dem Savepoint ausgeführt wurden. Savepoints legt man mit dem savepoint savepoint_name-Befehl an. Um die DML-Befehle seit dem letzten Savepoint
zurückzusetzen, benutzen Sie den Befehl rollback to savepoint savepoint_name.
7.2
Undos – Grundlagen
273
Eine Transaktion wird implizit festgeschrieben, sobald sich ein Benutzer normal von
Oracle abmeldet; sollte der Benutzerprozess scheitern, wird die letzte Transaktion mit
einem Rollback zurückgesetzt.
7.2
Undos – Grundlagen
Undo-Tablespaces erleichtern das Rollback von logischen Transaktionen. Zudem unterstützen sie zahlreiche andere Funktionalitäten, wie die Lesekonsistenz, verschiedene Recovery-Operationen und Flashback-Funktionen.
7.2.1
Rollback
Wie oben bereits ausgeführt, kann es notwendig sein, innerhalb einer Transaktion einen beliebigen DML-Befehl zurückzusetzen – wobei es keine Rolle spielt, ob die
Transaktion einen oder hunderte DML-Befehle umfasst. Führt ein DML-Befehl eine
Änderung auf eine Tabelle aus, wird der alte Wert der geänderten Daten im Undo-Tablespace in einem systemverwalteten Undo-Segment oder einem Rollback-Segment
protokolliert.
Wird die gesamte Transaktion zurückgesetzt (d.h. eine Transaktion ohne Savepoints),
macht Oracle alle seit Transaktionsbeginn durch DML-Befehle vorgenommenen Änderungen ungeschehen. Dazu nutzt Oracle die dazugehörigen Undo-Datensätze, gibt
die Sperren auf betroffene Zeilen frei (sofern vorhanden), und die Transaktion endet.
Werden Teile einer Transaktion auf einen Savepoint zurückgesetzt, macht Oracle alle
Änderungen durch DML-Befehle rückgängig, die seit dem Savepoint ausgeführt wurden. Alle nachfolgenden Savepoints sind verloren, alle Sperren nach dem Savepoint
werden freigegeben, und die Transaktion bleibt aktiv.
7.2.2
Lesekonsistenz
Undos bieten Benutzern die Lesekonsistenz für Zeilen, die gerade in die DML-Transaktion eines anderen Benutzers eingebunden sind. Anders gesagt, sehen alle anderen
Benutzer in den betroffenen Zeilen keine Änderungen, bis der DML-Benutzer ein
commit eingibt, und sie sich die Zeilen erneut ansehen. Mit Undo-Segmenten lassen
sich die Datenblöcke auf eine lesekonsistente Version zurücksetzen, und bieten damit
allen Benutzern die ursprünglichen Werte der Zeile, die vor dem Transaktionsende
eine select-Anweisung eingeben.
Nehmen wir beispielsweise an, der Benutzer CLOLSEN beginnt um 10:00 Uhr eine
Transaktion, die schätzungsweise um 10:15 Uhr mit dem Festschreiben der Daten endet. Die Transaktion umfasst verschiedene Aktualisierungen und Einfügungen in die
274
7
Transaktionsverwaltung mit Undo-Tablespaces
EMPLOYEES-Tabelle. Bei jedem insert, update und delete auf die EMPLOYEES-Tabelle werden die alten Werte im Undo-Tablespace gespeichert. Führt der Benutzer
SUSANP um 10:08 Uhr eine select-Anweisung auf die EMPLOYEES-Tabelle aus, kann
nur CLOLSEN die gemachten Änderungen sehen; der Undo-Tablespace bietet SUSANP und allen anderen Benutzern die ursprünglichen Werte an. Selbst wenn die Abfrage von SUSANP erst um 10:20 Uhr endet, scheint die Tabelle weiterhin unverändert
zu sein. Die Änderungen werden erst sichtbar, wenn man nach dem Festschreiben der
Daten eine neue Abfrage startet. Bis CLOLSEN um 10:15 Uhr seine Daten festschreibt,
werden die Daten unverändert mit dem Stand von 10:00 Uhr angezeigt.
Falls nicht genügend Undo-Platz vorhanden ist, um die vorherigen Werte von geänderten Zeilen aufzunehmen, erhält der Benutzer, der eine select-Anweisung ausgibt,
einen „ORA-01555: Snapshot Too Old“-Fehler. Im weiteren Verlauf des Kapitels zeigen wir, wie man dieses Problem löst.
7.2.3
Datenbank-Recovery
Die Undo-Tablespaces sind auch eine Schlüsselkomponente des Instanz-Recoverys.
Die Online Redo Logs setzen sowohl festgeschriebene als auch nicht festgeschriebene
Transaktionen auf den Zeitpunkt zurück, zu dem die Instanz scheiterte; mit Hilfe der
Undo-Daten werden alle Transaktionen zurückgesetzt, die zum Zeitpunkt des Zusammenbruchs noch nicht festgeschrieben waren.
7.2.4
Flashback-Operationen
Die Daten im Undo-Tablespace unterstützen verschiedene Typen von Flashback-Optionen: Flashback Table, Flashback Query und das DBMS_FLASHBACK-Package.
Flashback Table stellt eine Tabelle für einen Zeitpunkt in der Vergangenheit her, mit
Flashback Query sehen Sie eine Tabelle für eine SCN oder einen Zeitpunkt in der Vergangenheit, und DBMS_FLASHBACK bietet eine Programmschnittstelle für Flashback-Operationen. Sämtliche Flashback-Optionen stellen wir am Ende des Kapitels
vor.
7.3
Undo-Tablespaces verwalten
7.3
275
Undo-Tablespaces verwalten
Undo-Tablespaces müssen nur ein Mal parametrisiert werden, es handelt sich um eine
der „Set it and forget it“-Operationen. Innerhalb des Undo-Tablespace legt Oracle automatisch Undo-Segmente an, dimensioniert sie korrekt und verwaltet sie selbstständig – ganz im Gegensatz zu früheren Versionen, bei denen der DBA die Rollback-Segmente anlegen, überwachen und verwalten musste.
In den folgenden Abschnitten stellen wir Ihnen die Prozesse vor, mit denen man
Undo-Tablespaces anlegt und verwaltet, inklusive der relevanten Initialisierungsparameter. Weiterhin zeigen wir einige Szenarien, in denen wir mehrere Undo-Tablespaces anlegen und demonstrieren, wie man zwischen Undo-Tablespaces umschaltet.
7.3.1
Undo-Tablespaces anlegen
Zum Anlegen von Undo-Tablespaces gibt es zwei Möglichkeiten: beim Einrichten der
Datenbank oder zu einem späteren Zeitpunkt mit dem create tablespace-Befehl. Genau wie die anderen Tablespaces in Oracle 10g, kann auch der Undo-Tablespace ein
Bigfile-Tablespace sein, was die Verwaltung zusätzlich vereinfacht.
Anlegen eines Undo-Tablespace mit CREATE DATABASE
Eine Datenbank kann mehrere Undo-Tablespaces besitzen, allerdings kann zu einem
gegebenen Zeitpunkt nur einer aktiv sein. Nachfolgend sehen Sie den Code zum Anlegen des Undo-Tablespace beim Einrichten der Datenbank:
create database ord
user sys identified by ds88dkw2
user system identified by md78s233
sysaux datafile '/u02/oradata/ord/sysaux001.dbf' size 100m
default temporary tablespace temp01
tempfile '/u03/oradata/ord/temp001.dbf' size 25m
undo tablespace undotbs01
datafile '/u01/oradata/ord/undo001.dbf' size 50m;
Lässt sich der Tablespace im create database-Befehl nicht anlegen, scheitert die gesamte Operation. Der Fehler muss behoben werden und alle Dateien aus der Operation
sind zu löschen, dann kann der Befehl erneut eingegeben werden.
Die undo tablespace-Klausel im create database-Befehl ist optional, aber sollte das
Automatic Undo Management aktiviert sein und die Klausel wird ausgelassen, wird
trotzdem ein Undo-Tablespace mit einer selbst erweiternden Datendatei angelegt.
Die Anfangsgröße der Datei beträgt 10MB und der Standardname ist SYS_UNDOTBS.
276
7
Transaktionsverwaltung mit Undo-Tablespaces
Anlegen eines Undo-Tablespace mit CREATE TABLESPACE
Nach jedem Anlegen der Datenbank wird ein neuer Undo-Tablespace eingerichtet. Einen Undo-Tablespace legt man wie alle anderen Tablespaces an, fügt jedoch das
Schlüsselwort undo ein:
create undo tablespace undotbs02
datafile '/u01/oracle/rbdb1/undo0201.dbf'
size 25m reuse autoextend on;
Ungeachtet der Volatilität der Datenbank oder der Erwartung, dass der Undo-Bedarf
der Datenbank zukünftig dramatisch zunehmen könnte, geben wir für diesen Tablespace nur eine Größe von 25MB vor und lassen ihn wachsen.
Die Extents in einem Undo-Tablespace müssen systemverwaltet sein; somit können
Sie extent management nur als local autoallocate vorgeben.
Anlegen eines Undo-Tablespace mit EM Database Control
Das Anlegen eines Undo-Tablespace ist mit Hilfe der Enterprise Manager Database
Control relativ einfach. Im Administration-Register wählen Sie Tablespaces. Es wird
eine Liste der bestehenden Tablespaces angezeigt; klicken Sie auf Create. In Abbildung 7-1 legen wir einen neuen Undo-Tablespace namens UNDO_BATCH an. Im
unteren Teil des Bildschirms definieren wir den Namen der Datendatei, die im
Undo-Tablespace einzusetzen ist (siehe Abbildung 7-2).
Abbildung 7-1: Eingeben der allgemeinen Charakteristiken für den Undo-Tablespace.
7.3
Undo-Tablespaces verwalten
277
Abbildung 7-2: Definieren der Datendatei für den Undo-Tablespace.
Nach einem Klick auf Storage können wir die Extent-Reservierung vorgeben, obwohl dies ein Undo-Tablespace automatisch ausführen muss. Unterstützen wir
mehrere Blockgrößen, lässt sich die Blockgröße für den Undo-Tablespace vorgeben.
Abbildung 7-3 zeigt, dass wir die selbstständige Extent-Reservierung und eine
Blockgröße von 8192 vorgeben; die standardmäßige und einzige Blockgröße, die für
die Datenbank definiert wurde.
Im dritten Register können wir die Warnungen und kritischen Prozentsätze eingeben,
ab denen der DBA über potenzielle Platzprobleme im Undo-Tablespace informiert
wird. Wir können die Standard-Grenzwerte verwenden – 85 Prozent für Warnungen
und 97 Prozent als kritischen Wert – oder geben eigene Werte ein. In Abbildung 7-4
akzeptieren wir die Standardwerte.
Wie bei den meisten Verwaltungsbildschirmen in EM Database Control können wir
in einer Vorschau die SQL-Befehle sehen, die nach dem Bestätigen ausgeführt werden.
In Abbildung 7-5 sehen wir die Befehle zum Anlegen des Tablespace und zum Setzen
der dazugehörigen Grenzwerte für die Server-Warnungen.
Nach einem Klick auf OK wird der neue Undo-Tablespace angelegt (siehe Abbildung
7-6).
278
7
Transaktionsverwaltung mit Undo-Tablespaces
Abbildung 7-3: Extent-Zuweisung und Blockgröße.
Abbildung 7-4: Grenzwerte für den Undo-Tablespace.
7.3
Undo-Tablespaces verwalten
Abbildung 7-5: Die SQL-Vorschau.
Abbildung 7-6: Die Zusammenfassung.
279
280
7
Transaktionsverwaltung mit Undo-Tablespaces
Obwohl der DBA mit der EM Database Control erheblich Zeit sparen kann, deckt er
nicht alle möglichen Szenarien ab und schützt den DBA auch nicht davor, dass er den
Undo-Tablespace mit falschen Parametern anlegt. In Abbildung 7-3 könnten wir beispielsweise eine einheitliche Extent-Reservierung vorgeben. Versuchen wir allerdings,
den Tablespace anzulegen, scheitert die Operation (siehe Abbildung 7-7). Denn wie
bereits weiter oben ausgeführt, benötigen Undo-Tablespaces automatisch reservierte
Extents.
Abbildung 7-7: Illegale Parameter für Undo-Tablespaces.
Undo-Tablespaces löschen
Das Löschen eines Undo-Tablespace gleicht dem Löschen eines beliebigen Tablespace.
Der Undo-Tablespace darf allerdings nicht der aktive Undo-Tablespace sein, und er
darf auch keine Daten für nicht festgeschriebene Transaktionen enthalten. Falls Sie einen Undo-Tablespace mit aktiven Undo-Informationen löschen, kann das zum Scheitern von Abfragen führen. Um den oben angelegten Undo-Tablespace zu löschen, geben wir den drop tablespace-Befehl ein:
SQL> drop tablespace undo_batch;
Tablespace dropped.
SQL>
7.3
Undo-Tablespaces verwalten
281
Die including contents-Klausel ist für das Löschen von Undo-Tablespaces gedacht.
Um jedoch nach dem Entfernen des Undo-Tablespace die dazugehörigen Systemdatendateien zu löschen, ist including contents and datafiles anzugeben. Das Löschen
des aktiven Undo-Tablespace ist nicht erlaubt:
SQL> drop tablespace undotbs1;
drop tablespace undotbs1
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use
SQL>
Vor dem Löschen des aktiven Undo-Tablespace ist auf einen anderen Undo-Tablespace umzuschalten. Weitere Informationen zum Umschalten von Undo-Tablespaces
erhalten Sie im weiteren Verlauf des Kapitels.
Undo-Tablespaces ändern
Folgende Operationen sind auf Undo-Tablespaces erlaubt:
■ Hinzufügen einer Datendatei zu Undo-Tablespaces
■ Umbenennen einer Datendatei in einem Undo-Tablespace
■ Die Datendatei eines Undo-Tablespace online oder offline setzen
■ Starten oder Beenden eines offenen Tablespace-Backups
(alter tablespace undotbs begin backup)
■ Aktivieren oder Deaktivieren der Undo-Vorhaltegarantie
Alles andere wird automatisch von Oracle verwaltet.
OMF für Undo-Tablespaces
Zusätzlich zum Einsatz eines Bigfile-Tablespace für Undo-Tablespaces lässt sich ein
Undo-Tablespace auch mit OMF benennen und platzieren; der Initialisierungsparameter DB_CREATE_FILE_DEST enthält den Standort, an dem ein Undo-Tablespace
angelegt wird, falls im create undo tablespace-Befehl die datafile-Klausel nicht angegeben sein sollte. Im folgenden Beispiel legen wir mit OMF einen Undo-Tablespace an:
SQL> show parameter db_create_file_dest
NAME
TYPE
VALUE
------------------------------- ----------- ------------------------db_create_file_dest
string
/u09/oradata/ord
SQL> create undo tablespace undo_batch;
Tablespace created.
282
7
Transaktionsverwaltung mit Undo-Tablespaces
SQL> !ls -l /u09/oradata/ord/ORD/datafile
total 102512
-rw-r----1 oracle
oinstall 104865792 Apr 11 21:54
o1_mf_undo_bat_07n16plc_.dbf
SQL>
Da wir keine Größe angeben, ist der Tablespace standardmäßig 100MB groß. Zudem
ist die Datendatei ohne Größenbeschränkung selbsterweiternd, die Grenze setzt lediglich das Dateisystem.
7.3.2
Dynamische Performance Views für UndoTablespaces
Verschiedene dynamische Performance Views und Data Dictionary Views enthalten
Informationen über Undo-Tablespaces, Benutzertransaktionen und Undo-Segmente.
Tabelle 7-1 zeigt die View-Namen mit einer Beschreibung.
Die Views in Tabelle 7-1 werden im weiteren Verlauf des Kapitels noch ausführlicher
beschrieben.
Tabelle 7-1: Views für Undo-Tablespaces.
View
Beschreibung
DBA_TABLESPACES
Tablespace-Namen und -Charakteristiken, inklusive der
CONTENTS-Spalte, die PERMANENT, TEMPORARY oder
UNDO sein kann; die Undo RETENTION-Spalte ist NOT APPLY,
GUARANTEE oder NOGUARANTEE.
DBA_UNDO_EXTENTS
Alle Undo-Segmente in der Datenbank, mit Größe, Extents, dem
Tablespace, in dem sie liegen, und dem aktuellen Status (EXPIRED
oder UNEXPIRED).
V$UNDOSTAT
Der Umfang der Undo-Nutzung für die Datenbank in 10-MinutenIntervallen; enthält mindestens 1008 Zeilen (7Tage).
V$ROLLSTAT
Rollback-Segmentstatistiken, inklusive Größe und Status.
V$TRANSACTION
Enthält für jede aktive Transaktion der Instanz eine oder mehrere
Zeilen.
7.3.3
Initialisierungsparameter für Undo-Tablespaces
In den folgenden Abschnitten beschreiben wir die Initialisierungsparameter zur Definition des Undo-Tablespace für die Datenbank. Zudem steuern sie, wie lange Oracle
die Undo-Informationen in der Datenbank vorhält.
7.3
Undo-Tablespaces verwalten
283
UNDO_MANAGEMENT
Der Parameter UNDO_MANAGEMENT ist standardmäßig MANUAL. Das Setzen
des UNDO_MANAGEMENT-Parameters auf AUTO überführt die Datenbank in den
automatischen Undo Management-Modus. Damit dieser Parameter gültig ist, muss
zumindest ein Undo-Tablespace vorhanden sein, ungeachtet dessen, ob UNDO_TABLESPACE angegeben ist oder nicht. UNDO_MANAGEMENT ist kein dynamischer
Parameter; deshalb ist bei jeder Änderung von UNDO_MANAGEMENT die Datenbank herunterzufahren und neu zu starten.
UNDO_TABLESPACE
Der UNDO_TABLESPACE-Parameter definiert, welche Undo-Tablespaces für das
automatische Undo-Management eingesetzt werden. Ist UNDO_MANAGEMENT
entweder gar nicht oder auf MANUAL gesetzt, und UNDO_TABLESPACE angegeben, startet die Instanz nicht.
Hinweis:
Mit UNDO_TABLESPACE wird in RAC (Real Application
Clusters)-Umgebungen der Instanz ein bestimmter UndoTablespace zugewiesen, wobei die Gesamtzahl der UndoTablespaces in der Datenbank gleich oder mehr als die
Anzahl der Instanzen im Cluster ist.
Falls umgekehrt UNDO_MANAGEMENT auf AUTO gesetzt und kein Undo-Tablespace in der Datenbank vorhanden ist, startet die Instanz, aber das SYSTEM-Rollback-Segment wird für alle Undo-Operationen genutzt, und eine Meldung wird in
das Alert Log geschrieben. Jede Benutzer-DML, die Änderungen in Nicht-SYSTEMTablespaces ausführen möchte, erhält zusätzlich die Fehlermeldung „ORA-01552:
cannot use system rollback segment for non-system tablespace 'USERS'“, und die Anweisung scheitert.
UNDO_RETENTION
UNDO_RETENTION definiert den minimalen Zeitraum, den Undo-Informationen
für Abfragen vorgehalten werden. Im automatischen Undo-Modus ist UNDO_RETENTION standardmäßig 900 Sekunden. Dieser Wert ist nur gültig, wenn im UndoTablespace genügend Platz für die Unterstützung von lesekonsistenten Abfragen ist;
falls aktive Transaktionen zusätzlichen Undo-Platz benötigen, können nicht abgelaufene Undos zur Unterstützung der aktiven Transaktionen herangezogen werden und
zu „ORA-01555: Snapshot Too Old“-Fehlern führen.
Die TUNED_UNDORETENTION-Spalte der dynamischen Performance View
V$UNDOSTAT zeigt für jede Zeitperiode die getunete Undo-Vorhaltezeit; der Status
284
7
Transaktionsverwaltung mit Undo-Tablespaces
für die Nutzung im Undo-Tablespace wird in V$UNDOSTAT alle zehn Minuten aktualisiert:
SQL> show parameter undo_retention
NAME
TYPE
VALUE
------------------------------------ ----------- --------undo_retention
integer
43200
SQL> select to_char (begin_time, 'yyyy-mm-dd hh24:mi'),
2 undoblks, txncount, tuned_undoretention
3 from v$undostat where rownum = 1;
TO_CHAR(BEGIN_TI
UNDOBLKS
TXNCOUNT TUNED_UNDORETENTION
------------------ ---------- -------- ------------------2004-04-11 22:59
206
253
43200
1 row selected.
SQL>
Weil die Transaktionsbelastung ist der letzten Periode nur gering war, und die Instanz
erst vor kurzem gestartet wurde, ist der getunete Undo Retention-Wert der gleiche wie
das definierte Minimum im Initialisierungsparameter UNDO_RETENTION: 43200
Sekunden (12 Stunden).
Tipp:
UNDO_RETENTION ist nur für Vorhalteangaben bei
Flashbacks und LOBs anzugeben; zum Verwalten des
Transaktions-Rollbacks wird der UNDO_RETENTIONParameter nicht benötigt.
7.3.4
Mehrere Undo-Tablespaces
Wie bereits weiter oben erwähnt, kann eine Datenbank mehrere Undo-Tablespaces
enthalten. Allerdings kann für eine Instanz immer nur ein Undo-Tablespace aktiv
sein. In diesem Abschnitt zeigen wir Ihnen anhand eines Beispiels, wie man bei geöffneter Datenbank auf einen anderen Undo-Tablespace umschaltet.
Hinweis:
In einer Real Application Clusters (RAC)-Umgebung benötigt
jede Instanz im Cluster einen Undo-Tablespace.
In unserer ord-Datenbank gibt es zwei Undo-Tablespaces:
SQL> select tablespace_name, status from dba_tablespaces
2
where contents = 'UNDO';
7.3
285
Undo-Tablespaces verwalten
TABLESPACE_NAME
--------------------------UNDOTBS1
UNDO_BATCH
STATUS
---------ONLINE
ONLINE
2 rows selected.
Aber nur einer der Undo-Tablespaces ist aktiv:
SQL> show parameter undo_tablespace
NAME
TYPE
VALUE
-------------------------- ----------- --------undo_tablespace
string
UNDOTBS1
Für die Verarbeitung in der Nacht ändern wir den Undo-Tablespace von UNDOTBS1
in den UNDO_BATCH-Tablespace, der zur Unterstützung umfangreicherer DMLAktivitäten viel größer ist. Die Platte mit dem tagaktiven Undo-Tablespace ist viel
schneller, bietet aber dafür weniger Speicherplatz; das Laufwerk mit dem nachtaktiven
Undo-Tablespace ist größer, aber langsamer. Daher nutzen wir den kleineren UndoTablespace für das OLTP während des Tages, und den größeren Undo-Tablespace für
das Laden von Data Marts und Data Warehouses und für Aggregationen, weil nachts
die Antwortzeiten keine Rolle spielen.
Hinweis:
Im Gegensatz zu den hier beschriebenen Umständen werden
Sie wahrscheinlich nur selten zwischen Undo-Tablespaces
umschalten. Oracle empfiehlt, für jede Instanz nur einen
Undo-Tablespace anzulegen, der alle Transaktionsbelastungen
unterstützt; mit anderen Worten, „set it and forget it“.
Genau zu dem Zeitpunkt, wenn zwischen den Undo-Tablespaces umgeschaltet wird,
führt der Benutzer SCOTT Verwaltungsoperationen auf die HR.EMPLOYEES-Tabelle
aus, und hat im aktuellen Undo-Tablespace eine aktive Transaktion:
SQL> connect scott/tiger@ord;
Connected.
SQL> set transaction name 'Employee Maintenance';
Transaction set.
SQL> update hr.employees set commission_pct = commission_pct * 1.1;
107 rows updated.
SQL>
Eine Abfrage auf V$TRANSACTION zeigt SCOTTs nicht festgeschriebene Transaktion:
286
7
Transaktionsverwaltung mit Undo-Tablespaces
SQL> select t.status, t.start_time, t.name
2
from v$transaction t join v$session s on t.ses_addr =
3
where s.username = 'SCOTT';
s.saddr
STATUS
START_TIME
NAME
-------------- -------------------- ----------------------ACTIVE
04/12/04 21:56:53
Employee Maintenance
1 row selected.
Sie ändern den Undo-Tablespace wie folgt:
SQL> alter system set undo_tablespace=undo_batch;
System altered.
SCOTTs Transaktion ist weiterhin aktiv, und deshalb enthält der alte Undo-Tablespace weiterhin die Undo-Informationen für SCOTTs Transaktion. Damit steht das
Undo-Segment mit dem folgenden Status solange zur Verfügung, bis die Transaktion
festgeschrieben oder mit einem Rollback zurückgesetzt wird:
SQL> select r.status
2
from v$rollstat r join v$transaction t on r.usn=t.xidusn
3
join v$session s on t.ses_addr = s.saddr
4
where s.username = 'SCOTT';
STATUS
--------------PENDING OFFLINE
1 row selected.
Obwohl UNDO_BATCH der aktuelle Undo-Tablespace ist, lässt sich der UNDOTBS1Tablespace erst dann offline setzen oder löschen, wenn SCOTTs Transaktion festgeschrieben oder mit einem Rollback zurückgesetzt wurde:
SQL> show parameter undo_tablespace
NAME
TYPE
VALUE
-------------------------- ----------- ---------undo_tablespace
string
UNDO_BATCH
SQL> alter tablespace undotbs1 offline;
alter tablespace undotbs1 offline
*
ERROR at line 1:
ORA-30042: Cannot offline the undo tablespace
Die Fehlermeldung ORA-30042 tritt auf, falls Sie versuchen, einen aktiven Undo-Tablespace offline zu setzen – entweder handelt es sich um den aktuellen Undo-Tablespace oder er hat noch offene Transaktionen. Schalten wir auf den tagaktiven Table-
7.3
Undo-Tablespaces verwalten
287
space um, bevor SCOTT seine ursprüngliche Transaktion festgeschrieben oder mit
einem Rollback zurückgesetzt hat, ändert sich der Status von SCOTTs Rollback-Segment
wieder auf ONLINE:
SQL> alter system set undo_tablespace=undotbs1;
System altered.
SQL> select r.status
2
from v$rollstat r join v$transaction t on r.usn=t.xidusn
3
join v$session s on t.ses_addr = s.saddr
4
where s.username = 'SCOTT';
STATUS
--------------ONLINE
1 row selected.
7.3.5
Den Undo-Tablespace dimensionieren
und überwachen
In einem Undo-Tablespace gibt drei verschiedene Undo-Datentypen: aktiv, abgelaufen oder nicht abgelaufen. Aktive und nicht abgelaufene Undo-Daten werden weiterhin
für die Lesekonsistenz benötigt, selbst nach dem Festschreiben einer Transaktion.
Nachdem alle Abfragen beendet wurden, die aktive Undo-Daten benötigen, und
gleichzeitig das Ende der Vorhaltezeit erreicht ist, werden die aktiven zu abgelaufenen
Undo-Daten. Die abgelaufenen Daten lassen sich für andere Oracle-Funktionen wie
Flashbacks einsetzen, werden aber nicht mehr für die Lesekonsistenz bei lang laufenden Transaktionen benötigt. Ungenutzte Undo-Daten belegen im Undo-Tablespace
unnötig Platz.
Damit bietet die minimale Größe für einen Undo-Tablespace genügend Platz zum
Vorhalten der Before-Image-Versionen aller Daten aus sämtlichen aktiven Transaktionen, die noch nicht festgeschrieben oder mit einem Rollback zurückgesetzt wurden.
Falls der für den Undo-Tablespace reservierte Speicherplatz nicht einmal ausreicht,
um die Änderungen an nicht festgeschriebene Transaktionen für Rollback-Operationen zu unterstützen, erhält der Benutzer die Fehlermeldung „ORA-30036: unable to
extend segment by space_qty in undo tablespace tablespace_name.“ In diesem Fall
muss der DBA den Undo-Tablespace vergrößern. Alternativ kann der Benutzer unter
Einhaltung der Geschäftsregeln größere in kleinere Transaktionen aufteilen.
Manuelle Methoden
Die Größe eines Undo-Tablespaces kann ein DBA auch manuell korrigieren. Wie in
Kapitel 6 gezeigt, sehen wir in der dynamischen Performance View V$UNDOSTAT
die Nutzung der Undo-Segmente in 10-Minuten-Intervallen. Zusätzlich zeigt die
288
7
Transaktionsverwaltung mit Undo-Tablespaces
SSOLDERRCNT-Spalte, wie viele Abfragen mit einem „Snapshot too old“-Fehler
scheiterten:
SQL> select to_char (end_time,'yyyy-mm-dd hh24:mi') end_time,
2>
undoblks, ssolderrcnt from v$undostat;
END_TIME
UNDOBLKS SSOLDERRCNT
------------------ -------- ----------2004-04-13 08:12
2114
0
2004-04-13 08:09
4569
0
2004-04-13 07:59
7403
0
2004-04-13 07:49
2341
0
2004-04-13 07:39
8338
0
2004-04-13 07:29
1483
0
2004-04-13 07:19
1548
0
2004-04-13 07:09
61950
2
2004-04-13 06:59
4433
0
2004-04-13 06:49
5658
0
2004-04-13 06:39
757
0
Zwischen 6:59 Uhr und 7:09 Uhr haben wir eine Spitzenbelastung der Undos, was zu
gescheiterten Transaktionen führte. Die folgende Kalkulation ist eine gute Faustregel:
undo_tablespace_size
= UV * UPS + Overhead
In dieser Formel ist UR die Undo-Vorhaltezeit in Sekunden (vom Initialisierungsparameter UNDO_RETENTION), UPS sind die genutzten Undo-Blöcke pro Sekunde,
und Overhead sind die Undo-Metadaten – im Vergleich zur Gesamtgröße relativ wenige. Unsere ord-Datenbank hat eine Blockgröße von 8K und UNDO_RETENTION
ist 43200 (12 Stunden). Wenn wir pro Sekunde 500 Blöcke generieren, die stets 12
Stunden vorzuhalten sind, muss der gesamte Undo-Platz wie folgt sein:
undo_tablespace_size = 43200 * 500 * 8192 = 176947200000 = 177GB
Fügt man zu diesem Wert nochmals 10 bis 20 Prozent hinzu, ist man für alle denkbaren Situationen gerüstet. Alternativ können Sie die Datendateien im Tablespace als
selbsterweiternd definieren. Obgleich diese Kalkulation ein guter Ausgangspunkt ist,
können die Oracle-Ratgeber mit Hilfe von Trendanalysen eine besseres Gesamtbild
zeichnen.
Undo Advisor
Der Undo Advisor von Oracle 10g automatisiert viele Jobs zum Feintuning des Speicherplatzes für Undo-Tablespaces. In Kapitel 6 stellten wir zwei Beispiele mit dem
Undo Advisor vor: über die Schnittstelle von EM Database Control und den PL/SQL
DBMS_ADVISOR-Packages im Automatic Workload Repository (AWR).
Die Seite für den Undo Advisor sehen Sie in Abbildung 7-8.
7.3
Undo-Tablespaces verwalten
289
Abbildung 7-8: EM Database Control: der Undo Advisor.
Obwohl wir UNDO_RETENTION auf 43200 (720 Minuten) setzen, ist es im Undo
Advisor anscheinend so, dass die auto-getunete Undo-Vorhalteperiode zur Unterstützung einer lesekonsistenten Ansicht von geänderten Tabellen für die aktuelle
Undo-Segmentnutzung nicht höher oder niedriger als 720 sein muss.
Beim Anlegen unseres Undo-Tablespace (siehe Abbildung 7-4) setzen wir einige
Grenzwerte. Anhand dieser automatischen Warnungen können wir den Undo-Tablespace in den Fällen proaktiv neu dimensionieren, falls einer der Schwellenwerte überschritten wird – und zwar bevor irgendwelche DMLs oder Abfragen scheitern.
Steuern der Undo-Nutzung
Seit Oracle 9i unterstützt uns der Database Resource Manager beim Verwalten der
Speicherplatzbelegung, entweder nach Benutzer oder nach Benutzergruppen in einer Ressourcen-Verbrauchergruppe, mit Hilfe der UNDO_POOL-Direktive. Jede
Verbrauchergruppe kann einen eigenen Undo-Pool besitzen; überschreitet die Gesamtzahl der generierten Undos nach Gruppen den zugewiesenen Wert, wird die aktuelle Transaktion, die Undos generiert, beendet, und die Fehlermeldung „ORA30027: Undo quota violation—failed to get number (bytes).“ ausgegeben. Die Sitzung muss warten, bis der DBA den Undo-Pool vergrößert, oder bis sich Transaktionen von anderen Benutzern in der gleichen Verbrauchergruppe beenden.
290
7
Transaktionsverwaltung mit Undo-Tablespaces
Im folgenden Beispiel ändern wir für die Benutzer in der Verbrauchergruppe LOW_
GROUP den Standardwert von UNDO_POOL von NULL (unbeschränkt) auf 100KB:
begin
dbms_resource_manager.create_pending_area();
dbms_resource_manager.update_plan_directive(
plan => 'system_plan',
group_or_subplan => 'low_group',
new_comment => 'Limit undo space for low priority groups',
new_undo_pool => 10 00);
dbms_resource_manager.validate_pending_area();
dbms_resource_manager.submit_pending_area();
end;
Der Oracle Resource Manager und andere Ressourcendirektiven werden in Kapitel 5
besprochen.
7.3.6
Lesekonsistenz und erfolgreiche DMLs
Bei OLTP-Datenbanken möchten wir üblicherweise, dass die DML-Befehle erfolgreich sind – gegebenenfalls auf Kosten der Lesekonsistenz. In einer DSS-Umgebung
hingegen sollen sich lang laufende Abfragen möglichst ohne „Snapshot too old“-Fehler beenden. Obwohl ein Vergrößern des UNDO_RETENTION-Parameters oder ein
Anpassen des Undo-Tablespace sicherstellen kann, dass für lesekonsistente Abfragen
Undo-Blöcke zur Verfügung stehen, besitzen Undo-Tablespaces eine weitere Charakteristik, die das erfolgreiche Ausführen von Abfragen sicherstellt: die RETENTION
GUARANTEE-Einstellung.
RETENTION GUARANTEE wird auf Tablespace-Ebene gesetzt und lässt sich jederzeit ändern. Das Setzen einer Vorhaltegarantie für einen Undo-Tablespace stellt sicher, dass ein aktives Undo innerhalb des Tabelle auch dann vorgehalten wird, wenn
es so aussieht, als ob für das erfolgreiche Ausführen von DML-Transaktionen nicht
genügend Undo-Platz zur Verfügung steht. Ein Tablespace wird standardmäßig mit
NOGUARANTEE angelegt, außer Sie definieren das Schlüsselwort GARANTEE
gleich beim Anlegen des Tablespace oder zu einem späteren Zeitpunkt mit ALTER
TABLESPACE:
SQL> alter tablespace undotbs1 retention guarantee;
Tablespace altered.
SQL> select tablespace_name, retention
2
from dba_tablespaces
3
where tablespace_name = 'UNDOTBS1';
7.4
Flashback-Optionen
291
TABLESPACE_NAME
RETENTION
------------------------------ ----------UNDOTBS1
GUARANTEE
1 row selected.
Für Nicht-Undo-Tablespaces ist der Wert von RETENTION stets NOT APPLY.
7.4
Flashback-Optionen
In diesem Abschnitt besprechen wir die Flashback-Optionen, die von Undo-Tablespaces unterstützt werden: Flashback Query, Flashback Table, Flashback Version Query und Flashback Transaction Query. Zudem stellen wir das DBMS_FLASHBACK-Package vor.
Flashback Database und Flashback Drop werden in Kapitel 15 besprochen. Flashback
Database nutzt für die Flashback-Funktionalität die Flashback Logs in der Flash Recovery Area, und keine Undos in einem Undo-Tablespace. Flashback Drop platziert Tabellen in einer virtuellen Recycle bin innerhalb des Tablespace. Die Tabellen bleiben
dort solange, bis sie der Benutzer mit flashback table … to before drop abholt, die
Recycle bin leert, oder der Platz für permanente Objekte im Tablespace benötigt
wird.
Weiterhin kann der DBA in Oracle 10g System- und Objektberechtigungen zuweisen,
die es Benutzern erlauben, bestimmte Probleme selbstständig zu lösen. Im folgenden
Beispiel sorgen wir dafür, dass der Benutzer SCOTT Flashback-Operationen auf bestimmte Tabellen ausführen und innerhalb der Datenbank auf transaktionsbezogene
Metadaten zugreifen kann:
SQL> grant insert, update, delete, select on hr.employees to scott;
Grant succeeded.
SQL> grant insert, update, delete, select on hr.departments to scott;
Grant succeeded.
SQL> grant flashback on hr.employees to scott;
Grant succeeded.
SQL> grant flashback on hr.departments to scott;
Grant succeeded.
SQL> grant select any transaction to scott;
Grant succeeded.
7.4.1
Flashback Query
Seit Oracle 9i2 kann man mit der as of-Klausel in einer select-Anweisung mit Hilfe eines gegebenen Zeitstempels oder einer SCN den Status einer Tabelle abholen. Damit
lässt sich beispielsweise herausfinden, welche Zeilen in einer Tabelle seit Mitternacht
292
7
Transaktionsverwaltung mit Undo-Tablespaces
gelöscht wurden. Oder Sie vergleichen den aktuellen Stand von Zeilen in einer Tabelle
mit den gestrigen Inhalten.
Im folgenden Beispiel räumt SCOTT in der HR.EMPLOYEES-Tabelle auf und löscht
zwei Angestellte, die nicht mehr im Unternehmen arbeiten:
SQL> delete from hr.employees
2 where employee_id in (195,196);
2 rows deleted.
SQL> commit;
Commit complete.
SQL>
Normalerweise würde SCOTT diese Zeilen zuerst an die Tabelle HR.EMPLOYEES_
ARCHIVE kopieren, aber das vergaß er diesmal; er muss die Zeilen zwar nicht mehr
in die HR.EMPLOYEES-Tabelle zurückkopieren, aber er braucht sie für die Archivtabelle. Da SCOTT weiß, dass er die Zeilen vor weniger als einer Stunde gelöscht hat, lassen sich die Zeilen mit Flashback Query und einem relativen Zeitstempel zurückholen:
SQL>
2
3
4
5
insert into
select *
as of
where
hr.employees_archive
from hr.employees
timestamp systimestamp - interval '60' minute
hr.employees.employee_id not in
(select employee_id from hr.employees);
2 rows created.
SQL> commit;
Commit complete.
Da EMPLOYEE_ID der Primärschlüssel der Tabelle ist, können wir mit seiner Hilfe
die Angestelltendatensätze zurückholen, die vor einer Stunde vorhanden waren. Beachten Sie auch, dass wir nicht wissen müssen, welche Zeilen gelöscht wurden: Wir
verglichen die Tabelle mit dem Stand vor einer Stunde und fügten die nicht mehr vorhandenen Datensätze in die Archivtabelle ein.
Tipp:
Bei Flashbacks sollten Sie vorzugsweise SCNs und keine
Zeitstempel einsetzen: SCNs sind präzise, während die
Zeitstempelwerte für Flashback-Operationen nur alle
5 Minuten gespeichert werden. Deshalb können bei
zeitstempelbasierten Flashbacks Lücken von 150
Sekunden entstehen.
7.4
293
Flashback-Optionen
Obgleich wir zum Zurückholen der gesamten Tabelle auch Flashback Table einsetzen,
die Tabelle archivieren und die betroffenen löschen könnten, ist es in unserem Fall
einfacher, die gelöschten Zeilen abzuholen und in die Archivtabelle einzufügen.
Ein andere Variante von Flashback Table ist der Einsatz von Create Table As Select
(CTAS) mit einer Flashback Query als Unterabfrage:
SQL> create table hr.employees_deleted as
2
select * from hr.employees
3
as of timestamp systimestamp - interval '60' minute
4
where hr.employees.employee_id not in
5
(select employee_id from hr.employees);
Table created.
SQL> select employee_id, last_name from hr.employees_deleted;
EMPLOYEE_ID
----------195
196
LAST_NAME
--------------------------Jones
Walsh
2 rows selected.
Diese Variante bezeichnet man als Out-of-Place-Restore (mit andern Worten, das Wiederherstellen der Tabelle oder eines Tabellensubsets an einem anderen Standort). Das
hat den Vorteil, dass man die fehlenden Zeilen manipulieren kann, bevor man sie wieder in die Tabelle platziert; beispielsweise kann ein referenzieller Integritäts-Constraint verlangen, dass man die Zeile zuerst in eine Parent-Tabelle einfügt, bevor die
wiederhergestellte Zeile wieder in der Child-Tabelle hinterlegt werden darf.
Ein Nachteil des Out-of-Place-Restore mit CTAS ist, dass weder Constraints noch Indizes wiederhergestellt werden.
7.4.2
DBMS_FLASHBACK
Eine Alternative zu Flashback Query ist das DBMS_FLASHBACK-Package. Einer der
wichtigsten Unterschiede zwischen DBMS_FLASHBACK und Flashback Query ist,
dass DBMS_FLASHBACK auf Sitzungsebene und Flashback Query auf Objektebene operiert.
DBMS_FLASHBACK lässt sich innerhalb einer PL/SQL-Prozedur oder Benutzersitzung aktivieren, und alle nachfolgenden Operationen, inklusive der existierenden Applikationen, lassen sich ausführen, ohne in select-Anweisungen die as of-Klausel anzugeben. Nach dem Aktivieren von DBMS_FLASHBACK für einen bestimmten
Zeitstempel oder eine SCN erscheint die Datenbank so, als ob die Uhr auf den Zeitstempel oder eine SCN zurückgestellt worden sei. Dieser Zustand hält bis zum Deak-
294
7
Transaktionsverwaltung mit Undo-Tablespaces
tivieren von DBMS_FLASHBACK an. Obwohl bei aktiviertem DBMS_FLASHBACK
keine DMLs erlaubt sind, lässt sich vor dem Aktivieren von DBMS_FLASHBACK in
einer PL/SQL-Prozedur einen Cursor öffnen, damit man Daten von einem früheren
Zeitpunkt übernehmen und im aktuellen Zeitpunkt einfügen oder aktualisieren kann.
Tabelle 7-2 enthält die verfügbaren Prozeduren in DBMS_FLASHBACK.
Tabelle 7-2: DBMS_FLASHBACK-Prozeduren.
Prozedur
Beschreibung
DISABLE
Deaktiviert den Flashback-Modus für die
Sitzung.
ENABLE_AT_SYSTEM_CHANGE_NUMBER
Aktiviert den Flashback-Modus unter Angabe
einer SCN für die Sitzung.
ENABLE_AT_TIME
Aktiviert den Flashback-Modus für die
Sitzung, wobei die SCN verwendet wird, die
dem angegebenen Zeitstempel am nächsten
liegt.
GET_SYSTEM_CHANGE_NUMBER
Liefert die aktuelle SCN.
SCN_TO_TIMESTAMP
Konvertiert einen Oracle-TIMESTAMP und
liefert die SCN, die dem TIMESTAMP-Wert
am nächsten liegt.
TIMESTAMP_TO_SCN
Die Prozeduren zum Aktivieren und Deaktivieren des Flashback-Modus sind einfach
zu bedienen. Die Komplexität liegt üblicherweise in der PL/SQL-Prozedur, die beispielsweise zur Unterstützung von DML-Befehlen einen Cursor anlegt.
Im folgenden Beispiel widmen wir uns noch einmal dem Benutzer SCOTT und den
gelöschten Zeilen in der HR.EMPLOYEES-Tabelle, und zeigen, wie man die Tabelle
mit Hilfe des DBMS_FLASHBACK-Package wiederherstellt. In diesem Szenario bindet SCOTT die gelöschten Zeilen wieder in die Tabelle ein:
SQL> delete from hr.employees where employee_id in (195,196);
2 rows deleted.
SQL> commit;
Commit complete.
Nach einer halben Stunde entscheidet SCOTT, dass er die beiden Zeilen mit dem
DBMS_FLASHBACK-Package zurückholt, und aktiviert Flashback für die Sitzung.
SQL> execute dbms_flashback.enable_at_time(
2
to_timestamp(sysdate - interval '45' minute));
PL/SQL procedure successfully completed.
7.4
295
Flashback-Optionen
Danach prüft er, ob die beiden gelöschten Zeilen genauso wie vor einer dreiviertel
Stunde vorliegen:
SQL> select employee_id, last name from hr.employees
2
where employee_id in (195,196);
EMPLOYEE_ID
----------195
196
LAST_NAME
-----------------------Jones
Walsh
SQL>
Um die beiden Zeilen wieder in die HR.EMPLOYEES-Tabelle zurückzubringen,
schreibt SCOTT eine anonyme PL/SQL-Prozedur, in der er einen Cursor zur Aufnahme der gelöschten Zeilen anlegt, Flashback Query deaktiviert, und danach die Zeilen
wieder einfügt:
declare
-- cursor to hold deleted rows before closing
cursor del_emp is
select * from hr.employees where employee_id in (195,196);
del_emp_rec del_emp%rowtype; -- all columns of the employee row
begin
-- open the cursor while still in Flashback mode
open del_emp;
-- turn off Flashback so we can use DML to put the rows
-- back into the HR.EMPLOYEES table
dbms_flashback.disable;
loop
fetch del_emp into del_emp_rec;
exit when del_emp%notfound;
insert into hr.employees values del_emp_rec;
end loop;
commit;
close del_emp;
end; -- anonymous PL/SQL procedure
Beachten Sie, dass SCOTT das Flashback auch innerhalb der Prozedur aktivieren
könnte. Im konkreten Fall aktivierte er es zum Ausführen von Ad-hoc-Abfragen außerhalb der Prozedur. Danach nutzte der die Prozedur zum Anlegen des Cursors, zum
Ausschalten von Flashback und zum Wiedereinfügen der Zeilen.
7.4.3
Flashback Table
Die Flashback Table-Funktion in Oracle 10g stellt nicht nur den Status von Tabellenzeilen für einen Zeitpunkt in der Vergangenheit wieder her, sondern auch die dazugehörigen Indizes, Trigger und Constraints. Und das während die Datenbank online ist,
296
7
Transaktionsverwaltung mit Undo-Tablespaces
was die Verfügbarkeit der Datenbank wesentlich verbessert. Die Tabelle lässt sich für
einen Zeitstempel oder eine SCN zurücksetzen. Flashback Table bewährt sich gegenüber anderen Flashback-Methoden, wenn die Auswirkungen von Benutzerfehlern nur
gering sind und sich auf wenige Tabellen beschränken. Zur Wiederherstellung des Status von mehreren Tabellen ist Flashback Database die bessere Wahl. Flashback Table
lässt sich nicht auf eine Standby-Datenbank oder zum Rekonstruieren von DML-Operationen einsetzen, wie das Hinzufügen oder Entfernen von Spalten.
Um Flashback Table auf Tabellen einzusetzen, ist vor dem Ausführen der FlashbackOperation die Zeilenverschiebung zu aktivieren, die allerdings zu dem Zeitpunkt, wenn
der Benutzerfehler auftritt, nicht aktiv sein muss. Die Zeilenverschiebung benötigt
man auch zur Unterstützung der Segmentverkleinerung: Da die Zeilenverschiebung
allerdings die ROWID auf eine Tabellenzeile ändert, sollten Sie diese Option nicht aktivieren, falls Ihre Applikation davon abhängt, dass die ROWID bis zum Löschen einer
Zeile gleich sein muss. Da keine Ihrer Applikationen die Tabellen via ROWID referenziert, aktivieren Sie für Ihre Tabellen die Zeilenverschiebung:
SQL> alter table hr.employees enable row movement;
Table altered.
SQL> alter table hr.departments enable row movement;
Table altered.
Am nächsten Tag löscht SCOTT auf Grund eines Fehlers beim Ausschneiden/Einfügen aus einem bestehenden Skript versehentlich alle Zeilen in der HR.EMPLOYEESTabelle:
SQL> delete from hr.employees
2 /
107 rows deleted.
SQL> commit
2 ;
Commit complete.
SQL> where employee_id = 195
SP2-0734: unknown command beginning "where empl..." - rest of line
ignored.
Da der Undo-Tablespace groß genug ist und die Vorhalteperiode 12 Stunden beträgt,
kann SCOTT, auch ohne DBA, die gesamte Tabelle schnell wiederherstellen:
SQL> flashback table hr.employees
2
to timestamp systimestamp - interval '15' minute;
Flashback complete.
7.4
Flashback-Optionen
297
SQL> select count(*) from hr.employees;
COUNT(*)
---------107
Sollten Tabellen eine Parent/Child-Beziehung mit Primärschlüssel-Constraints besitzen, und aus beiden Zeilen wurden unbeabsichtigt Zeilen gelöscht, lassen sie sich im
gleichen Flashback-Befehl zurücksetzen:
SQL> flashback table hr.employees, hr.departments
2
to timestamp systimestamp - interval '15' minute;
Flashback complete.
Abbildung 7-9: EM Database Control Backup/Recovery.
SCOTT kann Tabellen auch mit der EM Database Control zurückblenden. In Abbildung 7-9 selektierte er im Maintenance-Register unter der Überschrift Backup/Recovery den Perform Recovery-Link.
Wählt SCOTT als Objekttyp Tables, hat er die Option, vorhandene oder gelöschte Tabellen zurückzusetzen. Im vorliegenden Fall wird er eine bestehende Tabelle zurückblenden (siehe Abbildung 7-10).
Nach einem Klick auf Next kennt er den genauen Zeitpunkt, wann die Tabelle gültig
war, und gibt es in Bildschirm 7-11 ein.
298
7
Transaktionsverwaltung mit Undo-Tablespaces
Abbildung 7-10: Bestehende Tabellen zurückblenden.
Abbildung 7-11: Auswahl von Datum und Zeit für Flashback Table.
7.4
Flashback-Optionen
299
In Abbildung 7-12 entscheidet sich SCOTT für das Flashback einer Tabelle (in diesem
Fall HR.EMPLOYEES). Dieser Bildschirm zeigt den gewählten Bildschirm und die
äquivalente SCN.
Abbildung 7-12: Auswahl von Flashback Table.
EM Database Control identifiziert alle Abhängigkeiten, wie Fremdschlüssel-Constraints, und alarmiert SCOTT in Abbildung 7-13. Falls es nicht gute Gründe zum Aufbrechen von Parent/Child-Beziehungen zwischen Tabellen gibt, lassen Sie die Standardoption Cascade selektiert.
Ein Klick auf Show Dependencies macht genau das: Es zeigt die Abhängigkeiten zwischen den Fremdschlüsseln in der zurückzusetzenden Tabelle und den Parent-Tabellen. Die gesamte Hierarchie von Abhängigkeiten ist in Abbildung 7-14 zu sehen.
In Abbildung 7-15 kann SCOTT nochmals die selektierten Optionen sehen.
Und wie bei den meisten EM Database Control-Anzeigen kann man sich die generierten SQL-Befehle ansehen:
FLASHBACK TABLE HR.EMPLOYEES, HR.JOBS, HR.DEPARTMENTS TO TIMESTAMP
to_timestamp('2004-04-15 06:15:12 PM', 'YYYY-MM-DD HH:MI:SS AM')
Ein Klick auf Submit führt den Befehl aus.
300
7
Transaktionsverwaltung mit Undo-Tablespaces
Abbildung 7-13: Abhängigkeitsoptionen bei Flashback.
Abbildung 7-14: Die Abhängigkeitshierarchie bei Flashback.
7.4
Flashback-Optionen
301
Abbildung 7-15: Flashback Table: Optionsübersicht.
Beachten Sie, dass Sie in SCOTTs Beispiel Ihr Ziel über die Befehlszeile weit schneller
erreicht hätten. Liegen allerdings unbekannte Abhängigkeiten vor, oder sollten Sie die
Syntax nicht kennen, ist der OEM die bessere Option.
7.4.4
Flashback Version Query
Die Flashback Version Query ist eine weitere Flashback-Option, die auf Undo-Daten
aufsetzt. Sie bietet eine feinere Detailebene als eine as of-Abfrage. Während die bisher
vorgestellten Flashback-Methoden für einen bestimmten Zeitpunkt entweder Zeilen
oder ganze Tabellen zurückbringen, liefert Flashback Version Query anhand von zwei
SCNs oder Zeitstempeln die gesamte Historie einer gegebenen Zeile zurück.
In diesem und dem folgenden Beispiel führt der Benutzer SCOTT an den Tabellen
HR.EMPLOYEES und HR.DEPARTMENTS zahlreiche Änderungen aus:
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
-----------------------1673333
SQL> update hr.employees set salary = salary*1.2 where employee_id=195;
1 row updated.
SQL> select dbms_flashback.get_system_change_number from dual;
302
7
Transaktionsverwaltung mit Undo-Tablespaces
GET_SYSTEM_CHANGE_NUMBER
-----------------------1673349
SQL> delete from hr.employees where employee_id = 196;
1 row deleted.
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
-----------------------1673406
SQL> insert into hr.departments values (660,'Security', 100, 1700);
1 row created.
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
-----------------------1673433
SQL> update hr.employees set manager_id = 100 where employee_id = 195;
1 row updated.
SQL> commit;
Commit complete.
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
-----------------------1673455
SQL> update hr.employees set department_id = 660 where employee_id =
195;
1 row updated.
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
-----------------------1673602
SQL> update hr.employees set salary = salary*1.2 where employee_id=195;
1 row updated.
SQL> commit;
Commit complete.
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
-----------------------1674188
SQL>
7.4
303
Flashback-Optionen
Am folgenden Tag ist SCOTT nicht im Büro und die HR-Abteilung möchte wissen,
welche Zeilen und Tabellen geändert wurden. Mit der Flashback Version Query kann
der Benutzer HR nicht nur die Werte einer Spalte für einen bestimmten Zeitpunkt sehen; er sieht die gesamte Änderungshistorie zwischen den angegebenen Zeitstempeln
oder SCNs.
Mit der versions between-Klausel geben Sie einer Flashback Version Query den SCNBereich oder die Zeitstempel zum Analysieren einer Tabelle vor (in unserem Fall die
HR.EMPLOYEES-Tabelle). Setzt man diese Klausel ein, lassen sich über verschiedene
Pseudospalten diverse Informationen ableiten: die SCNs und Zeitstempel für Änderungen, die Transaktions-ID und der ausgeführte Operationstyp. Tabelle 7-3 zeigt die
verfügbaren Pseudospalten für Flashback Version Queries:
Der Benutzer HR führt eine Flashback Version Query aus, und möchte alle Änderungen auf die Schlüsselspalten in HR.EMPLOYEES für die beiden Angestellten mit den
IDs 195 und 196 sehen:
SQL> select versions_startscn startscn, versions_endscn endscn,
2
versions_xid xid, versions_operation oper,
3
employee_id empid, last_name name, manager_id mgrid, salary sal
4 from hr.employees
5 versions between scn 1673333 and 1674188
6 where employee_id in (195,196);
STARTSCN
ENDSCN XID
OPER EMPID NAME
MGRID
SAL
--------- --------- ---------------- ---- ----- -------- ------------ --1674182
0400240098030000 U
195 Jones
100
4032
1673453
1674182 0400160098030000 U
195 Jones
100
3360
1673368
1673453 04000F0098030000 U
195 Jones
123
3360
1673368
195 Jones
123
2800
1673368
04000F0098030000 D
196 Walsh
124
3100
1673368
196 Walsh
124
3100
6 rows selected.
Tabelle 7-3: Pseudospalten für Flashback Version Query.
Pseudospalte
Beschreibung
VERSIONS_START{SCN|TIME}
Die Start-ID oder den Zeitstempel für den Zeitpunkt, als die
Änderung an der Zeile ausgeführt wurde.
VERSION_END{SCN|TIME}
Die End-ID oder den Endezeitstempel, als die Änderung für
die Zeile nicht mehr gültig war. Ist der Wert NULL, ist die
Zeilenversion entweder noch gültig oder die Zeile wurde
gelöscht.
304
7
Transaktionsverwaltung mit Undo-Tablespaces
Tabelle 7-3: Pseudospalten für Flashback Version Query. (Fortsetzung)
Pseudospalte
Beschreibung
VERSIONS_XID
Die Transaktions-ID der Transaktion, die die Zeilenversion
anlegte.
VERSIONS_OPERATION
Die auf die Zeile ausgeführte Operation (I=Insert,
D=Delete, U=Update).
Die Zeilen mit den letzten Änderungen werden zuerst angezeigt. Alternativ könnte HR
die Abfrage nach TIMESTAMP ausführen oder sich die TIMESTAMP-Werte anzeigen
lassen; aber beide Varianten lassen sich zu einem späteren Zeitpunkt in einer Flashback Query oder Flashback Table-Operation einsetzen. In dieser Ausgabe sehen wir,
dass ein Angestellter gelöscht wurde und ein anderer Angestellter gleich zwei Gehälter
erhielt. Zudem können wir festhalten, dass manche Operationen nur ein, andere hingegen zwei DML-Befehle enthielten.
Im folgenden Abschnitt versuchen wir, diese Probleme zu beheben.
7.4.5
Flashback Transaction Query
Nach dem Aufdecken von fehlerhaften oder falschen Änderungen in einer Tabelle lassen sich mit einer Flashback Transaction Query alle anderen Modifikationen ermitteln, die von der Transaktion mit den unpassenden Änderungen ausgeführt wurden.
Danach kann man sämtliche Änderungen als Gruppe rückgängig machen. Damit werden typischerweise die referenzielle Integrität oder die Geschäftsregeln gewahrt.
Eine Flashback Transaction Query referenziert im Unterschied zu einer Flashback
Version Query nicht die Tabelle, die in DML-Transaktionen eingebunden war; stattdessen fragen Sie die Data Dictionary View FLASHBACK_TRANSACTION_QUERY
ab. Die Spalten von FLASHBACK_TRANSACTION_QUERY sind in Tabelle 7-4 zusammengefasst.
Tabelle 7-4: Die Spalten in FLASHBACK_TRANSACTION_QUERY.
Spaltenname
Beschreibung
XID
Nummer der Transaktions-ID
START_SCN
SCN für die erste DML in der Transaktion
START_TIMESTAMP
Zeitstempel für die erste DML in der Transaktion
COMMIT_SCN
SCN, als die Transaktion festgeschrieben wurde
COMMIT_TIMESTAMP
Zeitstempel, als die Transaktion festgeschrieben wurde.
7.4
305
Flashback-Optionen
Tabelle 7-4: Die Spalten in FLASHBACK_TRANSACTION_QUERY. (Fortsetzung)
Spaltenname
Beschreibung
LOGON_USER
Benutzer, dem die Transaktion gehört
UNDO_CHANGE#
Undo-SCN
OPERATION
Ausgeführte DML-Operation: DELETE, INSERT, UPDATE, BEGIN
oder UNKNOWN
TABLE_NAME
Tabelle, die von der DML geändert wurde
TABLE_OWNER
Eigentümer der Tabelle, die von der DML geändert wurde
ROW_ID
ROWID der Zeile, die von der DML geändert wurde
UNDO_SQL
SQL-Anweisung, zum Zurücksetzen der DML-Operation
Um die Änderungen auf die HR.EMPLOYEES-Tabelle eingehender zu untersuchen,
fragen wir die View FLASHBACK_TRANSACTION_QUERY mit der ältesten Transaktion aus der Abfrage im letzten Abschnitt ab:
SQL> select start_scn, commit_scn, logon_user,
2
operation, table_name, undo_sql
3 from flashback_transaction_query
4 where xid = hextoraw('04000F0098030000');
START_SCN COMMIT_SCN LOGON_USER OPERATION
TABLE_NAME
--------- ---------- ----------- ------------ ---------UNDO_SQL
-------------------------------------------------------1673366
1673368 SCOTT
DELETE
EMPLOYEES
insert into "HR"."EMPLOYEES"("EMPLOYEE_ID","FIRST_NAME","LAS
T_NAME","EMAIL","PHONE_NUMBER","HIRE_DATE","JOB_ID","SALARY"
, "COMMISSION_PCT" , "MANAGER_ID" , "DEPARTMENT_ID" ) values ( ' 196
1,'Alana','Walsh','AWALSH','650.507.9811',TO_DATE('24-APR-98
', 'DD-MON-RR'),'SH_CLERK','3100',NULL,'124','50');
1673366
1673368 SCOTT
UPDATE
update "HR"."EMPLOYEES" set "SALARY" =
'AAAMAeAAFAAAABYABc ' ;
1673366
3
1673368 SCOTT
'2800'
EMPLOYEES
where ROWID =
BEGIN
rows selected.
Das Ergebnis bestätigt, was wir erwarteten – die Löschoperation und die Aktualisierung des Gehalts wurden von SCOTT ausgeführt. Die UNDO_SQL-Spalte enthält den
Code, mit dem sich der Effekt der Transaktion rückgängig machen lässt. In unserem
306
7
Transaktionsverwaltung mit Undo-Tablespaces
Beispiel geht es um die erste Transaktion zwischen den gewählten SCNs. Wurden zu
einem früheren Zeitpunkt auf dieselbe Spalte irgendwelche Änderungen ausgeführt,
sollte man vor dem Ausführen dieses SQL-Codes die anderen Aktualisierungen ansehen. Wenn wir uns in der Serie die letzte Transaktion ansehen, erkennen wir, dass es
für die SALARY-Spalte des Angestellten eine neuere Aktualisierung gibt:
SQL> select start_scn, commit_scn, logon_user,
2
operation, table_name, undo_sql
3 from flashback_transaction_query
4 where xid = hextoraw('04000F0098030000');
START_SCN COMMIT_SCN LOGON_USER OPERATION
TABLE_NAME
--------- ---------- ----------- ------------ ---------UNDO_SQL
-------------------------------------------------------1673386
1674182 SCOTT
UPDATE
EMPLOYEES
update "HR"."EMPLOYEES" set "SALARY" = '3360' where ROWID =
'AAAMAeAAFAAAABYABc ' ;
1673386
1674182 SCOTT
INSERT
JOB_HISTORY
delete from "HR"."JOB_HISTORY" where ROWID = 'AAAMAiAAFAAAAB
tAAB ' ;
1673386
1674182 SCOTT
UPDATE
EMPLOYEES
update "HR"."EMPLOYEES" set "DEPARTMENT_ID" = '50' where ROW
ID = 'AAAMAeAAFAAAABYABc';
1673386
4
1674182 SCOTT
BEGIN
rows selected.
Die erste Gehaltsaktualisierung war gültig, während die zweite (aktuellere) dupliziert
wurde, und daher ungültig ist. Innerhalb der gleichen Transaktion änderte SCOTT
auch die DEPARTMENT_ID des Angestellten und führte ein insert auf die HR.JOB_
HISTORY aus. Das insert war nicht das Ergebnis eines impliziten Befehls, sondern
geht auf das Konto eines Triggers auf die HR.JOB_HISTORY-Tabelle, der alle Änderungen von JOB_ID oder DEPARTMENT_ID in HR.EMPLOYEES an HR.JOB_HISTORY protokolliert. Damit ist lediglich die letzte Gehaltänderung rückgängig zu machen:
SQL> update "HR"."EMPLOYEES"
2
set "SALARY" = '3360' where ROWID ='AAAMAeAAFAAAABYABc';
1 row updated.
SQL> commit;
Commit complete.
In der vorletzten Transaktion sieht man das insert in die HR.DEPARTMENTS-Tabelle plus die erneute Zuordnung des Angestellten zur neuen Abteilung:
7.5
307
Migration zum automatischen Undo-Management
SQL> select start_scn, commit_scn, logon_user,
2
operation, table_name, undo_sql
3
from flashback_transaction_query
4
where xid = hextoraw(' 0400160098030000');
START_SCN COMMIT_SCN LOGON_USER OPERATION
TABLE_NAME
--------- ---------- ----------- ------------ ---------UNDO_SQL
-------------------------------------------------------1673449
1673453 SCOTT
UPDATE
EMPLOYEES
update "HR"."EMPLOYEES" set "MANAGER_ID" = '123' where ROWID
= ' AAAMAeAAFAAAABYABc ' ;
1673449
1673453 SCOTT
INSERT
DEPARTMENTS
delete from "HR"."DEPARTMENTS" where ROWID = 'AAAMAZAAFAAAAA
1AAB' ;
1673449
1673453 SCOTT
BEGIN
3 rows selected.
Da wir in der Flashback Version Query auf die DEPARTMENT_ID-Spalte verzichteten, war nicht sofort ersichtlich, dass auf die Spalten in der HR.EMPLOYEES-Tabelle
weitere Änderungen ausgeführt wurden und zusätzlich eine neue Zeile in HR.DEPARTMENTS eingefügt wurde.
7.5
Migration zum automatischen
Undo-Management
Um Ihre Umgebung von manuell verwalteten Rollback-Segmenten auf das automatische Undo Management zu migrieren, müssen Sie eine Sache kennen; die Größe des
Undo-Tablespace, basierend auf der Nutzung der Rollback-Segmente im manuellen
Undo-Modus. Sind alle manuellen Rollback-Segmente online gesetzt, führen Sie die
Prozedur DBMS_UNDO_ADV.RBU_MIGRATION aus: Sie liefert die aktuelle Belegung für die Rollback-Segmente in Megabytes zurück:
SQL> variable undo_size number
SQL> begin
2
:undo_size := dbms_undo_adv.rbu_migration;
3 end;
4 /
PL/SQL procedure successfully completed.
308
7
Transaktionsverwaltung mit Undo-Tablespaces
SQL> print :undo_size
UNDO_SIZE
2840
SQL>
In unserem Beispiel sollte ein Undo-Tablespace, der die Rollback-Segmente ersetzen
soll, mindestens 2840MB oder 2,84GB groß sein. Nur so kann er alle Undo-Anforderungen aufnehmen, die derzeit von Rollback-Segmenten unterstützt werden.