expdp and stop_job (and stop_job=immediate) - how does that work (or not as the case may be)

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.....

0 comments:

Post a Comment