Steps for migration with EXPDP and IMPDP DATAPUMP

Steps for migration of database with EXPDP and IMPDP DATAPUMP utility

In Example, EXPDP backup for migrating one schema, you can choose multiple also as you need. We are migrating 11g to 19c database using DATAPUMP. You can divide these steps into multiple categories:

  1. Pre-steps before EXPDP.
  2. Start EXPDP.
  3. Pre-steps before IMPDP.
  4. Start IMPDP.
  5. Post steps after IMPDP.

PRE-STEPS BEFORE EXPDP: Pre steps performed at 11g Source database:

  1. Check the Version or Edition of Source database. (like Standard or Enterprise).
Select * from v$version;

2. Check the count of objects present in Schema which needs to be backup.

select owner,object_type,count(*) from dba_objects where owner = 'schema_name' order by owner,object_type ;

--Count total objects for schema 
Select owner,count(*) from dba_objects group by owner order by owner;

3. Check invalid objects present in Schema and try to recompile first.

-- Recomplie the objects
@?\rdbms\admin\utlrp.sql

--List the invalid objects 
Select object_name,object_type from dba_objects where owner='Schema_name' order by object_type,object_name;

4. Check the DBA Registry Component.

COL version FORMAT a12
COL comp_id FORMAT a8
COL schema LIKE version
COL comp_name FORMAT a35
COL status FORMAT a12

SELECT comp_id,schema,status,version,comp_name FROM dba_registry ORDER BY 1;

5. Check the Characters Set for Database:

COL value FORMAT a15
SELECT * FROM nls_database_parameters WHERE parameter LIKE '%SET' ORDER BY 1;

6. Check Non-Default Parameter Set in Database:

Check the non default parameter
set pages 999 lines 100
col name format a40
col value format a50
select  name, value from v$parameter where isdefault = 'FALSE' and value is not null order by name;

7. Check the User Account status and information:

set pages 999
set line 999
col account_status for a20
col username for a20
select username,password,account_status,expiry_Date,profile from dba_users;

8. Check the Redolog file size & members count:

SELECT group#,bytes,blocksize,members,status FROM v$log ORDER BY 1;

SELECT * FROM v$logfile ORDER BY 1,3;

9. Check the tablespace for the USER and generate DDL for them:

SELECT tablespace_name, bytes, status, online_status, file_name   FROM dba_data_files ORDER BY 1,5;

--Create script for all tablespace which you need to create at destination. 
--Example for USERS tablespace.
SET lines 100
COL ddl FORMAT a100
SELECT dbms_metadata.get_ddl('TABLESPACE','USERS') "DDL" FROM dual;

10. Create a backup of spfile.

Create Pfile=’Backuplocation’ from spfile;

11. Back up the Network admin folder & listener status.

Start the EXPDP data pump process

  1. Create a directory for backup.
Create Directory DATA_PUMP_DIR as ‘E:\Backup’;

2. Start the EXPDP datapump command.

expdp \"sys/<PASSWORD>@INSTANCE as sysdba\" SCHEMAS=HR DIRECTORY=DATA_PUMP_DIR DUMPFILE=hr.dmp LOGFILE=hr.log

--For Enterprise Edtion use parallel option:
expdp \"sys/<PASSWORD>@INSTANCE as sysdba\" SCHEMAS=HR DIRECTORY=DATA_PUMP_DIR DUMPFILE=hr_%U.dmp LOGFILE=hr.log parallel=4

Pre-Steps before IMPDP

  1. Install the Oracle 19c and choose the edition and charachterset during installation.
  2. Check and modify the redo log files while creating the database.
  3. After installation configure the Listener and Networking setting for connectivity.
  4. Need to set the non-default parameter for the instance.
  5. Need to verify the spfile with old spfile to modified the parameter like SGA_TARGET, MEMORY_TARGET, Processes, CURSOR_SHARING.
  6. Create the tablespace need to be create for import user.
  7. Create user with permission before start the import process.

Start the IMPDP process for import into Destination.

  1. Create the directory for import process.
Create Directory DATA_PUMP_DIR as ‘E:\Backup’;

2. Copy the dump file from source to destination.

3. Start the IMPDP process for importing the schema.

impdp \"sys/<PASSWORD>@INSTANCE as sysdba\" dumpfile=HR.DMP logfile=impdp_HR.log directory=DATA_PUMP_DIR schemas=HR

--For Enterprise Edtion use parallel option:
impdp \"sys/<PASSWORD>@INSTANCE as sysdba\" dumpfile=HR_%U.dmp logfile=impdp_HR.log directory=DATA_PUMP_DIR schemas=HR parallel=4

Post steps after IMPDP.

  1. Recompile the invalid objects and verified invalid objects.
-- Recomplie the objects
@?\rdbms\admin\utlrp.sql

--List the invalid objects 
Select object_name,object_type from dba_objects where owner='Schema_name' order by object_type,object_name;

2. Verify the object count taken at pre-steps before the EXPDP process.

select owner,object_type,count(*) from dba_objects where owner = 'schema_name' order by owner,object_type ;

--Count total objects for schema 
Select owner,count(*) from dba_objects group by owner order by owner;

3. Verify the Redo log size & member count.

SELECT group#,bytes,blocksize,members,status FROM v$log ORDER BY 1;

SELECT * FROM v$logfile ORDER BY 1,3;

4. Verify the character set of the database.

COL value FORMAT a15
SELECT * FROM nls_database_parameters WHERE parameter LIKE '%SET' ORDER BY 1;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.