Thursday 27 June 2013

ORA-22924: snapshot too old

ORA-22924: snapshot too old ( While Exporting LOBs)

I have migration projects to migrate one particular Schema from 10g(Windows) to 11g(Linux). And the schema size was 100gb as one of the table got LOB Columns. But while exporting this schema to 10g datapump utility , we keep hitting following errors. The investigation result requires modification in database.

ORA-31693: Table data object "HPADMIN"."T7751" 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 

I instantly checked the undo tablespace whether it was undersized or not. It was set to "auto extend on" as expected. Then I checked out the undo retention parameter-

SQL> show parameter undo_retention

It was set to default 900. I was thinking to modify it but was not so sure as oracle supporting documents were not available at hand.

Later, when I  found that the error comes for the LOB segment undo problem. When data is modified, oracle keeps a version of the data in undo segment for read consistency.

I had two options-

1. Increase PCTVERSION in LOB table columns
2. Increase RETENTION in LOB table columns

1. PCTVERSION is the percentage of all used LOB data space that can be occupied by old versions of LOB data pages. As soon as old versions of LOB data pages start to occupy more than the PCTVERSION amount of used LOB space, Oracle Database tries to reclaim the old versions and reuse them. In other words, PCTVERSION is the percent of used LOB data blocks that is available for versioning old LOB data.

PCTVERSION has a default of 10 (%), a minimum of 0, and a maximum of 100. Setting PCTVERSION to twice the default value allows more free pages to be used for old versions of data pages. Because large queries may require consistent reads of LOB columns, it may be useful to retain old versions of LOB pages. In this case, LOB storage may grow because the database will not reuse free pages aggressively. PCTVERSION=0; the space allocated for older versions of LOB data in LOBSEGMENTS can be reused by other transactions and can cause "snapshot too old" errors.

2. As an alternative to the PCTVERSION parameter, one can specify the RETENTION in CREATE TABLE or ALTER TABLE statement. Doing so, configures the LOB column to store old versions of LOB data for a parameter in the LOB storage clause of the period of time, rather than using a percentage of the table space.

DBA Querty to check LOB Parameters

select column_name, nvl(retention, 0) retention, pctversion from dba_lobs where table_name = 'TABLE_NAME';

I have planned to go with option 2 -

ALTER SYSTEM SET UNDO_RETENTION=2700 scope=both;
ALTER TABLE TABLE_NAME MODIFY LOB (COLUMN_NAME) (RETENTION);

The LOB RETENTION will take value from UNDO_RETENTION parameter.

But it didn't work, And end up with same error. So I had to go with Option 1.

ALTER TABLE TABLE_NAME MODIFY LOB (COLUMN_NAME) (PCTVERSION 20);

and guess what  ??  it works !!



Summary -
Cause: The version of the LOB value needed for the consistent read was already overwritten by another writer.
Action: Use a larger version pool/retention time.

One very important article of Tom Kyte -

2 comments:

sguinales said...

When u set the lob use undo_retention if un change later undo_retention the lob never upgrade the new time u must reconfigure again, seting first pctversion and next again set RETENTION in the lob again.

sguinales said...

In ur configuration, LOB retention no take ur new UNDO_RETENTION=2700, still in default first value, 900.

Do what i said in my first post and value change.

check value:

select COLUMN_NAME,pctversion, retention from dba_lobs
where table_name = '[TABLE_NAME]' and OWNER = '[OWNER]';