Home > Oracle Datenbank > ORA-01555 und LOBs

ORA-01555 und LOBs

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 (PCTVERSION): Es wird festgelegt wie lange alte Versionen aufeghoben werden sollen. Dieser Wert wird aus dem Parameter UNDO_RETENTION der Datenbank abgeleitet.
  • Speicher-basierte Retention (RETENTION): 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_005 und C_006 die Speicher-basierte Retention und das LOB für C_007 die Zeit-basierte. Allerdings fällt auf, dass bei C_007 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