I ran into a strange problem, my daily data pump export job on a 11gr1 database runs well almost every day. But once in a while on Saturday evenings, it would hang near the end of the export. If I kill the job and re-submit the export script, it would run to success. Any idea what might be causing this occasional hanging issue? Thanks in advance for any tips.
Perhaps there is some other scheduled task running on Saturday evening conflicting with the export but the overlap is only small so sometimes it works and sometimes it doesn;t - you really need to see what the session is waiting for when its in a hung state and if another session is blocking it.
Look at v$session_wait for all the datapump processes next time it happens and see what event they are stuck on.
It could possibly be the auto space advisor which seems to go off and create compressed version sof lots of tables and some point in the weekend window.
Pl post details of OS and database versions. Why do you run a daily export job ? If this is for "backup" purposes, pl note that exports are not backups. You should be using RMAN
You could try and trace the Sat datapump run - check the TRACE parameter
Export/Import DataPump Parameter TRACE - How to Diagnose Oracle Data Pump [ID 286496.1]
The OS is RH linux 5, Oracle is 22.214.171.124. We are running Rman backup daily, the Data Pump export is in addition to Rman backup. We use our export dump file to refresh test databases here are there. I'll try to use to trace for our Saturday backups and see if it can capture more details. Thanks.
Good point, just checked our OEM for automated maitenance tasks. One of the segment advisor jobs seem to run at 6PM, which could be causing the export job hang as they run into the same window. I've changed my Saturday maintenance window from '6AM - for 20 hours' to '3AM - for 15 hours', but not sure if that can change the segment advisor job run time, still trying to figure out how to change that.