Error in database export

expdporacleoracle-11g

I'm using Oracle 11g databse. For logical backup, we are exporting database on daily basis using below command and dump size is almost 12GB. , but now I'm facing an issue with my expdp.

expdp 'sys/sys#123@Test as sysdba' DUMPFILE=Backup_04042017.dmp LOGFILE=BakupLog_04042017_1603.log FULL=y consistent=y

Export gets completed successfully, but two tables are not getting exported. The following error is shown in the log. 

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

As read from many blogs, I've set undo_retention value from default to 36000 and set the retention for its corresponding table using below command. 

alter table OWNER.TABLE modify lob(DESCRIPTION_HTML_) (retention);

It doesn't work as expected. So, I've set PCTVERSION to 40% using below command.

ALTER TABLE OWNER.TABLE MODIFY LOB (DESCRIPTION_HTML_) (PCTVERSION 40);

My bad, that too didn't work.
Also I tried to export the single table which also given me the same error.

Can I set PCTVERSION more than 40%? Does it inversely affect my production database?

Following are the results that I got related to undo size where my optimal undo_retention is showing lower value than undo_retention. Moreover, needed_undo_size is greater than actual_undo_size.

enter image description here

also,

enter image description here

Do I need to increase undo_size? How it should be calculated based on my database configuration?

Best Answer

  1. Do I need to increase undo_size? …

    Yes, that is exactly what you need to do. The export needs a consistent MVCC snapshot from the start of the operation. UNDO_RETENTION is ignored if your UNDO tablespace is fixed size or can't be extended:

    UNDO_RETENTION specifies (in seconds) the low threshold value of undo retention. For AUTOEXTEND undo tablespaces, the system retains undo for at least the time specified in this parameter, and automatically tunes the undo retention period to satisfy the undo requirements of the queries. For fixed- size undo tablespaces, the system automatically tunes for the maximum possible undo retention period, based on undo tablespace size and usage history, and ignores UNDO_RETENTION unless retention guarantee is enabled.

  2. … How it [undo size] should be calculated based on my database configuration?

    It needs to be calculated based on how quickly you are generating UNDO (with DML), and how long the export takes — you'll need to find a size that works for you or try and do the export when less UNDO is being generated, or speed up the export. If you have the space available, increasing the size of your UNDO is the easy solution.

Related Question