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.
Best Answer
The cause of the
ORA-01555
error isundo_retention
parameter has the lower value as compared to the time taken to complete the export with the parameterconsistent=y
(which is deprecated in 11g and should useflashback_time
orflashback_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 theundo_retention
should be set at least 2 hours if you wish to get consistent data.I have simulated the problem in my test server.
My undo retention is set to 40 minutes.
And my undo tablespace is 32GB large.
I tried to export one of my schemas.
It took 1 hour and 14 minutes. And there were moderate DML operations in the database.
And I got the
ORA-0155
error.Then I increased the undo retention to 1 hour and 20 minutes.
And again I tried to export the schema.
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.
Also, try to gather data dictionary statistics before starting the export job-
Futher Readings: