Solution to: ORA-1555/ORA-22924 on export with a LOB data 

May occur when accessing LOB columns, even when the LOB RETENTION seems to be sufficient.

This may occur when LOB column resides in a MSSM (Manual Segment Space Management) tablespace.
But if no, because is AUTO, maybe undo_retention not good? no, LOBS no use that parameter and no use undo tablespace! more….

In the past, one would use the PCTVERSION storage parameter for their LOB segments to reserve a percentage of storage space for read consistency of LOB segments. In Oracle 11g, you can now use the RETENTION parameter. The RETENTION parameter will use the UNDO_RETENTION parameter
for determining how long to keep LOB data for read-consistency purposes. But be advised that it does  not use the Undo tablespace! The LOB segment’s tablespace is used for read-consistency purposes.

note: pctversion is like “undo retention”. specifies how much of the old stuff to keep around for subsequent reads of the data.

When you change the UNDO_RETENTION parameter, the LOB segment’s retention value is not modified.
If you query the RETENTION column of the DBA_LOBS view, you will notice the old UNDO_RETENTION value still remains after you have modified the UNDO_RETENTION parameter.
To change the LOB segment’s RETENTION value to match the new UNDO_RETENTION value, momentarily changing the LOB to use PCTVERSION and then back to RETENTION, the new value of UNDO_RETENTION
will be used. You need to do this for all LOB segments that you intend to modify.
ALTER TABLE tablename MODIFY LOB (lob_col) (PCTVERSION 20);
ALTER TABLE tablename MODIFY LOB (lob_col) (RETENTION);

EXAMPLE:

other day we have a problem in a export:

Procesando el tipo de objeto DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
Procesando el tipo de objeto DATABASE_EXPORT/AUDIT
ORA-31693: Fallo del objeto de datos de tabla “LGTWEB”.”LGTW_REQUEST_FILE” al cargarse/descargarse y se está saltando debdo al error:
ORA-02354: error al exportar/importar datos
ORA-01555: instantánea demasiado antigua: número de segmento de rollback con nombre “” demasiado pequeño
ORA-22924: instantánea demasiado antigua
La tabla maestra “SYS”.”SYS_EXPORT_FULL_12″ se ha cargado/descargado correctamente
Checking table:

SQL> desc LGTWEB.LGTW_REQUEST_FILE
Nombre ¿Nulo? Tipo
—————————————– ——– —————————-
REQUESTFILEID NOT NULL NUMBER(10)
BINARY_CONTENT NOT NULL BLOB
COURIER_CERTIFICATE BLOB
LOGALTY_CERTIFICATE BLOB
XML_CERTIFICATE RAW(255)
XML_REQUEST RAW(255)
XML_REQUEST_SIGNED RAW(255)
REQUESTC_ID NUMBER(10)
NOTICE_REPORT BLOB
LOGALTY_PACK_DOC BLOB
LOGALTY_REF_DOC BLOB
checking retention:
SQL> select COLUMN_NAME,pctversion, retention from dba_lobs
where table_name = ‘LGTW_REQUEST_FILE’ and OWNER = ‘LGTWEB’;

COLUMN_NAME PCTVERSION RETENTION
——————- ———- ———-
BINARY_CONTENT 900
COURIER_CERTIFICATE 900
LOGALTY_CERTIFICATE 900
NOTICE_REPORT 900
LOGALTY_PACK_DOC 3200
LOGALTY_REF_DOC 3200
Our undo_retention=9000, we set this to undo_retention=45000

ALTER SYSTEM SET undo_retention=45000 SCOPE=BOTH SID=’*’;

If wer check again dba_lobs for our table RETENTION values show same
value, for give RETENTION the undo_retenetion we run this:

alter table LGTWEB.LGTW_REQUEST_FILE modify lob (BINARY_CONTENT) (pctversion 20);
alter table LGTWEB.LGTW_REQUEST_FILE modify lob (BINARY_CONTENT) (RETENTION);
alter table LGTWEB.LGTW_REQUEST_FILE modify lob (COURIER_CERTIFICATE) (pctversion 20);
alter table LGTWEB.LGTW_REQUEST_FILE modify lob (COURIER_CERTIFICATE) (RETENTION);
alter table LGTWEB.LGTW_REQUEST_FILE modify lob (LOGALTY_CERTIFICATE) (pctversion 20);
alter table LGTWEB.LGTW_REQUEST_FILE modify lob (LOGALTY_CERTIFICATE) (RETENTION);
alter table LGTWEB.LGTW_REQUEST_FILE modify lob (NOTICE_REPORT) (pctversion 20);
alter table LGTWEB.LGTW_REQUEST_FILE modify lob (NOTICE_REPORT) (RETENTION);
alter table LGTWEB.LGTW_REQUEST_FILE modify lob (LOGALTY_PACK_DOC) (pctversion 20);
alter table LGTWEB.LGTW_REQUEST_FILE modify lob (LOGALTY_PACK_DOC) (RETENTION);
alter table LGTWEB.LGTW_REQUEST_FILE modify lob (LOGALTY_REF_DOC) (pctversion 20);
alter table LGTWEB.LGTW_REQUEST_FILE modify lob (LOGALTY_REF_DOC) (RETENTION);
Another solution could be configure the pctversion temporarily to 100 that you would get your export done (that way no old LOB chunk is overwritten and your LOB segments will constantly grow due DML).

Note that the RETENTION doesn’t always work that well for LOBs under heavy
DML activity. It’s also said in Oracle docs:

http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/undo002.htm
Note:
Automatic tuning of undo retention is not supported for LOBs. This is
because undo information for LOBs is stored in the segment itself and not in
the undo tablespace. For LOBs, the database attempts to honor the minimum
undo retention period specified by UNDO_RETENTION. However, if space becomes low, unexpired LOB undo information may be overwritten.

Advertisements

Comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s