데이터 펌프 걸어 놓고 모니터링 하는 방법
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:-
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
<span style="color: #3366ff;"><strong>Export> 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 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 |
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:-
|
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:-
|
1 2 3 4 5 6 |
<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> |
4) Querying V$SESSION_LONGOPS & V$DATAPUMP_JOB views:-
|
1 2 3 4 5 |
<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> |
5) Querying all the related views with a single query:-
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
<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 > 0; </strong></span> |
6) Use the following procedure and replace the JOB_OWNER & JOB_NAME as per your env. which you fetch from import.log:-
|
1 2 3 4 5 6 7 8 |
;;; 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:-
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
<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> |
7) Also for any errors you can check the alert log and query the DBA_RESUMABLE view.
|
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.