Oracle Flashback – Ein Überblick DOAG Regionaltreffen NRW

Daniel Schulz • Seniorberater OPITZ CONSULTING Gummersbach GmbH

Jülich, 26.08.2009

Oracle Flashback – DOAG Regio NRW 26.8.2009

Seite 1

„Flashback“ kann vieles sein…

Flashback Transaction

Flashback Logs

Recyclebin Flash Recovery Area Flashback Version Query Flashback Transaction Backout Flashback Data Archive

Total Recall Flashback Drop

Flashback Table

Oracle Flashback – DOAG Regio NRW 26.8.2009

Seite 2

Agenda

● ● ● ● ●

Typische Fehlerszenarien „Klassische“ Lösungen und die Limitationen Vorstellung der Flashback Technologien Praktische Beispiele Fazit

Oracle Flashback – DOAG Regio NRW 26.8.2009

1

Einleitung

Oracle Flashback – DOAG Regio NRW 26.8.2009

Seite 4

Typische Fehler

● ● ● ● ● ●

Update/Delete mit falscher oder ohne(!) WHERE Klausel Massen-DML mit falschen Werten Tabelle gelöscht Tabelle mit TRUNCATE geleert Benutzer gelöscht ….


Recoverybedarf entsteht überwiegend durch menschliche Fehler oder Fehler in der Anwendung

Oracle Flashback – DOAG Regio NRW 26.8.2009

Klassischer Ansatz: Point-in-Time-Recovery

● ● ● ● ●

Wiederherstellung aus Bandsicherung Recovery bis kurz vor den Fehlerzeitpunkt (ggf. ermitteln!) Öffnen mit „Open Resetlogs“ Fehler und „Gute“ Änderungen werden verworfen! Dauer abhängig von DB Größe und Menge der Änderungen

Fehler! System Online

Restore Datafiles 8:00

9:00

Recovery Archivelogs

10:30

12:30

Datenbestand von 8:29 Oracle Flashback – DOAG Regio NRW 26.8.2009

Klassischer Ansatz: Verzögertes Physical Standby

● Die Standby-Seite wird mit z.B. 4 h Verzögerung nachgefahren ● Innerhalb dieser Zeit kann verhindert werden, dass die Standby DB den Fehler übernimmt.

Oracle Flashback – DOAG Regio NRW 26.8.2009

Der DBA Alltag

● Ein Point-In-Time-Recovery an Ort und Stelle ist oftmals ● ●

● ● ●

nicht möglich Es gibt „gute“ Änderungen, die nicht verloren gehen dürfen Standby-System mit Delay kann Ausfall des Primärsystems nicht zufriedenstellend absichern – Schließen der Delay Lücke kann Stunden dauern PITR als Klon, dann manueller Transfer der geretteten Daten, z.B. exp/imp, Datapump oder über DB-Link Bei großen Datenbanken oftmals ein Platzproblem Analyse/Identifikation der Fehlerquelle gefordert

Oracle Flashback – DOAG Regio NRW 26.8.2009

Seite 8

Oracle Flashback hilft!

● Schneller als klassisches Point-In-Time-Recovery ● Fehlerbehebung auf Datenbank-, Tabellen- oder Transaktionsebene ● Viele typische Fehlerszenarien können ohne Point-In-TimeRecovery gelöst werden ● Vereinfachte Ermittlung des Fehlerzeitpunktes

Oracle Flashback – DOAG Regio NRW 26.8.2009

Seite 9

Was ist eigentlich „Flashback“

● Verschiedene Funktionalitäten, um gelöschte Daten

wiederherzustellen oder ältere Versionen zu betrachten ● In Standard Editon: ● ●

Seit 9i: Flashback (Version) Query Ab 10g: Flashback Drop (Recycle Bin)

● Nur Enterprise Edition: ●

●

Ab 10g: ● Flashback Table ● Flashback Database ● Flashback Transaction Query Neu in 11g: ● Flashback Transaction Backout ● Flashback Data Archive – Kostenpflichtige Option!

● Trotz des gemeinsamen Namens sind hier unterschiedliche

Techniken in Verwendung! ● Die Flash Recovery Area hat auch nur bedingt damit zu tun! Oracle Flashback – DOAG Regio NRW 26.8.2009

2

Flashback (Version/Transaction) Query

Oracle Flashback – DOAG Regio NRW 26.8.2009

Seite 11

Ausgangspunkt

● ● ● ●

Wie sahen die Daten heute morgen aus? Welche Veränderungen sind heute morgen passiert? Wann genau wurde das falsche SQL ausgeführt? Wie können die Änderungen rückgängig gemacht werden?

Oracle Flashback – DOAG Regio NRW 26.8.2009

Flashback Query aktivieren

● Automatic Undo Management muß verwendet werden (UNDO_MANAGEMENT=AUTO) ● UNDO Retention anpassen (Voreinstellung sind 900 sec, also 15 min ● Bei akutem Platzmangel werde auch vor Ablauf der UNDO Retention die ältesten Daten über abgeschlossene Transaktionen überschrieben ● Bei Bedarf UNDO auf Retention Guarantee stellen
Achtung! Ist UNDO TBS zu klein, blockiert die Instanz wie bei Archiver Stuck!

Oracle Flashback – DOAG Regio NRW 26.8.2009

Flashback (Version) Query SELECT FROM AS OF TIMESTAMP ; SELECT FROM VERSIONS BETWEEN TIMESTAMP AND ; SELECT FROM VERSIONS BETWEEN TIMESTAMP minvalue AND maxvalue; SCN kann ebenfalls verwendet werden!

Oracle Flashback – DOAG Regio NRW 26.8.2009

Beispiel

● Benutzer „Meier“ meldet, Abteilung 50 ist ‚verschwunden‘… ● Er ist sehr aufgebracht und möchte wissen, was passiert ist ● Morgens um 7 Uhr war noch alles in Ordnung ● Wieso ist die Datenbank jetzt defekt?

Oracle Flashback – DOAG Regio NRW 26.8.2009

Flashback Query Beispiel SELECT * FROM dept AS OF TIMESTAMP to_timestamp('12-05-2009 07:00:00','dd-mm-yyyy hh24:mi:ss'); DEPTNO -----10 20 30 40 50

DNAME ---------ACCOUNTING RESEARCH SALES OPERATIONS MARKETING

Oracle Flashback – DOAG Regio NRW 26.8.2009

LOC ------------NEW YORK DALLAS CHICAGO BOSTON BERLIN

Flashback Version Query Beispiel SELECT deptno, dname, loc, versions_operation vo, versions_xid, versions_starttime FROM scott.dept VERSIONS between timestamp minvalue and maxvalue ORDER BY deptno, versions_starttime; D -10 20 30 40 50 50 50

DNAME ----------ACCOUNTING RESEARCH SALES OPERATIONS MARKETING

LOC -----NEW YORK DALLAS CHICAGO BOSTON BERLIN BERLIN BERLIN

VO VERSIONS_XID -- ----------------

U D

090013004A030000 06001F008F030000

Oracle Flashback – DOAG Regio NRW 26.8.2009

VERSIONS_STARTTIME -------------------

12.05.09 09:15:43 12.05.09 09:35:33

Flashback Version Pseudospalten

VERSIONS_STARTTIME (start timestamp of version) VERSIONS_STARTSCN (start SCN of version) VERSIONS_ENDTIME (end timestamp of version) VERSIONS_ENDSCN (end SCN of version) VERSIONS_XID (transaction ID of version) VERSIONS_OPERATION (DML operation of version)

Oracle Flashback – DOAG Regio NRW 26.8.2009

Wer war es denn nun?

● Verbinden der Transaktion mit Anwender: SQL> SELECT logon_user, table_name, table_owner, undo_sql FROM flashback_transaction_query WHERE table_owner='SCOTT' AND xid= '06001F008F030000';

LOGON_USER

TABLE_NAME

TABLE_OWNER

UNDO_SQL

----------

------------ ------------ -----------------

MEIER DEPT SCOTT insert into "SCOTT"."DEPT"("DEPTNO","DNAME","LOC") values ('50',NULL,'BERLIN');

Oracle Flashback – DOAG Regio NRW 26.8.2009

Undo SQL für mehrere Transaktionen

SQL> SELECT undo_sql FROM flashback_transaction_query WHERE table_owner = 'SCOTT'AND table_name = 'DEPT' ORDER BY commit_timestamp desc; UNDO_SQL ------------------------------------------------------insert into "SCOTT"."DEPT"("DEPTNO","DNAME","LOC") values ('50',NULL,'BERLIN'); update "SCOTT"."DEPT" set "DNAME" = 'MARKETING' where ROWID = 'AAAQ+JAAEAAAAAQAAA';

Oracle Flashback – DOAG Regio NRW 26.8.2009

Flashback Transaction Backout

● ● ● ●

Neu in 11g Integriert zusätzlich den Logminer Benötigt Supplemental Log Data Kann Abhängigkeiten behandeln, z.B. UPDATE auf einer zuvor eingefügte Zeile (WAW – Write after Write): ● Default: Nur konflikfreie Transaktionen zurücknehmen ● NOCASCADE FORCE: Nur diese eine Transaktion, auch wenn es

Abhängigkeiten gibt ● CASCADE: Alle abhängigen Transaktionen zurückrollen

Oracle Flashback – DOAG Regio NRW 26.8.2009

3

Flashback Table

Oracle Flashback – DOAG Regio NRW 26.8.2009

Seite 22

Flashback Table

● Basiert (auch) auf UNDO Informationen ● Tabelleninhalt wird auf einem bestimmten Zeitpunkt oder ● ● ● ● ●

SCN zurückgesetzt Row Movement muss aktiviert werden FLASHBACK (ANY) + DML Recht für fremde Tabelle Abhängige Tabellen können/müssen gleichzeitig behandelt werden Zugehörige Tabellen haben exklusives DML Lock während der Ausführung Trigger auf der Tabelle sind während des Vorgangs deaktiviert SQL> FLASHBACK TABLE employees, departments TO TIMESTAMP TO_TIMESTAMP ('12-May-2009,09:14','dd-mon-yyyy,hh24:mi');

Oracle Flashback – DOAG Regio NRW 26.8.2009

Einschränkungen Flashback Table

Keine Systemtabellen! Nicht über DDL Anweisungen auf der Tabelle hinweg Nicht über SHRINK hinweg Statistiken werden nicht zurückgesetzt! Zwischenzeitlich gelöschte Indices werden nicht wiederhergestellt ● Werden Constraints verletzt, so bricht das Flashback ab

● ● ● ● ●

Oracle Flashback – DOAG Regio NRW 26.8.2009

4

Flashback Drop Recyclebin

Oracle Flashback – DOAG Regio NRW 26.8.2009

Seite 25

FLASHBACK DROP (RECYCLEBIN)

● Neu in 10g ● Objekte werden nicht sofort komplett aus dem System entfernt ● Gelöschte Objekte werden nur im Data Dictionary umbenannt (BIN$...) ● Ist als Voreinstellung aktiv

● Deaktivieren: ALTER SYSTEM SET "_recyclebin"=FALSE SCOPE=BOTH; -- 10gR1 ALTER SYSTEM SET recyclebin=off;

-- ab 10gR2

● SELECT weiterhin möglich, wenn der neue Tabellenname angegeben wird, z.B.:

SELECT * FROM "BIN$jtYKhVPTQlyDjXynS8Noxw==$0"; Oracle Flashback – DOAG Regio NRW 26.8.2009

Recyclebin Inhalt anzeigen

SQL> show recyclebin ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME -------------- ------------------------------ ----------- ------------JOB_HISTORY BIN$cfgNYY8iv2jgQBCsDQobWw==$0 TABLE 2009-05-25:15:10:02

SQL> select object_name,original_name,type from user_recyclebin; OBJECT_NAME -----------------------------BIN$cfgNYY8ev2jgQBCsDQobWw==$0 BIN$cfgNYY8fv2jgQBCsDQobWw==$0 BIN$cfgNYY8gv2jgQBCsDQobWw==$0 BIN$cfgNYY8hv2jgQBCsDQobWw==$0 BIN$cfgNYY8iv2jgQBCsDQobWw==$0

ORIGINAL_NAME ------------------------JHIST_JOB_IX JHIST_EMPLOYEE_IX JHIST_DEPARTMENT_IX JHIST_EMP_ID_ST_DATE_PK JOB_HISTORY

Oracle Flashback – DOAG Regio NRW 26.8.2009

TYPE ----------INDEX INDEX INDEX INDEX TABLE

Objekte aus Recyclebin zurückholen

● Tabelle wiederherstellen (inkl. abhängige Objekte): FLASHBACK TABLE JOB_HISTORY TO BEFORE DROP; FLASHBACK TABLE ´BIN$cfgNYY8iv2jgQBCsDQobWw==$0´ TO BEFORE DROP; FLASHBACK TABLE ´BIN$cfgNYY8iv2jgQBCsDQobWw==$0´ TO BEFORE DROP RENAME TO JOB_HISTORY_OLD;

Oracle Flashback – DOAG Regio NRW 26.8.2009

Recyle Bin leeren

● Recycle Bin leeren: PURGE RECYCLEBIN; -PURGE TABLE ; -PURGE INDEX ; -PURGE TABLESPACE ; -löschen PURGE TABLESPACE USER -PURGE DBA_RECYCLEBIN; --

Für aktuellen Benutzer Tabelle permanent löschen Index löschen Recyclebin in bestimmtem TS ; Bestimmter User in Tablespace Leert gesamten Recyclebin

● Löschen ohne Recycle Bin: DROP TABLE TESTLADUNG PURGE; Oracle Flashback – DOAG Regio NRW 26.8.2009

Einschränkungen Recyclebin

● Bei Platzmangel werden Objekte dem Alter nach aus dem ● ● ●

● ● ●

Recyclebin gelöscht Autoextend erst nach Leeren des Recyclebin! Möglicherweise ist ein Teil der Indices wegen Platzmangel bereits gelöscht worden Zugehörige Indices, Trigger und Constraints werden (sofern noch vorhanden) wiederhergestellt, behalten aber den Recyclebin Namen! Größe und Vorhaltezeit nur durch freien Platz im Tablespace bestimmt
Keine Garantie möglich Nicht möglich für System Tablespace Objekte im Recyclebin nur lesbar

Oracle Flashback – DOAG Regio NRW 26.8.2009

5

Flashback Database Die Rückspultaste für die DB

Oracle Flashback – DOAG Regio NRW 26.8.2009

Seite 31

Funktion Flashback Database

● ● ● ● ● ●

Fehler wird um 9:00 Uhr bemerkt Um 8:30 wurde eine Tabelle mit Truncate geleert DB wird aus dem Betrieb genommen Flashback Database bis kurz vor Fehler Nur wenige MB Daten werden geändert Nach wenigen Minuten behoben Fehler !

6:00

8:30

9:00

FB + Arch. Logs

Oracle Flashback – DOAG Regio NRW 26.8.2009

Seite 32

Flashback Database

● Während des Normalbetriebs werden regelmäßig Block● ● ● ●

Images in die Flashback Logs kopiert Zusätzlich werden die Archivlogs benötigt Es kann innerhalb des abgedeckten Zeitraums vor und zurückgegangen werden Ideal für Softwaretests, um immer wieder schnell auf einen definierten Zustand zurückzugehen Flashback einer Standby DB kann Delayed Apply ersetzen

Oracle Flashback – DOAG Regio NRW 26.8.2009

Flashback Database aktivieren

● ● ● ●

Flash Recovery Area erforderlich DB muss im ARCHIVELOG Modus sein DB_FLASHBACK_RETENTION_TARGET Parameter setzen Im Mount Status:

ALTER DATABASE FLASHBACK ON;

Oracle Flashback – DOAG Regio NRW 26.8.2009

Restore Points

● Seit 10.2 verfügbar ● Normal: CREATE RESTORE POINT vor_test;

● Alias für SCN ● Altern aus Control File ● Garantiert: CREATE RESTORE POINT vor_test GUARANTEE FLASHBACK DATABASE;

● Altern nicht aus Controlfile ● Platz in Flash Recovery Area im Auge behalten! Oracle Flashback – DOAG Regio NRW 26.8.2009

Seite 35

Flashback Database Syntax

● Im MOUNT Status: FLASHBACK DATABASE... ... TO (SCN|TIMESTAMP); ... TO BEFORE RESETLOGS; ... TO RESTORE POINT ;

● Nach Überprüfung im READ ONLY Mode entweder weitere Flashback Database Kommandos oder OPEN RESETLOGS

Oracle Flashback – DOAG Regio NRW 26.8.2009

Einschränkungen Flashback Database

● Flashback Database ist nicht möglich, wenn: ● Ein Tablespace gelöscht worden ist ● Eine Datendatei verkleinert worden ist ● Die Controlfiles neu erstellt worden sind ● Die Flashback Logs nicht mehr vorhanden sind

Oracle Flashback – DOAG Regio NRW 26.8.2009

6

Flashback Data Archive Total Recall

Oracle Flashback – DOAG Regio NRW 26.8.2009

Seite 38

Architektur Flashback Data Archive

UNDO_MANAGEMENT=AUTO

FBDA Prozess

Archivierungs Tablespace mit ASSM

Quelle:Oracle Oracle Flashback – DOAG Regio NRW 26.8.2009

Flashback Data Archive: Was ist das?

● ● ● ● ● ● ● ● ●

Kostenpflichtige Option der 11g Enterprise Edition Sichere Speicherung historischer Daten über lange Zeit Flashback Queries über UNDO_RETENTION hinaus Langer Flashback Zeitraum mit wenig Platzbedarf, da auf Tabellenebene definiert Historisierte Daten werden in komprimierte und partitionierte Tabelle geschrieben Veränderungen werden auch über Jahre nachverfolgbar (betriebliche oder gesetzliche Anforderungen) Eigene Protokollierung über Trigger nicht mehr erforderlich Historische Daten werden nach der vorgegebenen Zeit automatisch entfernt Historientabellen sind auch für den DBA Read-Only!

Oracle Flashback – DOAG Regio NRW 26.8.2009

Einrichtung Flashback Data Archive

● Einrichtung eines Tablespaces mit ASSM ● Erstellung eines FLASHBACK ARCHIVE Bereichs: CREATE FLASHBACK ARCHIVE LONGARC TABLESPACE LANGZEIT RETENTION 5 YEAR; -- MONTH|DAY

● Optional: ALTER FLASHBACK ARCHIVE longarc DEFAULT;

● Tabelle zu FLASHBACK ARCHIVE hinzufügen: ALTER TABLE meinetabelle FLASHBACK ARCHIVE longarc;

● Ab jetzt: ● Kein DROP/TRUNCATE auf diese Tabelle ● Nur Spalten hinzufügen möglich

● Nicht möglich bei LONG Spalten, Nested Table Spalten, external/clustered/temporary Tabellen Oracle Flashback – DOAG Regio NRW 26.8.2009

Terminologie

● Flashback (Version) Query ● Alte Version der Daten anzeigen ● Alle Versionen zwischen zwei Zeitpunkten anzeigen

● Flashback Database ● Point-in-time Recovery „rückwärts“

● Flashback Drop ● Gelöschte Tabelle wiederherstellen

● Flashback Table ● Tabelleninhalt zu einem bestimmten Zeitpunkt

wiederherstellen ● Flashback Transaction (Backout) ● Bestimmte Transaktion rückgängig machen ● Flashback Data Archive ● Historisierung von Änderungen über Jahre Oracle Flashback – DOAG Regio NRW 26.8.2009

Architektur

● Flashback Table, Version, Query, Transaction ● Automatic Undo Management ● Transaction Backout: Zusätzlich Logminer

● Flashback Drop ● Recyclebin im jeweiligen Tablespace

● Flashback Database ● Flashback Logs in der Flash Recovery Area + Archivlogs

● „Total Recall“ ● Flashback Data Archives in geschützten komprimierten Partitionen ● Auslesen der UNDO Informationen

Oracle Flashback – DOAG Regio NRW 26.8.2009

Fazit

● Nur wenige Funktionalitäten in der Standard Edition ● Je nach benötigtem Zeitfenster erheblicher Platzbedarf für ● ● ● ●

UNDO, Flash Recovery Area oder FBDA Tablespace Viele Probleme können schneller oder mit weniger Einfluss auf Anwender gelöst werden als früher Die Flashback „Familie“ bekommt mit jeder Datenbankversion Zuwachs Interessante Erweiterung des DBA Werkzeugkastens Befehlssyntax relativ einfach

Oracle Flashback – DOAG Regio NRW 26.8.2009

& Fragen & Antworten Oracle Flashback – DOAG Regio NRW 26.8.2009

Seite 45

Kontakt: Daniel Schulz OPITZ CONSULTING GmbH [email protected]

Vielen Dank für Ihre Aufmerksamkeit ! Oracle Flashback – DOAG Regio NRW 26.8.2009

Seite 46