Introduction
Data Pump Export (invoked with the expdp command) is a utility as of Oracle Database 10g and compatible with later version as well. Its a utility for unloading data and metadata into a set of operating system files called a dump file set. The dump file set can be imported only by the Data Pump Import utility (invoked with the impdp command). The dump file set can be imported on the same system or it can be moved to another system and loaded there. The dump file set is made up of one or more
disk files that contain table data, database object metadata, and control information. The files are written in a proprietary, binary format. During an import operation, the Data Pump Import utility uses these files to locate each database object in the dump file set. In addition to basic import and export functionality data pump provides a PL/SQL API and support for external tables as well which is out of scope for this discussion.
NOTE: Because the dump files are written by the server, rather than by the client, the data base administrator (DBA) must create directory objects.
Preparing for Backup and Restore
Lets assume we have a schema named DEVINTEGWALLET001 containing few tables, sequences and procedures. The requirement is to backup or export the entire schema objects into a dump file and later import it to another server.
For simplicity I have created a schema with following configurations
Schema: DEVINTEGWALLET001
Username: DEVINTEGWALLET001
Password: DEVINTEGWALLET002
STEP 1:
First you need to grant “create any directory” permission to the schema user, in this example, DEVINTEGWALLET001.
Below are the set of commands you need to issue from shell to grant the permission:
C:\> sqlplus /nolog
SQL> connect / as sysdba
SQL> grant create any directory to DEVINTEGWALLET001;
SQL> grant EXP_FULL_DATABASE to DEVINTEGWALLET001;
SQL> grant IMP_FULL_DATABASE to DEVINTEGWALLET001;
SQL> exit
STEP 2:
Create a directory in the disk where you want the dump files to be created with exported data and meta information.
For this example I have created a directory db_backup in C:\ of my system
Then create a directory in oracle to map to the physical directory on the disk using below command
C:\> sqlplus DEVINTEGWALLET001/DEVINTEGWALLET002
SQL> create or replace directory mybackup_dir as ‘C:\db_backup’;
STEP 3:
Issue the command expdp to backup or export all the schema objects to a dump file in the location created in above step
C:\> expdp DEVINTEGWALLET001/DEVINTEGWALLET002 schemas=DEVINTEGWALLET001 directory=mybackup_dir dumpfile=DEVINTEGWALLET001_BKP.dmp logfile=DEVINTEGWALLET001_LOG.log
NB: In the above command DEVINTEGWALLET001 is username, DEVINTEGWALLET002 is password, schemas=DEVINTEGWALLET001 refers to the schema we want to export, mybackup_dir referes to the directory created in step 2, dumpfile & logfile are the name of .DMP & .LOG files respectively you want to be created as output of the expdp command
As a result of the above command execution two files will be created in C:\db_backup directory, one named DEVINTEGWALLET001_BKP.DMP containing all schema objects,
data, meta-data related to DEVINTEGWALLET001 schema. Another file is DEVINTEGWALLET001_LOG.LOG which contains all logs subject to execution of the above commands.
DB EXPORT COMPLETED
Lets now Import or Restore the above data on another system/server, here i’m connecting to a server (192.168.0.180) through RDP to demonstrate the export to external server
STEP 4:
Copy the file DEVINTEGWALLET001_BKP.DMP & DEVINTEGWALLET001_LOG.LOG from Exported Machine (localhost) to machine on which you want to import (192.168.0.180 in my case), lets create a directory C:\my_backup on remote machine where we will copy the dump files
NB: Same steps need to be followed for import in local machine as well
STEP 5:
Create a schema and user. Grant permissions to import all data from first machine to this machine.
C:> sqlplus system
Enter password:
SQL> create user DEVINTEGWALLET005 identified by DEVINTEGWALLET006;
SQL> grant create any directory to DEVINTEGWALLET005;
SQL> grant all privileges to DEVINTEGWALLET005;
SQL> exit
NB: As you may notice I have issued the command grant all privileges in this step which is not recommended on production db servers as this will make your data vulnerable to external systems and users. Just as in Export scenario I have granted limited privileges for instance EXP_FULL_DATABASE , create any directory is the standard procedure, but just for demonstration I am granting all privileges. Its always recommended to consult your DBA for permission related activities.
STEP 6:
As we have already created and copied dumpfile to C:\my_backup directory in STEP 1, we just need to map that physical directory on disk to logical directory in Oracle by issuing below commands:
C:\> sqlplus DEVINTEGWALLET005/DEVINTEGWALLET006
SQL> create or replace directory mybackup_dir as ‘C:\db_backup’;
STEP 7:
Issue the impdp command to import or restore all data from the dumpfile DEVINTEGWALLET001_BKP.DMP to schema DEVINTEGWALLET005
C:\> impdp DEVINTEGWALLET005/DEVINTEGWALLET006 schemas=DEVINTEGWALLET001 directory=mybackup_dir dumpfile=DEVINTEGWALLET001_BKP.dmp logfile=DEVINTEGWALLET001_LOG.log
REMAP_SCHEMA=DEVINTEGWALLET001:DEVINTEGWALLET005
NB:
DEVINTEGWALLET005 is the username,
DEVINTEGWALLET006 is the password,
DEVINTEGWALLET001 is the original db schema we have exported
mybackup_dir is the logical directory on server2 where we are importing
dumpfile & logfile refers to the files copied from first server as a result of export
REMAP_SCHEMA=OLD SCHEMA<EXPORTED>:NEW SCHEMA<IMPORTED> is used to remap the schema name as during export we referred to schema DEVINTEGWALLET001 (local machine) and here on this server (192.168.0.180) we are referring to another schema DEVINTEGWALLET005 for import
srkplanet 