Encountering "Snapshot too old" error during execution of expdp command

Question

As am facing an issue while performing expdp command in my production db.(Oracle 11g in windows enviornment)

cmd> expdp 'sys/123@PROD as sysdba' DUMPFILE=BACKUP_02082017_BACKUP.dmp LOGFILE=BakupLog_02082017_BACKUP.log SCHEMAS=A1,B2,C3,D4.. exclude=statistics consistent=y

It was taking more than 1 day to export the database sized 7GB. But my issue is that the exporting have error and shows error message

ORA-31693: Table data object "owner"."PASSWORD_HISTORY" 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 19 with name "_SYSSMU19_255734752$" too small

As i set my retention policy to 16500 from default 900. Even though, same error was occurring.

As i am planning to increase the retention policy up to 10 hrs ie, 36000. Is it viable? I am confused that do my undo table space is capable for this or not?

Providing some more details:

> show parameter undo_%;
 NAME TYPE VALUE 
-------------------------------------------------- ----------- -------- 
undo_management string AUTO 
undo_retention integer 16500 
undo_tablespace string UNDOTBS1 
> select file_name,tablespace_name,trunc(bytes/1024/1024) mb, trunc(maxbytes/1024/1024) mm
 FROM dba_data_files
 where tablespace_name = 'UNDOTBS1';
FILE_NAME TABLESPACE_NAME MB MM
--------------------------------------------------------------------
C:\APP\ADMIN\ORADATA\PROD\UNDOTBS01.DBF UNDOTBS1 5630 32767
>Size of undo with current undo_retention :
 Actual Undo size[MBytes]:5630
 UNDO retention[Sec]:16500 
 Needed Undo Size[MBytes]:909.433359

I am stuck with this issue. Anyone please advice how i deal with this error?

Thanks in advance.

• Philᵀᴹ: 1 day to export 7Gb is excessive. Is the database on a remote server with crapy connectivity?
• SHS: Database is on remote server but their is no issue with connectivity and the export is happening with in the server itself.
• Renan: I had the same problem The cause was LOB CORRUPTED. After fix , expdp works fine. Script to detect error here.

Answer 1

The cause of the ORA-01555 error is undo_retention parameter has the lower value as compared to the time taken to complete the export with the parameter consistent=y(which is deprecated in 11g and should use flashback_time or flashback_scn).

Your undo retention should able to retain the undo information for the time period of export job.

For example, if you export starts at 00:00 AM and it takes 2 hours to complete then the undo_retention should be set at least 2 hours if you wish to get consistent data.

I have simulated the problem in my test server.

[oracle@orcl expdp_dump]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Feb 8 09:41:40 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 2400
undo_tablespace string UNDOTBS1

My undo retention is set to 40 minutes.

SQL> select bytes/1024/1024 from v$datafile where ts#=2;
BYTES/1024/1024
---------------
 32764

And my undo tablespace is 32GB large.

I tried to export one of my schemas.

expdp system/password directory=DP_DIR schemas=MYSCHEMA dumpfile=expdp_orcl_MYSCHEMA.dmp logfile=expdp_orcl_MYSCHEMA.log FLASHBACK_TIME=SYSTIMESTAMP

Job "SYSTEM"."SYS_EXPORT_SCHEMA_07" completed with 1 error(s) at Tue Feb 7 22:14:46 2017 elapsed 0 01:14:39

It took 1 hour and 14 minutes. And there were moderate DML operations in the database.

ORA-31693: Table data object "MYSCHEMA"."MS_TABLE":"MS_TABLE."MS_TABLE_B_2005" 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 84 with name "_SYSSMU84_1745672109$" too small
. . exported "MYSCHEMA"."PRORATE_EVENT" 8.789 KB 4 rows
. . exported "MYSCHEMA"."PRORATE_RULE_GROUP" 5.945 KB 1 rows
. . exported "MYSCHEMA"."ORACLE_JOB_METADATA" 0 KB 0 rows

And I got the ORA-0155 error.

Then I increased the undo retention to 1 hour and 20 minutes.

SQL> alter system set undo_retention=4800;
System altered.

And again I tried to export the schema.

expdp system/password directory=DP_DIR schemas=MYSCHEMA dumpfile=expdp_orcl_MYSCHEMA.dmp logfile=expdp_orcl_MYSCHEMA.log FLASHBACK_TIME=SYSTIMESTAMP

Job "SYSTEM"."SYS_EXPORT_SCHEMA_07" successfully completed at Wed Feb 8 22:15:52 2017 elapsed 0 01:15:45.

And there was no ORA-01555 anymore.

Total estimation using BLOCKS method: 182.9 GB

and the actual file size is 141 GB.

Why the difference in estimation and the actual size of the dump file? Look at this post for the answer.

Regarding the time taken to complete the export. Try to use the following parameters.

Parallel

DIRECT=y

Also, try to gather data dictionary statistics before starting the export job-

SQL> connect / as sysdba
SQL> exec dbms_stats.gather_dictionary_stats;
SQL> exec dbms_stats.lock_table_stats (null,'X$KCCLH'); 
SQL> exec dbms_stats.gather_fixed_objects_stats;

Futher Readings:

Poor Performance With DataPump Export On Large Databases (Doc ID 473423.1)

Data Pump Export

• SHS: My undo tablespace is 5.5GB large.ANd I am setting my undo_retention to 36000 ie, 10hrs. Is this cause any kind of issue to my DB?
• atokpas: That's quite strange, you got to trace your expdp process what actually it is doing behind the scene. As I have shown you it took 1 hour to export 141GB schema. Don't just increase it without knowing the actual problem.
• atokpas: Add trace=1FF0300 parameter in expdp command and see which step is taking more time.
• Marinaio: Sometimes, if I don't need a full export, I just export each schema and that tends to avoid this error. Not always an option, but can work if migrating schemas and the like.

Answer 2

Another option is to increase the max_extents of your rollback segment highlighted and all others with the same max_extents, OR offline them and leave

Answer 3

In my case, I hit ORA-01555 exporting a static and huge subpartitioned table. My approach was to increment undo retention to 6 hours (in seconds) an mainly use a parfile with the detail of the partitions, with the next syntax:

tables=OWNER.TABLE_NAME:PARTITION_NAME1
tables=OWNER.TABLE_NAME:PARTITION_NAME2
tables=OWNER.TABLE_NAME:PARTITION_NAME3
...

And so on.

The partition names can be obtained with this query:

select PARTITION_NAME from DBA_TAB_PARTITIONS
where TABLE_OWNER = '<OWNER>' and TABLE_NAME = '<TABLE_NAME>'
order by PARTITION_POSITION asc

In this particular case the table had 500 partitions and thus the parfile was big as it required that quantity of "tables=" parameter.

The need to rollback reduces to the partition level (not sure if even at the subpartition level) so ORA-01555 could be controlled much better.

Hope it helps.