Lesson 1

Oracle AI Database 26ai provides several technologies for protecting and moving data. Recovery Manager (RMAN) creates and manages the physical backups needed for media recovery, while Oracle Data Pump exports and imports logical data and metadata. The two technologies solve different problems and should not be treated as interchangeable backup methods.

Oracle Data Pump is useful when a DBA needs to move a schema between databases, refresh a test environment, copy selected tables, preserve object definitions, migrate data into a pluggable database (PDB), or re-create selected objects after a recovery operation. Data Pump can process an entire database within the connected container, one or more schemas, selected tables, tablespaces, or transportable data sets. It can also move data directly across a database link without creating an intermediate dump file for a network import.

This module introduces Data Pump Export, Data Pump Import, their supporting packages, and the operational decisions that affect a logical data-movement job. The primary discussion targets Oracle AI Database 26ai and its multitenant architecture.

By the end of this module, you will be able to:

1. Distinguish a Data Pump logical export from an RMAN physical backup.
2. Explain the roles of expdp, impdp, DBMS_DATAPUMP, and DBMS_METADATA.
3. Choose an appropriate full, schema, table, tablespace, transportable, or network operation.
4. Configure a directory object and protect the credentials and privileges used by Data Pump.
5. Export and import selected data and metadata in the correct PDB.
6. Use parameter files, filters, parallel workers, and Data Pump job controls when appropriate.
7. Recognize the limits of logical exports and incorporate them into a broader RMAN-based protection strategy.

A physical backup copies or represents the files that Oracle Database uses to store and recover the database. These files include data files, control files, and archived redo logs. RMAN understands Oracle block structure, tracks backup metadata, detects corrupt blocks, supports incremental backups, and coordinates restore and recovery operations. For these reasons, Oracle identifies physical backups as the foundation of a sound backup and recovery strategy.

A logical export works at the object level. Depending on the selected mode and parameters, a Data Pump dump file set can contain table rows and metadata describing tables, indexes, constraints, grants, views, stored program units, and other supported objects. Data Pump Import reads that representation and creates or loads the selected objects in a target database.

Even a Data Pump operation that specifies FULL=YES is a full-mode logical export, not a physical whole-database backup. It does not back up the physical data files, control files, server parameter file, online redo logs, or archived redo logs required for media recovery. If storage failure destroys database files, a dump file alone cannot roll the database forward through its transaction history.

The practical rule is straightforward: use RMAN to protect the database against physical loss and use Data Pump when logical portability or selective object handling provides additional value. A mature strategy can use both. For example, a DBA might maintain tested RMAN backups for disaster recovery while also exporting an application schema before a major deployment or structural change.

Oracle Data Pump is more than a pair of command-line executables. It consists of four closely related components:

• expdp is the command-line client used to start, control, and monitor Data Pump Export jobs.
• impdp is the command-line client used to start, control, and monitor Data Pump Import jobs.
• DBMS_DATAPUMP is the PL/SQL application programming interface for creating and managing Data Pump jobs programmatically.
• DBMS_METADATA extracts, transforms, and re-creates the metadata associated with database objects.

The clients submit parameters to the database, but most Data Pump processing takes place in server processes. Data Pump creates a control job table and uses a control process and worker processes to perform the work. Because the job exists in the database, a client can detach from a running job and later attach to it again. This architecture also makes it possible to monitor, stop, restart, and interact with supported jobs without treating the client process as the job itself.

Data Pump writes status and diagnostic information to its log file. DBAs should review the process exit code and the log rather than assuming that the presence of a dump file proves that every requested object was exported successfully. Running jobs can also be inspected through Data Pump views and, for long-running table transfers, through the V$SESSION_LONGOPS dynamic performance view.

Traditional client-based Export wrote its dump file where the client was running. Data Pump normally writes and reads dump files on the database server or through a supported storage integration. For a file-system operation, Data Pump uses an Oracle directory object to associate a database name with an operating-system path.

A database administrator connected to the appropriate container can create a directory object and grant access to the account running the job:

CREATE DIRECTORY dpump_dir1 AS '/u01/app/oracle/dpump';

GRANT READ, WRITE ON DIRECTORY dpump_dir1 TO hr;

The SQL statement creates only the database object. It does not create /u01/app/oracle/dpump in the operating system. The directory must already exist, the Oracle Database processes must have the necessary operating-system access, and the path must comply with the security and storage requirements of the environment. The example path is illustrative rather than a required Oracle location.

The privileges on the directory object are also distinct from operating-system permissions. Export generally needs permission to write dump and log files, while Import needs permission to read dump files and may need permission to write its log and SQL files. Grant access only to the accounts that require it, and avoid using a broad administrative account merely for convenience.

Oracle AI Database 26ai uses the multitenant container database architecture. A container database (CDB) contains a root and one or more PDBs, and application data normally belongs to a PDB. A Data Pump client should therefore connect to the service for the PDB that owns the objects being exported or that will receive the import.

A full-mode export connected to a PDB represents the supported contents of that PDB; it is not a single export of every PDB in the CDB. Oracle warns that Data Pump operations are not typically needed when a client connects to the CDB root or seed. Connecting to the intended PDB also helps ensure that names, directory objects, privileges, and target objects are resolved in the expected container.

Data Pump supports important multitenant migration scenarios. It can move all or part of an earlier non-CDB into a PDB, move data between PDBs in the same or different CDBs, and participate in full transportable operations. Release compatibility, character sets, time-zone files, encryption, component registration, and transportable-data requirements must still be evaluated before a migration.

The correct scope depends on the business purpose of the job. Exporting substantially more data than necessary consumes storage, lengthens execution, and can expose sensitive information. Exporting too little can omit dependent metadata that the target requires. Common Data Pump scopes include:

• Full mode: processes the supported objects available to a full-mode operation in the connected database or PDB. It is not a physical backup and does not mean that every internal database component can be reconstructed from the dump file.
• Schema mode: moves the supported objects owned by one or more schemas. This is common for application migrations and development or test refreshes.
• Table mode: selects particular tables, partitions, or dependent objects when the syntax and object structure permit it.
• Tablespace mode: exports tables whose storage belongs to specified tablespaces, subject to the documented rules.
• Transportable mode: combines exported metadata with separately transported data files, avoiding the cost of unloading and reloading all table data.
• Network operations: use a database link to move data. A network import reads from the source and loads the target without an intermediate dump file, while a network export reads a remote source and writes dump files at the connected database.

Parameters such as INCLUDE, EXCLUDE, CONTENT, QUERY, and SAMPLE can further refine a job. Import transformations and remapping parameters can change schema names, tablespaces, data files, or selected object attributes. These controls are powerful, but each can affect dependencies and the method Data Pump uses to move data.

After a DBA creates the directory object and grants access, the owner of the HR schema can start a schema-mode export. Omitting a password from the command causes the client to obtain it interactively or through another approved authentication configuration:

expdp hr@pdb1 DIRECTORY=dpump_dir1 DUMPFILE=hr_export_%U.dmp LOGFILE=hr_export.log SCHEMAS=HR

DIRECTORY identifies the Oracle directory object. DUMPFILE names the dump file set, and LOGFILE names the job log. SCHEMAS=HR selects schema mode. The %U substitution variable provides a file-number template that can accommodate multiple dump files, which is useful if a later job uses multiple workers or file-size limits.

A corresponding introductory import command can read that dump file set in an appropriately prepared target PDB:

impdp hr@pdb1 DIRECTORY=dpump_dir1 DUMPFILE=hr_export_%U.dmp LOGFILE=hr_import.log SCHEMAS=HR

This example demonstrates the relationship between the clients, PDB service, directory object, dump files, and log files. A real import must also account for whether the target objects already exist, whether the source and target releases are compatible, and whether objects should be skipped, replaced, remapped, or transformed. Data Pump Import is therefore not simply an "undo" button for Export.

Longer and repeatable jobs should normally place parameters in a parameter file rather than on a lengthy command line. Parameter files reduce quoting errors, make the intended configuration reviewable, and keep reusable scripts free of passwords. They are also easier to place under controlled change management than ad hoc terminal commands.

A schema owner can generally export its own supported objects after receiving the required access to the directory object. Operations involving objects outside the connected user's schema can require the powerful DATAPUMP_EXP_FULL_DATABASE or DATAPUMP_IMP_FULL_DATABASE role. These roles should be granted only when the operational requirement justifies their reach.

Do not place a clear-text password in a shell command, script, process list, or lesson example. Use an interactive prompt or an approved Oracle credential, wallet, or external-authentication mechanism. Protect dump files as carefully as production data because they can contain table rows, object definitions, grants, and other sensitive information. Directory privileges, operating-system permissions, encryption choices, storage retention, and access auditing should all be part of the job design.

Data Pump also interacts with encrypted data and the unified audit trail. Oracle can warn when encrypted data would be exported without corresponding protection. The correct response is not merely to suppress the warning; it is to evaluate the export encryption mode, key availability, destination, retention period, and the people or services allowed to read the dump file.

Original Export exposed a direct-path option as a prominent manual choice. Modern Data Pump evaluates the operation and object characteristics and then selects an appropriate access method. When possible, direct path bypasses much of the SQL processing layer and moves rows with minimal interpretation. Data Pump can instead use external tables or conventional path when direct path is not available.

Active triggers, referential constraints, clustered tables, encrypted columns, fine-grained access control, object types, selected filtering parameters, and other characteristics can influence the chosen method. An export can use one method while the corresponding import uses another. The presence of a fallback is one reason that an operation can succeed while performing differently from a simple performance estimate.

Parallel workers can improve throughput when the edition, job type, object layout, CPU, storage, and dump-file configuration support parallel execution. More workers do not guarantee a faster job. Storage contention, serial metadata work, a single dump file, or objects that cannot be processed in parallel can limit the benefit. Performance planning should therefore be based on measured jobs and documented restrictions rather than a universal PARALLEL=4 recommendation.

Oracle AI Database 26ai extends Data Pump support for current database workloads, including documented facilities for loading and unloading vector data. Modern data-type support does not eliminate compatibility checks. A DBA must still confirm the source and target versions, selected VERSION setting, object features, character sets, time-zone data, and any release-specific restrictions.

Data Pump is especially valuable when the required unit of work is smaller than a database. If an application team needs a copy of one schema, a table must be moved to a test PDB, or object definitions must be captured before deployment, a logical export can be more focused than restoring an entire physical backup. RMAN can also invoke Data Pump internally in specialized operations such as recovering tables or transporting tablespaces.

Nevertheless, a logical export is only as current as the data captured by that job, and it does not preserve all physical recovery information. It cannot apply redo to reach an arbitrary recovery point, repair a lost control file, or restore a damaged data file. Exporting every night does not create the equivalent of an RMAN incremental-backup chain.

For production protection, define recovery point and recovery time requirements first. Build and test the RMAN strategy needed to satisfy those requirements, protect archived redo, validate backups, and rehearse recovery. Add Data Pump where logical portability, selective re-creation, migration, or an additional object-level copy provides a clear benefit.

Oracle introduced Data Pump in Oracle Database 10g as the high-performance successor to the original Export and Import utilities. Original Export was desupported for general use beginning with Oracle Database 11g and is desupported for all uses in Oracle AI Database 26ai. The original Import executable remains relevant only when a DBA must read a dump file created by original Export.

The formats are not interchangeable: impdp cannot read a dump file created by exp, and imp cannot read a Data Pump dump file created by expdp. Oracle Data Pump legacy mode can map certain original parameter names to Data Pump parameters for script migration, but that compatibility facility does not turn an original Export dump file into a Data Pump dump file.

Accordingly, this module teaches expdp and impdp as the current utilities. Original exp/imp syntax is retained only as historical context needed to recognize old scripts and dump files, not as a second recommended workflow.

The remaining lessons develop the concepts introduced here. You will examine export modes and parameters, create reusable job configurations, determine when direct path or another access method applies, inspect dump and log files, and use Import to re-create or transform selected objects. You will also consider version compatibility, PDB scope, security, and how logical exports complement the physical protection supplied by RMAN.

Keep one distinction in view throughout the module: Data Pump moves logical data and metadata; RMAN protects and recovers the physical database. Choosing the correct tool begins with identifying whether the requirement is data movement, selective object handling, or recovery from physical loss.

For authoritative details and parameter restrictions, consult the Oracle AI Database Database Utilities 26ai documentation. Backup architecture and recovery procedures are documented separately in the Oracle AI Database Backup and Recovery User's Guide.