ORA-01555 Snapshot too old obwohl UNDO_RETENTION und UNDO Tablespace ausreichend?
Kurze Einleitung
Seit der Einführung des Automatischen Undo Managements ist ein ORA-01555 Snapshot too old wohl eine Seltenheit, speziell wenn man zusätzlich die UNDO_RETENTION manuell auf einen für die Datenbank/Applikation passenden Wert gesetzt hat und der UNDO Tablespace auch groß genug ist.
Abgesehen von Eigentoren von Entwicklern, die immer noch den Fehler provozieren können, ist dieser praktisch ausgestorben. Praktisch, aber noch nicht ganz! Es gibt ja noch die LOBs, die Ihre eigenes, internes UNDO Management nutzen!
Das Problem im Detail
Automatic UNDO Management hat zwar viele der Gründe für den Fehler zum Verschwinden gebracht, aber es passiert immer noch. Am einfachsten läßt sich dieser Aspekt bei einem Datapump Export betrachten.
Nehmen wir an, Sie wollen ein Schema einer Datenbank exportieren und das Schema ist auch nicht sonderlich groß. Sie wissen dass der Export etwa eine Stunde dauern wird, und lassen den Export zu einer Zeit laufen, in der auf der Datenbank wenig los ist.
Sie setzen also zur Sicherheit den Wert der UNDO_RETENTION auf 2 Stunden (UNDO_RETENTION = 7200), und sehen, dass der UNDO Tablespace 300 GB groß werden kann, aber in den letzten Monaten nie mehr als 10 GB davon verwendet wurden.
Der Export startet und läuft auch gut an, und sie finden auch den Export Dump mit seinen 20 GB im entsprechenden Directory. Ein Blick in die Logdatei zeigt allerdings:
Processing object type DATABASE_EXPORT/SCHEMA/REFRESH_GROUP
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
. . exported "DBM"."T_TEST_1" 307.6 MB 213030 rows
. . exported "DBM"."T_TEST_2" 643.1 MB 134956 rows
. . exported "DBM"."T_TEST_3" 1.015 GB 208286 rows
ORA-31693: Table data object "DBM"."T_TEST_4" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-01555: snapshot too old: rollback segment number with name "" too small
ORA-22924: snapshot too old
. . exported "DBM"."T_TEST_5" 5.663 MB 7512 rows
. . exported "DBM"."T_TEST_6" 2.158 GB 303115 rows
...
Was ist da passiert?
Sie überprüfen nocheinmal die Einstellungen:
-
UNDO_RETENTION=7200
-
UNDO Tablespace Dateien sind insgesamt nur 10 GB groß
Außerdem stellen Sie fest, zum Zeitpunkt des Exports waren nur 14 Sessions aktiv, die betroffene Tabelle war nur von wenigen DMLs betroffen, insgesamt gab es etwa 200 MB an Änderungen in der Tabelle.
Werfen wir noch einmal einen Blick auf die Fehlermeldung. Vor allem die Zeile
ORA-01555: snapshot too old: rollback segment number with name "" too small
deutet schon an, dass hier etwas anders ist. Bitte beachten Sie: Ein leerer Wert für das Rollback Segment? Wird hier vielleicht nicht der UNDO Tablespace verwendet?
Werfen wir zuerst einen Blick auf die Tabelle:
SQL> desc DBM.T_TEST_4
Name Null? Type
--------------------------------- -------- ----------------------------
C_001 NOT NULL VARCHAR2(15)
C_002 VARCHAR2(254)
C_003 NOT NULL NUMBER(15)
C_004 VARCHAR2(254)
C_005 CLOB
C_006 CLOB
C_007 CLOB
...
Sieht man sich die Datentypen der Spalten an, fällt auf, dass neben varchar2 und number Spalten auch CLOB Spalten auftauchen. Kann es damit etwas zu tun haben?
Ein Blick in das Oracle White Paper (LOB Performance Guidelines) fördert folgendes etwas verklausuliert zu Tage:
Consistent Reads on LOBs: RETENTION and PCTVERSION
Consistent Reads (CR) on LOBs uses a different mechanism than that used for other data blocks in Oracle.
...
Vereinfacht gesprochen (bitte unbedingt die Anmerkung am Ende dazu beachten) verwenden LOBs also nicht den UNDO Tablespace um Read-Consistency sicherzustellen. Stattdessen werden alte Versionen von LOBs im LOB Segment selbst aufgehoben. Das kann auf zwei Arten geschehen:
-
Zeit-basierte Retention (RETENTION): Es wird festgelegt wie lange alte Versionen aufeghoben werden sollen. Dieser Wert wird aus dem Parameter UNDO_RETENTION der Datenbank abgeleitet.
-
Speicher-basierte Retention (PCTVERSION): Es wird festegelegt wie viele Prozent des Segments für alte Versionen der LOBs reserviert wird.
Wobei zu beachten ist, dass die beiden Arten sich gegenseitig ausschließen, aber für jedes LOB (auch in einer Tabelle) separat gesetzt werden können.
Diese Information ist in der View DBA_LOBS bzw. USER_LOBS sichtbar:
select owner
, table_name
, column_name
, segment_name
, index_name
, pctversion
, retention
from dba_lobs
where segment_name in (
,'T_TEST_4'
,'SYS_LOB0001905680C00092$$'
,'SYS_LOB0003449277C00075$$'
,'SYS_LOB0002683171C00013$$'
)
order by 1,2;
OWNER TABLE_NAME COLUMN_NAME SEGMENT_NAME INDEX_NAME PCTVERSION RETENTION
------ ------------ ------------ ---------------------------- ---------------------------- ---------- ----------
DBM T_TEST_4 C_005 SYS_LOB0001905680C00092$$ SYS_IL0001905680C00092$$ 900
DBM T_TEST_4 C_006 SYS_LOB0003449277C00075$$ SYS_IL0003449277C00075$$ 10
DBM T_TEST_4 C_007 SYS_LOB0002683171C00013$$ SYS_IL0002683171C00013$$ 10
Wie man sieht, kann man das für jedes LOB separat einstellen. Im obigen Fall verwenden die LOBS für C_006 und C_007 die Speicher-basierte Retention und das LOB für C_005 die Zeit-basierte. Allerdings fällt auf, dass bei C_005 die Retention 900 beträgt, obwohl wir ja diesen Wert auf 7200 gesetzt haben!
Woher kommt das?
Kann es sein, dass dieser Wert statisch ist?
Machen wir einen Versuch:
SQL> create table t_test_10 (c_001 clob);
Table created.
SQL> select table_name
2 , column_name
3 , segment_name
4 , index_name
5 , pctversion
6 , retention
7 from user_lobs
8 order by 1,2;
TABLE_NAME COLUMN_NAME SEGMENT_NAME INDEX_NAME PCTVERSION RETENTION
----------- ------------ --------------------------- ------------------------ ---------- ----------
T_TEST_10 C_001 SYS_LOB0003836930C00001$$ SYS_IL0003836930C00001$$ 7200
SQL> conn / as sysdba
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 7200
undo_tablespace string UNDOTBS1
Wie man sieht wird der Wert beim Anlegen des LOBs die RETENTION aus dem Parameter UNDO_RETENTION abgeleitet, wenn die Tabelle und das LOB in einem ASSM Tablespace liegen.
Nun ändern wir die UNDO_RETENTION:
SQL> alter system set undo_retention = 8000;
System altered.
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 8000
undo_tablespace string UNDOTBS1
und schauen uns nochmal das LOB an:
SQL> select table_name
2 , column_name
3 , segment_name
4 , index_name
5 , pctversion
6 , retention
7 from user_lobs
8 order by 1,2;
TABLE_NAME COLUMN_NAME SEGMENT_NAME INDEX_NAME PCTVERSION RETENTION
----------- ------------ --------------------------- ------------------------ ---------- ----------
T_TEST_10 C_001 SYS_LOB0003836930C00001$$ SYS_IL0003836930C00001$$ 7200
Kann man die RETENTION trotzdem ändern? Ja!
Und das geht so:
SQL> alter table t_test_10 modify lob (c_001) (pctversion 10);
SQL> alter table t_test_10 modify lob (c_001) (retention);
SQL> select table_name
2 , column_name
3 , segment_name
4 , index_name
5 , pctversion
6 , retention
7 from user_lobs
8 order by 1,2;
TABLE_NAME COLUMN_NAME SEGMENT_NAME INDEX_NAME PCTVERSION RETENTION
----------- ------------ --------------------------- ------------------------ ---------- ----------
T_TEST_10 C_001 SYS_LOB0003836930C00001$$ SYS_IL0003836930C00001$$ 8000
Man kann den Wert also ändern, allerdings geschieht das nicht automatisch, wenn man die UNDO_RETENTION der Datenbank verändert!
Conclusio:
Wenn man größere Bulk-Operationen auf Tabellen mit LOBs vorhat, ist es unbedingt notwendig, nicht nur die UNDO_RETENTION der Datenbank und den UNDO Tablespace zu vergrößern, sondern auch die betroffenen LOBs zu überprüfen und gegebenenfalls die RETENTION bzw. PCTVERSION entsprechend anzupasssen.
Das UNDO Verhalten der LOBs
Der Einfachheit halber haben wir hier nur Basicfile LOBs betrachtet.
LOBs bestehen immer aus einem LOB Segment mit den eigentlichen Daten und einem LOB INDEX. Änderungen am LOB INDEX generieren sehr wohl UNDO im UNDO Tablespace. Nur die LOB Segmente selbst sorgen entsprechend der Konfiguration für die Read-Consistency und das UNDO Management.
Weitere Informationen dazu finden sich im
-
Oracle Database SecureFiles and Large Objects Developer's Guide
-
Oracle White Paper: LOB Performance Guidelines