Simple Linux shell script for oracle export or expdp backup from cron job

This shell script is for expdp backup of Oracle database, it will take export ( data pump ) backup of "scott" user every night at 1:00AM and it will remove the backup older than 3 days.

1) save the script as below (with oracle user)

/app/scripts/expdb_backup.sh

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

#!/bin/bash
export ORACLE_BASE=/userdata/app/ora11gR2; #export ORACLE_BASE
export ORACLE_HOME=$ORACLE_BASE/prduct/11.2.0.4/db_1; #export ORACLE_HOME
export ORACLE_SID=orcl; #export ORACLE_SID
export EXPORT_FOLDER=/userdata/app/dump;
DATE=$(date +"%m%d%y") ;
$ORACLE_HOME/bin/expdp system/system schemas=scott directory=DIR_DUMP1 dumpfile=$DATE-EXPDP_CRON.DMP logfile=$DATE-EXPDP_CRON_LOG.log ;
tar cjf $EXPORT_FOLDER/$DATE-EXPDP_CRON_TAR.tar.bz2 $EXPORT_FOLDER/$DATE-EXPDP_CRON.DMP $EXPORT_FOLDER/$DATE-EXPDP_CRON_LOG.log ;
rm $EXPORT_FOLDER/$DATE-EXPDP_CRON.DMP $EXPORT_FOLDER/$DATE-EXPDP_CRON_LOG.log ;
find /userdata/app/dump/ -name *EXPDP_CRON_TAR.tar.bz2 -mtime +3 -delete;

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

Please change ORACLE_BASE,ORACLE_HOME,ORACLE_SID and EXPORT_FOLDER (directory location, where backup will store) values as per your environment and change or add schemas name as yours.

2) Create a cron job log location as below (with oracle user)

/app/logs/expdb_backup_logs.log



3) Adding to cron job-

oracle$ crontab -e

then add below line with vi editor commands

0 1 * * * /app/scripts/expdb_backup.sh >> /app/logs/expdb_backup_logs.log 2>&1


[ Note- for testing purpose, you can schedule it for every 5 minutes like below

*/5 * * * * /app/scripts/expdb_backup.sh >> /app/logs/expdb_backup_logs.log 2>&1

]

4) Make the script executable

oracle$ chmod +x /app/scripts/expdb_backup.sh

5) check the cron job logs for details

oracle$ cat /app/logs/expdb_backup_logs.log







===========Done====cheers=============