Export Backup Automation in Oracle Database using shell script

Hello, friends in this article we are going to learn how to schedule database export backup automatically. Yes, it is possible to export backup automation with a crontab scheduler.

Table of Contents

How to schedule Export Backup

Using shell scripting, we can schedule the export backup as per our convenient time. Using the following steps we can schedule expdp backup.

Step 1: Create Backup Location

First, we need a backup location where we create the export backup.

A. Create a directory at OS level.

OS level directory called physical directory.

$mkdir /u01/EXPORT_BKP/

B. Create directory at the Database level.

Database level directory called logical directory.

$sqlplus / as sysdba

SQL> create directory EXP_DUMP as '/u01/EXPORT_BKP/ ';

Check directory information at the Database level.

SQL> col OWNER for a20
SQL> col DIRECTORY_NAME for a30
SQL> col DIRECTORY_PATH for a65
SQL> set lin 300 pagesize 300
SQL> select * from dba_directories;

List of directories:

Step 2: Create Schema

To execute the export backup we need a dedicated schema to run the database backup. We also run the backup as an SYS user but sometimes clients require a dedicated schema.

SQL> create user master
         identified by master
        default tablespace users
        quota unlimited on users
        account unlock;


user created.

Step 3: Grant Permissions

In step 2 we created a schema with the name of the master, so this is the schemas that we are going to use for taking database export backup. So, we need to grant the required permissions to the master user. Use the below commands to grant permissions.

SQL> grant read, write on directory EXP_DUMP to master;

Grant succeeded.

SQL> grant exp_full_database, imp_full_database to master

Grant succeeded.

Step 4: Create a Backup shell script

It's time to prepare the shell script and grant 775 permission to shell script.

$vi /u01/EXPORT_BKP/export_bkp.sh

Add below code inside export_bkp.sh

export ORACLE_SID=digital
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1/
export PATH=/usr/sbin:$PATH
export PATH=$ORACLE_HOME/bin:$PATH
expdp master/master full=y directory=exp_dump dumpfile=full_bkp.dmp logfile=full_bkp.log

Set 775 permission for shell script

$chmod 775 /u01/EXPORT_BKP/export_bkp.sh

Step 5: Schedule export backup in crontab.

This is the final step, here we are going to make it automatic with the help of the crontab scheduler.

Open crontab and add the below entry inside.

$crontab -e

30 19 * * * /u01/EXPORT_BKP/export_bkp.sh

As per the above scheduler, we scheduled the export backup at 7:30 PM. So, you can change the time as per your requirement. Now Backup will run automatically. This is the way to make Export Backup Automation.

Advance Export Backup Script

There is an advance script for export backup, using this script we can take export backup with the date.

export ORACLE_SID=digital
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1/
export PATH=/usr/sbin:$PATH
export PATH=$ORACLE_HOME/bin:$PATH
mkdir -p /u01/EXPORT_BKP/`date +%d-%m-%y`
expdp master/master full=y directory=exp_dump dumpfile=full_bkp_`date +%d-%m-%y`.dmp logfile=full_bkp_`date +%d-%m-%y`.log
mv /u01/EXPORT_BKP/*`date +%d-%m-%y`.dmp /u01/EXPORT_BKP/`date +%d-%m-%y`/
mv /u01/EXPORT_BKP/*`date +%d-%m-%y`.log /u01/EXPORT_BKP/`date +%d-%m-%y`/

I hope you really enjoyed this article if yes please write in the comment box.

You Must Read:

Learn more about DataPump

Export Backup Automation in Oracle On Linux