MONITORING DATAPUMP SESSIONS/JOBS ORACLE
Export the schema from PROD database using the expdp datapump session
[oracle@exdbadm01 ~]$ time expdp directory=EDP dumpfile=testexdbx.dmp logfile=testexdbx.log schemas=test filesize=300M parallel=8 access_method=direct_path cluster=Y
Export: Release 11.2.0.4.0 - Production on Tue Jun 15 22:39:35 2021
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01": /******** AS SYSDBA directory=EDP dumpfile=testexdbx.dmp logfile=testexdbx.log schemas=test filesize=300M parallel=8 access_method=direct_path cluster=Y
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 2.933 GB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
We can monitor the datapump status by attaching the job name to attach parameter. we can see 8 parallel jobs run for each table with workers processes
[oracle@exdbadm01 ~]$ export ORACLE_SID=exdbx1
[oracle@exdbadm01 ~]$ expdp attach=SYS_EXPORT_SCHEMA_01
Export: Release 11.2.0.4.0 - Production on Tue Jun 15 22:57:49 2021
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Job: SYS_EXPORT_SCHEMA_01
Owner: SYS
Operation: EXPORT
Creator Privs: TRUE
GUID: C4D20A056AB61B8EE0532438A8C08164
Start Time: Tuesday, 15 June, 2021 22:39:47
Mode: SCHEMA
Instance: exdbx1
Max Parallelism: 8
EXPORT Job Parameters:
Parameter Name Parameter Value:
CLIENT_COMMAND /******** AS SYSDBA directory=EDP dumpfile=testexdbx. dmp logfile=testexdbx.log schemas=test filesize=300M parallel=8 access_method=di rect_path cluster=Y
DATA_ACCESS_METHOD DIRECT_PATH
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 8
Job Error Count: 0
Dump File: /home/oracle/expdp/testexdbx.dmp
size: 314,572,800
bytes written: 61,440
Worker 1 Status:
Process Name: DW00
State: WORK WAITING
Worker 2 Status:
Process Name: DW00
State: EXECUTING
Object Schema: TEST
Object Name: TABX
Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
Completed Objects: 1
Total Objects: 8
Worker Parallelism: 1
Worker 3 Status:
Process Name: DW02
State: EXECUTING
Object Schema: TEST
Object Name: SALES
Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
Completed Objects: 1
Total Objects: 8
Worker Parallelism: 1
Worker 4 Status:
Process Name: DW01
State: EXECUTING
Object Schema: TEST
Object Name: SALES_TAB_COPY
Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
Completed Objects: 1
Total Objects: 8
Worker Parallelism: 1
Worker 5 Status:
Process Name: DW04
State: EXECUTING
Object Schema: TEST
Object Name: DEMO_DUP
Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
Completed Objects: 1
Total Objects: 8
Worker Parallelism: 1
Worker 6 Status:
Process Name: DW02
State: EXECUTING
Object Schema: TEST
Object Name: SALES_T
Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
Completed Objects: 1
Total Objects: 8
Worker Parallelism: 1
Worker 7 Status:
Process Name: DW06
State: EXECUTING
Object Schema: TEST
Object Name: SALES_TAB
Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
Completed Objects: 1
Total Objects: 8
Worker Parallelism: 1
Worker 8 Status:
Process Name: DW03
State: EXECUTING
Object Schema: TEST
Object Name: SALES_TAB_COPY_TEMP
Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
Completed Objects: 1
Total Objects: 8
Worker Parallelism: 1
Export>
Here we have RAC database. So we used cluster parameter to speed up the export with resources like CPU from both nodes. Here both nodes have split the work with worker slave process(dw00 – dw08). We can see one master process(dm00) on node 1
[oracle@exdbadm01 ~]$ ps -ef|grep 'ora_dm'|grep -v 'grep'
oracle 7066 1 0 22:39 ? 00:00:03 ora_dm00_exdbx1
[oracle@exdbadm01 ~]$ ps -ef|grep 'ora_dw'|grep -v 'grep'
oracle 7078 1 1 22:39 ? 00:00:19 ora_dw00_exdbx1
oracle 7105 1 0 22:40 ? 00:00:02 ora_dw02_exdbx1
oracle 7107 1 0 22:40 ? 00:00:00 ora_dw04_exdbx1
oracle 7109 1 0 22:40 ? 00:00:00 ora_dw06_exdbx1
[oracle@exdbadm02 ~]$ ps -ef|grep 'ora_dw'|grep -v 'grep'
oracle 12739 1 0 22:40 ? 00:00:01 ora_dw00_exdbx2
oracle 12741 1 0 22:40 ? 00:00:00 ora_dw01_exdbx2
oracle 12743 1 0 22:40 ? 00:00:00 ora_dw02_exdbx2
oracle 12745 1 0 22:40 ? 00:00:00 ora_dw03_exdbx2
We can also monitor the datapump job using below query to get
kish@exdbx<>@colfrmt
Procedure created.
Procedure created.
kish@exdbx<>select INST_ID,JOB_NAME,OWNER_NAME,OPERATION,JOB_MODE,STATE,WORKERS from GV_$DATAPUMP_JOB;
INST_ID JOB_NAME OWNER_NAME OPERATION JOB_MODE STATE WORKERS
---------- --------------- --------------- ------------------------------ --------------- ------------------------------ ----------
1 SYS_EXPORT_SCHE SYS EXPORT SCHEMA EXECUTING 8
MA_01
Use below query to identify the sid and serial# of the datapump sessions
kish@exdbx<>select s.sid,s.serial#,s.username,s.event,dds.OWNER_NAME,
2 dds.JOB_NAME,dds.INST_ID,dds.SESSION_TYPE from DBA_DATAPUMP_SESSIONS dds
3 inner join v$session s on dds.SADDR=s.saddr;
SID SERIAL# USERNAME EVENT OWNER_NAME JOB_NAME INST_ID SESSION_TYPE
---------- ---------- --------------- ---------------------------------------------------------------- --------------- --------------- ---------- --------------
57 289 SYS wait for unread message on broadcast channel SYS SYS_EXPORT_SCHE 1 DBMS_DATAPUMP
MA_01
68 7 SYS wait for unread message on broadcast channel SYS SYS_EXPORT_SCHE 1 MASTER
MA_01
60 9 SYS wait for unread message on broadcast channel SYS SYS_EXPORT_SCHE 1 WORKER
MA_01
62 1447 SYS direct path read SYS SYS_EXPORT_SCHE 1 WORKER
MA_01
70 31 SYS wait for unread message on broadcast channel SYS SYS_EXPORT_SCHE 1 WORKER
MA_01
71 11 SYS wait for unread message on broadcast channel SYS SYS_EXPORT_SCHE 1 WORKER
MA_01
63 399 NULL VKRM Idle SYS SYS_EXPORT_SCHE 2 WORKER
MA_01
50 1 SYS SQL*Net message from client SYS SYS_EXPORT_SCHE 2 WORKER
MA_01
62 1447 SYS direct path read SYS SYS_EXPORT_SCHE 2 WORKER
MA_01
9 rows selected.
Check execution status of each slave worker process
kish@exdbx<>select OWNER_NAME,JOB_NAME,STATE,DEGREE,DATAPUMP_SESSIONS,OPERATION,JOB_MODE from dba_datapump_jobs;
OWNER_NAME JOB_NAME STATE DEGREE DATAPUMP_SESSIONS OPERATION JOB_MODE
--------------- --------------- ------------------------------ ---------- ----------------- ------------------------------------------------------------------------------------------------------------------------ ---------------
SYS SYS_EXPORT_SCHE EXECUTING 8 10 EXPORT SCHEMA
MA_01
SYS SYS_EXPORT_TABL NOT RUNNING 0 0 EXPORT TABLE
E_05
SYS SYS_EXPORT_TABL NOT RUNNING 0 0 EXPORT TABLE
E_01
SYS SYS_EXPORT_TABL NOT RUNNING 0 0 EXPORT TABLE
E_02
SYS SYS_EXPORT_TABL NOT RUNNING 0 0 EXPORT TABLE
E_03
SYS SYS_EXPORT_TABL NOT RUNNING 0 0 EXPORT TABLE
E_04
6 rows selected.
We can also map session longops with the datapump views to get more info on the export jobs
kish@exdbx<>select ddj.job_name,ddj.state,ddj.job_mode,sl.sid,
round(100*(sl.SOFAR/sl.TOTALWORK),2) PCT,s.event,sl.message,
sl.START_TIME,sl.ELAPSED_SECONDS
from dba_datapump_jobs ddj
inner join dba_datapump_sessions dds on ddj.job_name=dds.job_name
inner join v$session s on dds.saddr=s.saddr
inner join v$session_longops sl on s.sid=sl.sid; 2 3 4 5 6 7
JOB_NAME STATE JOB_MODE SID PCT EVENT MESSAGE START_TIM ELAPSED_SECONDS
--------------- ------------------------------ --------------- ---------- -------------------------------------------------- ---------------------------------------------------------------- --------------- --------- ---------------
SYS_EXPORT_SCHE EXECUTING SCHEMA 62 8.46 direct path read Rowid Range Sca 15-JUN-21 6291
MA_01 n: TEST.SALES:
21954 out of 2
59642 Blocks do
ne
SYS_EXPORT_SCHE EXECUTING SCHEMA 62 8.46 direct path read Rowid Range Sca 15-JUN-21 6291
MA_01 n: TEST.SALES:
21954 out of 2
59642 Blocks do
ne
SYS_EXPORT_SCHE EXECUTING SCHEMA 68 0 wait for unread message on broadcast channel SYS_EXPORT_SCHE 15-JUN-21 6344
MA_01 MA_01: EXPORT :
0 out of 3004
MB done
Elapsed: 00:00:00.19