Oracle data pump terminates the task
When we accidentally trigger a logical backup (expdp) in a production environment, it may cause IO blocking and affect the production business.
So how to terminate the expdp export task?
Manually execute the full library export script:
[oracle @ server1 ~] $. / expdp.sh
View process information:
[root @ server1 ~] # ps-ef | grep expdp
Oracle 3233 2842 0 04:58 pts/1 00:00:00 / bin/bash. / expdp.sh
Oracle 3247 3233 0 04:58 pts/1 00:00:00 expdp sysdba' directory=dump_file dumpfile=prod_20200803.dmp log=prod_20200803.log full=y compression=ALL
Root 3254 3081 0 04:58 pts/3 00:00:00 grep expdp
[root @ server1 ~] # kill-9 3247
. / expdp.sh: line 13: 3247 Killed expdp\'/ as sysdba\ 'directory=dump_file dumpfile=$FILE_NAME log=$FILE_LOG full=y compression=ALL
The amount of data is still growing: (kill semaphore cannot terminate expdp export task)
[oracle @ server1 backup] $du-sh
1.2G prod
48M prod_20200803.dmp
104K prod_20200803.log
[oracle @ server1 backup] $du-sh
1.2G prod
50M prod_20200803.dmp
104K prod_20200803.log
[oracle @ server1 backup] $du-sh *
1.2G prod
54M prod_20200803.dmp
108K prod_20200803.log
Query data pump job:
SQL > select job_name,state,owner_name from dba_datapump_jobs
JOB_NAME STATE OWNER_NAME
SYS_EXPORT_FULL_01 EXECUTING SYS
[oracle @ server1 ~] $expdp\'/ as sysdba\ 'attach= SYS_EXPORT_FULL_01
Export: Release 11.2.0.4.0-Production on Mon Aug 3 05:03:13 2020
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
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
Job: SYS_EXPORT_FULL_01
Owner: SYS
Operation: EXPORT
Creator Privs: TRUE
GUID: ABEC566151380D0DE05379C7A8C00DBB
Start Time: Monday, 03 August, 2020 5:02:18
Mode: FULL
Instance: prod
Max Parallelism: 1
EXPORT Job Parameters:
Parameter Name Parameter Value:
CLIENT_COMMAND "/ * * AS SYSDBA" directory=dump_file dumpfile=prod_20200803.dmp logfile=prod_20200803.log full=y reuse_dumpfiles=true
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: / backup/prod_20200803.dmp
Bytes written: 4096
Worker 1 Status:
Process Name: DW00
State: EXECUTING
Object Schema: APEX_030200
Object Name: WWV_FLOW_FL_CH_IDX
Object Type: DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
Completed Objects: 1466
Worker Parallelism: 1
Export > status
Job: SYS_EXPORT_FULL_01
Operation: EXPORT
Mode: FULL
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: / backup/prod_20200803.dmp
Bytes written: 4096
Worker 1 Status:
Process Name: DW00
State: EXECUTING
Object Type: DATABASE_EXPORT/SCHEMA/VIEW/COMMENT
Completed Objects: 2198
Total Objects: 2198
Worker Parallelism: 1
Stop the data pump job:
Export > stop_job / / or kill_job permanently terminates the task and automatically deletes the resulting status table
Are you sure you wish to stop this job ([yes] / no): yes
View the status table again:
SQL > select job_name,state,owner_name from dba_datapump_jobs
JOB_NAME STATE OWNER_NAME
SYS_EXPORT_FULL_01 NOT RUNNING SYS
Delete the status table:
SQL > drop table SYS_EXPORT_FULL_01 purge
Table dropped.
When there are too many status tables, use concatenation delete statements:
SQL > select 'drop' | | 'table' | | job_name | | 'purge' from dba_datapump_jobs
