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

haisins

3년 ago

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

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

<span style="color: #3366ff;"><strong>Export&gt; status</strong></span>

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

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&gt; 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&gt;

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

<span style="color: #3366ff;"><strong>select * from dba_datapump_jobs;
</strong></span>

1 2	<span style="color: #3366ff;"><strong>select * from dba_datapump_jobs; </strong></span>

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

<strong><span style="color: #3366ff;">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;
</span></strong>

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

<strong><span style="color: #3366ff;">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;
</span></strong>

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

<span style="color: #3366ff;"><strong>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 &gt; 0;
</strong></span>

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

;;;

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

;;;

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

<span style="color: #3366ff;"><strong>
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;
/</strong> </span>

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.

<span style="color: #3366ff;"><strong>select name, sql_text, error_msg from dba_resumable;
</strong></span>

1 2	<span style="color: #3366ff;"><strong>select name, sql_text, error_msg from dba_resumable; </strong></span>

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.