What is logical backup or datapump in oracle database? How to perform logical backup(Export and Import) or datapump?

This document describes what is logical backup in oracle database and how to do a logical backup. 

What is logical backup or Data Pump in oracle database? 

We are taking backup of logical objects(Schemas, Tables, Indexes, Views, Procedures, Tablespaces and Full database). That is the reason we are calling it as logical backup or Data Pump. We are using oracle provided utilities EXPDP and IMPDP which are command line utilities to take logical backups in the database. 

The Data Pump utilities EXPDP and IMPDP invoke Data Pump export and Data Pump import respectively. These utilities uses the procedures in DBMS_DATAPUMP pl/sql package to perform export/import.

We require one logical directory to use data pump(expdp/impdp) export or import.

Please click Here to find how to create a logical directory in the database. 

Full Database Export and Import:

Use below commands to export and import full database. We can utilize parallel option to improve data pump job and we can split the dump files instead of single dump file. It is easier to copy the dump files to  other servers as well. 

expdp "'/ as sysdba'" full=Y directory=EXPDP parallel=5 dumpfile=EXPDP_DBNAME_dump_%U.dmp job_name=EXPDP_DBNAME logfile=EXPDP_DBNAME.log

impdp "'/ as sysdba'" full=Y directory=IMPDP parallel=5 dumpfile=EXPDP_DBNAME_dump_%U.dmp job_name=IMPDP_DBNAME logfile=IMPDP_DBNAME.log

Schema Export and Import:

Use below command to take export or import of a single schema in the database.

expdp "'/ as sysdba'" schemas=INVOICE directory=EXPDP parallel=5 dumpfile=EXPDP_DBNAME_INVOICE_dump_%U.dmp job_name=EXPDP_DBNAME logfile=expdp_invoice.log

impdp "'/ as sysdba'" schemas=INVOICE directory=IMPDP parallel=5 dumpfile=EXPDP_DBNAME_INVOICE_dump_%U.dmp job_name=IMPDP_DBNAME logfile=impdp_invoice.log

 

Tablespace Export and Import:

Use below command to export or import tablespaces in the database.

expdp "'/ as sysdba'" tablespaces=hr_data directory=EXPDP parallel=5 dumpfile=EXPDP_TABLESPACE_dump_%U.dmp job_name=EXPDP_DBNAME logfile=expdp_tablespace.log

impdp "'/ as sysdba'" tablespaces=hr_data directory=IMPDP parallel=5 dumpfile=EXPDP_TABLESPACE_dump_%U.dmp job_name=IMPDP_DBNAME logfile=impdp_tablespace.log

 

Table Export and Import:

Use below command to export or import table in the database.

expdp "'/ as sysdba'" tables=SCOTT,HR directory=EXPDP parallel=5 dumpfile=EXPDP_TABLE_dump_%U.dmp job_name=EXPDP_DBNAME logfile=expdp_table.log

impdp "'/ as sysdba'" tables=SCOTT,HR directory=IMPDP parallel=5 dumpfile=EXPDP_TABLE_dump_%U.dmp job_name=IMPDP_DBNAME logfile=impdp_table.log

 

How to verify existing Data  Pump jobs?

We can use dba_datapump_jobs or user_datapump_jobs view to verify existing data pump jobs in the database.

 

 

 

Thanks for going through the post.......