Oracle Data Pump

Oracle Data Pump

Oracle data pump:

Oracle data pump is a new feature available from oracle 10g onwards. expdp and impdp tools are for logical backups.

To configure oracle data pump need to set the following parameter in the pfile.

Streams_pool_size = 10M;

Configuration of Oracle data pump:

$ cd $ORACLE_HOME/dbs

$ vi initSID.ora

Streams_pool_size = 10m

:wq

$ pwd

$ cd /Oracle/logicalbackup

$mkdir dpump

$ sqlplus ‘/as sysdba’

Sql>startup

Sql>select * from dba_directories;

Sql>create directory dpump as ‘/Oracle/logicalbackup/dpump’;

Sql>select * from dba_directories;

Sql>grant read,write on directory dpump to user1;

Sql>exit

How to take full database export by using expdp tool:

$ expdp directory = dpump dumpfile = dbfull.dmp full = y

Username: system/manager as sysdba

$pwd

$ cd dpump

$ ls

How to export user level tables by using expdp tool:

$ expdp directory = dpump dumpfile = user1.dmp

Username: user1/user1

$ cd dpump

$ ls

How to export individual tables by using expdp tool:

$ expdp directory = dpump dumpfile = user1emp.dmp tables = emp

Username: user1/user1

How to import full database by using impdp tool:

$ sqlplus ‘/as sysdba’

Sql> drop user user1 cascade;

$impdp directory = dpump dumpfile = dbfull.dmp full = y

Username: system/manager as sysdba

How to import tables from user level backup:

$ sqlplus ‘/as sysdba’

Sql>select * from tab;

Sql>select * from emp;

Sql>drop table emp;

Sql>purge recyclebin;

Sql>select * from tab;

Sql>exit

$ impdp directory = dpump dumpfile = user1.dmp tables = emp

Username: user1/user1

How to drop dump directory from database level:

$ sqlplus ‘/as sysdba’

Sql>grant read,write on directory dpump to user1;

Sql>drop directory dpump;

Sql>select * from dba_directories;