Oracle Data Pump: export / import, migration, schema refresh and transportable

Table, schema, tablespace or full export and import with expdp/impdp, migration between two databases (dump file + scp + checksum, or NETWORK_LINK) and DDL extraction from a dump (SQLFILE). Includes directory, user and parfile preparation, consistency, parallelism, remapping, monitoring and cleanup of interrupted jobs. On RAC: SCAN + service connection, CLUSTER=YES with a shared directory or CLUSTER=NO with local disk; optionally, scheduling the export with a wallet-based script and /etc/cron.d. Scenarios: schema refresh from production to test (DROP USER … CASCADE or TABLE_EXISTS_ACTION=REPLACE, preserving password/profile and grants), transportable tablespace (same/different platform and endian, RMAN CONVERT), full transportable (11g/12c → 19c PDB), dump encryption and compression, performance for large migrations, and managing an interrupted job with ATTACH/START_JOB/KILL_JOB.

Topology: 1 database (single instance, Oracle Restart or RAC); source + target for a migration

Scenarios

• Export: write a dump file from the database — A table, schema, tablespace or the whole database is written to a dump file (expdp). The database stays open and the application keeps running. This is a logical copy: it does not replace an RMAN backup (point-in-time and complete recovery require RMAN).
• Import: load a dump file into the database — A previously taken dump file is loaded with impdp; schema, tablespace and table names can be remapped. The target stays open; the application that uses the loaded objects must be stopped.
• Migration: move data from the source database to the target — Export → file copy → import, or directly with NETWORK_LINK without a dump file. Choose this card if the schema does not exist on the target yet (initial load); if the same schema already exists on the target and is to be replaced with the production copy, choose the "Schema refresh" card. The source stays open.
• Extract only the DDL from a dump file (SQLFILE) — Nothing is loaded; the CREATE statements of the objects are written to a .sql file. The database is not changed.
• Schema refresh from production to test — A schema that already exists in test/UAT is replaced with its current production version: backup of the target schema, closing sessions, DROP USER … CASCADE or TABLE_EXISTS_ACTION=REPLACE, preserving password/profile and grants. The source stays open; the target schema is unavailable for the duration of the job.
• Move a tablespace by copying datafiles (transportable tablespace) — Large tablespaces (hundreds of GB and more) are moved by copying datafiles rather than row by row; same or different platform/endian (RMAN CONVERT). The character sets must be the same on both sides. On the source the tablespaces are READ ONLY for the duration of the job (write outage).
• Move the whole database: full transportable (11g/12c → 19c, non-CDB → PDB) — With FULL=Y TRANSPORTABLE=ALWAYS the user tablespaces are moved to a new PDB as datafiles and everything else (users, PL/SQL, grants) as metadata; VERSION=12 on an 11.2.0.3+ source. On the source all user tablespaces are READ ONLY for the duration of the job.
• Interrupted / stalled Data Pump job: monitor, resume, terminate — An expdp/impdp job is stalled (resumable wait), stopped or interrupted; you want to attach to it with ATTACH and resume it, terminate it with KILL_JOB, or clean up an orphaned master table. The database stays open.

Before you start

• The commands run on the database server as the oracle user; this user's login profile defines the ORACLE_HOME, ORACLE_SID and PATH variables and expdp/impdp are in the path. expdp, impdp and sqlplus are under $ORACLE_HOME/bin; if it is not in PATH you get bash: expdp: command not found (. oraenv or the full path: $ORACLE_HOME/bin/expdp).… On RAC, ORACLE_SID on each node is that node's instance name (e.g. …………… on node 1).…
• Database 12.2 or later (19c, 21c, 23ai). The LOGTIME, ENCRYPTION_PWD_PROMPT and DISABLE_ARCHIVE_LOGGING parameters used in this guide were introduced in 12c. The parameter list differs for 10g/11g; .dmp files from Oracle 9i and earlier can only be opened with the legacy imp.… Full transportable: export is supported from 11.2.0.3 (with VERSION=12 on 11g), import from 12.1; 11g has no LOGTIME/METRICS, so the guide does not use them in that case.…
• The listener is running and the … service is registered…; the … service is registered on the target….
• The listener is running on the target server and the … service is registered.
• There is enough free space for …… (source) and … (target)…: for export, as much as the estimated size (usually 3–5 times less with COMPRESSION=ALL); for import, the dump files must already be in this directory. The directory must not be on the same disk as the datafiles and the FRA.
• To avoid ORA-01555 during a long consistent (FLASHBACK_TIME/SCN) export, UNDO_RETENTION is longer than the export duration (e.g. at least 10800 s for a 3-hour export) and the undo tablespace is large enough for that period (autoextend).
• Import generates a lot of redo: if the database is in ARCHIVELOG mode there must be space in the FRA (or the archive destination). DISABLE_ARCHIVE_LOGGING has no effect when FORCE LOGGING is enabled (Data Guard).
• The target database has a current RMAN backup and the fallback method is defined (an import cannot be undone: an import into the wrong schema or with TRUNCATE/REPLACE can only be reverted from a backup or with Flashback). In a critical environment a guaranteed restore point can be created before the import (requires ARCHIVELOG + FRA).
• A maintenance window is scheduled: …on the source the transported tablespaces are READ ONLY during the export and the datafile copy (applications that write get errors, ORA-00372); on the target the new objects must not be used during the plug-in…applications that use the target objects must be stopped for the duration of the import, or it must be verified that they are not affected…because no consistency option was selected, application writes must be stopped for the duration of the export….
• The oracle user on the source can SSH/SCP to the target without a password (key-based), or the files will be moved another way (shared storage, object storage).
• The target server can reach the source listener port (…:…) (firewall open).
• License check: …COMPRESSION=ALL (and DATA_ONLY) requires the Advanced Compression Option license. ……Dump encryption (ENCRYPTION, ENCRYPTION_PASSWORD/ENCRYPTION_PWD_PROMPT) requires the Advanced Security Option license on Enterprise Edition. …If you are not licensed, turn these options off.

Phases of the guide

• Preparation: environment, user and directory — Verify the environment and the listener service · Verify the environment and the listener service (source) · Verify the environment and the listener service (target) · Verify the SCAN and the service's instances · Check the release, character set and log mode · Check the release, character set and log mode (source) …
• 1. Diagnosis: is this the right target? (schema refresh) — Record the state of the target schema (target) · Record the state of the source schema (source)
• 2. Safety net: backup of the target schema and the user definition — Save the test user's definition and privileges (target) · Back up the target schema (target)
• 1. Diagnosis: transportable prerequisites — Check the platform, character set and tablespace set on the source (source) · Check the platform, character set and name conflicts on the target (target)
• 2. Safety net and preparation — Record the datafile list and the current READ ONLY state (source) · Prepare the datafile directory on the target (target)
• 3. Execution: READ ONLY, metadata export and copy on the source — Make the tablespaces READ ONLY (source) · Write the transportable export parfile (source) · Run the metadata export (source) · Copy the datafiles and the dump to the target (source)
• 4. Execution: plug-in on the target — Verify the copied files (target) · Convert the datafiles to this platform: RMAN CONVERT (target) · Create the object owner schemas (target) · Create the plug-in import parfile (target) · Run the plug-in import (target) · Make the tablespaces writable (target)
• 5. Verification and follow-up — Export the objects outside the tablespaces (source) · Load the objects outside the tablespaces (target) · Compile and verify invalid objects (target) · Set the tablespaces back to READ WRITE on the source (source)
• Export — Review large objects, LOBs and partitions · Estimate the size (ESTIMATE_ONLY) · Estimate the size (ESTIMATE_ONLY) (source) · Write the export parfile · Write the export parfile (source) · Run the export …
• Moving the dump files to the target — Create the checksum file · Copy the dump files to the target · Verify the checksum on the target
• Database link (NETWORK_LINK) — Create the database link on the target
• 3. Execution: empty the target schema — Lock the user and close the sessions (target) · Drop the schema and recreate the test user (target) · List the objects that will not be updated in REPLACE mode (target)
• Import — Check the dump files · Check the dump files (target) · Check space and existing schemas on the target · Check space and existing schemas on the target (target) · Write the import parfile · Write the import parfile (target) …
• After the import: compilation and statistics — Compile invalid objects · Compile invalid objects (target) · Gather statistics · Gather statistics (target)
• 4. Verification and follow-up (schema refresh) — Disable the scheduler jobs that came from production (target) · Reapply the privileges the schema grants to others (target) · Check objects, synonyms and links (target) · Unlock the user (target)
• SQLFILE: extracting DDL from a dump — Check the dump files · Write the SQLFILE parfile · Generate the DDL file · Review the DDL file
• Scheduling: run the export regularly (cron) — Prepare the script, log and TNS directories… (…)… · Create the wallet and add the credential · Copy the wallet to the other nodes · Write sqlnet.ora (for this job only)… (…)… · Write tnsnames.ora (for this job only)… (…)… · Write the export script… (…)… …
• 1. Diagnosis: what state is the job in? — Show the job's state, sessions and wait reason · Interpret the result and choose the action
• 2. Execution: resume the job — Fix the cause of the stop or suspension · Attach to the job and resume it (ATTACH, START_JOB) · Verify that the job is running
• 2. Execution: terminate the job (KILL_JOB) — Attach to the job and terminate it with KILL_JOB · Verify that the job is gone
• 2. Execution: clean up the orphaned master table — Drop the master table of the orphaned job
• Monitoring — Monitor the running job · Monitor the running job (source) · Monitor the running job (target) · Attach to the job: ATTACH and interactive commands · Attach to the job: ATTACH and interactive commands (source) · Attach to the job: ATTACH and interactive commands (target)
• Cleanup and security — List interrupted jobs and master tables · List interrupted jobs and master tables (source) · List interrupted jobs and master tables (target) · Terminate the job with KILL_JOB · Terminate the job with KILL_JOB (source) · Terminate the job with KILL_JOB (target) …