ORA-31693, ORA-02354 and ORA-01555 with Export Datapump (Doc ID 1580798.1), ora-31693datapump

Source: Internet
Author: User

ORA-31693, ORA-02354 and ORA-01555 with Export Datapump (Doc ID 1580798.1), ora-31693datapump

The following error is returned from the development email:

ORA-31693: Table data object "YCDATA ". "T_MESSAGESYNC_HIS" failed to load/unload and is being skipped due to error: ORA-02354: error in exporting/importing dataORA-01555: snapshot too old: rollback segment number 171 with name "_ syssmu173161375589658 $" too small adjusted the value of undo_retantion to 3600. The original value is 900.

In this Document

Symptoms
  Cause
  Solution
  References

Applies to: Oracle Database-Enterprise Edition-Version 10.2.0.1 and later
Information in this document applies to any platform.
SYMPTOMS

Using Database Data Pump (expdp), one table fails to export with the following errors:

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

Followed the steps in Note: 452341.1 and/or Note: 787004.1, and found that there is no LOB upload uption.

CAUSE

The old versions (consistent read) of the LOB can be specified by either the PCTVERSION or the RETENTION parameters.

For SecureFiles, only the RETENTION parameter can be specified.

For BasicFiles LOBs you can specify either PCTVERSION or RETENTION, but not both.


-PCTVERSION: This parameter specifies the percentage of all used BasicFiles LOB data space that can be occupied by old versions of BasicFiles LOB data pages. under 11g compatibility, this parameter is silently ignored when SecureFiles LOBs are created.

PCTVERSION is the default in manual undo mode and the default value is 10, meaning that older versions of the LOB data are not overwritten until they consume 10% of the overall LOB storage space.

You can specify the PCTVERSION parameter whether the database is running in manual or automatic undo mode.

-RETENTION is the default in automatic undo mode.

You can specify the RETENTION parameter only if the database is running in automatic undo mode. oracle Database uses the value of the UNDO_RETENTION initialization parameter to determine the amount of committed undo data to retain in the database. in automatic undo mode, RETENTION is the default value unless you specify PCTVERSION. you cannot specify both PCTVERSION and RETENTION.

You can specify the optional settings after RETENTION only if you are using SecureFiles.

You can see more details about the RETENTION parameter for SecureFiles and BasicFiles LOBs in the following link:

Http://docs.oracle.com/cd/E11882_01/appdev.112/e18294/adlob_tables.htm#ADLOB45282
RETENTION Parameter for SecureFiles LOBs

Http://docs.oracle.com/cd/E11882_01/appdev.112/e18294/adlob_tables.htm#ADLOB45281
RETENTION Parameter for BasicFiles LOBs


To Determine whether LOB segment is using RETENTION or PCTVERSION, use the following statement:

SQL> select COLUMN_NAME, SECUREFILE, PCTVERSION, RETENTION from dba_lobs where OWNER = upper ('& owner') and TABLE_NAME = upper (' & TABLE_NAME ');

COLUMN_NAME SEC PCTVERSION RETENTION
-----------------------------------------------------
FILE_DATA NO 900

 

Note: If You 've a value for both PCTVERSION and RETENTION columns, this is incorrect output and to really know which option the LOB is using Please Check Note 422826.1: how To Identify LOB Segment Use PCTVERSION Or RETENTION From Data Dictionary

 

The LOB Retention is not defined properly, This is confirmed by queries:


SQL> show parameter undo;

NAME TYPE VALUE
-----------------------------------------------------------------------------
Undo_management string AUTO
Undo_retention integer 900
Undo_tablespace string UNDOTBS1
SQL> select max (maxquerylen) from v $ undostat;

MAX (MAXQUERYLEN)
----------------
16331

 
We see the retention comes back showing 900 seconds (15 minutes) which is the same as the current UNDO_RETENTION, but the maxquery length is 16331 seconds.


When the LOB was created, the actual setting for RETENTION was defined by the current setting for UNDO_RETENTION.
This time is not long enough.

SOLUTION

1. Modify the current UNDO_RETENTION for the database:

SQL> ALTER SYSTEM SET UNDO_RETENTION = 16500 scope = both sid = '*';

 

2. Modify the LOB retention to become greater than the undersized retention parameter following the steps from Note: 563470.1

SQL> alter table APPLSYS. FND_LOBS modify lob (FILE_DATA) (pctversion 5 );
Table altered.

SQL> alter table APPLSYS. FND_LOBS modify lob (FILE_DATA) (retention );
Table altered.

 

3. Query the lob retention again to verify that the change has taken hold:

SQL> select COLUMN_NAME, SECUREFILE, PCTVERSION, RETENTION from dba_lobs where OWNER = upper ('& owner') and TABLE_NAME = upper (' & TABLE_NAME ');

COLUMN_NAME SEC PCTVERSION RETENTION
-----------------------------------------------------
FILE_DATA NO 16500

 
4. Perform the export again.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.