0

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.

  • Increasing undo_retention only does't fix the problem. Do you have enough space on Undo Tablespace to hold the undo data till the time defined by this parameter? – JSapkota Jan 30 '17 at 9:49
  • I checked UNDO tablespace size using the following command. SELECT SUM(a.bytes) "UNDO_SIZE" FROM v$datafile a, v$tablespace b, dba_tablespaces c where c.contents = 'UNDO' and c.status = 'ONLINE' and b.name = c.tablespace_name AND a.ts# = b.ts#; > and could see that it's 5903482880 bytes . I guess this is sufficient as per retention policy. – akr Jan 30 '17 at 10:56
  • You should check v$undostat view (docs.oracle.com/cd/E11882_01/server.112/e40402/…) Maybe this recipe will help: akadia.com/services/ora_optimize_undo.html – elfcheg Jan 30 '17 at 16:51
  • What is the purpose of this export? is it for recovery or is it to refresh some other env? if it is later, you don't need sysman (most likely). Do you need sysman to be included here? if sysman is required, can you schedule a separate one for that? – Raj Jan 31 '17 at 13:30
  • Have you been performing backups (not exports, but true backups via RMAN)? Is your database in archivelog mode? – CaM Feb 1 '17 at 13:47
1

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.

0

Try using FLASHBACK_SCN parameter in export if you don't. For example:

select CURRENT_SCN from v$database;

FLASHBACK_SCN=9883682053912
FULL=Y
DIRECTORY=DUMP
DUMPFILE=DZMW_FULL.dmp
LOGFILE=export_FULL.log
JOB_NAME=EXPFULL
PARALLEL=16
  • The value of FLASHBACK_SCN in my database is 962571043. So, could I add this in my command as follows. expdp 'sys/***@TEST as sysdba' DUMPFILE=BACKUP_02022017_1701.dmp LOGFILE=BakupLog_02022017_1701.log FULL=y consistent=y FLASHBACK_SCN=962571043 – akr Feb 1 '17 at 12:20
  • Yes, this is correct – Dmitry Lukyanov Feb 1 '17 at 12:22
  • But, I got the following error message after adding this parameter. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options ORA-39050: parameter FLASHBACK_TIME is incompatible with parameter FLASHBACK_SCN . I have no idea what's wrong. – akr Feb 2 '17 at 12:13
  • Looks like it's because of consistent=y Sorry, I didn't see it previous time. – Dmitry Lukyanov Feb 2 '17 at 12:25
0

Try counting required size of undo for your current undo_retention value using this query:

SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
       SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
       (TO_NUMBER(e.value) * TO_NUMBER(f.value) *
       g.undo_block_per_sec) / (1024*1024) 
      "NEEDED UNDO SIZE [MByte]"
  FROM (
       SELECT SUM(a.bytes) undo_size
         FROM v$datafile a,
              v$tablespace b,
              dba_tablespaces c
        WHERE c.contents = 'UNDO'
          AND c.status = 'ONLINE'
          AND b.name = c.tablespace_name
          AND a.ts# = b.ts#
       ) d,
      v$parameter e,
       v$parameter f,
       (
       SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
         undo_block_per_sec
         FROM v$undostat
       ) g
 WHERE e.name = 'undo_retention'
  AND f.name = 'db_block_size'
/
  • Following is the result of the query : Actual Undo size[MBytes]:5630 UNDO retention[Sec]:16500 Needed Undo Size[MBytes]:909.433359 – akr Feb 4 '17 at 4:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.