how to take full database backup using expdp in oracle 12c

Oracle data pump technology enables very high-speed movement of data and metadata from one database to another. Oracle data pump is available only on oracle database 10g release 1 and later. 

In this article we are going to discuss how to configure oracle data pump as well as how to take full database backup using data pump utility.

Steps to configure and how to take full database backup as follow.

Step 1: Create a directory or Folder

Step 2: Create user and Granting Privileges

Step 3: Perform Full Database Backup

====================================

Step 1: Create a directory or Folder

====================================

This is the first step for configurating data pump for export or import data. We create a directory where datapump backup will be stored.

# We create a physical directory. 

Directory path: E:\app\masud\virtual\DPBKP

# Login oracle database using sysdba and create directory object

sqlplus / as sysdba

SQL> create or replace directory db_back as 'E:\app\masud\virtual\DPBKP'; (db_back -- is directory name)

Directory created.

# Now check the created directory using following command

SQL> select DIRECTORY_NAME,DIRECTORY_PATH from dba_directories where directory_name='DB_BACK';

DIRECTORY_NAME DIRECTORY_PATH

---------------------------------------------------------------------

DB_BACK E:\app\masud\virtual\DPBKP

==================================

Step 2: Create user and Granting Privileges

==================================

For data pump backup purpose first we create a user (username: backup) and using this user we will take database full backup.

# Create user and grant privileges for export full database

SQL> create user backup identified by backup;

User created.

SQL> grant create session, connect to backup;

Grant succeeded.

SQL> grant resource to backup;

Grant succeeded.

SQL> grant unlimited tablespace to backup;

Grant succeeded.

# Grant read, write priviledge on db_back directory for backup user

SQL> grant read,write on directory db_back to backup;

Grant succeeded.

# Grant full database export priviledge for backup user

SQL> GRANT DATAPUMP_EXP_FULL_DATABASE TO backup;

Grant succeeded.

=================================

Step 3: Perform Database Full Backup

=================================

# Full database backup

We take full database backup using following command

C:\Users\masud>expdp backup/backup full=y directory=db_back dumpfile=Exp_TESTDB_Backup.dmp logfile=Exp_TESTDB_Backup.log

Export: Release 12.2.0.1.0 - Production on Tue Dec 22 12:41:13 2020

Copyright (c) 1982, 2020, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Starting "BACKUP"."SYS_EXPORT_FULL_01":  backup/******** full=y directory=db_back dumpfile=Exp_TESTDB_Backup.dmp logfile=Exp_TESTDB_Backup.log

Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA

Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA

Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type DATABASE_EXPORT/STATISTICS/MARKER

Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER

Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER

Processing object type DATABASE_EXPORT/TABLESPACE

Processing object type DATABASE_EXPORT/PROFILE

Processing object type DATABASE_EXPORT/SYS_USER/USER

Processing object type DATABASE_EXPORT/SCHEMA/USER

Processing object type DATABASE_EXPORT/RADM_FPTM

Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT

Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT

Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT

Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE

Processing object type DATABASE_EXPORT/SCHEMA/ON_USER_GRANT

Processing object type DATABASE_EXPORT/RESOURCE_COST

Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK

Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK

Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY

Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM

Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ

Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM

Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA

Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE

Processing object type DATABASE_EXPORT/EARLY_POST_INSTANCE_IMPCALLOUT/MARKER

Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE

Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE

Processing object type DATABASE_EXPORT/NORMAL_POST_INSTANCE_IMPCALLOUT/MARKER

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT

Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER

Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA

Processing object type DATABASE_EXPORT/AUDIT_UNIFIED/AUDIT_POLICY_ENABLE

Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER

. . exported "SYS"."KU$_USER_MAPPING_VIEW"               6.093 KB      38 rows

. . 

Master table "BACKUP"."SYS_EXPORT_FULL_01" successfully loaded/unloaded

******************************************************************************

Dump file set for BACKUP.SYS_EXPORT_FULL_01 is:

  E:\APP\MASUD\VIRTUAL\DPBKP\EXP_TESTDB_BACKUP.DMP

Job "BACKUP"."SYS_EXPORT_FULL_01" successfully completed at Tue Dec 22 12:43:23 2020 elapsed 0 00:01:53

Now we go to the directory which we created fist step to check whether backup file is created or not and same directory we found a log file which will store the detail log of backup.

Done!