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

3

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

Directory

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’;

OracleDir

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

expdp-output expdp-output-1

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.

expdp-output-2

                                   

                                      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

Server2-createuser

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’;

Server2-Dir

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

Server2-impdp

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

2013 in review

Posted: January 2, 2014 in Java

The WordPress.com stats helper monkeys prepared a 2013 annual report for this blog.

Here’s an excerpt:

The concert hall at the Sydney Opera House holds 2,700 people. This blog was viewed about 8,500 times in 2013. If it were a concert at Sydney Opera House, it would take about 3 sold-out performances for that many people to see it.

Click here to see the complete report.

Creating Sliding Popup like facebook/gtalk: jQuery Part


Creating Sliding Popup like facebook/gtalk: CSS Part


Creating Sliding Popup like facebook/gtalk: HTML Part


Collect the Collection in Spring: Set, Map & Properties


Collect the Collection in Spring: List 


Bean Identifiers: Name, Aliases, InnerBeans & Idref 


Autowiring of beans in Spring 


Bean Factory and its Hierarchy of Interfaces