At a client request I’ve implemented a daily consistent export of few schemas, using something like:
expdp "/ as sysdba" SCHEMAS=user1 dumpfile=export_%1_%datetime%.dmp logfile=export_%1_%datetime%.log flashback_time=systimestamp
I was set to run at 22:00 every night.
Next day the log showed some errors like:
ORA-31693: Table data object "USER1"."TABLE_TEST" failed to load/unload and is being skipped due to error: ORA-02354: error in exporting/importing data ORA-01466: unable to read data - table definition has changed
Looking closer, it was due to the automatic statistics collection, done within the daily maintenance window of Oracle. The LAST_ANALYZED column of DBA_TABLES changed, as well as LAST_DDL_TIME on DBA_OBJECTS. I did not expect neither I see nowhere else complaining about this interference between the maintenance windows and a export data pump. Strange!
To avoid any problem in the future we decided to change the maintenance window time and lenght.
On Oracle 11g and 12c there is a window per day. So to change lets say to 19:00 and having a duration of 2 hours you do:
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('MONDAY_WINDOW','repeat_interval','freq=daily;byday=MON;byhour=19;byminute=0; bysecond=0');
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('MONDAY_WINDOW',attribute => 'DURATION',value => numtodsinterval(2, 'hour'));
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('TUESDAY_WINDOW','repeat_interval','freq=daily;byday=TUE;byhour=19;byminute=0; bysecond=0');
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('TUESDAY_WINDOW',attribute => 'DURATION',value => numtodsinterval(2, 'hour'));
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('WEDNESDAY_WINDOW','repeat_interval','freq=daily;byday=WED;byhour=19;byminute=0; bysecond=0');
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('WEDNESDAY_WINDOW',attribute => 'DURATION',value => numtodsinterval(2, 'hour'));
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('THURSDAY_WINDOW','repeat_interval','freq=daily;byday=THU;byhour=19;byminute=0; bysecond=0');
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('THURSDAY_WINDOW',attribute => 'DURATION',value => numtodsinterval(2, 'hour'));
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('FRIDAY_WINDOW','repeat_interval','freq=daily;byday=FRI;byhour=19;byminute=0; bysecond=0');
EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('FRIDAY_WINDOW',attribute => 'DURATION',value => numtodsinterval(2, 'hour'));