Recover Logical Objects with Oracle Data Pump Import in 26ai

Oracle Data Pump Import, invoked with impdp, loads supported table data and metadata from a Data Pump dump file set. It can recreate a dropped table from an earlier logical export, refresh a schema, move objects into a staging schema, or populate a migration target. The target does not have to be the database from which the objects were exported, provided that release, feature, character-set, security, and object dependencies are compatible.

The word recovery needs a precise boundary in this lesson. Data Pump performs logical object loading. It does not restore data files, control files, the server parameter file, or online redo logs, and it does not apply archived redo for media recovery or database point-in-time recovery. RMAN provides those physical restore and recovery capabilities. A full-mode import is therefore not a physical database restore, even when the dump file contains a broad logical export.

The controlled example in this lesson recovers HR.EMPLOYEES from the Lesson 6 dump file set hr_access_%L.dmp. The table is assumed to be absent from a prepared target PDB. The workflow first inspects the DDL, then uses a safe existing-table policy, monitors the job, and validates more than the row count.

Data Pump documents five import modes. When a dump file is the source and no mode is supplied, Import attempts to load the file set in the mode used by Export. Explicitly naming the intended mode is still preferable in reviewed parameter files because it makes the job's scope visible.

Import mode	Parameter	Logical scope	Important boundary
Full	FULL=YES	All source content eligible for full-mode import	Not a physical database restore
Schema	SCHEMAS=HR	Objects owned by one or more schemas	Other schemas require approved privilege or remapping
Table	TABLES=HR.EMPLOYEES	Selected tables or partitions and eligible dependents	External and cross-schema dependencies may need preparation
Tablespace	TABLESPACES=USERS	Objects whose table data belongs to selected tablespaces	Target tablespaces normally must exist or be remapped
Transportable tablespace	TRANSPORT_TABLESPACES=...	Metadata plus separately transported data files	Requires a privileged and compatible transport workflow

FULL=YES loads eligible content available from the source; it does not recreate every component of a CDB or instance. Filters, unsupported objects, Oracle-managed schemas, privileges, and the contents of the dump still limit the result. A full network import requires DATAPUMP_IMP_FULL_DATABASE on the target and corresponding DATAPUMP_EXP_FULL_DATABASE authority on the source.

Schema mode replaces the old idea of “user mode.” An unprivileged account can normally import its own schema. Importing another schema requires approved cross-schema authority. Do not assume Import will create a missing target user: the dump must contain applicable user metadata, and the operator must have authority to create the account. In controlled environments, administrators often precreate the schema with approved authentication, tablespace, quota, and privileges.

Table mode is the best fit for this lesson. It loads specified tables or partitions and eligible dependent objects. It may not carry every external requirement, such as a user-defined type in another schema, a directory, a database link, a referenced application object, or configuration outside the database. Inventory those dependencies before treating an imported table as application-ready.

Data Pump jobs run in the database and access dump, log, and SQL files through database directory objects. The directory object stores a server-side path; it is not the current directory of the computer running impdp. Connect to the named service for the intended PDB and verify both the database object and the underlying operating-system access.

From an authorized administrative SQL session connected to the target PDB, verify the container, directory, and schema. Grant only the directory access required by the schema owner:

SHOW CON_NAME

SELECT directory_name, directory_path
FROM   dba_directories
WHERE  directory_name = 'DPUMP_DIR1';

SELECT username, account_status, default_tablespace
FROM   dba_users
WHERE  username = 'HR';

GRANT READ, WRITE ON DIRECTORY dpump_dir1 TO hr;

READ permits the job to read the dump file set. WRITE permits creation of the log and SQL preview. The Oracle software owner must also be able to access the mapped server path. Verify that HR has the quota and object privileges required to create the imported objects. This own-schema table import does not require DATAPUMP_IMP_FULL_DATABASE.

A directory object with the same name in another PDB does not make the dump files automatically available there. Confirm the service, PDB, object, and physical storage together. Managed cloud services can replace ordinary server-file access with service-specific object-storage credentials and procedures; follow the documentation for that service instead of copying an on-premises path.

Before changing the target, ask Import to write the DDL it would prepare. Store the following client-side parameters in employees_preview.par:

DIRECTORY=DPUMP_DIR1
DUMPFILE=hr_access_%L.dmp
LOGFILE=employees_preview.log
TABLES=HR.EMPLOYEES
SQLFILE=employees_preview.sql

Run the client without placing a password in the command or parameter file:

impdp hr@pdb1 PARFILE=employees_preview.par

SQLFILE writes the DDL that Import prepares, but it does not execute that DDL or load the table rows. Both the preview and log are written through DPUMP_DIR1. Review the owner, tablespaces, storage clauses, constraints, indexes, triggers, grants, and dependencies. The SQL file can also include session statements or anonymous PL/SQL blocks, so it is a review artifact rather than a script to execute blindly.

This preview confirms neither the readability of all table data nor the success of the eventual load. It also cannot be combined with QUERY. Use it to detect surprising DDL, then prove the complete workflow with an isolated test import and target-side validation.

After reviewing the preview, create employees_recovery.par. The explicit table mode limits scope, and TABLE_EXISTS_ACTION=SKIP protects a table that unexpectedly already exists:

DIRECTORY=DPUMP_DIR1
DUMPFILE=hr_access_%L.dmp
LOGFILE=employees_recovery.log
TABLES=HR.EMPLOYEES
TABLE_EXISTS_ACTION=SKIP
JOB_NAME=HR_EMPLOYEES_RECOVERY
METRICS=YES
LOGTIME=ALL

impdp hr@pdb1 PARFILE=employees_recovery.par

Data Pump prompts for the database credential unless an approved secure credential mechanism is in use. The parameter file is read by the client, while database processes read the dump and write the log. In the intended scenario, HR.EMPLOYEES is absent, so Import can create and load it. If the table unexpectedly exists, SKIP prevents the job from dropping, truncating, or appending to that table.

During or after the job, the owner can inspect its Data Pump job records:

SELECT job_name, operation, job_mode, state,
       degree, attached_sessions
FROM   user_datapump_jobs
ORDER  BY job_name;

Review the connected service, job owner, name, mode, state, files opened, warnings, object-level errors, and client exit status. A completed job does not prove that every requested object loaded. Data Pump can finish after recording errors for individual object paths, so the full log and the target state determine whether the recovery objective was met.

The primary workflow uses the safe action. Other actions are useful only after examining dependencies and the consequences for target data and metadata.

TABLE_EXISTS_ACTION	Effect	Main risk
SKIP	Leaves the existing table unchanged and continues	The dump's rows are not loaded into that table
APPEND	Adds imported rows without removing existing rows	Duplicate, conflicting, or unreconciled data
TRUNCATE	Removes existing rows and then loads source rows	Destructive data loss or referential-integrity failure
REPLACE	Drops, recreates, and loads the table	Loss of target-only structure, data, grants, or dependencies

The default is SKIP, except that CONTENT=DATA_ONLY defaults to APPEND. Neither SKIP nor REPLACE is valid with CONTENT=DATA_ONLY. With SKIP, APPEND, or TRUNCATE, Import does not modify source table-dependent objects such as indexes, grants, triggers, and constraints. With REPLACE, eligible dependent objects from the dump can be dropped and recreated with the source table.

APPEND does not deduplicate or reconcile rows. TRUNCATE and REPLACE are destructive and require a reviewed rollback plan, dependency analysis, and referential-integrity checks. An operator should never switch actions merely to make an import error disappear.

Staging can be safer than loading directly into a live application schema. Assume an administrator has already created and approved HR_STAGE and the permanent APP_DATA tablespace. An authorized operator could use this parameter file:

DIRECTORY=DPUMP_DIR1
DUMPFILE=hr_access_%L.dmp
LOGFILE=hr_stage_import.log
SCHEMAS=HR
REMAP_SCHEMA=HR:HR_STAGE
REMAP_TABLESPACE=USERS:APP_DATA
JOB_NAME=HR_STAGE_IMPORT
METRICS=YES
LOGTIME=ALL

impdp dp_operator@pdb1 PARFILE=hr_stage_import.par

The operator requires approved cross-schema authority, commonly DATAPUMP_IMP_FULL_DATABASE. APP_DATA must exist, and HR_STAGE needs sufficient quota there. Remapping selected owners and storage locations does not make an application automatically portable. Schema names embedded in type bodies, views, procedures, packages, strings, or external configuration might not be rewritten. Objects with OIDs can also require a reviewed TRANSFORM=OID:N strategy when loaded into the same database under another schema.

Review the generated DDL and validate the staging copy before reconciliation or cutover. Changes to owners, tablespaces, object identifiers, grants, or dependencies can alter both database behavior and application assumptions.

Import parameters can narrow or reshape a job. INCLUDE and EXCLUDE filter eligible metadata paths. CONTENT=METADATA_ONLY loads definitions without table rows, while CONTENT=DATA_ONLY loads rows into compatible structures. REMAP_SCHEMA, REMAP_TABLESPACE, and REMAP_TABLE change selected target names or locations. A TRANSFORM changes generated DDL, and QUERY filters rows while forcing a SQL-based loading path.

Each option changes scope or target behavior. Keep the primary recovery job simple, and test each additional filter or transform independently. A filter can exclude a necessary dependent object; a transformation can create a structure that differs materially from the source.

Relevant Oracle AI Database 26ai enhancements

For a planned migration of legacy definitions, 26ai supports the following optional transform:

TRANSFORM=LONG_TO_LOB:Y

It maps LONG columns to CLOB and LONG RAW columns to BLOB; the default is N. This is not a routine recovery switch. It changes target DDL and can affect storage, APIs, indexes, and application behavior. Preview, test, and define rollback before using it.

Oracle 26ai also permits longer Data Pump encryption passwords. If the dump requires one, prompt without exposing the secret:

impdp hr@pdb1 PARFILE=employees_recovery.par ENCRYPTION_PWD_PROMPT=YES

The supplied password must match the export password. Do not place it in the command, parameter file, article, shell history, or transcript. Encryption availability and licensing still depend on the environment.

Row counts are useful, but they are not enough. Confirm the target CDB, PDB, service, schema, and object names. Review the log and process exit code; compare counts with a trusted export inventory; inspect indexes, constraints, triggers, grants, comments, sequences, and dependencies; and test application authorization and transactions. Also verify tablespace placement, quota, partitions, LOB storage, and encryption requirements.

These dictionary queries summarize the current schema objects and the recovered table's constraints:

SELECT object_type, status, COUNT(*) AS object_count
FROM   user_objects
GROUP  BY object_type, status
ORDER  BY object_type, status;

SELECT constraint_name, constraint_type, status, validated
FROM   user_constraints
WHERE  table_name = 'EMPLOYEES'
ORDER  BY constraint_name;

Diagnose invalid objects before compiling them. Reset credentials for database links or similar objects when reusable secrets are intentionally not carried. Record elapsed time, warnings, errors, decisions, and remediation. Remove a staging import only through an approved cleanup plan. Neither a SQLFILE preview, a clean log, nor a successful exit status substitutes for a tested result in an isolated environment.

A dump from an earlier Oracle release is not automatically suitable for every 26ai target. Check source and target releases, Data Pump server versions, the export VERSION, object types, database character sets, time-zone files, CDB and PDB scope, and application support. Transported data files add platform, endian, closure, encryption, and conversion requirements. TDE keys, wallets, credentials, and encrypted columns or tablespaces need separate handling.

A logical import and a database upgrade are different operations. Use AutoUpgrade and the Upgrade Guide for a supported database upgrade. Use a transportable workflow only after validating its own prerequisites. Starting with 26ai, the default dump layout can use trailer blocks; a 26ai-format dump may require a 26ai or later Data Pump server. Set VERSION deliberately when an earlier target must read the export rather than assuming that a newer dump is backward-readable.

Display command-line parameters and interactive commands without connecting to a database:

impdp HELP=YES

After attaching to an active job, help at the Data Pump prompt lists the available interactive commands:

Import> HELP

This is separate from the optional SQL*Plus help repository. Always check the documentation for the database release that runs the server-side Data Pump job, especially before adopting a new parameter or dump format.

A recoverable dump is one that the team can locate, read, understand, and test before an incident. Record the export job name, database release, source PDB, mode, filters, dump-file pattern, encryption requirements, and retention date alongside the dump. Preserve the export log because it documents what the source job actually included and which objects produced warnings. Protect dump files as database data: they can contain sensitive rows and metadata even when their filenames look harmless.

Before an import, copy or expose the approved dump through the target's supported storage mechanism, verify file ownership and directory grants, and confirm that the operator is using the intended service. Freeze the parameter file through change control so that a last-minute edit cannot silently widen the scope. Capture the target schema inventory before the job. For destructive actions, obtain a separate recoverable copy of the target and a documented authorization; do not rely on the source dump as the rollback for target-only changes.

After the test import, retain the parameter file, log, preview, timing, exit status, validation results, and remediation notes as one recovery record. Repeat the exercise on a schedule that reflects the application's recovery objectives. A logical export may be older than the acceptable data-loss window even when it imports perfectly. RMAN recovery, redo availability, and Data Pump object recovery should therefore be tested as complementary procedures, each with a named owner and measurable acceptance criteria.

Use Data Pump Import when an existing logical dump contains the required object version and the target can accept it. Use RMAN restore and recovery for database-file loss, media failure, and physical point-in-time recovery. If a table or partition must be recovered from RMAN backups rather than an existing logical dump, consider RMAN RECOVER TABLE. That workflow builds an auxiliary environment and uses Data Pump as part of RMAN's orchestration; it is not the same operation as manually importing a prepared dump.

The durable lesson is to match the recovery source to the loss. Data Pump provides selective logical loading and flexible remapping. RMAN provides the physical recovery chain. For either tool, success means more than command completion: it requires a documented source, controlled target, reviewed log, verified dependencies, and application-level proof that the recovered result is usable.