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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s