Transaktionsverwaltung mit Undo-Tablespaces

777Transaktionsverwaltung

mit Undo-Tablespaces

In Kapitel 6 befassten wir uns kurz mit der Verwaltung des Speicherplatzes in einemUndo-Tablespace und untersuchten Views wie V$UNDOSTAT, die den DBA beimÜberwachen und Dimensionieren des Undo-Tablespace unterstützen. In diesemKapitel untersuchen wir die Konfigurierung und das Managen des Undo-Table-space, und wie man widersprüchlichen Anforderungen gerecht wird; beispielsweisemuss eine ausreichende Anzahl von Undos für die Lesekonsistenz zur Verfügungstehen, und gleichzeitig ist dafür zu sorgen, dass DML-Anweisungen keinesfallsscheitern, 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 dimensionierteUndo-Tablespaces die Benutzertransaktionen unterstützt. Danach geben wir eineÜbersicht über das Anlegen eines Undo-Tablespace, entweder beim Einrichten derDatenbank oder zu einem späteren Zeitpunkt mit dem bekannten create tablespace-Befehl. 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 Di-mensionierung von Undo-Tablespaces. Mit dem dbms_advisor-Package lässt sich dieNutzung des Undo-Tablespace überwachen (siehe Kapitel 6). Dieses Package werdenwir etwas genauer untersuchen und zeigen, wie diese Analysen von der Oracle Enter-prise Manager Database Control unterstützt werden.

Der letzte große Abschnitt beschäftigt sich mit den verschiedenen Flashback-Optio-nen, die auf adäquat dimensionierten Undo-Tablespaces aufsetzen, und dafür sor-gen, dass man aus verschiedenen Fehlersituationen zurücksetzen kann. Hier stellenwir alle wichtigen Flashback-Optionen auf Abfrage-, Tabellen- oder Transaktions-ebene 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 verwaltenund wurden von den meisten DBAs entweder zu groß oder zu klein dimensioniert.Oracle empfiehlt deshalb bei allen neuen Datenbanken den Einsatz des automatischenUndo Managements, und dass nach einem Upgrade unbedingt auf diese Option kon-vertiert werden sollte. Das manuelle Undo-Management ist nicht Gegenstand diesesKapitels, allerdings gehen wir kurz darauf ein, wie man von Rollback-Segmenten aufautomatische Undos migriert.

7.1 Transaktionen – GrundlagenEine Transaktion ist eine Sammlung von SQL DML-Anweisungen, die als logischeEinheit behandelt werden; das Scheitern einer Anweisung in der Transaktion impli-ziert, dass keine der übrigen Änderungen an der Datenbank permanent festgeschrie-ben wird. Falls die DML-Anweisungen in der Transaktion erfolgreich ausgeführt wur-den, schreiben die Applikationen oder SQL*Plus-Benutzer die Änderungen mit einemcommit fest. Beim Überweisen einer Summe zwischen zwei Konten ist die Transaktionnur dann erfolgreich, wenn sowohl die Soll-Seite des einen Kontos als auch die Haben-Seite des anderen Kontos aktualisiert wurde. Scheitert eine oder beide Transaktionen,wird die gesamte Transaktion ungültig. Falls nur eine der Transaktionen erfolgreichist, und die Applikation oder ein SQL*Plus-Benutzer gibt ein commit aus, hätte dieBank ein paar sehr unzufriedene Kunden!

Eine Transaktion wird implizit initiiert. Nachdem das commit einer vorherigenTransaktion abgeschlossen ist und zumindest eine Zeile einer Tabelle eingefügt, aktu-alisiert oder gelöscht hat, wird implizit eine neue Transaktion angelegt. Auch alleDML-Befehle wie create table und alter index schreiben eine aktive Transaktion festund beginnen eine neue. Eine Transaktion lässt sich mit dem set transaction … name'transaction_name'-Befehl benennen; obwohl die Applikation davon keinen direktenNutzen hat, wird der zugewiesene Name in der dynamischen Performance ViewV$TRANSACTION protokolliert und erlaubt dem DBA das Überwachen lang laufen-der Transaktionen. Der set transaction-Befehl muss, falls eingesetzt, der erste Befehlinnerhalb der Transaktion sein.

Innerhalb einer gegebenen Transaktion kann man einen Savepoint definieren. Ein Sa-vepoint ermöglicht das Partitionieren der DML-Befehlssequenzen innerhalb derTransaktion. Damit kann man DML-Befehle nach dem Savepoint mit einem Rollbackzurücksetzen, nachfolgend weitere DML-Befehle eingeben oder DML-Befehle fest-schreiben, die vor dem Savepoint ausgeführt wurden. Savepoints legt man mit dem sa-vepoint savepoint_name-Befehl an. Um die DML-Befehle seit dem letzten Savepointzurü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 vonOracle abmeldet; sollte der Benutzerprozess scheitern, wird die letzte Transaktion miteinem Rollback zurückgesetzt.

7.2 Undos – GrundlagenUndo-Tablespaces erleichtern das Rollback von logischen Transaktionen. Zudem un-terstützen sie zahlreiche andere Funktionalitäten, wie die Lesekonsistenz, verschiede-ne Recovery-Operationen und Flashback-Funktionen.

7.2.1 Rollback

Wie oben bereits ausgeführt, kann es notwendig sein, innerhalb einer Transaktion ei-nen beliebigen DML-Befehl zurückzusetzen – wobei es keine Rolle spielt, ob dieTransaktion 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-Ta-blespace in einem systemverwalteten Undo-Segment oder einem Rollback-Segmentprotokolliert.

Wird die gesamte Transaktion zurückgesetzt (d.h. eine Transaktion ohne Savepoints),macht Oracle alle seit Transaktionsbeginn durch DML-Befehle vorgenommenen Än-derungen ungeschehen. Dazu nutzt Oracle die dazugehörigen Undo-Datensätze, gibtdie 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 wur-den. Alle nachfolgenden Savepoints sind verloren, alle Sperren nach dem Savepointwerden freigegeben, und die Transaktion bleibt aktiv.

7.2.2 Lesekonsistenz

Undos bieten Benutzern die Lesekonsistenz für Zeilen, die gerade in die DML-Trans-aktion eines anderen Benutzers eingebunden sind. Anders gesagt, sehen alle anderenBenutzer in den betroffenen Zeilen keine Änderungen, bis der DML-Benutzer eincommit eingibt, und sie sich die Zeilen erneut ansehen. Mit Undo-Segmenten lassensich die Datenblöcke auf eine lesekonsistente Version zurücksetzen, und bieten damitallen Benutzern die ursprünglichen Werte der Zeile, die vor dem Transaktionsendeeine select-Anweisung eingeben.

Nehmen wir beispielsweise an, der Benutzer CLOLSEN beginnt um 10:00 Uhr eineTransaktion, die schätzungsweise um 10:15 Uhr mit dem Festschreiben der Daten en-det. Die Transaktion umfasst verschiedene Aktualisierungen und Einfügungen in die


EMPLOYEES-Tabelle. Bei jedem insert, update und delete auf die EMPLOYEES-Ta-belle werden die alten Werte im Undo-Tablespace gespeichert. Führt der BenutzerSUSANP um 10:08 Uhr eine select-Anweisung auf die EMPLOYEES-Tabelle aus, kannnur CLOLSEN die gemachten Änderungen sehen; der Undo-Tablespace bietet SUS-ANP und allen anderen Benutzern die ursprünglichen Werte an. Selbst wenn die Ab-frage von SUSANP erst um 10:20 Uhr endet, scheint die Tabelle weiterhin unverändertzu sein. Die Änderungen werden erst sichtbar, wenn man nach dem Festschreiben derDaten 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än-derten Zeilen aufzunehmen, erhält der Benutzer, der eine select-Anweisung ausgibt,einen „ORA-01555: Snapshot Too Old“-Fehler. Im weiteren Verlauf des Kapitels zei-gen 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 festgeschriebeneTransaktionen auf den Zeitpunkt zurück, zu dem die Instanz scheiterte; mit Hilfe derUndo-Daten werden alle Transaktionen zurückgesetzt, die zum Zeitpunkt des Zusam-menbruchs noch nicht festgeschrieben waren.

7.2.4 Flashback-Operationen

Die Daten im Undo-Tablespace unterstützen verschiedene Typen von Flashback-Op-tionen: Flashback Table, Flashback Query und das DBMS_FLASHBACK-Package.Flashback Table stellt eine Tabelle für einen Zeitpunkt in der Vergangenheit her, mitFlashback Query sehen Sie eine Tabelle für eine SCN oder einen Zeitpunkt in der Ver-gangenheit, und DBMS_FLASHBACK bietet eine Programmschnittstelle für Flash-back-Operationen. Sämtliche Flashback-Optionen stellen wir am Ende des Kapitelsvor.

7.3 Undo-Tablespaces verwalten 275

7.3 Undo-Tablespaces verwaltenUndo-Tablespaces müssen nur ein Mal parametrisiert werden, es handelt sich um eineder „Set it and forget it“-Operationen. Innerhalb des Undo-Tablespace legt Oracle au-tomatisch Undo-Segmente an, dimensioniert sie korrekt und verwaltet sie selbststän-dig – ganz im Gegensatz zu früheren Versionen, bei denen der DBA die Rollback-Seg-mente anlegen, überwachen und verwalten musste.

In den folgenden Abschnitten stellen wir Ihnen die Prozesse vor, mit denen manUndo-Tablespaces anlegt und verwaltet, inklusive der relevanten Initialisierungspara-meter. Weiterhin zeigen wir einige Szenarien, in denen wir mehrere Undo-Table-spaces 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 derDatenbank oder zu einem späteren Zeitpunkt mit dem create tablespace-Befehl. Ge-nau wie die anderen Tablespaces in Oracle 10g, kann auch der Undo-Tablespace einBigfile-Tablespace sein, was die Verwaltung zusätzlich vereinfacht.

Anlegen eines Undo-Tablespace mit CREATE DATABASEEine Datenbank kann mehrere Undo-Tablespaces besitzen, allerdings kann zu einemgegebenen Zeitpunkt nur einer aktiv sein. Nachfolgend sehen Sie den Code zum An-legen 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 gesam-te Operation. Der Fehler muss behoben werden und alle Dateien aus der Operationsind zu löschen, dann kann der Befehl erneut eingegeben werden.

Die undo tablespace-Klausel im create database-Befehl ist optional, aber sollte dasAutomatic Undo Management aktiviert sein und die Klausel wird ausgelassen, wirdtrotzdem ein Undo-Tablespace mit einer selbst erweiternden Datendatei angelegt.Die Anfangsgröße der Datei beträgt 10MB und der Standardname ist SYS_UN-DOTBS.


Anlegen eines Undo-Tablespace mit CREATE TABLESPACENach jedem Anlegen der Datenbank wird ein neuer Undo-Tablespace eingerichtet. Ei-nen Undo-Tablespace legt man wie alle anderen Tablespaces an, fügt jedoch dasSchlü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-Bedarfder Datenbank zukünftig dramatisch zunehmen könnte, geben wir für diesen Table-space nur eine Größe von 25MB vor und lassen ihn wachsen.

Die Extents in einem Undo-Tablespace müssen systemverwaltet sein; somit könnenSie extent management nur als local autoallocate vorgeben.

Anlegen eines Undo-Tablespace mit EM Database ControlDas Anlegen eines Undo-Tablespace ist mit Hilfe der Enterprise Manager DatabaseControl relativ einfach. Im Administration-Register wählen Sie Tablespaces. Es wirdeine Liste der bestehenden Tablespaces angezeigt; klicken Sie auf Create. In Abbil-dung 7-1 legen wir einen neuen Undo-Tablespace namens UNDO_BATCH an. Imunteren Teil des Bildschirms definieren wir den Namen der Datendatei, die imUndo-Tablespace einzusetzen ist (siehe Abbildung 7-2).

Abbildung 7-1: Eingeben der allgemeinen Charakteristiken für den Undo-Tablespace.


Abbildung 7-2: Definieren der Datendatei für den Undo-Tablespace.

Nach einem Klick auf Storage können wir die Extent-Reservierung vorgeben, ob-wohl dies ein Undo-Tablespace automatisch ausführen muss. Unterstützen wirmehrere 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 eineBlockgröße von 8192 vorgeben; die standardmäßige und einzige Blockgröße, die fürdie 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 informiertwird. Wir können die Standard-Grenzwerte verwenden – 85 Prozent für Warnungenund 97 Prozent als kritischen Wert – oder geben eigene Werte ein. In Abbildung 7-4akzeptieren wir die Standardwerte.

Wie bei den meisten Verwaltungsbildschirmen in EM Database Control können wirin 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 Setzender dazugehörigen Grenzwerte für die Server-Warnungen.

Nach einem Klick auf OK wird der neue Undo-Tablespace angelegt (siehe Abbildung7-6).


Abbildung 7-3: Extent-Zuweisung und Blockgröße.

Abbildung 7-4: Grenzwerte für den Undo-Tablespace.


Abbildung 7-5: Die SQL-Vorschau.

Abbildung 7-6: Die Zusammenfassung.


Obwohl der DBA mit der EM Database Control erheblich Zeit sparen kann, deckt ernicht alle möglichen Szenarien ab und schützt den DBA auch nicht davor, dass er denUndo-Tablespace mit falschen Parametern anlegt. In Abbildung 7-3 könnten wir bei-spielsweise eine einheitliche Extent-Reservierung vorgeben. Versuchen wir allerdings,den Tablespace anzulegen, scheitert die Operation (siehe Abbildung 7-7). Denn wiebereits weiter oben ausgeführt, benötigen Undo-Tablespaces automatisch reservierteExtents.

Abbildung 7-7: Illegale Parameter für Undo-Tablespaces.

Undo-Tablespaces löschenDas Löschen eines Undo-Tablespace gleicht dem Löschen eines beliebigen Tablespace.Der Undo-Tablespace darf allerdings nicht der aktive Undo-Tablespace sein, und erdarf auch keine Daten für nicht festgeschriebene Transaktionen enthalten. Falls Sie ei-nen Undo-Tablespace mit aktiven Undo-Informationen löschen, kann das zum Schei-tern von Abfragen führen. Um den oben angelegten Undo-Tablespace zu löschen, ge-ben wir den drop tablespace-Befehl ein:

SQL> drop tablespace undo_batch;Tablespace dropped. SQL>


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 Systemda-tendateien zu löschen, ist including contents and datafiles anzugeben. Das Löschendes 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 useSQL>

Vor dem Löschen des aktiven Undo-Tablespace ist auf einen anderen Undo-Table-space umzuschalten. Weitere Informationen zum Umschalten von Undo-Tablespaceserhalten Sie im weiteren Verlauf des Kapitels.

Undo-Tablespaces ändernFolgende 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-TablespacesZusätzlich zum Einsatz eines Bigfile-Tablespace für Undo-Tablespaces lässt sich einUndo-Tablespace auch mit OMF benennen und platzieren; der Initialisierungspara-meter DB_CREATE_FILE_DEST enthält den Standort, an dem ein Undo-Tablespaceangelegt wird, falls im create undo tablespace-Befehl die datafile-Klausel nicht angege-ben 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.


SQL> !ls -l /u09/oradata/ord/ORD/datafiletotal 102512-rw-r----- 1 oracle oinstall 104865792 Apr 11 21:54 o1_mf_undo_bat_07n16plc_.dbfSQL>

Da wir keine Größe angeben, ist der Tablespace standardmäßig 100MB groß. Zudemist die Datendatei ohne Größenbeschränkung selbsterweiternd, die Grenze setzt ledig-lich das Dateisystem.

7.3.2 Dynamische Performance Views für Undo-Tablespaces

Verschiedene dynamische Performance Views und Data Dictionary Views enthaltenInformationen ü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ührlicherbeschrieben.

7.3.3 Initialisierungsparameter für Undo-Tablespaces

In den folgenden Abschnitten beschreiben wir die Initialisierungsparameter zur Defi-nition des Undo-Tablespace für die Datenbank. Zudem steuern sie, wie lange Oracledie Undo-Informationen in der Datenbank vorhält.

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-Minuten-Intervallen; 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.


UNDO_MANAGEMENTDer Parameter UNDO_MANAGEMENT ist standardmäßig MANUAL. Das Setzendes UNDO_MANAGEMENT-Parameters auf AUTO überführt die Datenbank in denautomatischen Undo Management-Modus. Damit dieser Parameter gültig ist, musszumindest ein Undo-Tablespace vorhanden sein, ungeachtet dessen, ob UNDO_TA-BLESPACE angegeben ist oder nicht. UNDO_MANAGEMENT ist kein dynamischerParameter; deshalb ist bei jeder Änderung von UNDO_MANAGEMENT die Daten-bank herunterzufahren und neu zu starten.

UNDO_TABLESPACEDer UNDO_TABLESPACE-Parameter definiert, welche Undo-Tablespaces für dasautomatische Undo-Management eingesetzt werden. Ist UNDO_MANAGEMENTentweder gar nicht oder auf MANUAL gesetzt, und UNDO_TABLESPACE angege-ben, startet die Instanz nicht.

Hinweis:Mit UNDO_TABLESPACE wird in RAC (Real Application Clusters)-Umgebungen der Instanz ein bestimmter Undo-Tablespace zugewiesen, wobei die Gesamtzahl der Undo-Tablespaces in der Datenbank gleich oder mehr als die Anzahl der Instanzen im Cluster ist.

Falls umgekehrt UNDO_MANAGEMENT auf AUTO gesetzt und kein Undo-Table-space in der Datenbank vorhanden ist, startet die Instanz, aber das SYSTEM-Roll-back-Segment wird für alle Undo-Operationen genutzt, und eine Meldung wird indas Alert Log geschrieben. Jede Benutzer-DML, die Änderungen in Nicht-SYSTEM-Tablespaces ausführen möchte, erhält zusätzlich die Fehlermeldung „ORA-01552:cannot use system rollback segment for non-system tablespace 'USERS'“, und die An-weisung scheitert.

UNDO_RETENTIONUNDO_RETENTION definiert den minimalen Zeitraum, den Undo-Informationenfür Abfragen vorgehalten werden. Im automatischen Undo-Modus ist UNDO_RE-TENTION standardmäßig 900 Sekunden. Dieser Wert ist nur gültig, wenn im Undo-Tablespace genügend Platz für die Unterstützung von lesekonsistenten Abfragen ist;falls aktive Transaktionen zusätzlichen Undo-Platz benötigen, können nicht abgelau-fene Undos zur Unterstützung der aktiven Transaktionen herangezogen werden undzu „ORA-01555: Snapshot Too Old“-Fehlern führen.

Die TUNED_UNDORETENTION-Spalte der dynamischen Performance ViewV$UNDOSTAT zeigt für jede Zeitperiode die getunete Undo-Vorhaltezeit; der Status


für die Nutzung im Undo-Tablespace wird in V$UNDOSTAT alle zehn Minuten ak-tualisiert:

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 432001 row selected.SQL>

Weil die Transaktionsbelastung ist der letzten Periode nur gering war, und die Instanzerst vor kurzem gestartet wurde, ist der getunete Undo Retention-Wert der gleiche wiedas definierte Minimum im Initialisierungsparameter UNDO_RETENTION: 43200Sekunden (12 Stunden).

Tipp:UNDO_RETENTION ist nur für Vorhalteangaben bei Flashbacks und LOBs anzugeben; zum Verwalten des Transaktions-Rollbacks wird der UNDO_RETENTION-Parameter nicht benötigt.

7.3.4 Mehrere Undo-Tablespaces

Wie bereits weiter oben erwähnt, kann eine Datenbank mehrere Undo-Tablespacesenthalten. Allerdings kann für eine Instanz immer nur ein Undo-Tablespace aktivsein. In diesem Abschnitt zeigen wir Ihnen anhand eines Beispiels, wie man bei geöff-neter 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';


TABLESPACE_NAME STATUS--------------------------- ----------UNDOTBS1 ONLINEUNDO_BATCH ONLINE

2 rows selected.

Aber nur einer der Undo-Tablespaces ist aktiv:

SQL> show parameter undo_tablespaceNAME TYPE VALUE-------------------------- ----------- ---------undo_tablespace string UNDOTBS1

Für die Verarbeitung in der Nacht ändern wir den Undo-Tablespace von UNDOTBS1in den UNDO_BATCH-Tablespace, der zur Unterstützung umfangreicherer DML-Aktivitäten viel größer ist. Die Platte mit dem tagaktiven Undo-Tablespace ist vielschneller, bietet aber dafür weniger Speicherplatz; das Laufwerk mit dem nachtaktivenUndo-Tablespace ist größer, aber langsamer. Daher nutzen wir den kleineren Undo-Tablespace für das OLTP während des Tages, und den größeren Undo-Tablespace fürdas Laden von Data Marts und Data Warehouses und für Aggregationen, weil nachtsdie 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 einenUndo-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-Tabelleaus, 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 Transakti-on:


SQL> select t.status, t.start_time, t.name 2 from v$transaction t join v$session s on t.ses_addr = s.saddr 3 where s.username = 'SCOTT';

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-Table-space weiterhin die Undo-Informationen für SCOTTs Transaktion. Damit steht dasUndo-Segment mit dem folgenden Status solange zur Verfügung, bis die Transaktionfestgeschrieben 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 UNDOTBS1-Tablespace erst dann offline setzen oder löschen, wenn SCOTTs Transaktion festge-schrieben oder mit einem Rollback zurückgesetzt wurde:

SQL> show parameter undo_tablespaceNAME 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-Ta-blespace offline zu setzen – entweder handelt es sich um den aktuellen Undo-Table-space oder er hat noch offene Transaktionen. Schalten wir auf den tagaktiven Table-


space um, bevor SCOTT seine ursprüngliche Transaktion festgeschrieben oder miteinem Rollback zurückgesetzt hat, ändert sich der Status von SCOTTs Rollback-Segmentwieder 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, abgelau-fen oder nicht abgelaufen. Aktive und nicht abgelaufene Undo-Daten werden weiterhinfür die Lesekonsistenz benötigt, selbst nach dem Festschreiben einer Transaktion.Nachdem alle Abfragen beendet wurden, die aktive Undo-Daten benötigen, undgleichzeitig das Ende der Vorhaltezeit erreicht ist, werden die aktiven zu abgelaufenenUndo-Daten. Die abgelaufenen Daten lassen sich für andere Oracle-Funktionen wieFlashbacks einsetzen, werden aber nicht mehr für die Lesekonsistenz bei lang laufen-den Transaktionen benötigt. Ungenutzte Undo-Daten belegen im Undo-Tablespaceunnötig Platz.

Damit bietet die minimale Größe für einen Undo-Tablespace genügend Platz zumVorhalten der Before-Image-Versionen aller Daten aus sämtlichen aktiven Transakti-onen, 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-Operatio-nen zu unterstützen, erhält der Benutzer die Fehlermeldung „ORA-30036: unable toextend segment by space_qty in undo tablespace tablespace_name.“ In diesem Fallmuss der DBA den Undo-Tablespace vergrößern. Alternativ kann der Benutzer unterEinhaltung der Geschäftsregeln größere in kleinere Transaktionen aufteilen.

Manuelle MethodenDie Größe eines Undo-Tablespaces kann ein DBA auch manuell korrigieren. Wie inKapitel 6 gezeigt, sehen wir in der dynamischen Performance View V$UNDOSTATdie Nutzung der Undo-Segmente in 10-Minuten-Intervallen. Zusätzlich zeigt die


SSOLDERRCNT-Spalte, wie viele Abfragen mit einem „Snapshot too old“-Fehlerscheiterten:

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 02004-04-13 08:09 4569 02004-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 zugescheiterten 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 Initialisierungspa-rameter UNDO_RETENTION), UPS sind die genutzten Undo-Blöcke pro Sekunde,und Overhead sind die Undo-Metadaten – im Vergleich zur Gesamtgröße relativ we-nige. Unsere ord-Datenbank hat eine Blockgröße von 8K und UNDO_RETENTIONist 43200 (12 Stunden). Wenn wir pro Sekunde 500 Blöcke generieren, die stets 12Stunden 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 denkba-ren Situationen gerüstet. Alternativ können Sie die Datendateien im Tablespace alsselbsterweiternd definieren. Obgleich diese Kalkulation ein guter Ausgangspunkt ist,können die Oracle-Ratgeber mit Hilfe von Trendanalysen eine besseres Gesamtbildzeichnen.

Undo AdvisorDer Undo Advisor von Oracle 10g automatisiert viele Jobs zum Feintuning des Spei-cherplatzes für Undo-Tablespaces. In Kapitel 6 stellten wir zwei Beispiele mit demUndo Advisor vor: über die Schnittstelle von EM Database Control und den PL/SQLDBMS_ADVISOR-Packages im Automatic Workload Repository (AWR).

Die Seite für den Undo Advisor sehen Sie in Abbildung 7-8.


Abbildung 7-8: EM Database Control: der Undo Advisor.

Obwohl wir UNDO_RETENTION auf 43200 (720 Minuten) setzen, ist es im UndoAdvisor anscheinend so, dass die auto-getunete Undo-Vorhalteperiode zur Unter-stützung einer lesekonsistenten Ansicht von geänderten Tabellen für die aktuelleUndo-Segmentnutzung nicht höher oder niedriger als 720 sein muss.

Beim Anlegen unseres Undo-Tablespace (siehe Abbildung 7-4) setzen wir einigeGrenzwerte. Anhand dieser automatischen Warnungen können wir den Undo-Table-space in den Fällen proaktiv neu dimensionieren, falls einer der Schwellenwerte über-schritten wird – und zwar bevor irgendwelche DMLs oder Abfragen scheitern.

Steuern der Undo-NutzungSeit Oracle 9i unterstützt uns der Database Resource Manager beim Verwalten derSpeicherplatzbelegung, entweder nach Benutzer oder nach Benutzergruppen in ei-ner Ressourcen-Verbrauchergruppe, mit Hilfe der UNDO_POOL-Direktive. JedeVerbrauchergruppe kann einen eigenen Undo-Pool besitzen; überschreitet die Ge-samtzahl der generierten Undos nach Gruppen den zugewiesenen Wert, wird die ak-tuelle Transaktion, die Undos generiert, beendet, und die Fehlermeldung „ORA-30027: Undo quota violation—failed to get number (bytes).“ ausgegeben. Die Sit-zung muss warten, bis der DBA den Undo-Pool vergrößert, oder bis sich Transak-tionen von anderen Benutzern in der gleichen Verbrauchergruppe beenden.


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 5besprochen.

7.3.6 Lesekonsistenz und erfolgreiche DMLs

Bei OLTP-Datenbanken möchten wir üblicherweise, dass die DML-Befehle erfolg-reich sind – gegebenenfalls auf Kosten der Lesekonsistenz. In einer DSS-Umgebunghingegen sollen sich lang laufende Abfragen möglichst ohne „Snapshot too old“-Feh-ler beenden. Obwohl ein Vergrößern des UNDO_RETENTION-Parameters oder einAnpassen des Undo-Tablespace sicherstellen kann, dass für lesekonsistente AbfragenUndo-Blöcke zur Verfügung stehen, besitzen Undo-Tablespaces eine weitere Charak-teristik, die das erfolgreiche Ausführen von Abfragen sicherstellt: die RETENTIONGUARANTEE-Einstellung.

RETENTION GUARANTEE wird auf Tablespace-Ebene gesetzt und lässt sich jeder-zeit ändern. Das Setzen einer Vorhaltegarantie für einen Undo-Tablespace stellt si-cher, dass ein aktives Undo innerhalb des Tabelle auch dann vorgehalten wird, wennes so aussieht, als ob für das erfolgreiche Ausführen von DML-Transaktionen nichtgenügend Undo-Platz zur Verfügung steht. Ein Tablespace wird standardmäßig mitNOGUARANTEE angelegt, außer Sie definieren das Schlüsselwort GARANTEEgleich beim Anlegen des Tablespace oder zu einem späteren Zeitpunkt mit ALTERTABLESPACE:

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-OptionenIn diesem Abschnitt besprechen wir die Flashback-Optionen, die von Undo-Table-spaces unterstützt werden: Flashback Query, Flashback Table, Flashback Version Que-ry und Flashback Transaction Query. Zudem stellen wir das DBMS_FLASHBACK-Pa-ckage vor.

Flashback Database und Flashback Drop werden in Kapitel 15 besprochen. FlashbackDatabase nutzt für die Flashback-Funktionalität die Flashback Logs in der Flash Reco-very Area, und keine Undos in einem Undo-Tablespace. Flashback Drop platziert Ta-bellen in einer virtuellen Recycle bin innerhalb des Tablespace. Die Tabellen bleibendort solange, bis sie der Benutzer mit flashback table … to before drop abholt, dieRecycle bin leert, oder der Platz für permanente Objekte im Tablespace benötigtwird.

Weiterhin kann der DBA in Oracle 10g System- und Objektberechtigungen zuweisen,die es Benutzern erlauben, bestimmte Probleme selbstständig zu lösen. Im folgendenBeispiel sorgen wir dafür, dass der Benutzer SCOTT Flashback-Operationen auf be-stimmte Tabellen ausführen und innerhalb der Datenbank auf transaktionsbezogeneMetadaten 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 ei-nes gegebenen Zeitstempels oder einer SCN den Status einer Tabelle abholen. Damitlässt sich beispielsweise herausfinden, welche Zeilen in einer Tabelle seit Mitternacht


gelöscht wurden. Oder Sie vergleichen den aktuellen Stand von Zeilen in einer Tabellemit den gestrigen Inhalten.

Im folgenden Beispiel räumt SCOTT in der HR.EMPLOYEES-Tabelle auf und löschtzwei 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 mehrin die HR.EMPLOYEES-Tabelle zurückkopieren, aber er braucht sie für die Archivta-belle. Da SCOTT weiß, dass er die Zeilen vor weniger als einer Stunde gelöscht hat, las-sen sich die Zeilen mit Flashback Query und einem relativen Zeitstempel zurückholen:

SQL> insert into hr.employees_archive 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);

2 rows created.


Da EMPLOYEE_ID der Primärschlüssel der Tabelle ist, können wir mit seiner Hilfedie Angestelltendatensätze zurückholen, die vor einer Stunde vorhanden waren. Be-achten Sie auch, dass wir nicht wissen müssen, welche Zeilen gelöscht wurden: Wirverglichen die Tabelle mit dem Stand vor einer Stunde und fügten die nicht mehr vor-handenen 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.


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 Falleinfacher, 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 LAST_NAME----------- --------------------------- 195 Jones 196 Walsh

2 rows selected.

Diese Variante bezeichnet man als Out-of-Place-Restore (mit andern Worten, das Wie-derherstellen der Tabelle oder eines Tabellensubsets an einem anderen Standort). Dashat den Vorteil, dass man die fehlenden Zeilen manipulieren kann, bevor man sie wie-der in die Tabelle platziert; beispielsweise kann ein referenzieller Integritäts-Con-straint verlangen, dass man die Zeile zuerst in eine Parent-Tabelle einfügt, bevor diewiederhergestellte Zeile wieder in der Child-Tabelle hinterlegt werden darf.

Ein Nachteil des Out-of-Place-Restore mit CTAS ist, dass weder Constraints noch In-dizes wiederhergestellt werden.

7.4.2 DBMS_FLASHBACK

Eine Alternative zu Flashback Query ist das DBMS_FLASHBACK-Package. Einer derwichtigsten Unterschiede zwischen DBMS_FLASHBACK und Flashback Query ist,dass DBMS_FLASHBACK auf Sitzungsebene und Flashback Query auf Objektebene ope-riert.

DBMS_FLASHBACK lässt sich innerhalb einer PL/SQL-Prozedur oder Benutzersit-zung aktivieren, und alle nachfolgenden Operationen, inklusive der existierenden Ap-plikationen, lassen sich ausführen, ohne in select-Anweisungen die as of-Klausel an-zugeben. Nach dem Aktivieren von DBMS_FLASHBACK für einen bestimmtenZeitstempel oder eine SCN erscheint die Datenbank so, als ob die Uhr auf den Zeit-stempel oder eine SCN zurückgestellt worden sei. Dieser Zustand hält bis zum Deak-


tivieren von DBMS_FLASHBACK an. Obwohl bei aktiviertem DBMS_FLASHBACKkeine DMLs erlaubt sind, lässt sich vor dem Aktivieren von DBMS_FLASHBACK ineiner PL/SQL-Prozedur einen Cursor öffnen, damit man Daten von einem früherenZeitpunkt übernehmen und im aktuellen Zeitpunkt einfügen oder aktualisieren kann.

Tabelle 7-2 enthält die verfügbaren Prozeduren in DBMS_FLASHBACK.

Die Prozeduren zum Aktivieren und Deaktivieren des Flashback-Modus sind einfachzu bedienen. Die Komplexität liegt üblicherweise in der PL/SQL-Prozedur, die bei-spielsweise zur Unterstützung von DML-Befehlen einen Cursor anlegt.

Im folgenden Beispiel widmen wir uns noch einmal dem Benutzer SCOTT und dengelöschten Zeilen in der HR.EMPLOYEES-Tabelle, und zeigen, wie man die Tabellemit Hilfe des DBMS_FLASHBACK-Package wiederherstellt. In diesem Szenario bin-det SCOTT die gelöschten Zeilen wieder in die Tabelle ein:

SQL> delete from hr.employees where employee_id in (195,196);2 rows deleted.


Nach einer halben Stunde entscheidet SCOTT, dass er die beiden Zeilen mit demDBMS_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.

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

TIMESTAMP_TO_SCN

Konvertiert einen Oracle-TIMESTAMP und liefert die SCN, die dem TIMESTAMP-Wert am nächsten liegt.


Danach prüft er, ob die beiden gelöschten Zeilen genauso wie vor einer dreiviertelStunde vorliegen:

SQL> select employee_id, last name from hr.employees2 where employee_id in (195,196);

EMPLOYEE_ID LAST_NAME----------- ------------------------ 195 Jones 196 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 Aufnah-me der gelöschten Zeilen anlegt, Flashback Query deaktiviert, und danach die Zeilenwieder 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 rowbegin -- 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 aktivierenkö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, zumAusschalten 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 Tabellen-zeilen für einen Zeitpunkt in der Vergangenheit wieder her, sondern auch die dazuge-hörigen Indizes, Trigger und Constraints. Und das während die Datenbank online ist,


was die Verfügbarkeit der Datenbank wesentlich verbessert. Die Tabelle lässt sich füreinen Zeitstempel oder eine SCN zurücksetzen. Flashback Table bewährt sich gegen-über anderen Flashback-Methoden, wenn die Auswirkungen von Benutzerfehlern nurgering sind und sich auf wenige Tabellen beschränken. Zur Wiederherstellung des Sta-tus von mehreren Tabellen ist Flashback Database die bessere Wahl. Flashback Tablelässt sich nicht auf eine Standby-Datenbank oder zum Rekonstruieren von DML-Ope-rationen einsetzen, wie das Hinzufügen oder Entfernen von Spalten.

Um Flashback Table auf Tabellen einzusetzen, ist vor dem Ausführen der Flashback-Operation die Zeilenverschiebung zu aktivieren, die allerdings zu dem Zeitpunkt, wennder Benutzerfehler auftritt, nicht aktiv sein muss. Die Zeilenverschiebung benötigtman auch zur Unterstützung der Segmentverkleinerung: Da die Zeilenverschiebungallerdings die ROWID auf eine Tabellenzeile ändert, sollten Sie diese Option nicht ak-tivieren, falls Ihre Applikation davon abhängt, dass die ROWID bis zum Löschen einerZeile gleich sein muss. Da keine Ihrer Applikationen die Tabellen via ROWID referen-ziert, 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.EMPLOYEES-Tabelle:

SQL> delete from hr.employees2 /107 rows deleted.

SQL> commit 2 ;Commit complete.

SQL> where employee_id = 195SP2-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.


SQL> select count(*) from hr.employees; COUNT(*)---------- 107

Sollten Tabellen eine Parent/Child-Beziehung mit Primärschlüssel-Constraints besit-zen, und aus beiden Zeilen wurden unbeabsichtigt Zeilen gelöscht, lassen sie sich imgleichen 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 Abbil-dung 7-9 selektierte er im Maintenance-Register unter der Überschrift Backup/Reco-very den Perform Recovery-Link.

Wählt SCOTT als Objekttyp Tables, hat er die Option, vorhandene oder gelöschte Ta-bellen zurückzusetzen. Im vorliegenden Fall wird er eine bestehende Tabelle zurück-blenden (siehe Abbildung 7-10).

Nach einem Klick auf Next kennt er den genauen Zeitpunkt, wann die Tabelle gültigwar, und gibt es in Bildschirm 7-11 ein.


Abbildung 7-10: Bestehende Tabellen zurückblenden.

Abbildung 7-11: Auswahl von Datum und Zeit für Flashback Table.


In Abbildung 7-12 entscheidet sich SCOTT für das Flashback einer Tabelle (in diesemFall 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-Con-straints, und alarmiert SCOTT in Abbildung 7-13. Falls es nicht gute Gründe zum Auf-brechen von Parent/Child-Beziehungen zwischen Tabellen gibt, lassen Sie die Stan-dardoption Cascade selektiert.

Ein Klick auf Show Dependencies macht genau das: Es zeigt die Abhängigkeiten zwi-schen den Fremdschlüsseln in der zurückzusetzenden Tabelle und den Parent-Tabel-len. 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 generier-ten SQL-Befehle ansehen:

FLASHBACK TABLE HR.EMPLOYEES, HR.JOBS, HR.DEPARTMENTS TO TIMESTAMPto_timestamp('2004-04-15 06:15:12 PM', 'YYYY-MM-DD HH:MI:SS AM')

Ein Klick auf Submit führt den Befehl aus.


Abbildung 7-13: Abhängigkeitsoptionen bei Flashback.

Abbildung 7-14: Die Abhängigkeitshierarchie bei Flashback.


Abbildung 7-15: Flashback Table: Optionsübersicht.

Beachten Sie, dass Sie in SCOTTs Beispiel Ihr Ziel über die Befehlszeile weit schnellererreicht hätten. Liegen allerdings unbekannte Abhängigkeiten vor, oder sollten Sie dieSyntax 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-Datenaufsetzt. Sie bietet eine feinere Detailebene als eine as of-Abfrage. Während die bishervorgestellten Flashback-Methoden für einen bestimmten Zeitpunkt entweder Zeilenoder ganze Tabellen zurückbringen, liefert Flashback Version Query anhand von zweiSCNs oder Zeitstempeln die gesamte Historie einer gegebenen Zeile zurück.

In diesem und dem folgenden Beispiel führt der Benutzer SCOTT an den TabellenHR.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;


GET_SYSTEM_CHANGE_NUMBER------------------------ 1673349

SQL> delete from hr.employees where employee_id = 196;1 row deleted.


SQL> insert into hr.departments values (660,'Security', 100, 1700);1 row created.


SQL> update hr.employees set manager_id = 100 where employee_id = 195;1 row updated.



SQL> update hr.employees set department_id = 660 where employee_id = 195;1 row updated.


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;GET_SYSTEM_CHANGE_NUMBER------------------------ 1674188SQL>


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 kannder Benutzer HR nicht nur die Werte einer Spalte für einen bestimmten Zeitpunkt se-hen; er sieht die gesamte Änderungshistorie zwischen den angegebenen Zeitstempelnoder SCNs.

Mit der versions between-Klausel geben Sie einer Flashback Version Query den SCN-Bereich oder die Zeitstempel zum Analysieren einer Tabelle vor (in unserem Fall dieHR.EMPLOYEES-Tabelle). Setzt man diese Klausel ein, lassen sich über verschiedenePseudospalten diverse Informationen ableiten: die SCNs und Zeitstempel für Ände-rungen, die Transaktions-ID und der ausgeführte Operationstyp. Tabelle 7-3 zeigt dieverfügbaren Pseudospalten für Flashback Version Queries:

Der Benutzer HR führt eine Flashback Version Query aus, und möchte alle Änderun-gen auf die Schlüsselspalten in HR.EMPLOYEES für die beiden Angestellten mit denIDs 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.


Die Zeilen mit den letzten Änderungen werden zuerst angezeigt. Alternativ könnte HRdie Abfrage nach TIMESTAMP ausführen oder sich die TIMESTAMP-Werte anzeigenlassen; aber beide Varianten lassen sich zu einem späteren Zeitpunkt in einer Flash-back Query oder Flashback Table-Operation einsetzen. In dieser Ausgabe sehen wir,dass ein Angestellter gelöscht wurde und ein anderer Angestellter gleich zwei Gehältererhielt. Zudem können wir festhalten, dass manche Operationen nur ein, andere hin-gegen 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 las-sen sich mit einer Flashback Transaction Query alle anderen Modifikationen ermit-teln, die von der Transaktion mit den unpassenden Änderungen ausgeführt wurden.Danach kann man sämtliche Änderungen als Gruppe rückgängig machen. Damit wer-den typischerweise die referenzielle Integrität oder die Geschäftsregeln gewahrt.

Eine Flashback Transaction Query referenziert im Unterschied zu einer FlashbackVersion Query nicht die Tabelle, die in DML-Transaktionen eingebunden war; statt-dessen fragen Sie die Data Dictionary View FLASHBACK_TRANSACTION_QUERYab. Die Spalten von FLASHBACK_TRANSACTION_QUERY sind in Tabelle 7-4 zu-sammengefasst.

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).

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.

Tabelle 7-3: Pseudospalten für Flashback Version Query. (Fortsetzung)

Pseudospalte Beschreibung


Um die Änderungen auf die HR.EMPLOYEES-Tabelle eingehender zu untersuchen,fragen wir die View FLASHBACK_TRANSACTION_QUERY mit der ältesten Trans-aktion 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 EMPLOYEESinsert into "HR"."EMPLOYEES"("EMPLOYEE_ID","FIRST_NAME","LAST_NAME","EMAIL","PHONE_NUMBER","HIRE_DATE","JOB_ID","SALARY", "COMMISSION_PCT" , "MANAGER_ID" , "DEPARTMENT_ID" ) values ( ' 1961,'Alana','Walsh','AWALSH','650.507.9811',TO_DATE('24-APR-98', 'DD-MON-RR'),'SH_CLERK','3100',NULL,'124','50');

1673366 1673368 SCOTT UPDATE EMPLOYEESupdate "HR"."EMPLOYEES" set "SALARY" = '2800' where ROWID ='AAAMAeAAFAAAABYABc ' ;

1673366 1673368 SCOTT BEGIN

3 rows selected.

Das Ergebnis bestätigt, was wir erwarteten – die Löschoperation und die Aktualisie-rung des Gehalts wurden von SCOTT ausgeführt. Die UNDO_SQL-Spalte enthält denCode, mit dem sich der Effekt der Transaktion rückgängig machen lässt. In unserem

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

Tabelle 7-4: Die Spalten in FLASHBACK_TRANSACTION_QUERY. (Fortsetzung)

Spaltenname Beschreibung


Beispiel geht es um die erste Transaktion zwischen den gewählten SCNs. Wurden zueinem früheren Zeitpunkt auf dieselbe Spalte irgendwelche Änderungen ausgeführt,sollte man vor dem Ausführen dieses SQL-Codes die anderen Aktualisierungen anse-hen. Wenn wir uns in der Serie die letzte Transaktion ansehen, erkennen wir, dass esfü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 EMPLOYEESupdate "HR"."EMPLOYEES" set "SALARY" = '3360' where ROWID ='AAAMAeAAFAAAABYABc ' ;

1673386 1674182 SCOTT INSERT JOB_HISTORYdelete from "HR"."JOB_HISTORY" where ROWID = 'AAAMAiAAFAAAABtAAB ' ; 1673386 1674182 SCOTT UPDATE EMPLOYEES update "HR"."EMPLOYEES" set "DEPARTMENT_ID" = '50' where ROW ID = 'AAAMAeAAFAAAABYABc';

1673386 1674182 SCOTT BEGIN

4 rows selected.

Die erste Gehaltsaktualisierung war gültig, während die zweite (aktuellere) dupliziertwurde, und daher ungültig ist. Innerhalb der gleichen Transaktion änderte SCOTTauch 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, sonderngeht auf das Konto eines Triggers auf die HR.JOB_HISTORY-Tabelle, der alle Ände-rungen von JOB_ID oder DEPARTMENT_ID in HR.EMPLOYEES an HR.JOB_HIS-TORY protokolliert. Damit ist lediglich die letzte Gehaltänderung rückgängig zu ma-chen:

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-Tabel-le plus die erneute Zuordnung des Angestellten zur neuen Abteilung:

7.5 Migration zum automatischen Undo-Management 307

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 verzichte-ten, war nicht sofort ersichtlich, dass auf die Spalten in der HR.EMPLOYEES-Tabelleweitere Änderungen ausgeführt wurden und zusätzlich eine neue Zeile in HR.DE-PARTMENTS eingefügt wurde.

7.5 Migration zum automatischen Undo-Management

Um Ihre Umgebung von manuell verwalteten Rollback-Segmenten auf das automati-sche Undo Management zu migrieren, müssen Sie eine Sache kennen; die Größe desUndo-Tablespace, basierend auf der Nutzung der Rollback-Segmente im manuellenUndo-Modus. Sind alle manuellen Rollback-Segmente online gesetzt, führen Sie dieProzedur DBMS_UNDO_ADV.RBU_MIGRATION aus: Sie liefert die aktuelle Bele-gung für die Rollback-Segmente in Megabytes zurück:

SQL> variable undo_size numberSQL> begin 2 :undo_size := dbms_undo_adv.rbu_migration;3 end;4 /

PL/SQL procedure successfully completed.


SQL> print :undo_size

UNDO_SIZE

2840

SQL>

In unserem Beispiel sollte ein Undo-Tablespace, der die Rollback-Segmente ersetzensoll, mindestens 2840MB oder 2,84GB groß sein. Nur so kann er alle Undo-Anforde-rungen aufnehmen, die derzeit von Rollback-Segmenten unterstützt werden.

Documents

Transaktionsverwaltung mit Undo-Tablespaces