LLI Wiki — My Digital Knowledge Base

Migrate an Oracle schema to another server with Data Pump

Lock the account, export with expdp, copy the files, import with impdp and check the result.

Procedure to migrate an Oracle schema from one server to another with Data Pump (expdp / impdp). The example migrates schema APP_SCHEMA from oradb01 to oradb02.

1. Lock the application account

To prevent connections during the migration:

ALTER USER APP_SCHEMA ACCOUNT LOCK;
-- and, when finished:
ALTER USER APP_SCHEMA ACCOUNT UNLOCK;

2. Export on the source server

A Data Pump DIRECTORY object (DATA_PUMP_DIR_X) must exist on both servers, pointing for example to /u02/oracle/datapump.

date=$(date '+%Y%m%d%H%M%S')
expdp system/<PASSWORD> SCHEMAS=APP_SCHEMA DIRECTORY=DATA_PUMP_DIR_X compression=all \
  dumpfile=app_schema_expdp_${date}.dmp logfile=app_schema_expdp_${date}.log

# Prepare the import command for the target
echo "impdp system/<PASSWORD> SCHEMAS=APP_SCHEMA DIRECTORY=DATA_PUMP_DIR_X DUMPFILE=app_schema_expdp_${date}.dmp LOGFILE=app_schema_impdp_${date}.log" >> app_schema_impdp.sh

3. Copy the files to the target

scp /u02/oracle/datapump/app_schema* oracle@oradb02:/u02/oracle/datapump/

4. Import on the target server

chmod 755 app_schema_impdp.sh
./app_schema_impdp.sh

5. Cleanup and checks

Once the migration is validated, drop the schema on the old server:

DROP USER app_schema CASCADE;

To keep a table under another name before dropping (backup):

RENAME TABLE APP_SCHEMA.AUDIT_TABLE TO APP_SCHEMA.AUDIT_TABLE_BU;

Check the result:

SELECT table_name, owner FROM dba_tables WHERE owner = 'APP_SCHEMA';
SELECT COUNT(*) FROM APP_SCHEMA.AUDIT_TABLE;

Notes

Mastodon LLIMastodon SocialFosstodon