Oracle full database backup with Data Pump Export Utility

Introduction to Oracle Data Pump Utility:
This utility is used to backup database into operating system files, called as dump file. This file is only imported or accessible by import data pump utility.

Roles required to perform Data Pump Export-Import are EXP_FULL_DATABASE & IMP_FULL_DATABASE, Without these role we can’t do backup & restore database instead of schema level Export-Import.

 

Invoking Data Pump Utility:
We can invoke data pump utility by expdp command on terminal prompt.

Note:
Here we considered as oracle environment variable is already being set:
export ORACLE_SID=orcl
export ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_1/
export PATH=/home/oracle/app/oracle/product/11.2.0/dbhome_1/bin/

[oracle@oracle ~]$ expdp

Export: Release 11.2.0.1.0 – Production on Wed Feb 5 11:04:53 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username: <User_name>
Password: <Password>

Invoking Data Pump Export help:
[oracle@oracle ~]$ expdp help=y
Data Pump Export Modes:

  • Full Export Mode: The entire database has been back up in this mode.
  • Schema Mode: We can backup multiple schema’s & this is default export mode.

Note: You can export only your schema default if you don’t granted as EXP_FULL_DATABASE role.

  • Table Mode: We can backup multiple table’s, partitions, and their dependent objects in this mode.
  • Tablespace Mode: We can backup only the tables contains in specified set of tablespaces. Table will be backup with the dependent objects.

Note: In this mode both object metadata and data is backup.

  • Transportable Tablespace Mode: we can only backup the metadata for the tables (& their dependent objects) within a specified set of tablespaces.

Consider following hands-on on Data Pump Export with Full database backup example.

To backup database with the help of data pump we need to create database directory first, this directory should be valid on same server.

Log on to SQL prompt with sys user as sysdba & issue the following query:
SQL> create directory data_pump_bkup as ‘/home/oracle/app/data_pump_bkup’;
Directory created.

 

Grant read, write privileges to “data_pump_bkup” directory with the help of following query:
SQL> grant read, write on directory data_pump_bkup to system;
Grant succeeded.

Grant exp_full_database to system user in order to take full database backup:
SQL> grant exp_full_database to system;
Grant succeeded.

 

Ensure your changes by following query:
SQL> select directory_path from dba_directories;
DIRECTORY_PATH
——————————————————————–

/home/oracle/app/data_pump_bkup

 

To backup database in full export mode issue to following data pump command:

[oracle@oracle ~]$ expdp system/manager directory=data_pump_bkup dumpfile=full_db_export.dmp logfile=export.log full=y

Above data pump export command will use system user for exporting full database in full_db_export.dmp dump file & export log will be written on export.log text file situated at data_pump_bkup directory.

Export will be completed with following message:
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is: /home/oracle/app/data_pump_bkup/full_db_export.dmp
Job “SYSTEM”.”SYS_EXPORT_SCHEMA_01″ successfully completed at 11:52:56
—x—

Like wise you can backup your database with the help of Data Pump Export utility.

************************************************************************
Note: Please don’t hesitate to revert in case of any query OR feedback.

Thanking you.
Have a easy life ahead.

One thought on “Oracle full database backup with Data Pump Export Utility”

Leave a Reply