1. Introduction
Oracle Data Pump is
Oracle’s high-performance, logical backup and migration utility used for data
movement across databases, environments, and platforms. It provides a reliable
and efficient method to export and import database objects while preserving data
integrity, metadata, and object dependencies.
In enterprise
environments, Data Pump is widely used for:
• Production to Test/Dev refresh
• Schema-level migrations
• Tablespace-level data movement
• Cross-PDB data transfer
• Controlled and auditable database changes
This document provides a
complete, step-by-step implementation of schema-level and tablespace-level
data migration using Oracle Data Pump in an Oracle 19c Multitenant RAC
environment.
The implementation
ensures a clean, controlled, and validated migration between source and target
PDBs with proper tablespace remapping and post-migration verification.
2. Architecture Overview
2.1 Schema-Level Migration Architecture
|
Component |
Source Environment |
Target Environment |
|
CDB |
PROD |
TEST |
|
PDB |
PRODPDB |
TESTPDB |
|
Schema |
APPUSER |
APPUSER |
|
Tablespace |
PROD_TS |
TEST_TS |
|
Database Type |
Oracle 19c RAC |
Oracle 19c RAC |
2.2 Tablespace-Level Migration
Architecture
|
Component |
Source Environment |
Target Environment |
|
CDB |
TEST |
PROD |
|
PDB |
TESTPDB |
PRODPDB |
|
Schema Owner |
CMSUSER |
CMSUSER |
|
Tablespace |
TEST_TS |
PROD_TS |
|
Database Type |
Oracle 19c RAC |
Oracle 19c RAC |
Shared Storage
• Shared directory: /u01/dpump
• Accessible from all RAC nodes
3. Objectives of This
Implementation
• Perform
schema-level migration using Oracle Data Pump
• Perform
tablespace-level migration using Oracle Data Pump
• Remap tablespaces
between source and target environments
• Ensure data
integrity and object consistency
• Validate migration
results through post-import checks
• Establish a
reusable migration procedure
4. Prerequisites
4.1 Database & OS Requirements
• Oracle
Database 19c installed on source and target
• Multitenant architecture (CDB/PDB)
• RAC configuration on both environments
• Archive logging enabled
• Adequate disk space available
4.2 Configuration Requirements
• Source and
target PDB services configured
• Oracle Data Pump directory object
created
• Shared filesystem accessible across
RAC nodes
• Required users and tablespaces created
on target
• Oracle OS user privileges available
5. Schema-Level
Migration Implementation
This
section describes the complete step-by-step implementation of schema-level
migration using Oracle Data Pump utilities (expdp and impdp) from the source
PDB to the target PDB environment.
The
migration was performed in a controlled manner to ensure data integrity,
consistency, and minimal downtime.
5.1 Pre-Migration Checks
on Source Database
Before starting the
export activity, the source database environment was validated.
Login to the source
database server and set the Oracle environment & Connect to the database as
SYSDBA & Switch to the source PDB
Verify schema details:
Verify tables owned by
the schema & Check tablespace usage:
These checks ensure the
schema exists, is accessible, and all objects are located in the expected
tablespace.
5.2 Pre-Migration Checks
on Target Database
Login to the target
database server and configure the Oracle environment & Connect to the
database as SYSDBA & Switch to the target PDB:
Verify schema existence:
If the schema does not
exist, create it:
SQL> CREATE USER appuser IDENTIFIED BY
appuser
DEFAULT TABLESPACE TEST_TS
TEMPORARY TABLESPACE TEMP;
Grant required
privileges:
SQL> GRANT connect, resource TO
appuser;
5.3 Directory Object
Configuration for Data Pump
Oracle Data Pump requires
a directory object to read and write dump files.
Create a directory at OS
level:
Create Oracle directory
object & Grant permissions
On Source
On Target
5.4 Schema Export from
Source Database (expdp)
Execute schema-level
export:
This command exports all
schema objects including tables, indexes, constraints, and data.
Verify dump files:
Review log file for
warnings or errors.
5.5 Schema Import into
Target Database (impdp)
Execute schema-level
import with tablespace remapping:
This recreates schema
objects in the target PDB and moves them to the target tablespace.
5.6 Post-Migration
Verification
Connect as the
application user & Validate data access
Ø Schema
APPUSER successfully migrated from PRODPDB to TESTPDB
Ø All
objects imported without errors
Ø Tablespace
remapping completed successfully
Ø Data
integrity validated
Ø Migration
completed successfully
6. Tablespace-Level
Migration Implementation
This section describes the end-to-end
implementation of tablespace-level data migration using Oracle Data Pump from
the source PDB (TESTPDB) to the target PDB (PRODPDB).
The migration was performed in a controlled RAC environment using a shared Data
Pump directory.
6.1 Source Tablespace Ownership Verification
(TESTPDB)
Before starting the export, it was necessary
to identify which schemas owned objects in the source tablespace.
Identify schemas owning segments in the source tablespace:
Identify object types within the tablespace:
This confirmed that CMSUSER was the only schema using tablespace
TEST_TS.
6.2 Target Environment Validation (PRODPDB)
The target environment was validated to ensure readiness for import.
Verify schema existence and status & Verify default and temporary
tablespaces
This ensured the schema CMSUSER existed and was mapped to the target
tablespace PROD_TS.
6.3 Data Pump Directory Configuration
A shared directory was configured for Data Pump operations across all
RAC nodes.
Create a directory at OS
level:
Create Oracle directory
object & Grant permissions
On Source
On Target
This ensured both source
and target environments could access the same dump files.
6.4 Tablespace-Level
Export from Source PDB (TESTPDB)
Execute the export from
OS as the schema owner
This exported all objects (tables, indexes,
constraints, and data) stored in the source tablespace.
Verify dump file creation:
6.5 Tablespace-Level Import into Target PDB (PRODPDB)
Execute the import from OS as the schema owner:
This recreated all objects in the target tablespace PROD_TS.
6.6 Post-Migration Verification (PRODPDB)
Connect to the target PDB as the schema user & Verify
Ø Tablespace TEST_TS successfully migrated to
PROD_TS
Ø All CMSUSER objects imported successfully
Ø Tablespace remapping verified
Ø No data loss observed
Ø Migration completed successfully
7.
Risks and Mitigation
This section identifies potential risks associated with schema-level and
tablespace-level migration using Oracle Data Pump and the mitigation steps
taken to minimize their impact.
7.1 Insufficient Disk Space
Risk:
Insufficient disk space in the shared Data Pump directory or target tablespace
may cause export or import failure.
Mitigation:
- Verified filesystem
space availability before export
- Used FILESIZE
parameter to split dump files
- Confirmed adequate
free space in target tablespaces
7.2 Missing or Invalid Schema/User on Target
Risk:
Import may fail if the target schema does not exist or is locked/expired.
Mitigation:
- Verified schema
existence and account status before import
- Ensured correct
default and temporary tablespaces
- Confirmed required
privileges were in place
7.3 Tablespace Mapping Errors
Risk:
Objects may be created in incorrect tablespaces if remapping is misconfigured.
Mitigation:
- Explicitly used
REMAP_TABLESPACE during import
- Validated tablespace
mapping after import
- Performed
object-level verification using DBA views
7.4 RAC-Related Conflicts
Risk:
Parallel jobs across RAC nodes may cause unexpected behavior or file access
issues.
Mitigation:
- Used shared
filesystem accessible across all RAC nodes
- Set CLUSTER=NO
during export to avoid cross-node conflicts
- Controlled
parallelism to balance performance and stability
7.5 Performance Impact
Risk:
Export or import operations may impact database performance.
Mitigation:
- Executed migration
during low-usage window
- Used parallel
execution for faster completion
- Monitored database
and OS performance during migration
7.6 Data Consistency Issues
Risk:
Inconsistent data may occur if changes happen during export.
Mitigation:
- Performed migration
during controlled maintenance window
- Ensured application
inactivity during export
- Used Data Pump’s
transactional consistency mechanisms
7.7 Object or Data Loss
Risk:
Tables, indexes, or constraints may fail to import.
Mitigation:
- Reviewed export and
import logs thoroughly
- Verified object
counts and row counts post-migration
- Recompiled invalid
objects where required
8.
Rollback Strategy
This section describes the rollback procedure in case the migration
needs to be reversed due to failures or validation issues.
8.1 Rollback Criteria
Rollback may be initiated if any of the following occur:
- Import job fails
with unrecoverable errors
- Data validation does
not match source
- Application testing
fails post-migration
- Incorrect tablespace
or schema mapping detected
8.2 Schema-Level Rollback Procedure
If schema-level migration fails:
1.
Connect
to the target PDB as SYSDBA
2.
Identify
imported objects
3.
Drop the
affected schema objects or schema user
Example:
SQL> DROP USER appuser CASCADE;
4.
Recreate
the schema with correct settings
5.
Re-run
the import after resolving the issue
8.3 Tablespace-Level Rollback Procedure
If tablespace-level migration fails:
1.
Connect
to the target PDB as SYSDBA
2.
Identify
objects imported into the target tablespace
3.
Drop
affected objects or truncate the target tablespace
Example:
SQL> DROP TABLESPACE prod_ts
INCLUDING CONTENTS AND DATAFILES;
4.
Recreate
the tablespace
5.
Re-run
the import after correcting the issue
8.4 Dump File Preservation
- Source dump files
are retained until migration validation is complete
- Dump files provide
the ability to re-import data if needed
- Source database
remains unchanged throughout the process
8.5 Post-Rollback Validation
After rollback:
- Confirm schema and
tablespace cleanup
- Verify no residual
objects remain
- Ensure target
environment is restored to original state
9.
Conclusion
This implementation successfully demonstrates
schema-level and tablespace-level data migration using Oracle Data Pump
in an Oracle 19c RAC Multitenant environment.
The process followed best practices by
validating prerequisites, performing controlled exports and imports, remapping
tablespaces, and executing post-migration verification.
This approach is suitable for:
• Production to Test refresh
• Environment cloning
• Database consolidation
• Enterprise database administration tasks
The documented procedure can be reused as a standard
operating guideline for future Oracle database migrations.
10. Future Enhancements
In future, Oracle Data Pump can be used for
additional activities such as:
- Full
database exports and imports
- Transportable
tablespace migrations
- Cross-PDB
and cross-environment migrations
- Automated
environment refresh activities
- Database
version upgrade support