Oracle Data Pump & Data Migration: Export/Import, Performance & Security

TAKEAWAYS:

• Oracle Data Pump is a high-performance utility for exporting/importing data and metadata, replacing the legacy SQL*Loader and Export/Import tools.
• It supports parallel operations, compression, and network links for large-scale migrations with minimal downtime.
• Key components include EXPDP (export), IMPDP (import), and DUMP files (binary data containers).
• Data migration strategies must address schema compatibility, data transformation, and dependency resolution (e.g., constraints, triggers).
• Security risks in migration include unauthorized access, data corruption, and role/privilege mismatches—mitigated via encryption and parameter validation.
• Real-world applications: eSewa’s database upgrades, Ncell’s subscriber data transfers, and NEPSE’s market data synchronization.

1. Introduction to Oracle Data Pump

Oracle Data Pump is a server-based utility designed for high-speed data and metadata transfer between Oracle databases. It replaces older tools like SQL*Loader and Legacy Export/Import, offering:

• Parallel processing (multi-threaded operations).
• Compression (reduces dump file size by up to 80%).
• Network links (remote migrations without physical media).
• Metadata handling (exports/imports DDL, statistics, and dependencies).

Why Use Data Pump?

2. Data Pump Architecture

Data Pump operates in two modes:

1. Client-Server Mode: Commands run locally, but processing happens on the Oracle server.
2. Direct Path Mode: Bypasses SQL layer for direct data block access (faster for large tables).

Key Components

• EXPDP (Export): Exports data/metadata to a dump file (*.dmp).

  EXPDP username/password@database
    DIRECTORY=dpump_dir
    DUMPFILE=export.dmp
    LOGFILE=export.log
    FULL=Y  # Exports entire database
  

• IMPDP (Import): Imports from a dump file into a target database.

  IMPDP username/password@database
    DIRECTORY=dpump_dir
    DUMPFILE=export.dmp
    LOGFILE=import.log
    REMAP_SCHEMA=old_schema:new_schema  # Schema remapping
  

• Dump File: Binary file containing data + metadata (compressed by default).

sequenceDiagram
    participant Client
    participant Server
    participant DumpFile
    participant TargetDB

    Client->>Server: EXPDP command (export)
    Server->>DumpFile: Writes data/metadata
    DumpFile-->>Client: Returns .dmp file
    Client->>TargetDB: IMPDP command (import)
    TargetDB->>DumpFile: Reads data/metadata
    TargetDB-->>Client: Confirmation

3. Exporting Data with EXPDP

Basic Export Command

EXPDP system/password@ORCL
  DUMPFILE=full_export.dmp
  DIRECTORY=dpump_dir
  LOGFILE=export.log
  FULL=Y

• Parameters:
  • DUMPFILE: Name of the dump file (stored in DIRECTORY).
  • LOGFILE: Logs errors/warnings.
  • FULL=Y: Exports the entire database (use TABLES=emp for specific tables).
  • COMPRESSION=ALL: Maximizes compression (default: METADATA_ONLY or DATA_ONLY).

Worked Example: Exporting eSewa’s Transaction Table

Scenario: eSewa needs to migrate its transactions table to a new server for scalability.

EXPDP eSewa_admin/password@eSewaDB
  DUMPFILE=transactions.dmp
  DIRECTORY=dpump_dir
  TABLES=transactions
  COMPRESSION=ALL
  QUERY="WHERE transaction_date > TO_DATE('01-JAN-2023', 'DD-MON-YYYY')"

• Why?
  • Only exports 2023 transactions (saves space).
  • Compression reduces dump file size by 70%.
  • Parallel threads speed up the process.

4. Importing Data with IMPDP

Basic Import Command

IMPDP system/password@ORCL
  DUMPFILE=full_export.dmp
  DIRECTORY=dpump_dir
  LOGFILE=import.log
  REMAP_SCHEMA=old_db:new_db

• Key Parameters:
  • REMAP_SCHEMA: Renames schemas during import (e.g., eSewa_old:eSewa_new).
  • SQLFILE: Generates SQL scripts instead of importing directly.
  • EXCLUDE=STATISTICS: Skips index statistics (useful for large tables).

Worked Example: Importing Ncell’s Subscriber Data

Scenario: Ncell upgrades its database and needs to import subscriber data from a legacy system.

IMPDP ncell_admin/password@new_ncell_db
  DUMPFILE=subscribers.dmp
  DIRECTORY=dpump_dir
  REMAP_SCHEMA=legacy_ncell:ncell_new
  TRANSFORM=SEGMENT_ATTRIBUTES:COMPRESS
  PARALLEL=4  # Uses 4 threads

• Why?
  • REMAP_SCHEMA updates schema names automatically.
  • TRANSFORM enables segment compression for storage savings.
  • PARALLEL=4 speeds up import by 4x (adjust based on CPU cores).

5. Data Migration Strategies

Data Pump supports three migration approaches:

Handling Dependencies

• Constraints: Use CONSTRAINTS=Y to import foreign keys.
• Triggers: Use TRIGGERS=Y to preserve DML triggers.
• Statistics: Use STATISTICS=ALL for query optimization.

6. Security in Data Pump

Risks & Mitigations

Encrypting Dump Files

EXPDP admin/password@db
  DUMPFILE=secure_export.dmp
  ENCRYPTION_PASSWORD=MyPass123
  ENCRYPTION_ALGORITHM=AES256

7. Performance Tuning

Optimizing Data Pump

Worked Example: Pathao’s Ride Data Migration

Scenario: Pathao needs to migrate 10M ride records with minimal downtime.

EXPDP pathao_admin/password@pathao_db
  DUMPFILE=rides.dmp
  DIRECTORY=dpump_dir
  TABLES=rides
  PARALLEL=16
  COMPRESSION=ALL
  DIRECT=Y
  QUERY="WHERE ride_date > SYSDATE-30"  # Last 30 days only

• Why?
  • PARALLEL=16 uses all CPU cores.
  • DIRECT=Y avoids SQL overhead.
  • QUERY reduces data volume by 90%.

8. Real-World Applications

1. eSewa: Database Upgrades

• Problem: eSewa’s Oracle 12c database needed an upgrade to 19c.
• Solution: Used Data Pump’s full export with PARALLEL=32 and COMPRESSION=ALL.
• Result: 4-hour migration (vs. 24+ hours with legacy tools).

2. Ncell: Subscriber Data Transfer

• Problem: Merging two Ncell databases post-acquisition.
• Solution: Used IMPDP with REMAP_SCHEMA to align schemas.
• Result: Zero data loss, 12-hour import (vs. manual ETL taking weeks).

3. NEPSE: Market Data Synchronization

• Problem: Real-time stock price updates across 3 data centers.
• Solution: Scheduled incremental exports every 5 minutes.
• Result: Sub-second latency for traders.

9. Common Errors & Fixes

Exam Tip

1. Command Syntax: Memorize the basic EXPDP/IMPDP commands (including DUMPFILE, DIRECTORY, PARALLEL).
2. Parameters: Know when to use:
   • FULL=Y (full DB) vs. TABLES= (specific tables).
   • COMPRESSION=ALL vs. DIRECT=Y.
   • REMAP_SCHEMA for schema changes.
3. Worked Examples: Always tie answers to real scenarios (e.g., "eSewa’s migration used PARALLEL=32 to reduce downtime").
4. Security: Mention encryption and role-based access in migration plans.
5. Performance: Highlight parallelism and compression as key optimizations.