Data pump Process Architecture -Master Table, Worker process

Data Pump jobs use a master table, a master process, and worker processes to perform the work and keep track of the progress. For every Data Pump Export and Data Pump Import job, a master process is created. The master process controls the entire job, including communicating with the clients, creating and controlling a pool of worker processes, and performing logging operations.

Let's see an example.
1)Starting data pump jobs in one session.
expdp schemas=shaik userid=shaik/a dumpfile=shaik_30_04_2009.dmp logfile=arju_20_04_09.log

2)Query from dba_datapump_session to know the data pump job status.

set lines 150 pages 100
col program for a20
col username for a5
col spid for a7
col job_name for a25
select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') "DATE", s.program, s.sid,
   s.status, s.username, d.job_name, p.spid, s.serial#, p.pid
from v$session s, v$process p, dba_datapump_sessions d
where p.addr=s.paddr and s.saddr=d.saddr;
DATE                PROGRAM                  SID STATUS   USERN JOB_NAME                  SPID    SERIAL#     PID
------------------- -------------------- ------- -------- ----- ------------------------- ------- ------- -------
2009-04-30 16:49:46 expdp.exe                148 ACTIVE   SHAIK SYS_EXPORT_SCHEMA_01      3164         14      16
2009-04-30 16:49:46 ORACLE.EXE (DM00)        144 ACTIVE   SHAIK SYS_EXPORT_SCHEMA_01      5376         20      17

SQL> /

DATE                PROGRAM                  SID STATUS   USERN JOB_NAME                  SPID    SERIAL#     PID
------------------- -------------------- ------- -------- ----- ------------------------- ------- ------- -------
2009-04-30 16:49:50 expdp.exe                148 ACTIVE   SHAIK SYS_EXPORT_SCHEMA_01      3164         14      16
2009-04-30 16:49:50 ORACLE.EXE (DM00)        144 ACTIVE   SHAIK SYS_EXPORT_SCHEMA_01      5376         20      17
2009-04-30 16:49:50 ORACLE.EXE (DW01)        141 ACTIVE   SHAIK SYS_EXPORT_SCHEMA_01      7352       7      20

SQL> /

no rows selected

You see in the above output I ran the same query three times. First one is just after submitting data pump jobs. Second query I ran few seconds after data pump job is submitted. And third one is after data pump job is completed.

In the first output of the query just a master table is created as well as master process(DM00). This master process controls the entire job, including communicating with the clients, creating and controlling a pool of worker processes, and performing logging operations.

In the second output, worker process is created (DW01). Since we did not set any parallelism so by default PARALLEL is 1 (one) and there is only one worker process. If we set PARALLEL to more than 1, then we would see multiple worker process like DW01, DW02 etc. The worker processes are responsible for updating the master table with information on the status (pending, completed, failed) of each object being processed. This information is used to provide the detailed information required to restart stopped Data Pump jobs.

In the third output we see no rows as data pump process is disappeared when the data pump job is completed or it stops.

If you say architecture of data pump job then,
data pump job is initiated after creating Master Table. This table is created at the beginning of a Data Pump operation and is dropped at the end of the successful completion of a Data Pump operation. However if you kill the job by command kill_job in the interactive prompt then the Master Table can also be dropped. If a job is stopped using the stop_job interactive command or if the job is terminated unexpectedly, the Master Table will be retained.

Note that during data pump jobs we can set the keep_master parameter Y which ensure to retain the Master Table at the end of a successful job.

Suppose my datapump job command is,
C:>expdp schemas=shaik userid=shaik/a dumpfile=shaik_30_04_.dmp logfile=shaik_20_04_09.log keep_master=y
Export: Release 10.2.0.1.0 - Production on Thursday, 30 April, 2009 17:11:09

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SHAIK"."SYS_EXPORT_SCHEMA_02": schemas=shaik userid=shaik/******** dumpfile=shaik_30_04_.dmp logfile=shaik_20_04_0
9.log keep_master=y

As we set keep_master=y so we can see master table "SHAIK"."SYS_EXPORT_SCHEMA_02" anytime after data pump jobs is completed.

Structure of a master table is shown below.

SQL> desc "SHAIK"."SYS_EXPORT_SCHEMA_02";
Name                                                  Null?    Type
----------------------------------------------------- -------- ------------------------------------
PROCESS_ORDER                                                  NUMBER
DUPLICATE                                                      NUMBER
DUMP_FILEID                                                    NUMBER
DUMP_POSITION                                                  NUMBER
DUMP_LENGTH                                                    NUMBER
DUMP_ALLOCATION                                                NUMBER
COMPLETED_ROWS                                                 NUMBER
ERROR_COUNT                                                    NUMBER
ELAPSED_TIME                                                   NUMBER
OBJECT_TYPE_PATH                                               VARCHAR2(200)
OBJECT_PATH_SEQNO                                              NUMBER
OBJECT_TYPE                                                    VARCHAR2(30)
IN_PROGRESS                                                    CHAR(1)
OBJECT_NAME                                                    VARCHAR2(500)
OBJECT_LONG_NAME                                               VARCHAR2(4000)
OBJECT_SCHEMA                                                  VARCHAR2(30)
ORIGINAL_OBJECT_SCHEMA                                         VARCHAR2(30)
PARTITION_NAME                                                 VARCHAR2(30)
SUBPARTITION_NAME                                              VARCHAR2(30)
FLAGS                                                          NUMBER
PROPERTY                                                       NUMBER
COMPLETION_TIME                                                DATE
OBJECT_TABLESPACE                                              VARCHAR2(30)
SIZE_ESTIMATE                                                  NUMBER
OBJECT_ROW                                                     NUMBER
PROCESSING_STATE                                               CHAR(1)
PROCESSING_STATUS                                              CHAR(1)
BASE_PROCESS_ORDER                                             NUMBER
BASE_OBJECT_TYPE                                               VARCHAR2(30)
BASE_OBJECT_NAME                                               VARCHAR2(30)
BASE_OBJECT_SCHEMA                                             VARCHAR2(30)
ANCESTOR_PROCESS_ORDER                                         NUMBER
DOMAIN_PROCESS_ORDER                                           NUMBER
PARALLELIZATION                                                NUMBER
UNLOAD_METHOD                                                  NUMBER
GRANULES                                                       NUMBER
SCN                                                            NUMBER
GRANTOR                                                        VARCHAR2(30)
XML_CLOB                                                       CLOB
NAME                                                           VARCHAR2(30)
VALUE_T                                                        VARCHAR2(4000)
VALUE_N                                                        NUMBER
IS_DEFAULT                                                     NUMBER
FILE_TYPE                                                      NUMBER
USER_DIRECTORY                                                 VARCHAR2(4000)
USER_FILE_NAME                                                 VARCHAR2(4000)
FILE_NAME                                                      VARCHAR2(4000)
EXTEND_SIZE                                                    NUMBER
FILE_MAX_SIZE                                                  NUMBER
PROCESS_NAME                                                   VARCHAR2(30)
LAST_UPDATE                                                    DATE
WORK_ITEM                                                      VARCHAR2(30)
OBJECT_NUMBER                                                  NUMBER
COMPLETED_BYTES                                                NUMBER
TOTAL_BYTES                                                    NUMBER
METADATA_IO                                                    NUMBER
DATA_IO                                                        NUMBER
CUMULATIVE_TIME                                                NUMBER
PACKET_NUMBER                                                  NUMBER
OLD_VALUE                                                      VARCHAR2(4000)
SEED                                                           NUMBER
LAST_FILE                                                      NUMBER
USER_NAME                                                      VARCHAR2(30)
OPERATION                                                      VARCHAR2(30)
JOB_MODE                                                       VARCHAR2(30)
CONTROL_QUEUE                                                  VARCHAR2(30)
STATUS_QUEUE                                                   VARCHAR2(30)
REMOTE_LINK                                                    VARCHAR2(4000)
VERSION                                                        NUMBER
DB_VERSION                                                     VARCHAR2(30)
TIMEZONE                                                       VARCHAR2(64)
STATE                                                          VARCHAR2(30)
PHASE                                                          NUMBER
GUID                                                           RAW(16)
START_TIME                                                     DATE
BLOCK_SIZE                                                     NUMBER
METADATA_BUFFER_SIZE                                           NUMBER
DATA_BUFFER_SIZE                                               NUMBER
DEGREE                                                         NUMBER
PLATFORM                                                       VARCHAR2(101)
ABORT_STEP                                                     NUMBER
INSTANCE                                                       VARCHAR2(60)

Let's see the number of rows populated inside master table.
SQL> select count(*) from "SHAIK"."SYS_EXPORT_SCHEMA_02";

COUNT(*)
--------
1125

We see in the master table the total rows are 1125. In fact master table contains all data pump log messages. It is used to track the detailed progress information of a Data Pump job - which is more than the log messages. It conatins following information.

- Completed rows of a table.

- Total number of errors during data pump operation.

- Elapsed time for each table to do data pump export/import operation.

- The current set of dump files.

- The current state of every object exported or imported and their locations in the dump file set.

- The job's user-supplied parameters.

- The status of every worker process.

- The state of current job status and restart information.

- The dump file location, the directory name information.

And many other useful information.

Comments

Post a Comment

Popular posts from this blog

ORA-04062: timestamp of procedure has been changed

Problem Description In the database I have created one procedure named a as below. create or replace procedure a(a number) as begin insert into t1 values(1); commit; end; / Now after creating database link using remote database machine whenever I access this procedure "A" it executes successfully and I get value "1" in table t1. Like below in example where orastdby_m is the database link, shaik is the schema name and value 1 is the argument value though argument value is not used in the procedure. SQL> exec shaik.a@orastdby_m(1); PL/SQL procedure successfully completed. Now in the source database machine I changed the procedure as below. Though you can change anything like any literal; adding space or remove space. I changed value to be inserted from 1 to 2. create or replace procedure a(a number) as begin insert into t1 values(2); commit; end; / Now in the other database whenever I execute the procedure using database link it throws error ORA-04062. But subseq...
Read more

ORA-00939: too many arguments -when case arguments exceed 255

Problem Description In all Oracle 10.1g and oracle 10.2.0.1, 10.2.0.2, 10.2.0.3 whenever there is more than 255 arguments inside CASE statements then following error is returned. ORA-00939: too many arguments for function But surprisingly on Oracle 9.2 database the same code does not return any error. Cause of the Problem As we see the same code return error in 10g but it is well on 9.2. In fact this is problem of oracle 9.2 that it does not return any error. It should return error if there is more than 255 arguments inside CASE statements. In oracle 9.2 this is bug number 2909927 and this bug is fixed in oracle 10.1 and 10.2. That's why in 10.2 and 10.1 error returns if arguments exceed 255. Up to Oracle 10.2.0.3 and earlier version, the arguments of CASE statement is limit to 255 and it does not support exceed over 255 and hence error return if it exceed. However in Oracle 10.2.0.4 and later the arguments limit of CASE statement has increased to 32767. Solution of the Problem Opt...
Read more

ORA-31655: no data or metadata objects selected for job

Problem Description You are going to do data pump export operation in order to export objects based on filtering via EXCLUDE or INCLUDE parameter of expdp. In this example you wanted tables 'ACCOUNT_GROUP','ADDRESS','AREA_GROUP' and all the tables starting with word CV(like 'CV', 'CV_EXPERIENCE', 'CV_EDUCATION' etc) Your parameter file is like below. userid=smilebd/a directory=d dumpfile=b.dmp include =TABLE:"IN('ACCOUNT_GROUP','ADDRESS','AREA_GROUP')" include =TABLE:"LIKE 'CV%' " And you invoke expdp as expdp parfile=d:\parfile.txt from command line. But it fails with below message on my windows PC. C:\>expdp parfile=d:\parfile.txt Export: Release 10.1.0.4.2 - Production on Thursday, 29 January, 2009 14:53 Copyright (c) 2003, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc tion With the Partitioning, OLAP and Data Mining ...
Read more