Automatic Oracle Data Pump Backup Process

I have used the following process to automate a full Data Pump Export on a nightly basis. The backups will be saved to another server on the network. Backups will be kept locally on the Oracle server for a week when they will be removed. The network storage area will contain all previous backups.

1. On the Oracle server create a mount to the location in which the backups will be stored. In this case, a Windows share will be used to write the backups.

# mkdir -p /mnt/serverName
# mount -t cifs //serverName/shareName /mnt/serverName -o username=windowsUser,password=windowsPassword

2. Edit /etc/fstab to mount during startup.

# more fstab
/dev/VolGroup00/LogVol00 /                      ext3    defaults        1 1
LABEL=/boot             /boot                   ext3    defaults        1 2
tmpfs                   /dev/shm                tmpfs   defaults        0 0
devpts                  /dev/pts                devpts  gid=5,mode=620  0 0
sysfs                   /sys                    sysfs   defaults        0 0
proc                    /proc                   proc    defaults        0 0
/dev/VolGroup00/LogVol01 swap                   swap    defaults        0 0
//serverName/shareName  /mnt/serverName         cifs    username=windowsUsername,password=windowsPassword        0       0
#

3. In the Oracle user's home directory create two directories, Scripts and Backups. The Scripts directory will host the Python script used to automate the commands, the Backups directory will host the backups locally for seven days.

$ pwd
/home/oracle
$ ls -l
total 32
drwxr-xr-x 2 oracle oinstall 4096 May 26 13:28 Backups
drwxr-xr-x 2 oracle oinstall 4096 Apr 26 09:47 Desktop
drwxr-xr-x 2 oracle oinstall 4096 May 26 13:28 Scripts
$

4. Ensure Python 3 has been installed.

$ python3 --version
Python 3.1.2
$

5. Create the following Python 3 scripts in the Oracle user's Scripts directory. The script will do the following:

• Execute the expdp to run a full export.

  expdp '/ as sysdba' directory=data_pump_dir dumpfile=fileNamePrefix.dmp logfile=fileNamePrefix_expdp.log full=y



• Move the dump and log files to the Backups directory in the Oracle user's home.

  mv  /opt/oracle/admin/orcl/dpdump/fileNamePrefix* /home/oracle/Backups/



• Compress the backup.

  tar cvfj /home/oracle/Backups/fileNamePrefix.tbz2 /home/oracle/Backups/fileNamePrefix.dmp /home/oracle/Backups/fileNamePrefix_expdp.log



• Once compressed, remove the original dump and log files.

  rm /home/oracle/Backups/fileNamePrefix.dmp /home/oracle/Backups/fileNamePrefix_expdp.log



• Copy the files to the network drive using rsync. The destination directory will be in the format of YYYY-MM.

  rsync -avz --no-o --no-g --no-p --no-t /home/oracle/Backups/ /mnt/serverName/`date +%Y-%m`



• Go through the local Backups directory and remove the backups which are older than seven days.


• Send an email showing the status of the commands.


• Log all actions taken. The log file will be created as scriptName.log located in the same directory of the the script itself.

import datetime
import time
import os
import re
import sys
import smtplib

class RunCommand():
        
    def __init__(self):
        # Write the log in the same directory as the script. Logname will be ScriptName.log
        self.f = open(str(sys.path[0] + "/" + re.sub('\.py(c)?','.log',os.path.basename(sys.argv[0]))),'a')
        
        self.logger("Initializing", "Started")
        
        # Get the current time and set the fileprefix.
        now = datetime.datetime.now() 
        self.timestr = now.strftime("%Y-%m-%d_%Hh%Mm%Ss")
        self.fileprefix = "orcl_full_expdp_" + self.timestr
        
        self.dpdumpdir = "/opt/oracle/admin/orcl/dpdump/"
        self.backupsdir = "/home/oracle/Backups/"
        
        # Build Data Pump Full Export command. Need to initially set environment variables.
        self.expdpcmd =  "source /home/oracle/.bash_profile; "
        self.expdpcmd += "expdp \\'/ as sysdba\\' directory=data_pump_dir dumpfile="
        self.expdpcmd += self.fileprefix
        self.expdpcmd += ".dmp logfile="
        self.expdpcmd += self.fileprefix
        self.expdpcmd += "_expdp.log full=y"
        
        # Build Move Command
        self.movecmd =  "mv " + self.dpdumpdir + self.fileprefix + "* " + self.backupsdir
        
        # Build Compress Command
        self.compresscmd =  "tar cvfj " + self.backupsdir + self.fileprefix
        self.compresscmd += ".tbz2 " + self.backupsdir + self.fileprefix
        self.compresscmd += ".dmp " + self.backupsdir + self.fileprefix + "_expdp.log"
        
        # Remove the dmp and log files as they have been compress into the tgz file.
        self.removecmd =  "rm " + self.backupsdir + self.fileprefix
        self.removecmd += ".dmp " + self.backupsdir + self.fileprefix + "_expdp.log"
        
        # Rsync command.
        self.rsynccmd = "rsync -avz --no-o --no-g --no-p --no-t " + self.backupsdir + " /mnt/serverName/`date +%Y-%m`"
        
        self.status = {}
        self.status['expdpcmd'] = -1
        self.status['movecmd'] = -1
        self.status['compresscmd'] = -1
        self.status['removecmd'] = -1
        self.status['rsynccmd'] = -1
        self.status['removeOldBackups'] = []      
        
        # Setup Email
        self.smtpserver = 'mail.company.com'
        self.sender = 'david@company.com'
        self.receivers = ['david@company.com']
        self.emailheader =  "From: David \n"
        self.emailheader += "To: David \n"
        self.emailheader += "Subject: OracleBackup: orcl\n\n"
        
        self.logger("Initializing", "Finished")
        
    def logger(self,section,action,msg=""):
        self.f.write(datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S,") + section + "," + action + "," + msg + "\n")
        
    def run(self):
        # Run the commands only if the previous commands were successful.
        self.logger("Running", "Started")
        self.logger("expdpcmd", "Started", str(self.expdpcmd))
        self.status['expdpcmd'] = os.system(self.expdpcmd)
        self.logger("expdpcmd", "Finished", str(self.status['expdpcmd']))
        if self.status['expdpcmd'] == 0:
            self.logger("movecmd", "Started", str(self.movecmd))
            self.status['movecmd'] = os.system(self.movecmd)
            self.logger("movecmd", "Finished", str(self.status['movecmd']))
            if self.status['movecmd'] == 0:
                self.logger("compresscmd", "Started", str(self.compresscmd))
                self.status['compresscmd'] = os.system(self.compresscmd)
                self.logger("compresscmd", "Finished", str(self.status['compresscmd']))
                if self.status['compresscmd'] == 0:
                    self.logger("removecmd", "Started", str(self.removecmd))
                    self.status['removecmd'] = os.system(self.removecmd)
                    self.logger("removecmd", "Finished", str(self.status['removecmd']))
        self.logger("Running", "Finished", str(self.status))
            
    def rsyncFiles(self):
        self.logger("Synchronizing", "Started")
        self.logger("rsynccmd", "Started", str(self.rsynccmd))
        self.status['rsynccmd'] = os.system(self.rsynccmd)
        self.logger("rsynccmd", "Finished", str(self.status['rsynccmd']))
        self.logger("Synchronizing", "Finished", str(self.status))

    def removeOldBackups(self):
        self.logger("BackupCleanup", "Started")
        # Remove local backup files which are over 7 days old only if the synchronization job ran.
        if self.status['rsynccmd'] == 0:
            for file in os.listdir(self.backupsdir):
                filetime = os.path.getmtime(self.backupsdir + file) 
                currenttime = time.time()
                fileage = currenttime - filetime
                if fileage > 604800:
                    try:
                        os.unlink(self.backupsdir + file)
                    except:
                        self.logger("BackupCleanup", "Failed Removing", str(self.backupsdir + file))
                        self.status['removeOldBackups'].append([str(self.backupsdir + file),"Failed"])
                    else:
                        self.logger("BackupCleanup", "Success Removing", str(self.backupsdir + file))
                        self.status['removeOldBackups'].append([str(self.backupsdir + file),"Success"])
            self.logger("BackupCleanup", "Finished", str(self.status['removeOldBackups']))
        else:
            self.logger("BackupCleanup", "Finished", "No backups removed due to synchronization failure!")
            
    def sendStatusEmail(self):
        self.logger("SendingEmail", "Started")
        self.message = self.emailheader + "Oracle Data Pump Backup: " + self.timestr + "\n"
        
        self.message += "\nCommand Status:\n"
        for s in self.status:
            if s != 'removeOldBackups':
                self.message += "\t" + s + ": "
                if self.status[s] == 0:
                    self.message += "Success\n"
                elif self.status[s] == -1:
                    self.message += "NOP\n"
                else:
                    self.message += "Failed\n"
        
        self.message += "\nOld Backup Removal:\n"
        for f in self.status['removeOldBackups']:
            self.message += "\t" + f[1] + " removing backup file: " + f[0] + "\n"
        
        try:
            smtpObj = smtplib.SMTP(self.smtpserver)
            smtpObj.sendmail(self.sender, self.receivers, self.message)
        except SMTPException:
            self.logger("SendingEmail", "Finished", "Failed to send email!")
        else:
            self.logger("SendingEmail", "Finished", str(self.status))

rc = RunCommand()
rc.run()
rc.rsyncFiles()
rc.removeOldBackups()
rc.sendStatusEmail()

6. Use cron to execute the Python script every morning at 1:00 AM. Login as the oracle user and add the job to the oracle user's crontab file.

1. Issue the following command to start the default editor to add the job.

   $ crontab -e



2. Add the following line then save and quit.

   0 1 * * * /usr/local/bin/python3 /home/oracle/Scripts/OracleBackup.py



3. List the jobs.

   $ crontab -l
   0 1 * * * /usr/local/bin/python3 /home/oracle/Scripts/OracleBackup.py
   $