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

Development Mail today back to say backup error:

Ora-31693:table data Object "Ycdata". " T_messagesync_his "failed to Load/unload and was being skipped due to error:ora-02354:error in exporting/importing DataORA -01555:snapshot too old:rollback segment number 171 with name "_syssmu171_1375589658$" too small adjusted undo_retantion value of 3600 , the original 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 was 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 this there is no LOB corruption. Cause The old versions (consistent read) of the LOB can is 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 and not both. -Pctversion:this parameter specifies the percentage of all used Basicfiles LOB data space The can is occupied by old V Ersions of Basicfiles LOB data pages. Under 11g compatibility, this parameter was silently ignored when Securefiles lobs was created. Pctversion is the default in manual Undo mode and the default value is ten, meaning that older versions of the LOB data is Not overwritten until they consume 10% of the overall LOB storage space. You can specify the Pctversion parameter whether the database are 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 was running in automatic undo mode. Oracle Database uses the value of the Undo_retention initialization parameter to determine the amount of committed UNDO da Ta to retain in the database. In automatic undo mode, RETENTION are 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 on 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 are 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 opt Ion the LOB is using the * * Check Note 422826.1:how to Identify LOB Segment use pctversion Or RETENTION from Data Dictio Nary The LOB Retention is not a defined properly, this is a 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 seconds (minutes) which are the same as the current undo_retention, but the Maxquery length is 16331 seconds. When the LOB is created, the actual setting for RETENTION is defined by the current setting for undo_retention. This was not a 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, the change have 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.

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