내맘대로긍정이 알려주는
Oracle 23ai 신기능
무료 세미나 발표자료
다운로드
trending_flat
OS환경 : Oracle Linux 6.8 (64bit)
DB 환경 : Oracle Database 11.2.0.4
쿼리 : 데이터 펌프 작업 모니터링 쿼리, datapump 작업 모니터링, datapump 상태
실행중인 datapump 작업을 모니터링하는데 사용할 수있는 쿼리 리스트임
사전작업
데이터 펌프용 디렉토리 생성 및 권한부여
1 2 3 | SQL> create directory pumptest as '/home/oracle/'; grant read, write on directory pumptest to public; |
테이블 row 확인
1 2 3 4 5 6 7 | SQL> select count(*) from random; COUNT(*) ---------- 10352000 1 row selected. |
datapump 실행
jsh유저의 모든 오브젝트 expdp 실행 job_name=pump_job
1 | $ expdp system/oracle dumpfile=jsh.dmp directory=pumptest schemas=jsh job_name=pump_job |
expdp 조회
expdp 잡 실행중 Ctrl+C 를 눌려 빠져나온 뒤 status 명령으로 상태 조회
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 | $ expdp system/oracle dumpfile=jsh.dmp directory=pumptest schemas=jsh job_name=pump_job Export: Release 12.2.0.1.0 - Production on Tue Feb 26 10:55:44 2019 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production Starting "SYSTEM"."PUMP_JOB": system/******** dumpfile=jsh.dmp directory=pumptest schemas=jsh job_name=pump_job Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS ^C <- Ctrl+C 입력 Export> status <- status 명령으로 조회 Job: PUMP_JOB Operation: EXPORT Mode: SCHEMA State: EXECUTING Bytes Processed: 0 Current Parallelism: 1 Job Error Count: 0 Job heartbeat: 1 Dump File: /home/oracle/jsh.dmp bytes written: 12,288 Worker 1 Status: Instance ID: 1 Instance name: CCDB12 Host name: oracle12 Object start time: Tuesday, 26 February, 2019 10:55:51 Object status at: Tuesday, 26 February, 2019 10:55:51 Process Name: DW00 State: EXECUTING |
쿼리로 조회
1. DBA_DATAPUMP_JOBS 뷰를 이용해 조회
1 2 3 4 5 6 7 8 9 10 11 12 13 | SQL> col OWNER_NAME for a10 col JOB_NAME for a10 col OPERATION for a10 col JOB_MODE for a10 col STATE for a10 select * from dba_datapump_jobs; OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS ---------- ---------- ---------- ---------- ---------- ---------- ----------------- ----------------- SYSTEM PUMP_JOB EXPORT SCHEMA EXECUTING 1 1 3 1 row selected. |
STATE 컬럼은 EXPDP 또는 IMPDP 작업이 여전히 실행 중인지 또는 성공 또는 실패 상태로 종료되었는지 나타내는 JOB의 상태를 나타냄
2. V$SESSION_LONGOPS 및 V$SESSION 뷰를 이용해 조회
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | SQL> col username for a10 col opname for a33 col target for a10 col %DONE for a10 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; USERNAME SID OPNAME TARGET %DONE TIME_REMAINING START_TIME ---------- ---------- --------------------------------- ---------- ---------- -------------- ------------------- SYS 64 Gather Table Partition Statistics (null) 100% 0 2019/02/25 22:00:33 SYS 64 Gather Table's Index Statistics (null) 100% 0 2019/02/25 22:00:33 SYS 64 Gather Table Partition Statistics (null) 100% 0 2019/02/25 22:00:33 SYSTEM 54 PUMP_JOB (null) 0% (null) 2019/02/26 10:40:36 182 rows selected. |
3. V$SESSION_LONGOPS 및 V$DATAPUMP_JOB 뷰를 이용해 조회
1 2 3 4 5 6 7 8 9 | SQL> 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; SID SERIAL# SOFAR TOTALWORK OWNER_NAME STATE JOB_MODE ---------- ---------- ---------- ---------- ---------- ---------- ---------- 64 46128 0 1 SYSTEM COMPLETING SCHEMA |
4. 단일 쿼리로 모든 관련 뷰를 조회
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 | SQL> col job_name for a10 col state for a10 col job_mode for a10 col degree for 999 col owner_name for a10 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; JOB_NAME STATE JOB_MODE DEGREE OWNER_NAME ---------- ---------- ---------- ------ ---------- SQL_TEXT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- MESSAGE -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- TOTALWORK SOFAR DONE TIME_REMAINING ---------- ---------- ---------- -------------- PUMP_JOB EXECUTING SCHEMA 1 SYSTEM BEGIN SYS.KUPW$WORKER.MAIN('PUMP_JOB', 'SYSTEM', 0); END; Rowid Range Scan: JSH.RANDOM: 184808 out of 362428 Blocks done 362428 184808 50.99 7 |
5. 아래 내용을 보고 JOB_NAME과 JOB_OWNER을 확인
expdp시 최상단에 나오는 내용
1 2 3 4 5 6 7 | Export: Release 12.2.0.1.0 - Production on Tue Feb 26 10:55:44 2019 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production Starting "SYSTEM"."PUMP_JOB": system/******** dumpfile=jsh.dmp directory=pumptest schemas=jsh job_name=pump_job Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA |
여기서 JOB_OWNER는 SYSTEM이고 JOB_NAME은 PUMP_JOB임
pl/sql로 조회
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 | SQL> 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; / *** Job percent done = 0 restarts - 0 rows completed - 10352000 PL/SQL procedure successfully completed. SQL> / *** Job percent done = 99 restarts - 0 rows completed - 0 |
12열의 JOB_NAME과 JOB_OWNER 부분을 알맞게 수정한 후 실행
6. DBA_RESUMABLE 뷰를 이용해 발생하는 에러를 확인
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | SQL> col name for a15 col sql_text for a60 col error_msg for a20 select name, sql_text, error_msg from dba_resumable; NAME SQL_TEXT ERROR_MSG --------------- ------------------------------------------------------------ -------------------- SYSTEM.PUMP_JOB SELECT COUNT(*) FROM v$open_cursor WHERE sid = SYS_CONTEXT(' (null) USERENV', 'SID') AND cursor_type = 'OPEN_PLSQL' SYSTEM.PUMP_JOB BEGIN :1 := sys.kupc$que_int.transceive_int(:2, :3, :4, :5, (null) .1 :6); END; 2 rows selected. |
참조 : https://databaseinternalmechanism.com/2016/09/13/how-to-monitor-datapump-jobs/
'ORACLE > Admin' 카테고리의 다른 글
| 오라클 다른 포트 리스너 설정(로컬리스너 설정)(RAC포함) (0) | 2019.10.17 |
|---|---|
| 오라클 30gb 정통 export import 테스트, 시간 측정 (0) | 2019.10.14 |
| DB 관련 사이트 리스트 (0) | 2019.02.25 |
| 오라클 db name 변경 방법 nid, controlfile 재생성 (1) | 2019.02.25 |
| grid Infrastructure의 ocr, asm 제거하기 (0) | 2019.02.20 |