Data Pump:

Data pump is just mordern name given to the features and utilities of Logical Backup enabled by Export Data Pump (expdp) and Import Data Pump (impdp).

Data Pump Architecture:

Flowchart in Details:

+----------------+     +---------------------------+     +-----------------------------+     +-------------------------------+
|  expdp/impdp   | --> |      Master Process       | --> |         Worker process      | --> |     Sorce/Target Database     |
| client utility |     | corporate job and workers |     |unload/load metadata and rows|     | Table, indexes, code, grants  |
+----------------+     +------------+--------------+     +----------------+------------+     +-------------------------------+
                                    |                                     |
               +--------------------+           +-------------------------+-------------+
               |                                |                                       |
               v                                v                                       v
+-----------------------------+    +-------------------------+           +-----------------------------+
|         Master Table        |    |     Directory Object    |           |       Dump and Log Files    |
| Stores job stare and queue  |    | Maps DB Name to OS Path |           | Binary data + execution log |
+-----------------------------+    +------------+------------+           +-------------+---------------+
                                                |                                      |
                                                v                                      v
                                   +---------------------------+        +------------------------------+
                                   |       Access Method       |        |          Parallelism         |
                                   | Direct path/external path |        | Multiple workers/dump files  |
                                   +---------------------------+        +------------------------------+

Architecture Explanation:

The client starts or attaches to a server-side Data Pump.
The master process co-ordinates object order, job stare and worker allocation.
Workers unload or load metadata and table data.
A master table stores restartable job state in the job owner's schema.
The directory object maps a database name to an OS path used for dump and log files.

Logical Migration flow:

SOURCE ASSESSMENT          
       │-- version/charset/schema size/LOBs/partitions/invalids
       ▼  
TARGET PREPARATION
       │-- users/tablespaces/quotas/directory/capacity
       ▼  
CONSISTENT EXPORT
       │-- FLASHBLACK_TIME or FLASHBACK_SCN
       ▼ 
TRANSFER AND CHECKSUM 
       ▼ 
SQLFILE REVIEW
       ▼ 
IMPORT WITH REMAP_SCHEMA / REMAP_TABLESPACE
       ▼ 
VALIDATE OBJECTS / ROWS / GRANTS / INVALIDS / STATS
       ▼ 
CUTOVER OR ROLLBACK

Core Concepts:

Directory Object:

Database object that points to an OS Directory.

Dump File:

Binary Data pump file containing metadata and/or table data.

Log File:

Text execution record containing warning and errors.

Master Table:

stores job state and object-processes details.

PARALLEL:

Number of workers processes used by the job.

FLASHBACK_TIME/SCN:

Creates a transactionally consistent source snapshot.

REMAP_SCHEMA:

Imports source objects under another schema.

REMAP_TABLESPACE:

Places imported segements in a different target tablespace.

Directory Object and Permissions:

Operating-System Directory:

mkdir -p /u06/datapump chown -R oracle:oinstall /u06 chmod -R 750 /u06

Directory Directory Object:

create or replace directory data_pump_dir_app as '/u06/datapump';

grant read, write on directory data_pump_dir_app to dp_user;

Validation:

select owner, directory_name, directory_path from dba_directories where directory_name='DATA_PUMP_DIR_APP';

select grantee, table_name, privilege from dba_tab_privs where table_name = 'DATA_PUMP_DIR_APP' order by grantee, privilege;

Note: The database user needs READ/WRITE on the directory object, and the oracle softwere owner needs OS permission on the physical path.

Export and Import Modes:

+-------------+--------------------------+--------------------------------------------------------+
| Mode        | Parameter                | Use                                                    |
+-------------+--------------------------+--------------------------------------------------------+
| Schema      | SCHEMAS=APP_SRC          | Migrate one or more application schemas.               |
+-------------+--------------------------+--------------------------------------------------------+
| Table       | TABLES=APP_SRC.T1        | Move selected tables.                                  |
+-------------+--------------------------+--------------------------------------------------------+
| Full        | FULL=Y                   | Logical export of most user data and metadata.         |
+-------------+--------------------------+--------------------------------------------------------+
| Tablespace  | TABLESPACES=APP_DATA     | Export objects in selected tablespaces.                |
+-------------+--------------------------+--------------------------------------------------------+ 
| Network     | NETWORK_LINK=SRC_LINK    | Import directly without dump files.                    |
+-------------+--------------------------+--------------------------------------------------------+

Associated Views:

select owner_name, job_name, operation, job_mode, state, degree, attached_sessions, datapump_sessions from dba_datapump_jobs order by owner_name, job_name;

select owner_name, job_name, session_type, inst_id, saddr from dba_datapump_sessions order by owner_name, job_name, session_type;

select sid, serial#, opname, target_desc, sofar, totalwork, units, round(sofar*100/nullif(totalwork, 0), 2) as pct_done, elapsed_seconds, time_remaining from v$session_longops where opname like 'SYS_EXPORT%' or opname like 'SYS_IMPORT%' order by start_time;

Pre-Migration Checks:

Version and Character set:

select banner_full from v$version where banner_full like 'Oracle Database%';

select parameter, value from nls_database_parameters where parameter in ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET') order by parameter;

Schema size and object count:

select owner, round(sum(bytes)/1024/1024/1024, 2) size_gb from dba_segments where owner='APP_SRC' group by owner;

select object_type, status, count(*) as object_count from dba_objects where owner='APP_SRC' group by object_type, status order by object_type, status;

Table, LOBs, Dependencies, DB Links:

select table_name, num_rows, blocks, last_analyzed, tablespace_name, partitioned from dba_tables where owner='APP_SRC' order by table_name;

select owner, table_name, column_name, segment_name, tablespace_name from dba_lobs where owner='APP_SRC' order by table_name, column_name;

select owner, name, type, referenced_owner, referenced_name, referenced_type from dba_dependencies where owner='APP_SRC' order by name, referenced_owner, referenced_name;

select owner, db_link, username, username, host from dba_db_links where owner in ('APP_SRC', 'PUBLIC');

Complete Schema Migration Scenario:

Step 1: Create target tablespace

create tablespace app_data_new datafile '/u01/app/oracle/oradata/PROD/app_data_new01.dbf' size 1G autoextend on next 100M maxsize 2G;

create tablespace app_index_new datafile '/u01/app/oracle/oradata/PROD/app_index_new01.dbf' size 1G autoextend on next 100M maxsize 2G;

Step 2: Create Target user.

create user app_tgt identified by "app#target" default tablespace app_data_new temporary tablespace temp quota unlimited on app_data_new quota unlimited on app_index_new;

grant create session to app_tgt;

Step 3: Consistent schema export.

expdp system@oracle schemas=APP_SRC directory=DATA_PUMP_DIR_APP dumpfile=app_src_%U.dmp logfile=app_src_export.log parallel=4 filesize=1G flashback_time=systimestamp exclude=statistics metrics=yes logtime=all (?)

First connect 2 servers using listener and tnsname. The server in the left side is the one where we will use to control the right server where the actual data is present.

The directory datapump of the actual data server (that one is actually present in the right side server)

The actual export command which we run from left side server.

See the dump file is actually present in the right side server.

Step 4: Verify export

ls -lh /u06/datapump/app_src_*.dmp
grep -i "successfully completed" /u06/datapump/app_src_export.log
grep -Ei "ORA-|UDI-|LRM-" /U06/datapump/app_src_export.log

Step 5: Transfer and checksum

(?)

Generate sqlfile.

impdp system@oracle directory=DATA_PUMP_DIR_APP dumpfile=app_src_%U.dmp sqlfile=app_src_preview.sql remap_schema=APP_SRC:APP_TGT remap_tablespace=APP_DATA:APP_DATA_NEW remap_tablespace=APP_INDEX:APP_INDEX_NEW

(?) %U format variable, its different every time, so how can it be used dumpfile, also why there are 2 remap tablespace, and there are remap tablespace in sql file

Import with remapping.

impdp system@oracle directory=DATA_PUMP_DIR_APP dumpfile=app_src_%U.dmp logfile=app_tgt_import.log parallel=4 remap_schema=APP_SRC:APP_TGT remap_tablespace=APP_DATA:APP_DATA_NEW remap_tablespace=APP_INDEX:APP_INDEX_NEW exclude=statistics metrics=yes logtime=all

See the same table got imported into the same server in another schema.

Gather statictics:

BEGIN
   DBMS_STATS.GATHER_SCHEMA_STATS(
       ownname          => 'APP_TGT',
       estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
       method_opt       => 'FOR ALL COLUMNS SIZE AUTO',
       degree           => DBMS_STATS.AUTO_DEGREE,
       cascade          => TRUE
   );
END;

(?)

REMAP_SCHEMA and REMAP_TABLESPACE

REMAP_SCHEMA=APP_SRC:APP_TGT

REMAP_TABLESPACE=APP_DATA:APP_DATA_NEW 
REMAP_TABLESPACE=APP_INDEX:APP_INDEX_NEW
REMAP_TABLESPACE=APP_LOB:APP_LOB_NEW

Check Hard-Coded Source References

select owner, name, type, line, text, from dba_source where owner='APP_TGT' and upper(text) like '%APP_SRC.%' order by name, type, line;

Important: REMAP_SCHEMA does not rewrite every hard-coded schema name inside PL/SQL, views, jobs, synonyms, DB links, external tables, or application configuration.

Table-Level Migration:

expdp system@oracle tables=APP_SRC.CUSTOMERS , APP_SRC.ORDERS, APP_SRC.ORDER_ITEMS directory=DATA_PUMP_DIR_APP dumpfile=app_tables_%U.dmp logfile=app_tables_export.log parallel=2 flashback_time=systimestamp

impdp system@oracle tables=APP_SRC.CUSTOMERS , APP_SRC.ORDERS, APP_SRC.ORDER_ITEMS directory=DATA_PUMP_DIR_APP dumpfile=app_tables_%U.dmp logfile=app_tables_import.log remap_schema=APP_SRC:APP_TGT remap_tablespace=APP_DATA:APP_DATA_NEW

Existing Table Handling:

+---------------------------------+-------------------------------------------------------+
| TABLE_EXISTS_ACTION             |    Behavior                                           |
+---------------------------------+-------------------------------------------------------+
| SKIP                            | Default; existing table is not loaded.                |
| APPEND                          | Add rows to existing data.                            |
| TRUNCATE                        | Truncate target table, then load.                     |
| REPLACE                         | Drop, recreate, and load the table.                   |
+---------------------------------+-------------------------------------------------------+

Full Database Mode:

expdp system@SRCDB full=y directory=DATA_PUMP_DIR_APP dumpfile=full_db_%U.dmp logfile=full_db_export.log parallel=8 filesize=10G exclude=statistics metrics=yes logtime=full_db_import.log parallel=8 metrics=yes logtime=all

Filters and Transforms:

CONTENT=METADATA_ONLY
CONTENT=DATA_ONLY
EXCLUDE=STATISTICS
EXCLUDE=INDWX
INCLUDE=TABLE
INCLUDE=INDEX
QUERY=APP_SRC.ORDERS:"WHERE ORDER_DATE>=DATE '2026-01-01'"
TRANSFORM=SEGMENT_ATTRIBUTES:N
TRANSFORM=STORAGE:N
TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y

Recovery Impact: DISABLE_ARCHIVE_LOGGING reduces redo but may create unrecoverable operations. Review Data gaurd implications and take a fresh backup after import.

Network Link Migration:

create database link src_link connect to dp_user identified by "dp#source2" using 'SRCDB';

impdp system@TGTDB network_link=src_link schemas=app_src logfile=network_schema_import.log remap_schema=app_src:app_tgt remap_tablespace=app_data:app_data_new exclude=statistics

Parameter Files:

Export Parfile

-- app_src_export.par

schemas=APP_SRC
directory=DATA_PUMP_DIR_APP
dumpfile=app_src_%U.dmp
logfile=app_src_export.log
parallel=4 
filesize=1G
flashback_time=systimestamp
exclude=statistics
metrics=yes
logtime=all

expdp system@tnsname parfile=app_src_export.par

Import Parfile

-- app_tgt_import.par

schemas=APP_SRC
directory=DATA_PUMP_DIR_APP
dumpfile=app_src_%U.dmp
logfile=app_src_import.log
parallel=4 
remap_schema=APP_SRC:APP_TGT
remap_tablespace=APP_DATA:APP_DATA_NEW
remap_tablespace=APP_INDEX:APP_INDEX_NEW
exclude=statistics
metrics=yes
logtime=all

impdp system@tnsname parfile=app_tgt_import.par

Monitor and Control Jobs

select owner_name, job_name, operation, job_mode, state, degree, attached_sessions, datapump_sessions from dba_datapump_jobs order by owner_name, job_name;

expdp system@tnsname attach=SYS_EXPORT_SCHEMA_01

Interactive Commands:

STATUS
PARALLEL=6
STOP_JOB=IMMEDIATE
START_JOB
KILL_JOB
CONTINUE_CLIENT

STOP_JOB vs KILL_JOB: STOP_JOB preserves the master table for restart. KILL_JOB removes the job state.

Post import validation:

Object count and Invalids:

select object_type, status, count(*), object_count from dba_objects where owner='APP_TGT' group by object_type, status order by object_type, status;

select owner, object_name, object_type, status from dba_objects where owner='APP_TGT' and status='INVALID' order by object_type, object_name;

Compile Schema and check Errors:

BEGIN UTL_RECOMP.RECOMP_SERIAL('APP_TGT'); END; /

Select owner, name, type, line, position, text from dba_errors where owner='APP_TGT' order by name, sequence;

Segment Placement:

select tablespace_name, segment_type, count(*) segment_count, round(sum(bytes)/1024/1024/1024, 2) size_gb from dba_segments where owner='APP_TGT' group by tablespace_name, segment_type order by tablespace_name, segment_type;

Constraints, Indexes, Grants:

select constraint_type, status, validated, count(*) constraint_count from dba_constraints where owner='APP_TGT' group by constraint_type, status, validated;

select status, count(*) index_count from dba_indexes where owner='APP_TGT' group by status;

select grantee, owner, table_name, privilege, from dba_tab_privs where owner='APP_TGT' order by grantee, table_name, privilege;

Troubleshooting:

+-----------------------+-----------------------------------------------------------------------------+
| Error                 |    Likely Cause/Action                                                      |
+-----------------------+-----------------------------------------------------------------------------+
| ORA-39087             | Invalid directory object.Check DBA_DIRECTORIES                              |
+-----------------------+-----------------------------------------------------------------------------+
| ORA-39070/ORA-29283   | Cannot read/write file.Check OS path, permissions,mount,space and inodes    |
+-----------------------+-----------------------------------------------------------------------------+
| ORA-31626             | Job not found.Verify exact owner and job name in DBA_DATAPUMP_JOBS.         |
+-----------------------+-----------------------------------------------------------------------------+
| ORA-39151             | Object already exists.Usually informational;review desired target bahavior. |
+-----------------------+-----------------------------------------------------------------------------+
| ORA-01950             | Table exists. Choose TABLE_EXISTS_ACTION carefully.                         |
+-----------------------+-----------------------------------------------------------------------------+
| ORA-00959             | Target user has no quota on remapped tablespace.                            |
+-----------------------+-----------------------------------------------------------------------------+
| ORA-39126             | Worker failure. Review full log, alert log, pathes and falling object.      |
+-----------------------+-----------------------------------------------------------------------------+

Quota Fix:

alter user app_tgt quota unlimited on app_data_new;

alter user app_tgt quota unlimited on app_index_new;

OS Checks:

ls -ld /u05/datapump
ls -l /u05/datapump
ls -h /u05/datapump
ls -i /u05/datapump

Donot repeatedly rerun a failed import without reviewing the target state.

Partial loads can create duplicate data and inconsistent metadata.

Production safe checklist:

Before Export:

Compare source/target versions, patch levels, options, characters sets, and time zones.
Inventory schemes, size, partitions, LOBs, invalids, grants, jobs, DB links, and outdated integrations.
Decide handling of existing objects and data.
Control scheduler jobs, triggers, DB links and outbound integrations.
Review redo and Data Gaurd Imapct.

After Import:

Review log files of ORA-, UDI-, and LRM- errors.
Compare objects, rows, sizes, grants, constraints and indexes.
Compile invalid objects and gather satistics.
Validate application connectivity and bussiness transactions.
Take a fresh target backup.