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 -

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:275215756923

http://www.oracle.com/technetwork/articles/lob-performance-guidelines-128437.pdf

I was tasked to move an entire small database from 1 server to another and finish the task ASAP. The reason was the site will be down until the task is completed. A quick test of exporting, coping files to remote server then importing had the following numbers:

Export expdp with parallel = 16, generating 1 file only: 13 mins Copying 12 GB to remote server: 10 mins Import with parallel = 16: 45 mins Total Time: 1 hour and 8 minutes. Way too much for a downtime in our environment.

Here are the commands we initially used:

expdp username/password directory=dump_dir dumpfile=full.dmp logfile=full.log full=y parallel=16

impdp username/password directory=dump_dir dumpfile=full.dmp logfile=full.log parallel=16

In our attempt to tune this process, we introduced exporting in parallel to multiple files and the results were amazing

expdp username/password directory=dump_dir filesize=1Gdumpfile=full%U.dmp logfile=fulllog parallel=16

Execution time: about 2 minutes

We figured the import will be improve significantly as well, but we were a bit disappointed

impdp username/password directory=dump_dir dumpfile=full%U.dmp logfile=full.log parallel=16

Execution time: about 30 minutes. Better, but not as expected improvement.

We noticed that during import operation, the process takes a lot of time during creating indexes, and the first thing came to our mind is to increase the pga_aggregate_target to big number.

After increasing pga_aggregate_target to 6 GB, performance of import came down to 14 minutes.

Now total time of entire operation: 26 minutes down from 1:08 hours, not bad

 

We can still improve by copying files in parallel between servers, but this wasn’t needed.

(added Jun 26, 2011)
Tip: This is another tip that will improve importing time. When importing using Data Pump, exclude statistics and then use dbms_stats package to calculate statistics in parallel after Data Pump finishes importing. Importing using Data Pump is done serially (one segment after another, not in parallel) even when you use the parallel option, Data Pump will still have 1 thread only calculating statistics.

Here is an example:

impdp username/password EXCLUDE=STATISTICS other options …