1. Introduction

Oracle Data Pump (expdp / impdp) is the de-facto standard for extracting and loading data between Oracle databases. When the source is an on-premises Oracle EBS, E-Business Suite, or any legacy Oracle RDBMS and the target is Oracle Autonomous Transaction Processing (ATP) on OCI, the recommended staging layer is OCI Object Storage — a durable, scalable, and cost-efficient intermediate store that both worlds can read from and write to natively.

This article walks through the complete pipeline:

• Exporting data from a source Oracle DB into .dmp files

• Uploading those dump files to an OCI Object Storage bucket using Python

• Importing the dump directly into an ATP database table using impdp or DBMS_DATAPUMP

Code snippets, architecture notes, and production-readiness tips are included throughout.

2. When Is This Process Required?

This pipeline addresses several common scenarios in Oracle cloud adoption and migration projects:

2.1  Legacy EBS / On-Premises to Cloud Migration

Organizations migrating Oracle E-Business Suite R12 to Oracle Fusion Cloud Applications need to stage historical transactional data — GL journals, AR invoices, AP payments, inventory transactions — in ATP or ADW for reporting continuity. Data Pump is the fastest, most reliable extraction mechanism available on legacy Oracle instances.

2.2  Cross-Environment Data Refresh

Development and UAT teams frequently need production-like data snapshots. Rather than copying the full database, a selective schema or table-level dump can be exported, staged in Object Storage, and imported on demand into ATP dev/test instances.

2.3  Historical Data Archival

Regulatory requirements (SOX, VAT retention, local statutory rules) mandate retaining transactional data for 7–10 years. Periodic Data Pump exports staged in OCI Object Storage with lifecycle policies applied for Infrequent Access / Archive tiers provide a cost-efficient long-term retention model.

2.4  Disaster Recovery Seeding

In DR scenarios where RPO requirements are relaxed, nightly or weekly .dmp exports to Object Storage provide a recoverable baseline without the cost of Active Data Guard cross-region replication.

3. Solution Architecture & Approach

The end-to-end pipeline consists of three logical stages:

 Source Oracle DB  →  OCI Object Storage Bucket  →  ATP Database Table 

Stage 1 — Export: Data Pump on Source DB

Run expdp on the source Oracle instance. Use the %L wildcard to auto-split large exports into multiple sequenced .dmp files capped at a defined file size (recommended ≤ 5 GB per file for Object Storage compatibility).

⚡  Use the HIGH or FAST service name and set PARALLEL = number of CPU cores for best export throughput.

Stage 2 — Stage: OCI Object Storage

OCI Object Storage acts as the neutral staging layer. It is accessible by both the source (via OCI CLI or REST) and the ATP target (via DBMS_CLOUD credential). Key design decisions:

• Bucket in the same OCI region as ATP to avoid egress charges

• Pre-Authenticated Request (PAR) or IAM credential for secure access

• Object prefix (virtual folder) per migration wave for clean organization

• Lifecycle policy to auto-delete staged dumps after import confirmation

Stage 3 — Load: impdp or DBMS_DATAPUMP into ATP

ATP supports two import mechanisms — the traditional impdp client utility (with a credential object pointing to the bucket) and the programmatic DBMS_DATAPUMP PL/SQL API. Both reference the dump file using its OCI Object Storage URI directly.

4. Step-by-Step Implementation

4.1  Export from Source Oracle Database

On the source Oracle server, run:

expdp system/password@SOURCEDB
schemas=APPS,GL,AR
dumpfile=ebs_export_%L.dmp
directory=DATA_PUMP_DIR
filesize=5GB
parallel=8
logfile=ebs_export.log

📁  This produces files: ebs_export_100.dmp, ebs_export_101.dmp, ... in DATA_PUMP_DIR.

4.2  Upload .dmp Files to OCI Object Storage (Python)

Install the OCI SDK and run the upload script. The script uses UploadManager for multipart uploads (handles files > 5 GB cleanly), parallel file uploads via ThreadPoolExecutor, and resume-safe skipping of already-uploaded files.

pip install oci tqdm 

Key code structure:

import oci
import os
import sys
CONFIG_FILE = r"C:\Users\Users.oci\config" # OCI config file path
NAMESPACE = "NAMESPACE" # oci os ns get
BUCKET_NAME = "BUCKET_NAME"
OBJECT_NAME = "*.dmp" # name it will have in OCI
FILE_PATH = r"C:\Users\Users\*.dmp" # local file path
PART_SIZE_MB = 256
PARALLEL = 5
CERT_FILE = r"C:\Users\Users.oci\corporate.cer" # ← your exported cert
def progress(bytes_uploaded):
file_size = os.path.getsize(FILE_PATH)
pct = (bytes_uploaded / file_size) * 100
done = int(50 * bytes_uploaded / file_size)
bar = '█' * done + '░' * (50 - done)
print(f"\r [{bar}] {pct:.1f}% "
f"({bytes_uploaded/10243:.2f} GB / {file_size/10243:.2f} GB)",
end="", flush=True)
def upload():
print("=" * 60)
print("OCI Object Storage — Large File Upload")
print("=" * 60)
print(f"File : {FILE_PATH}")
print(f"Bucket : {BUCKET_NAME}")
print(f"Object : {OBJECT_NAME}")
print(f"File Size : {os.path.getsize(FILE_PATH)/1024**3:.2f} GB")
print(f"Part Size : {PART_SIZE_MB} MB")
print(f"Parallel : {PARALLEL} threads")
print("=" * 60)

if not os.path.exists(FILE_PATH):
    print(f"ERROR: File not found: {FILE_PATH}")
    sys.exit(1)

config    = oci.config.from_file(CONFIG_FILE)
os_client = oci.object_storage.ObjectStorageClient(
    config,
    verify = CERT_FILE    # ← use corporate cert
)

upload_mgr = oci.object_storage.UploadManager(
    os_client,
    allow_multipart_uploads          = True,
    allow_parallel_multipart_uploads = True
)

print("\nUploading...")
response = upload_mgr.upload_file(
    namespace_name         = NAMESPACE,
    bucket_name            = BUCKET_NAME,
    object_name            = OBJECT_NAME,
    file_path              = FILE_PATH,
    part_size              = PART_SIZE_MB * 1024 * 1024,
    parallel_process_count = PARALLEL,
    progress_callback      = progress
)

print(f"\n\nUpload complete!")
print(f"ETag : {response.data.etag}")

head       = os_client.head_object(NAMESPACE, BUCKET_NAME, OBJECT_NAME)
oci_size   = int(head.headers.get('content-length', 0))
local_size = os.path.getsize(FILE_PATH)

print(f"Local size : {local_size:,} bytes")
print(f"OCI size   : {oci_size:,} bytes")
print(f"Match      : {'✅ YES' if oci_size == local_size else '❌ NO — reupload!'}")
print("=" * 60)

if name == "main":
upload()

♻️  Run with --no-skip flag to force re-upload on retry. Default behavior skips already-present objects.

4.3  Create OCI Credential in ATP

Before importing, create a credential object in ATP that authenticates to OCI Object Storage:

BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'OCI_DUMP_CRED',
    username        => 'oracleidentitycloudservice/user@example.com',
    password        => '<auth_token>'   -- OCI Auth Token
  );
END;

/

🔑  Generate the Auth Token from OCI Console → Identity → Users → Auth Tokens. It acts as the password above.

4.4  Import into ATP Using impdp

From a client machine with Oracle Instant Client + Data Pump utilities, run impdp pointing the dump file directly at the Object Storage URI:

impdp admin/password@ATP_HIGH
  credential=OCI_DUMP_CRED
  dumpfile=https://namespace.objectstorage.us-ashburn-1.oci.customer-oci.com/
          n/namespace/b/EBS_MIGRATION_DUMPS/o/ebs_dumps/ebs_export_%l.dmp
  parallel=16
  table_exists_action=REPLACE
  transform=segment_attributes:n
  exclude=cluster,db_link
  logfile=atp_import.log

4.5  Alternative: DBMS_DATAPUMP (PL/SQL, No Client Required)

If you cannot install Oracle client tools, use the DBMS_DATAPUMP API entirely from within ATP SQL Worksheet or SQLcl:

DECLARE
  h  NUMBER;
BEGIN
  h := DBMS_DATAPUMP.OPEN(
         operation  => 'IMPORT',
         job_mode   => 'SCHEMA',
         job_name   => 'EBS_IMP_JOB');
 
  DBMS_DATAPUMP.ADD_FILE(
    handle    => h,
    filename  => 'https://namespace.objectstorage.us-ashburn-1.oci.customer-oci.com/
                  n/namespace/b/EBS_MIGRATION_DUMPS/o/ebs_dumps/ebs_export_%l.dmp',
    directory => 'OCI_DUMP_CRED',
    filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE);
 
  DBMS_DATAPUMP.SET_PARALLEL(handle => h, degree => 8);
  DBMS_DATAPUMP.START_JOB(h);
END;
/

5. Validation & Post-Load Checks

After the import, run these checks in ATP to validate completeness:

-- Check import job status
SELECT job_name, state, degree, error_count
FROM   dba_datapump_jobs;

-- Row count validation against source
SELECT table_name, num_rows
FROM   dba_tables
WHERE  owner = 'GL'
ORDER  BY table_name;

-- Object Storage upload verification (Python)
response = client.list_objects(namespace, BUCKET, prefix='ebs_dumps/')
print(f'{len(response.data.objects)} objects staged in bucket')

✅  Cross-check num_rows with source DB counts as part of migration UAT sign-off.

6. Production Considerations & Best Practices

7. Common Errors & Troubleshooting

ORA-39001 / ORA-39000 — Invalid argument on impdp

Cause: Incorrect Object Storage URI format or credential name mismatch. Check that the URI uses the OCI Dedicated Endpoint format (namespace.objectstorage.region.oci.customer-oci.com) and that the credential is created in the same ATP schema running the import.

ORA-20401 — Credential not found (DBMS_CLOUD)

Cause: The credential was created in a different schema. Grant access or recreate under ADMIN. Verify with: SELECT credential_name FROM all_credentials;

Zero-byte .dmp files in Object Storage console

Cause: This is expected behaviour when exporting directly via Data Pump to Object Storage. The actual data is in chunk files (.dmp_aaaaaa, .dmp_aaaaab, etc.). Use impdp or Swift/curl — not the OCI Console download button — to retrieve full files.

Python upload fails with 401 Unauthorized

Cause: OCI config fingerprint or private key path mismatch. Run: oci iam user get --user-id <your_ocid> to validate config. On Compute VMs, switch to Instance Principal auth by removing the config file argument.

8. Summary

The pipeline of Oracle Data Pump export → OCI Object Storage staging → ATP import is the standard, Oracle-recommended approach for moving bulk schema data into Autonomous Database. It avoids database links, intermediate servers, and proprietary ETL tooling.

The three-stage approach provides:

• Decoupling — source and target databases never connect directly

• Durability — Object Storage retains the dump as a recoverable point

• Scalability — multipart uploads and parallel impdp scale with data volume

• Auditability — timestamped log files at every stage for migration sign-off 

The Python automation layer brings resume-safety, structured logging, and parallel throughput that the OCI CLI alone does not provide out of the box — making it the preferred choice for production migration involving dozens of schemas or terabytes of data.