Issue in DB export

dumpexpdporacleoracle-11g-r2

I've scheduled to export my database at every midnight via Windows Task Scheduler. This was properly exported for so long. But, recently in some days, export is not happening as required. The logs are showing the following error. 

Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCOBJ
Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/AUDIT
. . exported "AAAA"."AA_DETAILS"                 881.0 MB   36568 rows
ORA-31693: Table data object "AA"."PS_TXN" 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
. . exported "APEX_030200"."WWV_FLOW_STEP_PROCESSING"    1.248 MB    2238 rows
. . exported "APEX_030200"."WWV_FLOW_REGION_REPORT_COLUMN"  1.146 MB    7903 rows
..
. . exported "SYSMAN"."MGMT_BLACKOUT_SCHEDULE"           11.51 KB       0 rows
. . exported "SYSMAN"."MGMT_COLLECTIONS"                 32.67 KB     202 rows
ORA-31693: Table data object "SYSMAN"."MGMT_COLLECTION_TASKS" 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 6 with name "_SYSSMU6_2560781624$" too small
. . exported "SYSMAN"."MGMT_DB_INIT_PARAMS_ECM"          25.67 KB     352 rows
. . exported "SYSMAN"."MGMT_ECM_MD_ALL_TBL_COLUMNS"      110.2 KB     704 rows
. . exported "SYSMAN"."MGMT_INV_COMPONENT"               46.15 KB     144 rows
. . exported "SYSMAN"."MGMT_IP_SQL_STATEMENTS"           64.57 KB      31 rows
. . exported "SYSMAN"."MGMT_JOB_PARAM_SOURCE"            79.73 KB     527 rows
. . exported "SYSMAN"."MGMT_JOB_SCHEDULE"                11.13 KB       2 rows
. . exported "SYSMAN"."MGMT_JOB_SEC_INFO"                    9 KB       7 rows
. . exported "SYSMAN"."MGMT_JOB_USER_PARAMS"             7.515 KB      15 rows
. . exported "SYSMAN"."MGMT_LOADER_QTABLE"               16.68 KB       0 rows
. . exported "SYSMAN"."MGMT_NOTIFY_INPUT_QTABLE"         16.69 KB       0 rows
ORA-31693: Table data object "SYSMAN"."MGMT_SYSTEM_PERFORMANCE_LOG" 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 15 with name "_SYSSMU15_3269182342$" too small
 . exported "XX"."AAA"                   15.97 KB      11 rows
ORA-31693: Table data object "XX"."AAA" 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 15 with name "_SYSSMU15_3269182342$" too small
. . exported "SYSMAN"."ESM_COLLECTION"                   23.32 KB     219 rows
. . exported "SYSMAN"."MGMT_AGENT_SEC_INFO"              7.960 KB       1 rows
. . exported "SYSMAN"."MGMT_ARU_PRODUCT_RELEASE_MAP"     84.79 KB    5956 rows
. . exported "SYSMAN"."MGMT_CATEGORY_MAP"                46.76 KB     637 rows
. . exported "SYSMAN"."MGMT_CURRENT_VIOLATION"           61.67 KB     161 rows
. . exported "SYSMAN"."MGMT_DB_RECSEGMENTSETTINGS_ECM"   23.33 KB     200 rows
. . exported "SYSMAN"."MGMT_ECM_SNAPSHOT_MD_COLUMNS"     87.19 KB     839 rows
. . exported "SYSMAN"."MGMT_IP_ELEM_DEFAULT_PARAMS"      31.75 KB     130 rows
. . exported "SYSMAN"."MGMT_JOB_COMMAND_BLOCK_PROCS"     5.914 KB       3 rows
. . exported "SYSMAN"."MGMT_JOB_LARGE_PARAMS"            5.929 KB       2 rows
. . exported "SYSMAN"."MGMT_JOB_OUTPUT"                  5.859 KB       0 rows
. . exported "SYSMAN"."MGMT_JOB_PARAMETER"               8.859 KB       2 rows
. . exported "SYSMAN"."MGMT_JOB_SQL_PARAMS"              6.828 KB       7 rows
. . exported "SYSMAN"."MGMT_JOB_SUBST_PARAMS"            6.296 KB      13 rows
. . exported "SYSMAN"."PARAM_VALUES_TAB"                 21.74 KB     240 rows
. . exported "SYSMAN"."MGMT_LAST_VIOLATION"              56.28 KB     762 rows
. . exported "SYSMAN"."MGMT_LICENSE_DEFINITIONS"         54.65 KB      59 rows
. . exported "SYSMAN"."MGMT_NOTIFY_QTABLE"               22.36 KB       0 rows
. . exported "SYSMAN"."MGMT_PAF_MSG_QTABLE_1"            18.85 KB       0 rows
. . exported "SYSMAN"."MGMT_PAF_MSG_QTABLE_2"            18.85 KB       0 rows
. . exported "SYSMAN"."MGMT_POLICY_ASSOC_CFG_PARAMS"     61.80 KB     715 rows
ORA-31693: Table data object "SYSMAN"."MGMT_POLICY_ASSOC_EVAL_DETAILS" 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 6 with name "_SYSSMU6_2560781624$" too small
. . exported "SYSMAN"."MGMT_POLICY_TYPE_VERSIONS"        20.38 KB     585 rows
. . exported "SYSMAN"."MGMT_POLICY_VIOL_CTXT_DEF"        68.09 KB     642 rows
ORA-31693: Table data object "SYSMAN"."MGMT_TASK_QTABLE" failed to load/unload and is being skipped due to error:
ORA-29913: error in executing ODCIEXTTABLEPOPULATE callout
ORA-01555: snapshot too old: rollback segment number 19 with name "_SYSSMU19_255734752$" too small

Although I don't have much knowledge in this, I just increased give a try by increasing the retention policy of UNDO tablespace using below command.

alter system set undo_retention = 16500 scope = BOTH;

But still I'm getting the same error. This is happening in intermittent days. Also, my database size was a little bit high, so I've deleted some unwanted data and freed some space. Now, the size is below 10GB. This too didn't help. I'm stuck still. Can anyone please help in resolving this error in DB export.

Best Answer

Even with a long undo retention and a large undo tablespace, it's still possible to run into this if you're frequently committing while the job is running. This is because you'll be scattering your work across many undo segments, increasingly the likelihood that one ages out.

Does your job take longer than four hours thirty-five minutes? Your undo_retention might still be too low.

Related Question