데이터 펌프 걸어 놓고 모니터링 하는 방법 정리

데이터 펌프 걸어 놓고 모니터링 하는 방법

1) Using the datapump client (expdp & impdp) STATUS command:-

When the export or import job is running press +C keys to get to the respective datapump client prompt OR you can use another session of datapump client and using the ATTACH clause attach to the running job and then issue the STATUS command:-

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: /u01/app/oracle/dpump/admin.dmp
    bytes written: 4,096

Worker 1 Status:
  Process Name: DW00
  State: EXECUTING
  Object Schema: ADMIN
  Object Name: TEST_01
  Object Type: DATABASE_EXPORT/SCHEMA/PACKAGE_BODIES/PACKAGE/PACKAGE_BODY
  Completed Objects: 78
  Worker Parallelism: 1

Import> status

Job: SYS_IMPORT_SCHEMA_01
  Operation: IMPORT
  Mode: SCHEMA
  State: EXECUTING
  Bytes Processed: 2,788,707,576
  Percent Done: 99
  Current Parallelism: 6
  Job Error Count: 0
  Dump File: /apps/keplero/backup/ORA11G/dpump/cishd-34173_%u.dmp
  Dump File: /apps/keplero/backup/ORA11G/dpump/cishd-34173_01.dmp
  Dump File: /apps/keplero/backup/ORA11G/dpump/cishd-34173_02.dmp
  Dump File: /apps/keplero/backup/ORA11G/dpump/cishd-34173_03.dmp
  Dump File: /apps/keplero/backup/ORA11G/dpump/cishd-34173_04.dmp
  Dump File: /apps/keplero/backup/ORA11G/dpump/cishd-34173_05.dmp
  Dump File: /apps/keplero/backup/ORA11G/dpump/cishd-34173_06.dmp

Worker 1 Status:
  Process Name: DW00
  State: EXECUTING
  Object Schema: XTP_AC
  Object Name: SYS_C0063284986
  Object Type: SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
  Completed Objects: 1,120
  Worker Parallelism: 1

Worker 2 Status:
  Process Name: DW01
  State: WORK WAITING

Worker 3 Status:
  Process Name: DW02
  State: WORK WAITING

Worker 4 Status:
  Process Name: DW03
  State: WORK WAITING

Worker 5 Status:
  Process Name: DW04
  State: WORK WAITING

Worker 6 Status:
  Process Name: DW05
  State: WORK WAITING

Import>

2) Querying DBA_DATAPUMP_JOBS view:-

select * from dba_datapump_jobs;

The STATE column of the above view would give you the status of the JOB to show whether EXPDP or IMPDP jobs are still running, or have terminated with either a success or failure status.

3) Querying V$SESSION_LONGOPS & V$SESSION views:-

SELECT b.username, a.sid, b.opname, b.target,
            round(b.SOFAR*100/b.TOTALWORK,0) || '%' as "%DONE", b.TIME_REMAINING,
            to_char(b.start_time,'YYYY/MM/DD HH24:MI:SS') start_time
     FROM v$session_longops b, v$session a
     WHERE a.sid = b.sid      ORDER BY 6;

4) Querying V$SESSION_LONGOPS & V$DATAPUMP_JOB views:-

SELECT sl.sid, sl.serial#, sl.sofar, sl.totalwork, dp.owner_name, dp.state, dp.job_mode
     FROM v$session_longops sl, v$datapump_job dp
     WHERE sl.opname = dp.job_name
     AND sl.sofar != sl.totalwork;

5) Querying all the related views with a single query:-

select x.job_name,b.state,b.job_mode,b.degree
, x.owner_name,z.sql_text, p.message
, p.totalwork, p.sofar
, round((p.sofar/p.totalwork)*100,2) done
, p.time_remaining
from dba_datapump_jobs b
left join dba_datapump_sessions x on (x.job_name = b.job_name)
left join v$session y on (y.saddr = x.saddr)
left join v$sql z on (y.sql_id = z.sql_id)
left join v$session_longops p ON (p.sql_id = y.sql_id)
WHERE y.module='Data Pump Worker'
AND p.time_remaining > 0;

6) Use the following procedure and replace the JOB_OWNER & JOB_NAME as per your env. which you fetch from import.log:-

;;;
Import: Release 12.1.0.2.0 - Production on Thu Jun 29 00:29:09 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
;;;
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_04" successfully loaded/unloaded

Here the JOB_OWNER is SYSTEM and JOB_NAME is SYS_IMPORT_FULL_04.

And below is the procedure:-

SET SERVEROUTPUT ON
DECLARE
  ind NUMBER;              
  h1 NUMBER;               
  percent_done NUMBER;     
  job_state VARCHAR2(30);  
  js ku$_JobStatus;        
  ws ku$_WorkerStatusList; 
  sts ku$_Status;          
BEGIN
h1 := DBMS_DATAPUMP.attach('JOB_NAME', 'JOB_OWNER');
dbms_datapump.get_status(h1,
           dbms_datapump.ku$_status_job_error +
           dbms_datapump.ku$_status_job_status +
           dbms_datapump.ku$_status_wip, 0, job_state, sts);
js := sts.job_status;
ws := js.worker_status_list;
      dbms_output.put_line('*** Job percent done = ' ||
                           to_char(js.percent_done));
      dbms_output.put_line('restarts - '||js.restart_count);
ind := ws.first;
  while ind is not null loop
    dbms_output.put_line('rows completed - '||ws(ind).completed_rows);
    ind := ws.next(ind);
  end loop;
DBMS_DATAPUMP.detach(h1);
end;
/ 

7) Also for any errors you can check the alert log and query the DBA_RESUMABLE view.

select name, sql_text, error_msg from dba_resumable;

That’s all what I can think of at the moment, would add the queries to this post if I find another view which can be used to get the information of the datapump jobs.