datapump stop_job 후 재기동 후 start_job

OS환경 : Oracle Linux6.8(64bit)

DB 환경 : Oracle Database 11.2.0.4

쿼리 : datapump stop_job 후 재기동 후 start_job 

datapump용 directory 생성 및 권한 부여

SQL> create directory shuttest as '/home/oracle/';
SQL> grant read, write on directory shuttest to public;

scott유저의 모든 오브젝트 expdp 실행 job_name=shut_job

$ expdp system/oracle dumpfile=scott.dmp directory=shuttest schemas=scott job_name=shut_job
 
Export: Release 11.2.0.4.0 - Production on Tue Jan 8 10:13:43 2019
 
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, Real Application Clusters and Data Mining options
Starting "SYSTEM"."SHUT_JOB":  system/******** dumpfile=scott.dmp directory=shuttest schemas=scott job_name=shut_job 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 256.9 MB
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/TABLE/TABLE
^C (컨트롤 + C로 중지)

expdp 상태 확인

Export> status
 
Job: SHUT_JOB
  Operation: EXPORT                         
  Mode: SCHEMA                         
  State: EXECUTING                      
  Bytes Processed: 0
  Current Parallelism: 1
  Job Error Count: 0
  Dump File: /home/oracle/scott.dmp
    bytes written: 4,096
  
Worker 1 Status:
  Process Name: DW00
  State: EXECUTING                      
  Object Schema: SCOTT
  Object Name: T2
  Object Type: SCHEMA_EXPORT/TABLE/TABLE
  Completed Objects: 1
  Worker Parallelism: 1

expdp job 중지

Export> stop_job
Are you sure you wish to stop this job ([yes]/no): yes

db 재기동

$ sqlplus / as sysdba
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
 
SQL> startup
ORACLE instance started.
 
Total System Global Area  830930944 bytes
Fixed Size            2257800 bytes
Variable Size          515902584 bytes
Database Buffers      310378496 bytes
Redo Buffers            2392064 bytes
Database mounted.
Database opened.

dba_datapump_jobs 확인

SQL> 
col owner_name for a10
col job_name for a10
col operation for a10
col job_mode for a10
col state for a20
select owner_name, job_name, operation, job_mode, state
from dba_datapump_jobs;
 
OWNER_NAME JOB_NAME   OPERATION  JOB_MODE   STATE
---------- ---------- ---------- ---------- --------------------
SYSTEM       SHUT_JOB   EXPORT     SCHEMA     NOT RUNNING

재기동 후에도 shut_job이 NOT RUNNING 상태로 존재하는것을 확인할수 있음

expdp job 재실행

$ expdp system/oracle attach=shut_job
 
Export: Release 11.2.0.4.0 - Production on Tue Jan 8 10:19:54 2019
 
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, Real Application Clusters and Data Mining options
 
Job: SHUT_JOB
  Owner: SYSTEM                         
  Operation: EXPORT                         
  Creator Privs: TRUE                           
  GUID: 7EE90E879C74122CE0537900A8C0DB05
  Start Time: Tuesday, 08 January, 2019 10:20:04
  Mode: SCHEMA                         
  Instance: SJEDB2
  Max Parallelism: 1
  EXPORT Job Parameters:
  Parameter Name      Parameter Value:
     CLIENT_COMMAND        system/******** dumpfile=scott.dmp directory=shuttest schemas=scott job_name=shut_job 
  State: IDLING                         
  Bytes Processed: 0
  Current Parallelism: 1
  Job Error Count: 0
  Dump File: /home/oracle/scott.dmp
    bytes written: 4,096
  
Worker 1 Status:
  Process Name: DW00
  State: UNDEFINED 

Export> start_job

실행 상태 확인(Percent Done 99=> 99%완료되었다는 뜻)

Export> status
 
Job: SHUT_JOB
  Operation: EXPORT                         
  Mode: SCHEMA                         
  State: EXECUTING                      
  Bytes Processed: 354,152,944
  Percent Done: 99
  Current Parallelism: 1
  Job Error Count: 0
  Dump File: /home/oracle/scott.dmp
    bytes written: 354,312,192
  
Worker 1 Status:
  Process Name: DW00
  State: EXECUTING

실행 상태 재확인(Percent Done 100=> 100%완료되고 JOB를 정리중이라는 뜻)

Export> status
 
Job: SHUT_JOB
  Operation: EXPORT                         
  Mode: SCHEMA                         
  State: COMPLETING                     
  Bytes Processed: 354,152,945
  Percent Done: 100
  Current Parallelism: 1
  Job Error Count: 0
  Dump File: /home/oracle/scott.dmp
    bytes written: 354,312,192
  
Worker 1 Status:
  Process Name: DW00
  State: WORK WAITING

이상태에서 조금 기다리면 expdp에서 쉘로 나와진다.

dba_datapump_jobs 확인

SQL> 
col owner_name for a10
col job_name for a10
col operation for a10
col job_mode for a10
col state for a20
select owner_name, job_name, operation, job_mode, state
from dba_datapump_jobs;
 
no rows selected

dba_datapump_jobs 뷰에도 더이상 datapump job이 나오지 않는것을 확인 할 수있다.

expdp 후 dump 파일 확인

$ ls -al
total 449104
drwxr-xr-x. 6 oracle dba       4096 Jan  8 10:13 .
drwxr-xr-x. 4 root   root      4096 Nov  9 16:10 ..
-rw-r--r--  1 oracle dba       3782 Jan  8 10:20 export.log
-rw-r-----  1 oracle dba  354684928 Jan  8 10:20 scott.dmp

결론 : datapump 중 stop_job 명령 수행후 재기동 후 다시 start_job 하면 정상적으로 expdp job이 수행된다

참조 : http://blog.goodus.com/39