How to Take schema backup using Expdp

Export, Import are the most commonly used backup and restoration technique in Oracle Database by Database Administrator. This tool is improved in every version of Oracle Database.  Before Oracle 10g exp and imp were used, which is not upgraded to expdp and impdp.

Here, I am using Expdp to take schema backup, because exp is almost obsolete now a days. In both Impdp and Expdp I will use system database user for taking and restoring backups. System user will take backup with user creation and grant scripts. So that, on the destination server it will automatically create and provide same grants.

The backup command after successful execution will make a .dmp file at defined directory on the Database Server, To restore it DBA has to move this file from source to destination and execute Impdp command.

Perquisites for Export and Import Backups:

     Database must be in open mode.
     A directory in database should have created.

Steps to take Schema Backup using Expdp:

Below are the four steps to take schema backup using expdp.

1. Set Database Environment variable

Export the sid of database using export command

[oracle@test ~]$ export ORACLE_SID=test01
[oracle@test ~]$ echo $ORACLE_SID
test01

2. Backup Command Syntax:

Support you want to take backup of schema remote_dba, Then Use this command to take backup

Syntax:

[oracle@test ~]$ expdp {user_name}/{Password} dumpfile={Dump file name} directory={Name of directory } logfile={Log file Name} schemas={Backup schema Name}

Example:

[oracle@test ~]$ expdp system/sys dumpfile=remote_dba.dmp directory=backup logfile=remote_dba_imp.log  schemas=remote_dba

Detail of parameters used in expdp command:

There are various parameters used with expdp command, We must have a knowledge about these to have a successful backup.

User Name/Password: For a full schema backup use system user name. It will include all commands to make a user and assign grants to the user at time of schema restoration at destination database.

Dumpfile: Name of the backup file created. Give any appropriate name with extension (.dmp).

Directory:  This is the path at which backups will be placed. We can use default directory made in oracle “data_pump_dir”. Data_pump_dir is physically located at $ORACLE_HOME/rdbms/log.

If you want to place Database Expdp backup at some other place then DBA has to create a new database directory. Use following to Create new database directory

a. Before creating the directory check

The physical location of directory must exist. like /opt/oracle .
Ownership of the directory to Oracle user. Use this command

[oracle@test opt]$ ls -ltrh
total 12K
drwxrwxrwx  15 oracle oinstall  12K Aug  4  2010 oracle

Here Oracle is the owner of /op/oracle folder.

b. Create Database Directory

SQL> create directory backup  as '/opt/oracle';
Directory created.

SQL> grant read, write on directory backup  to remote_dba;
Grant succeeded.

Here, I have created a new database directory for backup, Now i can use this "backup" name in the directory parameter of expdp command and my backups will store at "/opt/oracle"

Schemas:  Name of the schema for which backup is taken.  More than one name is also allowed by command separator. Only one backup file will generate for all schemas.

Logfile:  The name of log file to keep log of the backups. The path for log file will be same as given in directory path. It's recommended to give logfile name for future references of backup.

Above are minimum parameters to take a schema backup.


3. Execute backup command: Once you will execute backup command you will see this type of output on your screen. 

[oracle@test ~]$ expdp system/sys dumpfile=remote_dba.dmp directory=backup logfile=remote_dba.log  schemas=remote_dba

Export: Release 10.2.0.4.0 - Production on Thursday, 19 May, 2011 11:45:48
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 -Production 
With the Partitioning, OLAP, Data Mining and Real Application Testing option Starting       
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 38 MB
------------------------------------------------
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
exported "REMOTE_DBA"."TEST1"           11.16 MB  123342 rows
exported "REMOTE_DBA"."TEST2"                     4.671 MB   25545 rows
exported "REMOTE_DBA"."TEST3"           2.380 MB   20221 rows
 Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:  /opt/oracle/remote_dba.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 11:46:16

Here database back is done, Path of backup file is /opt/oracle/remote_dba.dmp.