Schema Refresh Using EXPDP-IMPDP command

Sruti Banerjee 15 mins2
()

Step by Step guide for Schema Refresh using DataPump Utility

Schema Refresh using Datapump Utility

Oracle Data Pump is a high-speed data movement utility provided by Oracle (10g onwards). It’s an extension to the old EXP and IMP utility with improved performance and enhanced security features.


Here we are performing a schema refresh activity using Datapump:

PARAMETERSSOURCETARGET
DATABASELAB04LAB01
SERVERMACHINE1MACHINE1
SCHEMATESTUSER4TESTUSER4

Pre-checks before performing Export-Import:

Before performing import in the target database, we can perform the following pre-checks to avoid any last-minute error :

 

  • Check the space utilization of schema before export and prior import, check whether the schema exists or not in the target database, if yes required space should be available for import in the target database.
  • Check the username and tablespaces associated with the source schema so that we can compare the same in the target database. If required we can create the tablespace or use parameter remap_tablespace if you want to import data in a different tablespace other than the source schema’s tablespace.
  • List down Roles/Profiles/Grants associated with the schema and it should be available in the target database.
  • Note down the object and their status and count prior to export and post-import.
  • Take DDL backup of target schema before import, this can help us when we need to maintain the old password along with all kinds of privileges.

Click here for How to check schema size.

Click here for How to check tablespace size.

Click here for How to check the object count of the schema.


Pre-requisites before taking export backup in Source Database :

The first step in Oracle Data Pump is to create an OS-level directory that will be used by Oracle for performing exports and imports. Below are the queries for the same :

  • Creating an OS-level directory (expdp)  in the source DB
[oracle@machine1 ~]$ mkdir -p /oradata/expdp/lab04
[oracle@machine1 ~]$ ls -lrt /oradata/expdp/lab04
total 0
  • Creating a directory ( TESTUSER4) inside the database
CREATE OR REPLACE DIRECTORY TESTUSER4 AS '/oradata/expdp/lab04';
Directory created.
  • Granting Read-write permissions to the directory TESTUSER4
  • Note: If we are invoking data pump utility from any other user apart from SYS, grant read/write permission to the user on that particular directory.
GRANT READ, WRITE ON DIRECTORY TESTUSER4 TO SYSTEM;
Grant succeeded.
  • To view the directory information
set lines 200
col DIRECTORY_NAME for a30
col DIRECTORY_PATH for a30
col OWNER for a20
select DIRECTORY_NAME,DIRECTORY_PATH,OWNER from dba_directories 
where DIRECTORY_NAME='TESTUSER4';


DIRECTORY_NAME                 DIRECTORY_PATH                 OWNER
------------------------------ ------------------------------ --------------------
TESTUSER4                      /oradata/expdp/lab04           SYS

To Perform Data Pump Export (expdp)

In case we want to know all the options available with expdp utility, we can use the below command to see the available parameters and their utilities:

  • expdp help=y
[oracle@machine1 lab01]$ expdp help=y
The Data Pump export utility provides a mechanism for transferring data objects
between Oracle databases. The utility is invoked with the following command:
   Example: expdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp

---- Trimmed---
The available keywords and their descriptions follow. Default values are listed within square brackets.
ESTIMATE
Calculate job estimates.
Valid keyword values are: [BLOCKS] and STATISTICS.
EXCLUDE
Exclude specific object types.
For example, EXCLUDE=SCHEMA:"='HR'".
FILESIZE
Specify the size of each dump file in units of bytes.
  • To perform schema level export :
[oracle@machine1 lab04]$ . oraenv
ORACLE_SID = [lab04] ? lab04
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@machine1 lab04]$ expdp dumpfile=TESTUSER4_SCH.dmp directory=TESTUSER4 logfile=TESTUSER4_SCH.log SCHEMAS=TESTUSER4 EXCLUDE=STATISTICS
Export: Release 12.2.0.1.0 - Production on Mon Nov 2 15:34:49 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  /******** AS SYSDBA dumpfile=TESTUSER4_SCH.dmp directory=TESTUSER4 logfile=TESTUSER4_SCH.log SCHEMAS=TESTUSER4 EXCLUDE=STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported "TESTUSER4"."OBJECTS"                       1.964 MB   19734 rows
. . exported "TESTUSER4"."DRP"                           429.5 KB    1521 rows
. . exported "TESTUSER4"."ROLES"                         401.0 KB    1521 rows
. . exported "TESTUSER4"."TEST16OCT20"                   15.67 KB      22 rows
. . exported "TESTUSER4"."TEST15OCT20"                   11.60 KB      10 rows
. . exported "TESTUSER4"."USERS"                         11.35 KB      99 rows
. . exported "TESTUSER4"."TABLE_TEST"                    5.812 KB      24 rows
. . exported "TESTUSER4"."TABLE_11"                      5.593 KB       6 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  /oradata/expdp/lab04/TESTUSER4_SCH.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Nov 2 15:35:52 2020 elapsed 0 00:00:46


To Perform Data Pump Import

  • To perform import in the target database, follow all those steps of directory creation as we did for source database.

Here is the sample output for the same :

[oracle@machine1 ~]$ mkdir -p /oradata/impdp/lab01
[oracle@machine1 ~]$ ls -lrt /oradata/impdp/lab01
total 0

CREATE OR REPLACE DIRECTORY TESTUSER4 AS '/oradata/impdp/lab01';
Directory created.

GRANT READ, WRITE ON DIRECTORY TESTUSER4 TO SYSTEM;
Grant succeeded.
  • If the target database is in the same location as the source database, copy the specified files to the import directory. If it’s in a different server, copy the dump files to the specified import directory in the target server. We followed here source and target are in the same server.
-- Source DB (LAB04)
[oracle@machine1 lab04]$ ls -lrt
total 3488
-rw-r-----. 1 oracle oinstall 3563520 Nov  2 15:35 TESTUSER4_SCH.dmp
-rw-r--r--. 1 oracle oinstall    1800 Nov  2 15:35 TESTUSER4_SCH.log
[oracle@machine1 lab04]$ cp TESTUSER4_SCH.dmp /oradata/impdp/lab01

=====================

-- Target DB (LAB01)
[oracle@machine1 lab01]$ pwd
/oradata/impdp/lab01
[oracle@machine1 lab01]$ ls -lrt
-rw-r-----. 1 oracle oinstall 3563520 Nov  3 12:28 TESTUSER4_SCH.dmp

In case we want to know all the options available with impdp utility, we can use the below command to see the available parameters and their utilities :

  • impdp help=y
[oracle@machine1 lab01]$ impdp help=y
Import: Release 12.2.0.1.0 - Production on Tue Nov 3 13:41:44 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
The Data Pump Import utility provides a mechanism for transferring data objects
between Oracle databases. The utility is invoked with the following command:
     Example: impdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp
---- Trimmed----
PARALLEL
Change the number of active workers for current job.
PARFILE
Specify parameter file.
REMAP_DATAFILE
Redefine data file references in all DDL statements.
REMAP_SCHEMA
Objects from one schema are loaded into another schema.
  • Just to clear the concept here, we took export as SYS user and doing import here as SYSTEM user.

NOTE: Set the environment first and then proceed for import, make sure after set environment you are in the right target database where you have to import else it may start import in the wrong database and then fail.

[oracle@machine1 lab01]$ . oraenv
ORACLE_SID = [lab01] ? lab01
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@machine1 lab01]$ impdp system/****** DIRECTORY=TESTUSER4 DUMPFILE=TESTUSER4_SCH.dmp LOGFILE=TESTUSER4_03112020.log SCHEMAS=TESTUSER4
Import: Release 12.2.0.1.0 - Production on Tue Nov 3 13:35:30 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/******** DIRECTORY=TESTUSER4 DUMPFILE=TESTUSER4_SCH.dmp LOGFILE=TESTUSER4_03112020.log SCHEMAS=TESTUSER4
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "TESTUSER4"."OBJECTS"                       1.964 MB   19734 rows
. . imported "TESTUSER4"."DRP"                           429.5 KB    1521 rows
. . imported "TESTUSER4"."ROLES"                         401.0 KB    1521 rows
. . imported "TESTUSER4"."TEST16OCT20"                   15.67 KB      22 rows
. . imported "TESTUSER4"."TEST15OCT20"                   11.60 KB      10 rows
. . imported "TESTUSER4"."USERS"                         11.35 KB      99 rows
. . imported "TESTUSER4"."TABLE_TEST"                    5.812 KB      24 rows
. . imported "TESTUSER4"."TABLE_11"                      5.593 KB       6 rows
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully completed at Tue Nov 3 13:35:46 2020 elapsed 0 00:00:12
  • Validate the import log and object count should be the same as before export.
  • Check all objects and their status after import. If any invalid object please recompile it.

Click here for How to validate invalid objects in Oracle

Click here for How to check the object count of the schema.

This document is only for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.


Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the latest update. Click here to know more about our pursuit.



Loading

How useful was this post?

Click on a star to rate it!

Average rating / 5. Vote count:

No votes so far! Be the first to rate this post.

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?

Sruti Banerjee

Hello and welcome to DBsGuru, I am Sruti Banerjee, having experience of 3 years as an Oracle DBA, I have good interpersonal skills that can help in attaining the desired level of team coordination, self-starter & quick learner. I am having rich experience in Oracle database management, Backup and Recovery, Security, Installation, Up-gradation, Patching, Migration, Cloning of databases, Dataguard (Physical), and a wide range of other database administration activities.Thanks for the visits!Share Learn Grow!

2 thoughts on “ Schema Refresh Using EXPDP-IMPDP command”

Comments are closed.

Related Articles