Oracle Data Pump (expdp/impdp) for Large ERP Schemas: Migration & Performance Tuning

Executive Summary

Migrating large ERP schemas — SAP, Oracle EBS, JD Edwards, PeopleSoft — with Oracle Data Pump is a routine but high-risk operation. ERP schemas are characterized by thousands of tables, deep dependency chains, LOB-heavy attachment tables, and multi-terabyte segment footprints. Default expdp/impdp settings are deliberately conservative: a single worker process, uncompressed dump files, and archive logging enabled on import. That combination turns a weekend migration into a multi-week ordeal.

This guide provides a production-tested methodology for planning, executing, and validating large-scale ERP schema migrations with Data Pump on Oracle Database 19c and 23ai. It covers performance tuning parameters, diagnostic commands, a step-by-step runbook, and remediation for the most common failure mode: ORA-00600 internal errors during LOB-heavy exports.

The bottom line: right-size parallelism to your CPU and I/O capacity, compress dump sets, stage on fast NVMe storage, disable archive logging during import, and validate with ESTIMATE_ONLY before committing to the migration window.

1. Overview

The migration process follows six phases. Each phase has specific inputs, outputs, and validation gates. Skipping a phase — especially assessment or post-import validation — is the leading cause of failed ERP migrations.

2. Diagnostic Checklist

Before touching any parameter file, run the following diagnostics to baseline the environment. These commands identify the root cause of slow or failing Data Pump operations.

2.1 Version & Patch Level

# Data Pump client help and parameter listing
expdp help=y

# Database version and component versions
sqlplus -S / as sysdba <<'SQL'
SET LINESIZE 200
SELECT banner FROM v$version;
SELECT comp_name, version, status FROM dba_registry WHERE comp_id = 'CATALOG';
SQL

# Installed patches
sqlplus -S / as sysdba <<'SQL'
SET LINESIZE 200
SELECT patch_id, action, status, description
FROM dba_registry_sqlpatch
ORDER BY patch_date DESC;
SQL

2.2 Directory Objects & Staging Space

sqlplus -S / as sysdba <<'SQL'
SET LINESIZE 200
COLUMN directory_name FORMAT A30
COLUMN directory_path FORMAT A80
SELECT directory_name, directory_path FROM dba_directories;
SQL

# Check free space on the staging mount point
df -h /u01/app/oracle/dpdump

2.3 Parallelism & Resource Configuration

sqlplus -S / as sysdba <<'SQL'
SHOW PARAMETER parallel_max_servers
SHOW PARAMETER parallel_degree_policy
SHOW PARAMETER cpu_count
SHOW PARAMETER db_block_size
SHOW PARAMETER compatible
SQL

2.4 Schema Size & Object Inventory

sqlplus -S / as sysdba <<'SQL'
SET LINESIZE 200
COLUMN segment_type FORMAT A30
SELECT segment_type, COUNT(*) AS object_count,
       ROUND(SUM(bytes)/1024/1024/1024, 2) AS size_gb
FROM dba_segments
WHERE owner = '&SCHEMA_OWNER'
GROUP BY segment_type
ORDER BY size_gb DESC;
SQL

2.5 LOB-Heavy Tables (ORA-00600 Risk)

sqlplus -S / as sysdba <<'SQL'
SET LINESIZE 200
COLUMN table_name FORMAT A40
SELECT table_name, COUNT(*) AS lob_count
FROM dba_lobs
WHERE owner = '&SCHEMA_OWNER'
GROUP BY table_name
ORDER BY lob_count DESC
FETCH FIRST 20 ROWS ONLY;
SQL

2.6 Invalid Objects & Dependency Chains

sqlplus -S / as sysdba <<'SQL'
SET LINESIZE 200
SELECT owner, object_type, COUNT(*) AS invalid_count
FROM dba_objects
WHERE owner = '&SCHEMA_OWNER' AND status = 'INVALID'
GROUP BY owner, object_type
ORDER BY invalid_count DESC;
SQL

2.7 Alert Log & Trace Files (ORA-00600)

# Check for ORA-00600 in the alert log
grep -i "ORA-00600" $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace/alert_$ORACLE_SID.log | tail -20

# List recent trace files
ls -lt $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace/*.trc | head -10

2.8 Network Bandwidth (for NETWORK_LINK Mode)

# Test throughput between source and target
iperf3 -c <target_host> -P 8 -t 30

# Or use scp/rsync timing as a proxy
time scp /dev/zero <target_host>:/tmp/testfile

3. Step-by-Step Resolution Runbook

Step 0: Safety Checks

Stop. Do not proceed until all of the following are verified.

1. Confirm a valid backup exists. Run an RMAN backup or ensure the source is protected by an active Data Guard standby. Data Pump is not a backup strategy.

   rman target / <<'EOF'
   BACKUP DATABASE PLUS ARCHIVELOG TAG 'PRE_DATAPUMP_MIGRATION';
   EOF

2. Verify target database compatibility. The target must run the same or higher Oracle version than the source. Check the compatible parameter:

   SHOW PARAMETER compatible;

3. Apply the latest Release Update (RU) or CPU. Confirm the target and source are patched to the latest available RU for their respective versions. This reduces the risk of known Data Pump bugs, including ORA-00600 on LOB-heavy exports.

   SELECT patch_id, description FROM dba_registry_sqlpatch
   ORDER BY patch_date DESC;

4. Verify disk space. You need at least 2× the estimated dump size on the staging mount, plus 20% headroom for logs and temporary files.

   df -h /u01/app/oracle/dpdump

5. Check for active batch jobs. Coordinate with ERP functional teams to ensure no batch processes (payroll, month-end close, interfaces) are running during the export window.

6. Review Oracle documentation for known issues. Consult the official Oracle Data Pump documentation for your exact version and RU to identify any documented limitations or bugs affecting your migration path.

Step 1: Pre-Migration Assessment

Run an estimate-only export to size the job and validate the parameter file before the real migration:

expdp \'/ as sysdba\' PARFILE=export_erp_estimate.par

# export_erp_estimate.par
DIRECTORY=DATA_PUMP_DIR
SCHEMAS=ERP_APP
DUMPFILE=erp_app_estimate_%U.dmp
LOGFILE=export_erp_estimate.log
PARALLEL=4
ESTIMATE_ONLY=Y

Review the log for:

• Total estimated dump size
• Per-table row counts and sizes
• Any tables with unsupported data types
• LOB and partition counts

Also generate a full object inventory:

SELECT object_type, COUNT(*) FROM dba_objects
WHERE owner = 'ERP_APP'
GROUP BY object_type ORDER BY 2 DESC;

Step 2: Configure Directory Objects & Privileges

On both source and target:

-- Create staging directory (DBA must have OS-level write permission)
CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR AS '/u01/app/oracle/dpdump';

-- Grant access to the schema owner or a dedicated migration user
GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO SYSTEM;

-- For full-schema export/import, the user needs EXP_FULL_DATABASE / IMP_FULL_DATABASE
GRANT EXP_FULL_DATABASE TO MIGRATION_USER;
GRANT IMP_FULL_DATABASE TO MIGRATION_USER;

For NETWORK_LINK mode (direct source-to-target without dump files), create a database link on the target:

CREATE DATABASE LINK SOURCE_ERP
CONNECT TO MIGRATION_USER IDENTIFIED BY "password"
USING 'source_tns_alias';

GRANT DATAPUMP_IMP_FULL_DATABASE TO MIGRATION_USER;

Step 3: Build the Export Parameter File

A production-grade export parfile for a large ERP schema:

# export_erp.par
DIRECTORY=DATA_PUMP_DIR
SCHEMAS=ERP_APP
DUMPFILE=erp_app_%U.dmp
LOGFILE=export_erp_app.log
PARALLEL=8
COMPRESSION=ALL
COMPRESSION_ALGORITHM=MEDIUM
STATUS=300
METRICS=Y
FLASHBACK_TIME=SYS_EXTRACT_UTC(SYSTIMESTAMP) - INTERVAL '1' HOUR

Parameter rationale:

Parameter	Value	Why
PARALLEL	8	Right-size to CPU count and I/O channels. Rule of thumb: min(CPU_COUNT, 4 × IO_channel_count). Each worker writes its own dump file.
COMPRESSION=ALL	ALL	Compresses data + metadata. Reduces dump size 3–5× for ERP data (lots of repeated strings, numeric codes).
COMPRESSION_ALGORITHM	MEDIUM	Balances CPU cost vs. compression ratio. HIGH can bottleneck CPU-bound exports.
FLASHBACK_TIME	1 hour ago	Ensures a consistent snapshot without blocking DML. Use FLASHBACK_SCN for stricter consistency.
STATUS=300	300	Prints progress every 5 minutes to the log.
METRICS=Y	Y	Adds per-object timing to the log for post-analysis.

23ai enhancement: On Oracle 23ai, add ACCESS_METHOD=DIRECT_PATH to force direct-path reads, or ACCESS_METHOD=EXTERNAL_TABLE as a fallback for LOB-heavy tables that trigger ORA-00600.

Cross-version migrations: If the target runs a lower version than the source, add VERSION=<target_version> to the export parfile (e.g., VERSION=19.0.0). This ensures the dump file metadata is compatible.

Security note: If encryption is required by compliance (PCI, GDPR, SOX), add:

ENCRYPTION=ENABLED
ENCRYPTION_ALGORITHM=AES256
ENCRYPTION_PASSWORD=<use_secure_external_store>

Never hardcode the encryption password in the parfile. Use an external credential store or Oracle Wallet.

Step 4: Execute the Export

Run the export in the background with nohup so it survives terminal disconnects:

nohup expdp \'/ as sysdba\' PARFILE=export_erp.par > export_erp.out 2>&1 &
echo $! > export_erp.pid

Monitor progress:

# Attach to the running job
expdp \'/ as sysdba\' attach=export_erp_app

# In the interactive prompt:
#   Status        -> shows worker progress
#   Stop_job=immediate  -> aborts cleanly

Check the log periodically:

tail -50 export_erp_app.log

Expected output: One dump file per parallel worker (erp_app_01.dmp through erp_app_08.dmp). The master table (SYS_EXPORT_SCHEMA_01) tracks job state. If the job fails, the master table remains — restart the job with ATTACH or drop it with SQL> DROP TABLE SYS_EXPORT_SCHEMA_01 PURGE; after confirming the job is dead.

Step 5: Transfer & Stage Dump Files

Use parallel, checksummed transfer to the target staging area:

# Parallel rsync with checksum verification
rsync -avz --progress --checksum /u01/app/oracle/dpdump/erp_app_*.dmp \
  target_host:/u01/app/oracle/dpdump/

# Or use GNU parallel + scp for higher throughput
ls /u01/app/oracle/dpdump/erp_app_*.dmp | \
  parallel -j 8 scp {} target_host:/u01/app/oracle/dpdump/

Verify checksums on both sides:

md5sum /u01/app/oracle/dpdump/erp_app_*.dmp > source_checksums.md5
# Run md5sum on target and diff

Performance tip: Stage on NVMe or local SSD on the target. Avoid NFS for the import staging directory — NFS latency will bottleneck parallel workers.

Step 6: Pre-Import Target Validation

Before importing, verify the target is ready:

-- Check target tablespaces exist (or plan REMAP_TABLESPACE)
SELECT tablespace_name, ROUND(SUM(bytes)/1024/1024/1024, 2) AS free_gb
FROM dba_free_space
GROUP BY tablespace_name;

-- Check for conflicting objects
SELECT owner, object_name, object_type
FROM dba_objects
WHERE owner = 'ERP_APP' AND ROWNUM <= 10;

-- Verify default tablespace for the schema
SELECT username, default_tablespace, temporary_tablespace
FROM dba_users WHERE username = 'ERP_APP';

Step 7: Build the Import Parameter File

# import_erp.par
DIRECTORY=DATA_PUMP_DIR
SCHEMAS=ERP_APP
DUMPFILE=erp_app_%U.dmp
LOGFILE=import_erp_app.log
PARALLEL=8
TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y
REMAP_TABLESPACE=ERP_DATA:ERP_DATA_NEW
TABLE_EXISTS_ACTION=SKIP
EXCLUDE=STATISTICS
STATUS=300
METRICS=Y

Parameter rationale:

Parameter	Value	Why
PARALLEL	8	Match or slightly exceed export parallelism. Workers load tables and create indexes concurrently.
TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y	Y	Single biggest import speedup. Tables and indexes are created with NOLOGGING, eliminating redo generation. Take an RMAN backup immediately after import.
REMAP_TABLESPACE	as needed	Redirect objects to target tablespaces without editing DDL.
TABLE_EXISTS_ACTION=SKIP	SKIP	Safe for re-runs. Use TRUNCATE for retry with data refresh, or REPLACE for full rebuild.
EXCLUDE=STATISTICS	STATISTICS	Import data first, gather stats in parallel afterward. Faster than importing stale stats.

Optional for maximum speed: Exclude indexes and create them manually in parallel after data load:

EXCLUDE=INDEX

Then create indexes with PARALLEL and NOLOGGING:

ALTER SESSION ENABLE PARALLEL DDL;
CREATE INDEX idx_erp_orders_01 ON erp_orders(order_date) PARALLEL 16 NOLOGGING;

Step 8: Execute the Import

nohup impdp \'/ as sysdba\' PARFILE=import_erp.par > import_erp.out 2>&1 &
echo $! > import_erp.pid

Monitor:

impdp \'/ as sysdba\' attach=import_erp_app
# Status -> shows table load progress, index creation progress

Watch for common errors in the log:

• ORA-31693 — table data load failed; check the log for the underlying error
• ORA-39083 — object creation failed; often due to missing tablespaces or privileges
• ORA-00600 — internal error; see the troubleshooting section below

Step 9: Post-Import Validation

Compare row counts between source and target:

-- Generate count queries for all tables (run on both source and target)
SELECT 'SELECT ''' || table_name || ''', COUNT(*) FROM ' || table_name || ';'
FROM user_tables
WHERE table_name NOT LIKE 'BIN$%'
ORDER BY table_name;

Check for invalid objects:

SELECT owner, object_type, COUNT(*) AS invalid_count
FROM dba_objects
WHERE owner = 'ERP_APP' AND status = 'INVALID'
GROUP BY owner, object_type
ORDER BY invalid_count DESC;

Recompile invalid objects:

BEGIN
  DBMS_UTILITY.COMPILE_SCHEMA(schema => 'ERP_APP', compile_all => FALSE);
END;
/

Validate constraints and triggers:

SELECT constraint_name, status, validated
FROM dba_constraints
WHERE owner = 'ERP_APP' AND status = 'DISABLED';

SELECT trigger_name, status
FROM dba_triggers
WHERE owner = 'ERP_APP' AND status = 'DISABLED';

Step 10: Statistics & Performance Verification

Gather fresh statistics in parallel:

EXEC DBMS_STATS.GATHER_SCHEMA_STATS(
  ownname => 'ERP_APP',
  degree  => 16,
  cascade => TRUE,
  options => 'GATHER AUTO'
);

Verify SQL performance with representative ERP workloads:

ALTER SESSION SET statistics_level = 'ALL';

-- Run a representative query (e.g., order header + line join)
SELECT COUNT(*) FROM erp_orders o JOIN erp_order_lines l ON o.order_id = l.order_id;

Check the execution plan:

EXPLAIN PLAN FOR
SELECT * FROM erp_orders WHERE order_date >= DATE '2026-01-01';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Step 11: Cleanup & Decommission

1. Take a full RMAN backup of the target immediately after import (required because of NOLOGGING).

2. Remove dump files from staging:

   rm /u01/app/oracle/dpdump/erp_app_*.dmp

3. Revoke temporary privileges:

   REVOKE EXP_FULL_DATABASE FROM MIGRATION_USER;
   REVOKE IMP_FULL_DATABASE FROM MIGRATION_USER;
   DROP DATABASE LINK SOURCE_ERP;

4. Update the CMDB/inventory with the new schema location, size, and object counts.

5. Decommission the source only after a full parallel run of the ERP application against the target passes UAT.

4. Troubleshooting: ORA-00600 During Data Pump

ORA-00600 is an internal error that always requires the accompanying trace file to diagnose. In Data Pump contexts, the most common triggers are:

Scenario	Typical Arguments	Resolution
LOB-heavy export with parallel workers	ORA-00600 [kghfrem:dsf] or [kpudpux]	Apply latest RU; reduce PARALLEL; use ACCESS_METHOD=EXTERNAL_TABLE for LOB tables
Corrupted dump file during import	ORA-00600 [kupc$C_Update]	Re-export the affected table; verify checksums
Version mismatch source/target	ORA-00600 [kupdtdr]	Ensure target version >= source version; use VERSION parameter on export

Immediate actions:

1. Locate the trace file:

   ls -lt $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace/*.trc | head -5

2. Search My Oracle Support for the exact error signature (e.g., ORA-00600 [kupf$C_Read] expdp).

3. Check if the issue is fixed in a newer RU and plan a patch window.

4. As a workaround, isolate the failing table and export it separately with PARALLEL=1.

5. Security Best Practices for Data Pump

Data Pump operations involve privileged operations and, in many cases, sensitive ERP data. Follow these security best practices:

1. Apply the latest Release Update (RU) or CPU to both source and target databases. This addresses known security vulnerabilities and stability bugs in Data Pump.

2. Restrict EXP_FULL_DATABASE / IMP_FULL_DATABASE grants to trusted migration accounts only. These are highly privileged roles.

3. Use a dedicated, least-privilege migration account rather than SYS for routine Data Pump jobs.

4. Never import dump files from untrusted sources. Dump files can contain malicious DDL or data. Validate the provenance of every dump file.

5. Inspect dump file metadata safely using DBMS_DATAPUMP.GET_DUMPFILE_INFO before executing import:

   DECLARE
     ku_file_info sys.ku$_dumpfile_info;
     file_type    NUMBER;
   BEGIN
     DBMS_DATAPUMP.GET_DUMPFILE_INFO(
       filename   => 'erp_app_01.dmp',
       directory  => 'DATA_PUMP_DIR',
       info_table => ku_file_info,
       filetype   => file_type
     );
   END;
   /

6. Store encryption passwords in an external credential store or Oracle Wallet — never in the parfile.

7. Audit Data Pump activity by enabling unified auditing for EXP_FULL_DATABASE and IMP_FULL_DATABASE usage.

📚 Official Documentation & Technical References

Oracle Documentation

• Oracle Database 23ai: Oracle Data Pump Administrator’s Guide
• Oracle Database 19c: Oracle Data Pump Administrator’s Guide
• Oracle Database 23ai New Features Summary
• Oracle Database 19c New Features Summary
• Oracle Database Security Guide, 23ai

My Oracle Support

For official My Oracle Support performance tuning and patch recommendations for Data Pump exports/imports on LOB-heavy schemas, search the MOS Knowledge Base directly via the MOS portal.

Related Resources

• Oracle Database PL/SQL Packages and Types Reference — DBMS_DATAPUMP

---

Need Expert Help?

Large ERP schema migrations are high-risk, high-stakes operations. If you need hands-on assistance with Oracle Data Pump tuning, migration planning, or post-migration performance validation, our team of Oracle Certified Masters is ready to help.

Contact DBPros.Net | View Our Services