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 = 'NO'
 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

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
About these ads

8 Gedanken zu „Risiken bei der Verwendung von NOLOGGING

  1. LukasG

    Grundsätzlich ein interessanter Artikel. Danke!

    Der Abschnitt ‘Wie und wo kann man NOLOGGING gefahrlos verwenden?’ enttäuscht aber etwas. Die Frage ist IMHO nicht beantwortet. Mich würde z.B. die Bewertung der Nutzung von NOLOGGING für Indexe interessieren. (Ich fange gerade an mich mit diesem Thema zu beschäftigen und kam auf der Suche nach einer Antwort zu eben dieser Frage auf diesen Artikel.)

    Ist LOGGING für einen Index notwendig? Es macht das Recovery sicherlich einfacher. Aber ich kann den Index ja auch einfach neu bauen. Wie sieht ein NOLOGGING-Index nach einem Recovery aus?

    Dann habe ich noch einen Verbesserungsvorschlag: Beim Suchen nach betroffenen Tabellen sollten temporäre ausgeschlossen werden, oder nicht?
    SELECT table_name, owner
    FROM dba_tables
    WHERE logging = ‘NO’
    AND temporary = ‘NO’
    ORDER BY owner, table_name;

    Gruß,
    Lukas

    Gefällt mir

    Antwort
    1. SQLCulix

      Hallo Lukas,
      nach der Info und der Doku die ich kenne, werden bei Insert-, Update-, Delete-Operationen Einträge in den Tabellen und natürlich auch in den Index(-Tabellen), zugehörig zu der Tabelle/Spalte auf die die Indexe angelegt wurde.
      Wenn dort das Nologging aktiviert ist werden diese Aktionen wie oben im Artikel beschrieben
      nicht in die Redo-Logs und damit auch nicht in die Archive-Redo-Logs.
      Wenn der Datennankserver dann ein Recovery erfährt und Du die Datenbank mit
      Hilfe der Archive-Redo-Logs wieder herstellen musst, wird Dir die Meldung siehe im Artikel angezeigt. Aber eine Lösung ist im Artikel auch genannt. (Recovery-Konzept anpassen!)
      Was im Artikel nicht benannt ist, ich aber als nicht zu vernachlässigenden Faktor ansehe, das selbst wenn das im Recovery-Konzept bedacht ist.
      Sie wollen / müssen die Indexe auf dem System wieder herstellen, weil Sie die Option Nologging gewählt haben. Wenn ich jetzt unsere Datenbanken sehe und eine Tablle mit ca. 15 Indexen sehe, brauche ich für die Erstellung dieser Indexe Zeit! Und im Recovery Fall müssen Sie so schnell wie möglich wieder Online sein! Da kann es sein das die Erstellung der Indexe 3 – 4 Stunden zusätzlich einnimmt. Als zweiter Faktor kommt dazu das Sie den Index und die dazugehörige Datentabelle Analysieren müssen, damit der CBO Ihnen die korrekten Ausfhrungspläne liefert. Auch das kostet noch einmal Zeit.
      Wie gesagt diese ganzen Punkte sollten Sie bei dem Backup-Konzept beachten.
      Und dann kann man das Logging / Nologging auch korrekt bei Oracle einsetzen.

      Viele Grüße SQLCulix

      Gefällt mir

      Antwort
      1. oraculix Autor

        Hallo SQLCulix,
        Deine ersten beiden Sätze verstehe ich nicht ganz. Um Missverständnissen vorzubeugen: Nologging betrifft *nicht* Updates und Deletes! Es betrifft nicht einmal das gewöhnliche Insert, sondern nur ein Insert in Verbindung mit dem APPEND-Hint. Hinter meinem ersten Link auf “Ask Tom” liegt eine Tabelle, in der eine kurze Aufstellung der möglichen Konstellationen mit und ohne Logging steht.

        Technisch betrachtet kommt Nologging nur zum Tragen, wenn ein LEERER Datenblock ERSTMALIG beladen wird; wird ein bereits befüllter Datenblock geändert (Update, Delete, “normales” Insert), dann wird Redo generiert.

        Ansonsten habe ich meiner Meinung nach schon benannt, daß die Zeit zur Wiederherstellung (kurz: MTTR) ein Thema ist. Und da pflichte ich Deinem Beispiel bei: Was bringt es mir, wenn ich bei Zurückspielen des Backups etwas Zeit spare, dafür aber möglicherweise stundenlange Beladungen und Index Rebuilds fahren muss?
        Das lässt sich natürlich nicht pauschal beantworten, schließlich kann die Zeitersparnis beim Lauf der Anwendung und beim Erzeugen des Backups schon signifikant sein. Auch kann sich die Platzersparnis beim Backup finanziell auswirken, besonders, wenn Backups nicht auf Band sondern auf Platte gemacht werden. Gut gesicherter und gewarteter Plattenplatz ist auch heute noch ein deutlicher Kostenfaktor.
        Hier wollte ich bewusst dem geneigten Leser nicht das Denken abnehmen… ;-)
        Viele Grüße, Uwe

        Gefällt mir

      2. SQLCulix

        Hallo Uwe,
        vielen Dank für Deine Infos. Wie gesagt nach meinem “kleinen” bescheidenen Wissen in Bezug auf die Logging Funktion –> nach meinem Wissen wird, wenn eine neue Datenzeile in einer Tabelle eingefügt wird, wird auch eine neue ROWID gebildet. Damit werden auch bei einem “normalen Insert” Datensätze in die Indextabellen eingetragen.
        Und zwar die ROWID und weitere Verwaltungsdaten. Beim Delete sieht es genau so aus, das die Datenzeile gelöscht wird und damit auch die Datenzeile(n)
        (Verwaltungsdaten + ROWID).
        Das ist auch der Grund warum man in regelmässigen Abständen den Index reorganisiern muss. Und sobald man Datenzeilen in eine Tabelle einfügt oder löscht wirden entsprechend REDO/UNDO -Daten erzeugt. Und meine Vermutung (wie gesagt nur “angelesenes Wissen” nach) wenn NOLOGGIN verwendet wird, werden auch kein Redo-Daten in die Redo-Log / Archiv-Log geschrieben.
        Aber gerne nehme ich Deinen Hinweis auf und schaue bei ASK*TOM nach um weiter Infos zu bekommen. Vielen Dank für diesen Hinweis :)

        Zu den Backups das muss man wirklich abschätzen und ist für jede Firma anders.
        Man kann es noch in einer Nacht schaffen eine 1TB Datenbank zu sichern. (Backup to Disk) Wenn man Backup to Tape nutzt kommt es auf die Hardware an… Und man sollte beachten das im Recovery-Fall die Daten erst vom Band zurück kopiert werden müssen und dann erst Recovert werden können. Da ist man je nach Datenbankgrösse schnell 1 – 2 Tage weiter….
        Vielen Dank für Euren Input.
        Viele Grüße SQLCulix

        Gefällt mir

      3. oraculix Autor

        Hallo SQLCulix,
        uiii – ganz andere Baustelle… die Indizes, meine ich. Da möchte ich jetzt nicht zu weit vom Thema abkommen, aber: Ja, bei Änderungen an einer Tabellenzeile werden die indizierten Spalten sowie die zugehörige ROWID eingetragen oder angepasst. Und dafür wird Redo generiert, mit einer Ausnahme: “CREATE index … nologging”.

        Was ein regelmäßiges Rebuild von Indizes angeht, da bin ich vor geraumer Zeit vom Gegenteil überzeugt worden. Aber das ist wirklich eine andere Baustelle – und für mich eine Anregung für einen neuen Artikel…
        Auf Englisch wurde zu dem Thema schon sehr viel und feurig diskutiert, natürlich auch wieder bei Onkel Tom: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:35336203098853

        Viele Grüße, Uwe.

        Gefällt mir

    2. oraculix Autor

      Hallo Lukas,
      zunächst einmal vielen Dank für die konstruktive Kritik – auch wenn die Vokabel “enttäuscht” bei mir etwas hart rüberkommt. Aber ich freue mich über Beiträge, die mir helfen, meine Artikel noch mehr an den Lesern zu orientieren und im Zuge dessen auch selbst etwas dazu zu lernen. Daher werde ich in Kürze das Thema “Indizes” noch mit in den letzten Abschnitt einbauen.
      Vorab aber schon mal im Rahmen eines Kommentars:

      1. Ob Logging für einen Index notwendig ist, muss ebenfalls im Rahmen eines Recovery-Konzeptes überlegt werden. Grundsätzlich bringt Nologging nur wirklich etwas, wenn Indizes *komplett neu* mit der Nologging-Option aufgebaut werden. Im laufenden Betrieb, also bei Inserts (auch mit APPEND-Hint), wird in jedem Fall Logging betrieben. Es würde den Rahmen dieses Kommentars sprengen, aber einen nachvollziehbaren Testfall kann ich zum Beweis gerne auf Anfrage hier posten.
      Meine grobe Richtschnur in der Praxis: Darf die Zeit bis zum Recovery etwas länger sein (wie das oben SQLCulix beschreibt), dann kann man Nologging auch für Indizes verwenden und im Rahmen des Recoverys dann REBUILDs fahren. Persönlich halte ich es für besser, nach dem Aufbau eines Indizes mit NOLOGGING diesen wieder auf LOGGING zu stellen und dann eine neue Backup-Generation zu beginnen.

      2. Zu Deiner Anregung mit den temporären Tabellen: Eine gute Idee, die in der Abfrage explizit mit auszuschließen. Möglicherweise liegt hier aber ein Mißverständnis vor: Mit der Formulierung “temporäre Objekte” in meinem Text meinte ich z.B. Tabellen, die als sog. Staging Area benutzt werden, also nicht unbedingt “global temporary tables”.
      Aber wo wir schon bei diesem Thema sind: Global Temporary Tables sind per se (jedenfalls in Oracle 10g) im Nologging-Modus – was auch sinnvoll erscheint, schließlich ist ein Recovery dieser Objekte ohnehin nicht möglich.

      Viele Grüße, Uwe

      Gefällt mir

      Antwort
  2. Rumburak

    Ja, ich denke auch, daß man NOLOGGING so pauschal nicht ausschließen sollte. Man muß eben nur wissen, welche Auswirkungen es hat.

    Wie oben beschrieben, Indizes kann man mit einem REBUILD wieder aufbauen. Mat.Views ebenfalls, so wie Tabellen, deren Inhalte über Programmlogik aus anderen Tabellen befüllt werden (ETL).

    Wenn die Anwendung mit einem Ausfall der Datenbank leben kann und die Daten komplett durch Programmlogik (ETL, Select) wiederhergestellt werden können, dann kann man auch NOLOGGING einsetzen: “Ok, ist kaputt, verschieben wir die Auswertung eben auf morgen, bis die IT-Abteilung das Schema neu aufgebaut hat”

    Gruß,
    Rumburak

    Gefällt mir

    Antwort

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ photo

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s