Datapump (expdp and impdp)

Oracle Datapump is the latest version of original exp and imp.It is much more faster and flexible.it provides very high-speed movement of data and metadata from one database to another.
In order to use Data Pump, DBA must create a directory  object and grant privileges to the user on that directory object. If a directory object does not exists  default directory object data_pump_dir is provided.
Create a OS directory and provide read,write, execute permission to this directory
mkdir -p /backup/datapump
chmod -R 755 /backup/datapump
sqlplus sys as sysdba create or replace directory datapump as ‘/u02/datapump’; grant read,write on directory datapump to scott;
Example-of-Table-export-and-import
expdp scott/***** tables=scott.SALES,scott.DEPT directory=datapump dumpfile=export.dmp logfile=export.log impdp scott/***** tables=SALES,DEPT directory=DATAPUMP dumpfile=export.dmp logfile=impdp.log
Example of schema export and import expdp scott/***** schemas=scott directory=datapump  dumpfile=scott.dmp logfile=scott.log expdp scott/***** schemas=scott directory=datapump  dumpfile=scott.dmp logfile=scott.log
Example of full database backup.
Grant this privleges to scott user to take full database export and import. grant EXP_FULL_DATABASE to scott; grant IMP_FULL_DATABASE to scott expdp scott/***** full=y directory=datapump dumpfile=fullbackup.dmp logfile=fullbackup.log
impdp scott/***** full=y directory=datapump dumpfile=fullbackup.dmp logfile=fullbackup.log EXPDP INCLUDE / EXCLUDE expdp scott/***** schemas=SCOTT include=TABLE:”IN (‘SALES’,’DEPT’)” directory=dumpfile dumpfile=SCOTT.dmp logfile=expdp.log
expdp scott/***** schemas=SCOTT exclude=TABLE:”= ‘SALES'” directory=dumpfile dumpfile=SCOTT.dmp logfile=expdp.log
Import the scott’ objects on sales. impdp scott/***** remap_schemas=SCOTT:SALES directory=datapump dumpfile=scott.dmp logfile=scott.log
Import the scott’ objects on sales. impdp scott/***** remap_schemas=SCOTT:SALES directory=datapump dumpfile=scott.dmp logfile=scott.log
Import the scott’ objects on sales in different tablespace in UAT DB.
impdp scott/***** remap_schemas=SCOTT:SALES remap_tablespace=PROD_TBLSPC:UAT_TBLSPC directory=datapump dumpfile=scott.dmp logfile=scott.log
Refresh the uat database from production tables impdp scott/***** table_exists_action=replace directory=datapump dumpfile=scott.dmp logfile=scott.log
impdp scott/***** table_exists_action=skip directory=datapump dumpfile=scott.dmp logfile=scott.log
Network Exports/Imports (NETWORK_LINK) The database link can be used as the source for the export import,foth this use a parameter NETWORK_LINK
CONN / AS SYSDBA GRANT CREATE DATABASE LINK TO SYSTEM;
CONN system/manager CREATE DATABASE LINK remote_system
CONNECT TO system IDENTIFIED BY manager USING ‘PROD’;
Fot export,the NETWORK_LINK parameter pointing to the source servver.All the objects are exported from source database but stored in the local server. For this both user (remote and local server) needs EXP_FULL_DATABASE privleges.
expdp system/manager tables=SCOTT.SALES network_link=REMOTE_SYSTEM directory=local_dumpfile dumpfile=SALES.dmp logfile=export.log
here export dump file will be created on local server instead of remote server. In case of Import by NETWORK_LINK,import will be done directoly on target while keepng the dumpfile on local server.Both users local and remote needs IMP_FULL_DATABASE role granted to them.There is no need of dumpfile parameter
impdp system/manager tables=SCOTT.EMP network_link=REMOTE_SCOTT directory=local_dumpfile logfile=impdp.log remap_schema=SCOTT:SALES
Datapump in parallel mode Datapump can be speed up by taking the backup in parallel mode.
expdp system/manager schemas=SCOTT directory=dumpfile parallel=4 dumpfile=SCOTT%U.dmp logfile=expdpSCOTT.log
impdp system/manager schemas=SCOTT directory=dumpfile parallel=4 dumpfile=SCOTT%U.dmp logfile=impdpSCOTT.log
Consistent Flashback backup
expdp system/manager schemas=SCOTT flashback_time=systimestamp directory=dumpfile parallel=4 dumpfile=SCOTT%U.dmp logfile=expdpSCOTT.log
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s