Oracle Database: Schedule backups using exp

Despite I recommend taking backups using RMAN (Oracle Database: Schedule hot backups using RMAN) sometimes taking backups using EXP utility could be useful.

The main advantage of using EXP utility instead of RMAN is that the former (EXP) allows to take easy backups of specific tables and doesn't require the database to be in ARCHIVELOG mode to run.

Conversely RMAN it's faster than EXP because it doesen't require to examine every data block of the backup source, allow parallelized backups/restores, has a catalog for tracking backups, etc.

Set up EXP backups it's really simple, let's create a script which will be added to our crontab to perform EXP backups daily at 8PM.

I created a file called backup under /home/oracle with this content:

export DATE=$(date +"%m_%d_%y_%H:%M")

/u01/app/oracle/product/11.2.0/db_1/bin/exp username/password@instance_name file=/home/oracle/backup/backup_$DATE.dmp log=/home/oracle/backup/backup_$DATE.log statistics=none

First line creates a variable called DATE which is set as the current system date in month,day,year,hour,minute format.

Second line performs the backup calling EXP utility and passing following parameters:

username/password@instance_name tells EXP what to backup

file=/home/oracle/backup/backup_$DATE.dmp tells EXP where to save backup

log=/home/oracle/backup/backup_$DATE.log tells EXP where to save backup log

statistics=none tells EXP not to save statistics related to objects we are exporting

To schedule EXP backup using crontab:

[root@db1 ~]# crontab -e -u oracle

I will schedule backup everyday at 20:00 (8.00 PM)

00 20 * * * /home/oracle/backup

The first five fields are:

minute (0-59)
hour (0-23)
day of the month(1-31)
month of the year (1-12)
day of the week (0-6 with 0 = Sunday)

And the last parameter is absolute path to my backup script.

For more info on crontab have a look at crontab.org

NOTE: If you are unable to edit crontab file set EDITOR variable according to your preferred file editor.

In my case:

[root@db1 ~]# export EDITOR=nano

[root@db1 ~]# crontab -e -u oracle

That's all!!