Migrating database using expdp and impdp checklist

Expdp and Impdp database migration checklist

On source

Purge dba_recyclebin;

expdp \'/ as sysdba\' directory=nfs dumpfile=full_bkp_%U.dmp full=y parallel=8

Use toad to get the ddl for all the roles and users.

Comment out all unnecessary stuff except grants. We will run this grants script at the end of import on Target.

On Target

Create target DB.

Make sure you have same character set.

Create all the tablespaces as source DB.

Create all the roles as source.

Create all the profiles as source.

Make sure you have same temp tablespace name as source.

Once the export is completed. Copy it to the corresponding location and start import. Its better to use nfs storage so that you can skip copying.

Import only the necessary schemas and exclude all the sys related schemas.

Make sure you have a log file assigned to each schema import.

Easier way to do is create script like this on your server and run in nohup so it will run in background.

vi import.sh

#!/bin/sh

export ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome_1
export ORACLE_SID=skwh1

impdp \'/ as sysdba\' directory=nfs dumpfile=full_bkp_%U.dmp parallel=8 schemas=x1 table_exists_action=replace logfile=x1_import.log
impdp \'/ as sysdba\' directory=nfs dumpfile=full_bkp_%U.dmp parallel=8 schemas=x2 table_exists_action=replace logfile=x2_import.log
impdp \'/ as sysdba\' directory=nfs dumpfile=full_bkp_%U.dmp parallel=8 schemas=x3 table_exists_action=replace logfile=x3_import.log


nohup ./import.sh &

Once everything is imported. Verify the logs.

You might see a lot of errors related to grants. Please ignore as we will run the scripts we captured from source.

Try to troubleshoot the errors.

Now run role grants script and user grants script.

If any materialized views failed to create as part of import. Re run now by choosing table_exists_action=skip so you dont need to import whole table again.

After solving all the issues. Go to $ORACLE_HOME/rdbms/admin and run @utlrp.sql

This should compile all invalid objects.