Ever wanted to temporarily pause a datapump job while some other activity was happening. Or suddenly have a requirment to stop a job for a reboot of a server that can't wait even though the job has been running for many hours. I wanted to do the same thing so i did some googling and the results were inconclusive with sites often conflicting with each other - so i did some tests of my own just on the export phase of the process.
datapump export offers 3 possibilites to pause/stop a job - these are (either via interactive command line or plsql calls):
stop_job
stop_job=immediate
kill_job
Now how these are supposed to work as as follows:
stop_job - wait for current task to finish then cleanly shut down the job but leave it in a state where it can carry on
stop_job=immediate (same as stop_job but does not wait for the current task to finish - the action it was currently doing will have to be redone)
kill_job - just gets rid of the job
I wanted to check that stop_job works as expected (kill_job i was already happy with). Here are the steps I followed and the results (which may be just a feature of the version I'm on as it didnt work as documented....)
FIrst up lets kick off an export of a resonable large schema
[oracle@server-name]:EMZA3:[~]# expdp / schemas=aligne logfile=export.log dumpfile=aligne.dmp
Export: Release 11.2.0.3.0 - Production on Mon Aug 12 12:06:19 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option
Starting "OPS$ORACLE"."SYS_EXPORT_SCHEMA_04": /******** schemas=aligne logfile=export.log dumpfile=aligne.dmp
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 39.84 GB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PACKAGE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-31693: Table data object "ALIGNE"."TSHAPE" failed to load/unload and is bein
ORA-19502: write error on file "/oracle/11.2.0.3.0.DB/rdbms/log/aligne.dmp", blo
ORA-27085: Message 27085 not found; product=RDBMS; facility=ORA
Additional information: 249856
Additional information: 262144
OK - first step - make sure there is enough room.......
Lets find a filesystem with more room
[oracle@server-name]:EMZA3:[~]# s
SQL*Plus: Release 11.2.0.3.0 Production on Mon Aug 12 12:15:15 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option
SYS@EMZA3>select * from dba_directories;
OWNER DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS ORACLE_OCM_CONFIG_DIR
/oracle/11.2.0.3.0.DB/ccr/state
SYS DATA_PUMP_DIR
/oracle/11.2.0.3.0.DB/rdbms/log/
SYS TMP
/scratch
OWNER DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS SCRATCH
/scratch
SYS XMLDIR
/oracle/11.2.0.3.0.DB/rdbms/xml
SYS PUMPY
/oracle/EIMD/oradata/pumpy
6 rows selected.
SYS@EMZA3>exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option
OK TMP has lots of room after looking on the os lets use that. Lets just login to check something and we see that we can't datapump didn't clean up after that last failed attempt
[oracle@server-name]:EMZA3:[~]# s
SQL*Plus: Release 11.2.0.3.0 Production on Mon Aug 12 12:13:34 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
ERROR:
ORA-09817: Write to audit file failed.
Linux-x86_64 Error: 28: No space left on device
Additional information: 12
ORA-01075: you are currently logged on
Lets remove the file manally:
[oracle@server-name]:EMZA3:[~]# df -k /oracle
Filesystem 1K-blocks Used Available Use% Mounted on
/dev/mapper/rootvg-lv_oracle
72248648 68596216 0 100% /oracle
[oracle@server-name]:EMZA3:[~]# rm /oracle/11.2.0.3.0.DB/rdbms/log/aligne.dmp
[oracle@server-name]:EMZA3:[~]# s
SQL*Plus: Release 11.2.0.3.0 Production on Mon Aug 12 12:13:34 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
ERROR:
ORA-09817: Write to audit file failed.
Linux-x86_64 Error: 28: No space left on device
Additional information: 12
ORA-01075: you are currently logged on
Hmm -still a problem - the file handle must still be open - lets kill any old dm/dw processes. This works but not immediatly (even with kill -9)
[oracle@server-name]:EMZA3:[~]# ps -ef |grep dm0
oracle 14469 19552 0 12:13 pts/3 00:00:00 grep dm0
oracle 14824 1 0 12:06 ? 00:00:02 ora_dm00_EMZA3
[oracle@server-name]:EMZA3:[~]# kill -9 14824
[oracle@server-name]:EMZA3:[~]# cd /oracle
[oracle@server-name]:EMZA3:[/oracle]# df -k .
Filesystem 1K-blocks Used Available Use% Mounted on
/dev/mapper/rootvg-lv_oracle
72248648 68596116 0 100% /oracle
[oracle@server-name]:EMZA3:[/oracle]# ps -ef |grep dw0
oracle 14562 19552 0 12:14 pts/3 00:00:00 grep dw0
oracle 14830 1 31 12:06 ? 00:02:31 ora_dw00_EMZA3
[oracle@server-name]:EMZA3:[/oracle]# kill -9 14830
[oracle@server-name]:EMZA3:[/oracle]# df -k .
Filesystem 1K-blocks Used Available Use% Mounted on
/dev/mapper/rootvg-lv_oracle
72248648 68596112 0 100% /oracle
[oracle@server-name]:EMZA3:[/oracle]# ps -ef |grep dw0
oracle 14830 1 29 12:06 ? 00:02:31 ora_dw00_EMZA3
oracle 17363 19552 0 12:14 pts/3 00:00:00 grep dw0
[oracle@server-name]:EMZA3:[/oracle]# kill -9 14830
-bash: kill: (14830) - No such process
[oracle@server-name]:EMZA3:[/oracle]# df -k .
Filesystem 1K-blocks Used Available Use% Mounted on
/dev/mapper/rootvg-lv_oracle
72248648 56789596 11789524 83% /oracle
Right lets start again with the 'TMP' directory
[oracle@server-name]:EMZA3:[~]# expdp / schemas=aligne logfile=export.log dumpfile=aligne.dmp directory=tmp
Export: Release 11.2.0.3.0 - Production on Mon Aug 12 12:15:51 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option
Starting "OPS$ORACLE"."SYS_EXPORT_SCHEMA_05": /******** schemas=aligne logfile=export.log dumpfile=aligne.dmp directory=tmp
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 39.84 GB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PACKAGE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
OK at this point we are ticking along nicely - lets open up another client session and attach to this job. I keep checking status until i can see its exporting a big table and then i will stop_job it and see what happens.
[oracle@server-name]:EMZA3:[/oracle]# expdp / attach=SYS_EXPORT_SCHEMA_05
Export: Release 11.2.0.3.0 - Production on Mon Aug 12 12:16:13 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option
Job: SYS_EXPORT_SCHEMA_05
Owner: OPS$ORACLE
Operation: EXPORT
Creator Privs: TRUE
GUID: E3BF3F4B305F5475E0430200007F72AC
Start Time: Monday, 12 August, 2013 12:15:52
Mode: SCHEMA
Instance: EMZA3
Max Parallelism: 1
EXPORT Job Parameters:
Parameter Name Parameter Value:
CLIENT_COMMAND /******** schemas=aligne logfile=export.log dumpfile=aligne.dmp directory=tmp
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: /scratch/aligne.dmp
bytes written: 4,096
Worker 1 Status:
Process Name: DW00
State: EXECUTING
Object Schema: ALIGNE
Object Name: RTPROFILE
Object Type: SCHEMA_EXPORT/TABLE/TABLE
Completed Objects: 934
Worker Parallelism: 1
Export> status
Job: SYS_EXPORT_SCHEMA_05
Operation: EXPORT
Mode: SCHEMA
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: /scratch/aligne.dmp
bytes written: 4,096
Worker 1 Status:
Process Name: DW00
State: EXECUTING
Object Schema: ALIGNE
Object Name: PR_SCA261
Object Type: SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Completed Objects: 3
Total Objects: 3
Worker Parallelism: 1
Export> status
Job: SYS_EXPORT_SCHEMA_05
Operation: EXPORT
Mode: SCHEMA
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: /scratch/aligne.dmp
bytes written: 5,574,656
Worker 1 Status:
Process Name: DW00
State: EXECUTING
Object Schema: ALIGNE
Object Name: TSHAPE
Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
Completed Objects: 1
Total Objects: 948
Worker Parallelism: 1
OK at this point i can see it is on TSHAPE which i know is a big table (in fact it's the first table to be exported - datapump starts with the biggest objects). Lets now stop it cleanly.
Export> stop_job
Are you sure you wish to stop this job ([yes]/no): yes
This pauses for quite a while until the table finishes exporting and then the prompt comes back. In my original export window i see:
Processing object type SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "ALIGNE"."TSHAPE" 18.60 GB 7832312 rows
Job "OPS$ORACLE"."SYS_EXPORT_SCHEMA_05" stopped by user request at 12:22:41
So it has completed the TSHAPE table and then been stopped after that.
Here we can see the export file on the filesystem
-rw-r----- 1 oracle oinstall 19981160448 2013-08-12 12:22 /scratch/aligne.dmp
Now lets start it running again:
[oracle@server-name]:EMZA3:[/oracle]# expdp / attach=SYS_EXPORT_SCHEMA_05
Export: Release 11.2.0.3.0 - Production on Mon Aug 12 12:25:13 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option
Job: SYS_EXPORT_SCHEMA_05
Owner: OPS$ORACLE
Operation: EXPORT
Creator Privs: TRUE
GUID: E3BF3F4B305F5475E0430200007F72AC
Start Time: Monday, 12 August, 2013 12:25:15
Mode: SCHEMA
Instance: EMZA3
Max Parallelism: 1
EXPORT Job Parameters:
Parameter Name Parameter Value:
CLIENT_COMMAND /******** schemas=aligne logfile=export.log dumpfile=aligne.dmp directory=tmp
State: IDLING
Bytes Processed: 19,975,585,000
Percent Done: 61
Current Parallelism: 1
Job Error Count: 0
Dump File: /scratch/aligne.dmp
bytes written: 19,981,160,448
Worker 1 Status:
Process Name: DW00
State: UNDEFINED
Export> continue
Now the job is running again from the point it got to - client output is as shown below (the original log session is now disconnected from the job and wil receive no new output - the logfile will owever carry on from before). Log messages to the new attached session continue as below:
Job SYS_EXPORT_SCHEMA_05 has been reopened at Monday, 12 August, 2013 12:25
Restarting "OPS$ORACLE"."SYS_EXPORT_SCHEMA_05": /******** schemas=aligne logfile=export.log dumpfile=aligne.dmp directory=tmp
. . exported "ALIGNE"."PRCPOW" 5.431 GB 5416442 rows
. . exported "ALIGNE"."RPTSTAT" 1.348 GB 9602879 rows
.. lots of tables
. . exported "ALIGNE"."ZXRD001" 0 KB 0 rows
. . exported "ALIGNE"."ZXRD005" 0 KB 0 rows
Master table "OPS$ORACLE"."SYS_EXPORT_SCHEMA_05" successfully loaded/unloaded
******************************************************************************
Dump file set for OPS$ORACLE.SYS_EXPORT_SCHEMA_05 is:
/scratch/aligne.dmp
Job "OPS$ORACLE"."SYS_EXPORT_SCHEMA_05" successfully completed at 12:29:37
So that all seemed to work fine - inbetween the stop/start we could have a restart of the database and the job would still be restartable.
Now lets check the immediate flag works as expected.
We'll repeat the same tests:
[oracle@server-name]:EMZA3:[/scratch]# expdp / schemas=aligne logfile=export.log dumpfile=aligne.dmp directory=tmp
Export: Release 11.2.0.3.0 - Production on Mon Aug 12 12:38:43 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option
Starting "OPS$ORACLE"."SYS_EXPORT_SCHEMA_05": /******** schemas=aligne logfile=export.log dumpfile=aligne.dmp directory=tmp
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 39.84 GB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PACKAGE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
So job all nicely running as before
Now lets get another session attached and kill it immediately
[oracle@server-name]:EMZA3:[/oracle]# expdp / attach=SYS_EXPORT_SCHEMA_05
Export: Release 11.2.0.3.0 - Production on Mon Aug 12 12:39:03 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option
Job: SYS_EXPORT_SCHEMA_05
Owner: OPS$ORACLE
Operation: EXPORT
Creator Privs: TRUE
GUID: E3BF9106CF0945D2E0430200007F3BDF
Start Time: Monday, 12 August, 2013 12:38:43
Mode: SCHEMA
Instance: EMZA3
Max Parallelism: 1
EXPORT Job Parameters:
Parameter Name Parameter Value:
CLIENT_COMMAND /******** schemas=aligne logfile=export.log dumpfile=aligne.dmp directory=tmp
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: /scratch/aligne.dmp
bytes written: 4,096
Worker 1 Status:
Process Name: DW00
State: EXECUTING
Object Type: SCHEMA_EXPORT/TABLE/COMMENT
Completed Objects: 2
Total Objects: 2
Worker Parallelism: 1
Export> status
Job: SYS_EXPORT_SCHEMA_05
Operation: EXPORT
Mode: SCHEMA
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: /scratch/aligne.dmp
bytes written: 5,574,656
Worker 1 Status:
Process Name: DW00
State: EXECUTING
Object Schema: ALIGNE
Object Name: TSHAPE
Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
Completed Objects: 1
Total Objects: 948
Completed Rows: 119,262
Worker Parallelism: 1
Export> stop_job=immediate
Are you sure you wish to stop this job ([yes]/no): yes
All good so far but there was a long pause here - lets check the log output
Job "OPS$ORACLE"."SYS_EXPORT_SCHEMA_05" stopped due to fatal error at 12:39:47
Hmm - that doesn't look good and indeed when we restart the job and let it run to completion it does carry on to the end but the file is much smaller and it's clear something went wrong
[oracle@server-name]:EMZA3:[/oracle]# ls -l
-rw-r----- 1 oracle oinstall 29871669248 2013-08-12 12:29 original-aligne.dmp
-rw-r----- 1 oracle oinstall 9896083456 2013-08-12 12:48 aligne.dmp
So it seems that other than the table i stopped the job in the middle of everything else was unloaded.
Checking for any kind of errors in trace files related to this and i find this from a trace file
*** 2013-08-12 12:45:50.440
SHDW: Eoj_synch - Timeout (300 seconds) waiting for job process cleanup
So it seems that it tried to clean up neatly but gave up after 5 minutes which produced the fatal error.
The job i was running was pretty small to be honest - 30GB and only 5 minute run time so I'm not sure what the timeout problem was being caused by. I'm pretty much patched up 11.2.0.3 + some psu's so it's unliely to be a bug from something in an earlier release.
So my conclusion is stop_job is fine but stop_job=immediate seems to not work as it's meant to and should be avoided.....
I've not done any tests with impdp - maybe it works ok there.....
datapump export offers 3 possibilites to pause/stop a job - these are (either via interactive command line or plsql calls):
stop_job
stop_job=immediate
kill_job
Now how these are supposed to work as as follows:
stop_job - wait for current task to finish then cleanly shut down the job but leave it in a state where it can carry on
stop_job=immediate (same as stop_job but does not wait for the current task to finish - the action it was currently doing will have to be redone)
kill_job - just gets rid of the job
I wanted to check that stop_job works as expected (kill_job i was already happy with). Here are the steps I followed and the results (which may be just a feature of the version I'm on as it didnt work as documented....)
FIrst up lets kick off an export of a resonable large schema
[oracle@server-name]:EMZA3:[~]# expdp / schemas=aligne logfile=export.log dumpfile=aligne.dmp
Export: Release 11.2.0.3.0 - Production on Mon Aug 12 12:06:19 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option
Starting "OPS$ORACLE"."SYS_EXPORT_SCHEMA_04": /******** schemas=aligne logfile=export.log dumpfile=aligne.dmp
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 39.84 GB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PACKAGE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-31693: Table data object "ALIGNE"."TSHAPE" failed to load/unload and is bein
ORA-19502: write error on file "/oracle/11.2.0.3.0.DB/rdbms/log/aligne.dmp", blo
ORA-27085: Message 27085 not found; product=RDBMS; facility=ORA
Additional information: 249856
Additional information: 262144
OK - first step - make sure there is enough room.......
Lets find a filesystem with more room
[oracle@server-name]:EMZA3:[~]# s
SQL*Plus: Release 11.2.0.3.0 Production on Mon Aug 12 12:15:15 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option
SYS@EMZA3>select * from dba_directories;
OWNER DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS ORACLE_OCM_CONFIG_DIR
/oracle/11.2.0.3.0.DB/ccr/state
SYS DATA_PUMP_DIR
/oracle/11.2.0.3.0.DB/rdbms/log/
SYS TMP
/scratch
OWNER DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS SCRATCH
/scratch
SYS XMLDIR
/oracle/11.2.0.3.0.DB/rdbms/xml
SYS PUMPY
/oracle/EIMD/oradata/pumpy
6 rows selected.
SYS@EMZA3>exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option
OK TMP has lots of room after looking on the os lets use that. Lets just login to check something and we see that we can't datapump didn't clean up after that last failed attempt
[oracle@server-name]:EMZA3:[~]# s
SQL*Plus: Release 11.2.0.3.0 Production on Mon Aug 12 12:13:34 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
ERROR:
ORA-09817: Write to audit file failed.
Linux-x86_64 Error: 28: No space left on device
Additional information: 12
ORA-01075: you are currently logged on
Lets remove the file manally:
[oracle@server-name]:EMZA3:[~]# df -k /oracle
Filesystem 1K-blocks Used Available Use% Mounted on
/dev/mapper/rootvg-lv_oracle
72248648 68596216 0 100% /oracle
[oracle@server-name]:EMZA3:[~]# rm /oracle/11.2.0.3.0.DB/rdbms/log/aligne.dmp
[oracle@server-name]:EMZA3:[~]# s
SQL*Plus: Release 11.2.0.3.0 Production on Mon Aug 12 12:13:34 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
ERROR:
ORA-09817: Write to audit file failed.
Linux-x86_64 Error: 28: No space left on device
Additional information: 12
ORA-01075: you are currently logged on
Hmm -still a problem - the file handle must still be open - lets kill any old dm/dw processes. This works but not immediatly (even with kill -9)
[oracle@server-name]:EMZA3:[~]# ps -ef |grep dm0
oracle 14469 19552 0 12:13 pts/3 00:00:00 grep dm0
oracle 14824 1 0 12:06 ? 00:00:02 ora_dm00_EMZA3
[oracle@server-name]:EMZA3:[~]# kill -9 14824
[oracle@server-name]:EMZA3:[~]# cd /oracle
[oracle@server-name]:EMZA3:[/oracle]# df -k .
Filesystem 1K-blocks Used Available Use% Mounted on
/dev/mapper/rootvg-lv_oracle
72248648 68596116 0 100% /oracle
[oracle@server-name]:EMZA3:[/oracle]# ps -ef |grep dw0
oracle 14562 19552 0 12:14 pts/3 00:00:00 grep dw0
oracle 14830 1 31 12:06 ? 00:02:31 ora_dw00_EMZA3
[oracle@server-name]:EMZA3:[/oracle]# kill -9 14830
[oracle@server-name]:EMZA3:[/oracle]# df -k .
Filesystem 1K-blocks Used Available Use% Mounted on
/dev/mapper/rootvg-lv_oracle
72248648 68596112 0 100% /oracle
[oracle@server-name]:EMZA3:[/oracle]# ps -ef |grep dw0
oracle 14830 1 29 12:06 ? 00:02:31 ora_dw00_EMZA3
oracle 17363 19552 0 12:14 pts/3 00:00:00 grep dw0
[oracle@server-name]:EMZA3:[/oracle]# kill -9 14830
-bash: kill: (14830) - No such process
[oracle@server-name]:EMZA3:[/oracle]# df -k .
Filesystem 1K-blocks Used Available Use% Mounted on
/dev/mapper/rootvg-lv_oracle
72248648 56789596 11789524 83% /oracle
Right lets start again with the 'TMP' directory
[oracle@server-name]:EMZA3:[~]# expdp / schemas=aligne logfile=export.log dumpfile=aligne.dmp directory=tmp
Export: Release 11.2.0.3.0 - Production on Mon Aug 12 12:15:51 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option
Starting "OPS$ORACLE"."SYS_EXPORT_SCHEMA_05": /******** schemas=aligne logfile=export.log dumpfile=aligne.dmp directory=tmp
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 39.84 GB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PACKAGE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
OK at this point we are ticking along nicely - lets open up another client session and attach to this job. I keep checking status until i can see its exporting a big table and then i will stop_job it and see what happens.
[oracle@server-name]:EMZA3:[/oracle]# expdp / attach=SYS_EXPORT_SCHEMA_05
Export: Release 11.2.0.3.0 - Production on Mon Aug 12 12:16:13 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option
Job: SYS_EXPORT_SCHEMA_05
Owner: OPS$ORACLE
Operation: EXPORT
Creator Privs: TRUE
GUID: E3BF3F4B305F5475E0430200007F72AC
Start Time: Monday, 12 August, 2013 12:15:52
Mode: SCHEMA
Instance: EMZA3
Max Parallelism: 1
EXPORT Job Parameters:
Parameter Name Parameter Value:
CLIENT_COMMAND /******** schemas=aligne logfile=export.log dumpfile=aligne.dmp directory=tmp
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: /scratch/aligne.dmp
bytes written: 4,096
Worker 1 Status:
Process Name: DW00
State: EXECUTING
Object Schema: ALIGNE
Object Name: RTPROFILE
Object Type: SCHEMA_EXPORT/TABLE/TABLE
Completed Objects: 934
Worker Parallelism: 1
Export> status
Job: SYS_EXPORT_SCHEMA_05
Operation: EXPORT
Mode: SCHEMA
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: /scratch/aligne.dmp
bytes written: 4,096
Worker 1 Status:
Process Name: DW00
State: EXECUTING
Object Schema: ALIGNE
Object Name: PR_SCA261
Object Type: SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Completed Objects: 3
Total Objects: 3
Worker Parallelism: 1
Export> status
Job: SYS_EXPORT_SCHEMA_05
Operation: EXPORT
Mode: SCHEMA
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: /scratch/aligne.dmp
bytes written: 5,574,656
Worker 1 Status:
Process Name: DW00
State: EXECUTING
Object Schema: ALIGNE
Object Name: TSHAPE
Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
Completed Objects: 1
Total Objects: 948
Worker Parallelism: 1
OK at this point i can see it is on TSHAPE which i know is a big table (in fact it's the first table to be exported - datapump starts with the biggest objects). Lets now stop it cleanly.
Export> stop_job
Are you sure you wish to stop this job ([yes]/no): yes
This pauses for quite a while until the table finishes exporting and then the prompt comes back. In my original export window i see:
Processing object type SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "ALIGNE"."TSHAPE" 18.60 GB 7832312 rows
Job "OPS$ORACLE"."SYS_EXPORT_SCHEMA_05" stopped by user request at 12:22:41
So it has completed the TSHAPE table and then been stopped after that.
Here we can see the export file on the filesystem
-rw-r----- 1 oracle oinstall 19981160448 2013-08-12 12:22 /scratch/aligne.dmp
Now lets start it running again:
[oracle@server-name]:EMZA3:[/oracle]# expdp / attach=SYS_EXPORT_SCHEMA_05
Export: Release 11.2.0.3.0 - Production on Mon Aug 12 12:25:13 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option
Job: SYS_EXPORT_SCHEMA_05
Owner: OPS$ORACLE
Operation: EXPORT
Creator Privs: TRUE
GUID: E3BF3F4B305F5475E0430200007F72AC
Start Time: Monday, 12 August, 2013 12:25:15
Mode: SCHEMA
Instance: EMZA3
Max Parallelism: 1
EXPORT Job Parameters:
Parameter Name Parameter Value:
CLIENT_COMMAND /******** schemas=aligne logfile=export.log dumpfile=aligne.dmp directory=tmp
State: IDLING
Bytes Processed: 19,975,585,000
Percent Done: 61
Current Parallelism: 1
Job Error Count: 0
Dump File: /scratch/aligne.dmp
bytes written: 19,981,160,448
Worker 1 Status:
Process Name: DW00
State: UNDEFINED
Export> continue
Now the job is running again from the point it got to - client output is as shown below (the original log session is now disconnected from the job and wil receive no new output - the logfile will owever carry on from before). Log messages to the new attached session continue as below:
Job SYS_EXPORT_SCHEMA_05 has been reopened at Monday, 12 August, 2013 12:25
Restarting "OPS$ORACLE"."SYS_EXPORT_SCHEMA_05": /******** schemas=aligne logfile=export.log dumpfile=aligne.dmp directory=tmp
. . exported "ALIGNE"."PRCPOW" 5.431 GB 5416442 rows
. . exported "ALIGNE"."RPTSTAT" 1.348 GB 9602879 rows
.. lots of tables
. . exported "ALIGNE"."ZXRD001" 0 KB 0 rows
. . exported "ALIGNE"."ZXRD005" 0 KB 0 rows
Master table "OPS$ORACLE"."SYS_EXPORT_SCHEMA_05" successfully loaded/unloaded
******************************************************************************
Dump file set for OPS$ORACLE.SYS_EXPORT_SCHEMA_05 is:
/scratch/aligne.dmp
Job "OPS$ORACLE"."SYS_EXPORT_SCHEMA_05" successfully completed at 12:29:37
So that all seemed to work fine - inbetween the stop/start we could have a restart of the database and the job would still be restartable.
Now lets check the immediate flag works as expected.
We'll repeat the same tests:
[oracle@server-name]:EMZA3:[/scratch]# expdp / schemas=aligne logfile=export.log dumpfile=aligne.dmp directory=tmp
Export: Release 11.2.0.3.0 - Production on Mon Aug 12 12:38:43 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option
Starting "OPS$ORACLE"."SYS_EXPORT_SCHEMA_05": /******** schemas=aligne logfile=export.log dumpfile=aligne.dmp directory=tmp
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 39.84 GB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PACKAGE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
So job all nicely running as before
Now lets get another session attached and kill it immediately
[oracle@server-name]:EMZA3:[/oracle]# expdp / attach=SYS_EXPORT_SCHEMA_05
Export: Release 11.2.0.3.0 - Production on Mon Aug 12 12:39:03 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option
Job: SYS_EXPORT_SCHEMA_05
Owner: OPS$ORACLE
Operation: EXPORT
Creator Privs: TRUE
GUID: E3BF9106CF0945D2E0430200007F3BDF
Start Time: Monday, 12 August, 2013 12:38:43
Mode: SCHEMA
Instance: EMZA3
Max Parallelism: 1
EXPORT Job Parameters:
Parameter Name Parameter Value:
CLIENT_COMMAND /******** schemas=aligne logfile=export.log dumpfile=aligne.dmp directory=tmp
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: /scratch/aligne.dmp
bytes written: 4,096
Worker 1 Status:
Process Name: DW00
State: EXECUTING
Object Type: SCHEMA_EXPORT/TABLE/COMMENT
Completed Objects: 2
Total Objects: 2
Worker Parallelism: 1
Export> status
Job: SYS_EXPORT_SCHEMA_05
Operation: EXPORT
Mode: SCHEMA
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: /scratch/aligne.dmp
bytes written: 5,574,656
Worker 1 Status:
Process Name: DW00
State: EXECUTING
Object Schema: ALIGNE
Object Name: TSHAPE
Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
Completed Objects: 1
Total Objects: 948
Completed Rows: 119,262
Worker Parallelism: 1
Export> stop_job=immediate
Are you sure you wish to stop this job ([yes]/no): yes
All good so far but there was a long pause here - lets check the log output
Job "OPS$ORACLE"."SYS_EXPORT_SCHEMA_05" stopped due to fatal error at 12:39:47
Hmm - that doesn't look good and indeed when we restart the job and let it run to completion it does carry on to the end but the file is much smaller and it's clear something went wrong
[oracle@server-name]:EMZA3:[/oracle]# ls -l
-rw-r----- 1 oracle oinstall 29871669248 2013-08-12 12:29 original-aligne.dmp
-rw-r----- 1 oracle oinstall 9896083456 2013-08-12 12:48 aligne.dmp
So it seems that other than the table i stopped the job in the middle of everything else was unloaded.
Checking for any kind of errors in trace files related to this and i find this from a trace file
*** 2013-08-12 12:45:50.440
SHDW: Eoj_synch - Timeout (300 seconds) waiting for job process cleanup
So it seems that it tried to clean up neatly but gave up after 5 minutes which produced the fatal error.
The job i was running was pretty small to be honest - 30GB and only 5 minute run time so I'm not sure what the timeout problem was being caused by. I'm pretty much patched up 11.2.0.3 + some psu's so it's unliely to be a bug from something in an earlier release.
So my conclusion is stop_job is fine but stop_job=immediate seems to not work as it's meant to and should be avoided.....
I've not done any tests with impdp - maybe it works ok there.....
Comments
Post a Comment