Example Data Pump Export script for Linux/Solaris

Data Pump Export script for Linux/Solaris


Data Pump is a command-line utility for importing and exporting objects like user tables and pl/sql source code from a Oracle database. It’s new since Oracle 10g, and it’s a better alternative for the “old” exp/imp utilities. However, do not use Data Pump to replace a full physical database backup with RMAN. Complete point-in-time recovery is not possible with Data Pump. Therefore, it should only be used for data migrations or in conjunction with RMAN.
In this blog post, I will show you how you can create a script to execute and schedule a full Data Pump export on Linux.
First, we need to define a directory object. This is an alias for a file system folder that we will need in the Data Pump script. Execute with user SYS as SYSDBA:

create directory expdir as '/expdir';
select * from dba_directories;

Note: make sure user “oracle” has write permissions on the file system folder used for the Data Pump export files (in this case: /expdir).
Next, we will create a database user that will be used for the export. This user will at least need the “EXP_FULL_DATABASE” role, CREATE SESSION and CREATE TABLE rights, and read/write access on the directory object we previously created.

Note: do NOT use “SYS as SYSDBA” for the export, this user should only be used when requested by Oracle support!

CREATE USER EXPORT
IDENTIFIED BY password
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
PROFILE "UNLIMITED PASSWORD EXPIRATION"
ACCOUNT UNLOCK;

GRANT CREATE SESSION TO EXPORT;
GRANT CREATE TABLE TO EXPORT;
ALTER USER EXPORT QUOTA UNLIMITED ON USERS;
GRANT EXP_FULL_DATABASE TO EXPORT;
GRANT READ, WRITE ON DIRECTORY EXPDIR TO EXPORT;

Now we will create the Linux shell script:

$ vi ora_expdp_full.sh 

 #!/bin/sh

# script to make full export of Oracle db using Data Pump

STARTTIME=`date`
export ORACLE_SID=oratst
export ORACLE_HOME=`cat /etc/oratab|grep ^${ORACLE_SID}:|cut -d':' -f2`
export EXPLOG=expdp_${ORACLE_SID}.log
export EXPDIR=/expdir
export PATH=$PATH:$ORACLE_HOME/bin
DATEFORMAT=`date +%Y%m%d`
STARTTIME=`date`

# Data Pump export
expdp export/password content=ALL directory=expdir 

dumpfile=expdp_`echo $ORACLE_SID`_%U_`echo $DATEFORMAT`.dmp 

filesize=2G full=Y logfile=$EXPLOG nologfile=N parallel=2

ENDTIME=`date`
SUBJECT=`hostname -s`:$ORACLE_SID:`tail -1 $EXPDIR/$EXPLOG`
echo -e "Start time:" $STARTTIME "\nEnd time:" $ENDTIME | mail -s "$SUBJECT" dba@mydomain.com

This script will create 2GB export files and dynamically append the date to them. So, in this case, the first file will be “expdp_oratst_01_20120503.dmp”, the second one “expdp_oratst_02_20120503.dmp”, and so on. Finally, a mail will be sent to the DBA with as mail subject the last line of the export log file, and as mail body the start and end time.
Note: You need to replace the ORACLE_SID and EXPDIR variables in the script by the ones suitable for your environment.
Make sure only the owner of the script has read and write access on it:
$ chmod 700 ora_expdp_full.sh
Finally, we can schedule the script with the Linux utility cron:
$ crontab -e
Add the following lines:

# Daily logical export
00 23 * * * /home/oracle/scripts/export/ora_expdp_full.sh 1>/dev/null 2>&1