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.
Do I need to increase undo_size? How it should be calculated based on my database configuration?
JOB_QUEUE_PROCESSESto zero might work