Risiken bei der Verwendung von NOLOGGING

Haben Sie Ihr Backup+Recovery-Szenario schon einmal komplett durchgetestet? Wenn nein (und dann sind Sie leider in zahlreicher Gesellschaft): Benutzen Sie für Tabellen, Indizes oder Materialized Views die NOLOGGING-Option? Dann könnten Ihre Daten in Gefahr sein!

Dieser Artikel entstand aus meiner Materialsammlung von mehr oder weniger identischen Problemfällen, mit denen ich über die letzten fünf Jahre zu tun hatte. Ich hoffe, damit ein wenig Licht in das Dunkel um dieses Thema bringen und DBAs (denen meist die Sicherheit ihrer Datenbank am Herzen liegt) sowie Entwicklern (denen meist die Geschwindigkeit ihrer Anwendung am Herzen liegt) ein paar brauchbare Hinweise geben zu können.

Kurzer Abriss der NOLOGGING-Option:

• Kann für Tabellen, Indizes oder Materialized Views definiert werden.
• Kann auch als Attribut für einen ganzen Tablespace gewählt werden.
• Einsparung von mehrfachem I/O, da Veränderungen an den Datafiles nicht in den Redo Log Files protokolliert werden. Dadurch werden wiederum weniger Archive Log Files erzeugt.
• Daher Zeitersparnis bei Beladungen, insbesondere großer Tabellen, z.B. mit SQL*Loader im Direct Mode und mit „INSERT /*+ APPEND */“.
• Ebenso Zeitersparnis beim Aufbau von Tabellen und Indizes mit „CREATE TABLE | INDEX … [AS SELECT …] NOLOGGING“  sowie Materialized Views.

Das Nologging-Attribut wird in Oracle-Datenbanken gerne genutzt, um die Performance von Beladungen und Änderungen großer Datenmengen, wie sie z.B. im Data Warehouse-Umfeld vorkommen, zu verbessern. Das Attribut kann auf Objekt- und Tablespace-Ebene gesetzt werden, sein Gegenstück („FORCE LOGGING“) sogar auf der Ebene der gesamten Datenbank.

Damit tatsächlich eine Beladung ohne Logging stattfindet, müssen mehrere Kriterien erfüllt sein. Thomas Kyte hat in einem Artikel auf asktom.oracle.com eine Tabelle erstellt, die diese Kriterien kurz zusammenfasst. Vereinfacht gesagt, muß sowohl das beladene Objekt auf „NOLOGGING“ stehen als auch eine entsprechend gekennzeichnete Operation (siehe Aufzählung oben) verwendet werden.

Beispiele:

-- Erstellen einer Tabelle mit minimalem Redo
CREATE TABLE lognix
NOLOGGING
AS SELECT * FROM emp;

-- Die Tabelle ist jetzt im Nologging-Modus, damit kann auch ein
-- Insert mit minimalem Redo durchgeführt werden:
INSERT /*+ APPEND */ INTO lognix
SELECT * FROM emp;

-- Erstellen eines Indizes mit minimalem Redo
CREATE INDEX lognix_ix1 ON lognix(empno) NOLOGGING;

Gefahren bei Backup/Recovery:

In Zeiten vor Oracle 8i gab es eine vergleichbare Methode, die Option „UNRECOVERABLE“. Dieses Wort („nicht wiederherstellbar“) beschreibt eigentlich schon genau, welche Probleme auf den Betreiber der Datenbank zukommen, sollten die so einmal beladenen Daten wiederhergestellt werden müssen: Es wird nämlich in vielen Fällen nicht gehen.

„NOLOGGING“ sagt aus, daß die Änderungen an Datenblöcken nicht in die Redo Logs und damit auch nicht in die Archive Logs geschrieben werden. Das spart natürlich I/O und verkürzt dadurch die Laufzeit von Schreiboperationen. Andererseits ist eine Wiederherstellung von Daten bis zum Zeitpunkt eines Crashs (Point-in-time-Recovery) nicht mehr möglich, da diese auf den Archive Logs beruht.

Ausgehend von einem Full Backup ist die Rücksicherung der Daten zunächst möglich. Werden aber nun die Archive Logs appliziert, kann es beim Zugriff auf Datenblöcke, die zuvor mit einer Nologging-Operation verändert wurden, zu folgenden Fehlermeldungen kommen (Beispiel):

ORA-01578: ORACLE data block corrupted (file # 31, block # 148044)
ORA-01110: data file 31: '/oradata/ORCL/dupl/31_PB_EMAILACC.DBF'
ORA-26040: Data block was loaded using the NOLOGGING option

Daten, die in diesen Blöcken gespeichert waren, sind ab dem Zeitpunkt der ersten Beladung ohne Logging nach dem letzten Full Backup unwiederbringlich verloren! Die Oracle-Doku empfiehlt das Löschen des betroffenen Objektes, allerdings gibt es auch noch Kniffe, um wenigstens die Daten aus nicht korrupten Blöcken zu retten. Dies wird in einem künftigen Teil 2 besprochen.

Wie kann ich herausfinden, ob meine Datenbank betroffen ist? Wie identifiziere und bereinige ich die betroffenen Objekte?

Um herauszufinden, ob es überhaupt Objekte in der Datenbank gibt, bei denen das Attribut NOLOGGING gesetzt ist, reicht eine einfache Abfrage (als DBA oder mit dem Recht „SELECT ANY DICTIONARY“):

SELECT table_name, owner
FROM dba_tables
WHERE logging = 'NO'
AND temporary = 'N'
ORDER BY owner, table_name;

Das folgende SQL listet die Objekte, deren Besitzer und Tablespace auf, die bereits ohne Redo verändert wurden. Die Spalten für die Zeit und SCN der letzten NOLOGGING-Operation geben dabei an, wann zuletzt auf diese Weise auf das Objekt zugegriffen wurde. Spätestens ab diesem Zeitpunkt ist dann bei einem Recovery mit Datenverlusten in diesem Objekt zu rechnen.

SELECT DISTINCT u.table_name, u.owner, t.NAME as tablespace
, d.unrecoverable_time, d.unrecoverable_change#
FROM v$datafile d, v$tablespace t, dba_tables u
WHERE d.ts# = t.ts#
AND t.NAME NOT IN
( 'SYSTEM' -- hier können beliebige, irrelevante
, 'SYSAUX' -- Tablespaces aufgeführt werden
, 'TEMP'
, 'UNDO'
)
AND d.unrecoverable_time IS NOT NULL
AND t.name = u.tablespace_name
AND u.logging = 'NO'
ORDER BY TO_CHAR( d.unrecoverable_time, 'yyyymmdd' ) DESC, 2, 1;

Mit dieser Liste kann dann ein Aktionsplan gemacht werden, der für die gefundenen Objekte folgende, verschiedene Aktionen vorsehen kann:

• Umstellen des Objekts auf LOGGING, anschließend Full Backup
• Umstellen des Objekts auf FORCE LOGGING (s.u.), anschließend Full Backup
• Belassen der Einstellung und evt. Anpassung des Recovery-Konzepts

Wie und wo kann man NOLOGGING gefahrlos verwenden?

Eines kurz vorneweg: Bei der Verwendung einer Standby Database sollte diese Option gar nicht verwendet werden. Der Umgang mit Nologging-Problemen bei Standby-Datenbanken wird in einem künftigen Artikel behandelt.

Zunächst sollte anwendungsspezifisch bestimmt werden, ob eine Online- oder Offline-Sicherung einzusetzen ist. Hier empfiehlt es sich, den Kunden / das Management in die Entscheidung einzubinden, klar die Vor- und Nachteile beider Szenarien darzustellen (Frage: Braucht ein Data Mart ein Online-Backup?) und schriftlich festzuhalten, welches Szenario gewählt wird.

Im Falle einer Online-Sicherung sollten alle Tabellen bis auf verzichtbare, temporäre Objekte in der Regel im Modus „Logging“ sein, um ein vollständiges Recovery zu gewährleisten. Tabellen im Nologging-Modus sollten am besten in separaten Tablespaces angelegt werden. Das gleiche gilt für Indizes, wobei hier abgewogen werden sollte, ob nach einem Recovery auch noch die Zeit beansprucht werden kann, um mit „Nologging“ angelegte Indizes wieder neu aufzubauen. Dieser Neuaufbau ist dann aber für alle betroffenen Indizes und Tabellen in das Recovery-Konzept mit aufzunehmen.

Auch für die bewusste Verwendung von NOLOGGING für ausgewählte Objekte sollte am besten eine schriftliche Vereinbarung getroffen werden, der der Kunde / das Management zustimmen muss. Die Verlängerung der durchschnittlichen Dauer einer Wiederherstellung (MTTR) sollte in dieser Vereinbarung den Vorteilen des Performancegewinns gegenübergestellt werden. Dies schützt den DBA vor der „Vergesslichkeit“ derjenigen, die bei einem späteren Datenverlust nach einem Schuldigen für das Debakel suchen.

Wenn keine NOLOGGING-Operationen erwünscht sind, kann die Datenbank komplett dagegen gesperrt werden. Entsprechendes SQL läuft dann fehlerfrei, aber mit Logging.

Wenn es bewusst gewählte Objekte für NOLOGGING gibt, dann ist es gute Praxis, alle Tablespaces, in denen keine solchen Objekte liegen, mit FORCE LOGGING zu schützen. Ansonsten kann diese Sperre auch auf Objektebene gesetzt werden.

Hier ein paar Beispiele:

-- Logging für die gesamte DB erzwingen
-- Pflicht bei Einsatz von Standby Databases!
ALTER DATABASE FORCE LOGGING;

-- Logging auf Tablespace-Ebene erzwingen:
ALTER TABLESPACE meintablespace FORCE LOGGING;

-- Logging für einzelne Objekte erzwingen:
[CREATE | ALTER] TABLE meinetabelle FORCE LOGGING;

Und zu guter Letzt sollte das Recovery-Konzept getestet werden. Dies natürlich grundsätzlich, aber eben auch nach bewusster Anwendung von Nologging-Operationen.

Weblinks

• Metalink Note 290161.1 „The Gains And Pains Of Nologging Operations“ (Konto bei Oracle Support erforderlich)
• Ask Tom: Frage und Überblick zu Nologging (Englisch)
• Ask Tom: „Nologging does not work (yes it does…)“
• Wer es ganz genau wissen will und des Englischen mächtig ist, erfährt in diesem Artikel von Riyaj Shamsudeen und dem dort angehängten Dokument viele Einzelheiten.

Es ist anwendungsspezifisch zu bestimmen, ob eine Online- oder Offline-Sicherung einzusetzen ist. Die Entscheidung für zu verwendende Backuptools ist durch den Betrieb zu treffen (vgl. Kp. 3.1.10). Im Falle einer Online-Sicherung sollen alle Tabellen bis auf verzichtbare, temporäre Objekte in der Regel im Modus „Logging“ sein, um ein vollständiges Recovery zu gewährleisten. Tabellen im Nologging-Modus sollen in separaten Tablespaces angelegt werden. Das gleiche gilt für Indizes, wobei hier abgewogen werden kann, ob nach einem Recovery auch noch die Zeit beansprucht werden kann, um mit „Nologging“ angelegte Indizes wieder neu aufzubauen. Dieser Neuaufbau ist dann aber für alle betroffenen Indizes in das Recovery-Konzept mit aufzunehmen