Full Database Refresh Using EXPDP-IMPDP Datapump command

January 26, 2021
()

Step by Step guide for Full Database 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 full database refresh activity using Datapump:

PARAMETERSSOURCETARGET
DATABASELAB02LAB03
SERVERMACHINE1MACHINE1

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 the database(tablespaces) before export and prior import, check whether the tablespaces exist or not in the target database, required space should be available for import in the target database.
  • If required we can create the tablespace in the target database in case the target database file system structures/ASM DISKS group are different from the source database or use the parameter REMAP_TABLESPACE  if we want to import data in a different tablespace other than the source database’s tablespace.
  • Note down the schema objects and their status and count prior to export and post-import.

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.


To Perform Data Pump Export( exppdp )

Prerequisite: Create a minimum of one directory in the database. Click here for step on how to create a directory, follow the section Prerequisite.


To perform Full Database export :

Click here to see the sample output of expdp.

[oracle@machine1 lab02]$ expdp dumpfile=LAB02_FULL_%U.dmp directory=LAB02FULL logfile=LAB02_FULL.log full=y parallel=2

Export: Release 12.1.0.2.0 - Production on Tue Jan 12 10:54:18 2021

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYS"."SYS_EXPORT_FULL_01":  /******** AS SYSDBA dumpfile=LAB02_FULL_%U.dmp directory=LAB02FULL logfile=LAB02_FULL.log full=y parallel=2
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
-----------------------------------------------------
--------------------TRIMMED DATA---------------------
-----------------------------------------------------
Processing object type DATABASE_EXPORT/AUDIT_UNIFIED/AUDIT_POLICY_ENABLE
Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER
Master table "SYS"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_FULL_01 is:
  /oradata/expdp/lab02/LAB02_FULL_01.dmp
  /oradata/expdp/lab02/LAB02_FULL_02.dmp
Job "SYS"."SYS_EXPORT_FULL_01" successfully completed at Tue Jan 12 10:59:39 2021 elapsed 0 00:05:04


Parameters used in the above EXPDP Command :

  • User Login: The first parameter is a user login. Here we need to provide the login information of that user using which we want to perform the export. So here in my case, I have performed the export as a sys user.
  • Directory: Using the DIRECTORY parameter we can specify the default location to which Export can write the dump file set and the log file. In this case, the directory named LAB02FULL has been created in the path: /oradata/expdp/lab02 .
  • Dumpfile : The dump file is made up of one or more disk files that contain table data, database objects metadata, and control information. In addition to that, these files are written in binary format, and dump files can be imported only by using the data pump impdp import utility. Since they are written in binary format by the server using expdp export utility thus they should not have tampered. Changing information in these files might compromise the data consistency which may cause an error while importing. 
  • Logfile : The log file parameter expdp export utility will generate a human-readable log file for us which can be helpful in tracking the status of the export which we are performing.
  • Full: The parameter FULL indicates that we want to perform a full database export. This parameter can have YES or NO values. If we set this parameter to YES it means expdp utility will export all the data and metadata of the database.
  • Parallel: The parameter PARALLEL specifies the maximum number of processes that are actually executing the export or import job.


To Perform Data Pump Import ( impdp )

Prerequisite: Create a minimum of one directory in the database. Click here for the steps on how to create a directory, follow the section Prerequisite.


To perform Full Database import:

Click here to see the sample output.

impdp dumpfile=LAB02_FULL_%U.dmp directory=LAB02FULL logfile=LAB02_FULL.log full=y parallel=2

Import: Release 12.1.0.2.0 - Production on Tue Jan 12 12:44:40 2021

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  /******** AS SYSDBA dumpfile=LAB02_FULL_%U.dmp directory=LAB02FULL logfile=LAB02_FULL.log full=y parallel=2
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/TABLESPACE
-----------------------------------------------------
--------------------TRIMMED DATA---------------------
-----------------------------------------------------
Processing object type DATABASE_EXPORT/AUDIT_UNIFIED/AUDIT_POLICY_ENABLE
Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER
Job "SYS"."SYS_IMPORT_FULL_01" completed with 33 error(s) at Sat Jan 23 17:21:44 2021 elapsed 0 00:00:46
  • 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 most recent 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?

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!

4 Comments

    1. Thanks Sadanand to review it! REMAP option depends on your requirement in target database. We have mention situation in section prerequisite.

      Thanks.
      Team DBsGuru.

  • I am going to export and import of SIEBEL schema from source to target instance. My commands are as follows :

    nohup expdp system/@PDB1 directory=EXP29MAR22 SCHEMAS=SIEBEL DUMPFILE=UAT_SIEBEL_%U.dmp LOGFILE=EXP_UAT_SIEBEL.log filesize=20G parallel=16 &

    nohup impdp system/@PDB1 directory=IMP02APR22 SCHEMAS=SIEBEL DUMPFILE=UAT_SIEBEL_%U.dmp LOGFILE=IMP_UAT_SIEBEL.log parallel=16 &

    NOTE – SOURCE & TARGET SCHEMAS AND TBS ARE ‘SIEBEL’ , so i didnt include any REMAP_SCHEMA OR REMAP_TABLESPACE commands in my impdp but still i get impdp errors like

    ORA-39111: Dependent object type OBJECT_GRANT:”SIEBEL”.”SELECT” skipped, base object type VIEW:”SIEBEL”.”********” already exists
    ORA-31684: Object type PACKAGE_BODY:”SIEBEL”.”*********” already exists
    Job “SYSTEM”.”SYS_IMPORT_SCHEMA_10″ completed with 5924 error(s) at Sun Apr 2 06:32:45 2023 elapsed 0 00:08:54

    HOW TO FIX THESE ERRORS DURING IMPDP, whats wrong my with impdp command? Are these errors ignorable ?

Leave a Reply

Your email address will not be published. Required fields are marked *