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:
PARAMETERS | SOURCE | TARGET |
DATABASE | LAB02 | LAB03 |
SERVER | MACHINE1 | MACHINE1 |
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.
It is require to remap schema and remap tablespace
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 ?
Please check in target, schema along with objects are exist so that its error.