This Howto describe how run a backup Oracle Databases with Bacula without downtime.

Contributed by Victor Hugo dos Santos <contacto.vhs at gmail.com>

In production environment downtimes of DB isn't allowed, for around this problem, we will need:

1. Configure Oracle DB in backup mode
2. Create a Snapshot from files
3. Configure Oracle DB in backup mode off.
4. Run a backup from files with bacula
5. Destroy the Snapshot

• Linux Server
• Bacula Server
• Oracle DB (version 10.2.0.0 or superior)

• 2 Logical Volumes
  • /dev/VG_DATA/oraappl
  • /dev/VG_DATA/oradata
• Oracle Data is mounted in /var/oradata
• Oracle Applicative is mounted in /opt/oraappl
• Oracle Archive Logs is created in /var/backup/oracle/arch
• and two folders for snapshots in /mnt/snapshots/
  • /mnt/snapshots/var/oradata/
  • /mnt/snapshots/opt/oraappl/
• Bacula client
• LVM + Snapshots capability

• RunBeforeJob and RunAfterJob directives that run commands/scripts before and after execute jobs.

• Archive Log modes that freeze DBF files and send changes in database for others files (/var/backup/oracle/arch/), allowed the system copy DBF files without data lost.
• ALTER DATABASE [BEGIN|END] BACKUP command that configure ALL databases in backup mode (before version 9i was necessary configure the

tablespace individually).

First step is configure your Oracle DB for work in Archive Log modes, you can read this documents:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/archredo.htm#i1006184

http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/archredo004.htm

In my case, I send archive logs to folder /var/backup/oracle/arch

Now, we need create a script called start-backup-mode.sh in /opt/oraappl/scripts with this content:

#!/bin/bash

sqlplus /nolog <<EOF
conn sys/managger as sysdba
alter database begin backup;
exit
EOF

This lines putting database in backup mode.

This step is optional because in that moment is possible running a backup DB files directly from original folder (/var/oradata) with bacula, but personally I prefer create a snapshot of data to reduce backup mode operation time (the bad news is that if you have much access/changes in snapshot source, the IO can be reduced performance on server).

If you prefer (a same that me) create a snapshot, so you need aggregate this lines to end start-backup-mode.sh

/usr/sbin/lvcreate -L 20G -s -n oradata-snap oradata
/usr/sbin/lvcreate -L 20G -s -n oraappl-snap oraappl
mount /dev/VG_DATA/oradata-snap /mnt/snapshots/var/oradata/
mount /dev/VG_DATA/oraappl-snap /mnt/snapshots/opt/oraappl/

that lines create and mount Snapshots volumes. Obs.: In this example, your Linux Server need have a 40GB free in VolumeGroup to create a SnapShots

Now, we have a Snapshot of files and we can unset Oracle DB backup mode, add this lines to end start-backup-mode.sh:

sqlplus /nolog <<EOF
conn sys/managger as sysdba
alter database end backup;
exit
EOF

the start-backup-mode.sh final script is:

#!/bin/bash

# Put DB in backup mode
sqlplus /nolog <<EOF
conn sys/managger as sysdba
alter database begin backup;
exit
EOF

# Create and mount Snapshots
/usr/sbin/lvcreate -L 20G -s -n oradata-snap oradata
/usr/sbin/lvcreate -L 20G -s -n oraappl-snap oraappl
mount /dev/VG_DATA/oradata-snap /mnt/snapshots/var/oradata/
mount /dev/VG_DATA/oraappl-snap /mnt/snapshots/opt/oraappl/

# Unset DB backup mode
sqlplus /nolog <<EOF
conn sys/managger as sysdba
alter database end backup;
exit
EOF

exit 0

If all is OK.. we having:

• originally files in /var/oradata and /opt/oraappl
• snapshots of files in /mnt/snapshots/var/oradata/ and /mnt/snapshots/opt/oraappl/

now is need configure Bacula's Jobs.

Bacula need run scripts start-backup-mode.sh and stop-backup-mode.sh as oracle's user, for this we need aggregate this lines in /etc/sudoers in Linux Server that is running a BD Oracle instance:

     Cmnd_Alias      BACKUP_ORACLE = /bin/su - oracle -c /opt/oraappl/scripts/start-backup-mode.sh, /bin/su - oracle -c /opt/oraappl/scripts/start-backup-mode.sh
     bacula          LOCAL = NOPASSWD: BACKUP_ORACLE

Too is need configure the Job in Bacula Director, add this lines for your configuration:

FileSet {
  Name = "Linux-Oracle-SNAPs"
    Include {
      Options { signature = SHA1; compression=GZIP6; strippath=2 }
        File = /mnt/snap/var/oradata
        File = /mnt/snap/opt/oraappl
        File = /usr/local/bin/
        File = /etc/oratab
      }
    }

FileSet {
  Name = "Linux-Oracle-ARCs"
    Include {
      Options { signature = SHA1; compression=GZIP6 }
        File = /var/backup/oracle/arch/
      }
    }

Schedule {
  Name = "Oradata-Cycle"
    Run = Full sun at 02:30
    Run = Incremental mon-sat at 02:30
  }

Schedule {
  Name = "Oracle-AchiveLogs-Cycle"
    Run = Full sun at 04:30
    Run = Incremental mon-sat at 04:00
    Run = Incremental mon-sat at 08:00
    Run = Incremental mon-sat at 12:00
    Run = Incremental mon-sat at 16:00
    Run = Incremental mon-sat at 20:00
    Run = Incremental mon-sat at 24:00
}

Job {
  Name = "oracle-hotbackup"
  Client = oracleserver-fd
  JobDefs = "DefaultJob"
  Schedule = "Oradata-Cycle"
  RunBeforeJob = "sudo /bin/su - oracle -c /opt/oraappl/scripts/start-backup-mode.sh"
  RunAfterJob  = "sudo /bin/su - oracle -c /opt/oraappl/scripts/stop-backup-mode.sh"
  FileSet = "Linux-Oracle-SNAPs"
  Write Bootstrap = "/var/lib/bacula/oracle-hotbackup.bsr"
}

Job {
  Name = "oracle-archivelog"
  Client = oracleserver-fd
  JobDefs = "DefaultJob"
  Schedule = "Oracle-AchiveLogs-Cycle"
  FileSet = "Linux-Oracle-ARCLOGS"
  Write Bootstrap = "/var/lib/bacula/oracle-archivelog.bsr"
}

Well, in this example we have a two jobs:

1. one backup all content of snapshots volumes (mnt/snap/var/oradata and /mnt/snap/opt/oraappl) one time for day
2. other backup all archive logs (/var/backup/oracle/arch/) each 4 hours everyday.

In the first jobs (oracle-hotbackup) we are using the directives:

1. RunBeforeJob that active Oracle DB backup mode.
2. RunAfterJob that disable Oracle DB backup mode.

Well, after of finish the Backup Job, we have a backup of all data and application of Oracle in we storages and the Snapshot isn't more need, so we can remove it. But, the true is that Bacula automatic remove it because we configured RunAfterJob directive in Director and only needing create a /opt/oraappl/scripts/stop-backup-mode.sh script file with this content:

#!/bin/sh

# Umount and Destroy Snapshots
umount /mnt/snapshots/var/oradata/
umount /mnt/snapshots/opt/oraappl/
lvremove -f /dev/VG_DATA/oradata-snap
lvremove -f /dev/VG_DATA/oraappl-snap

exit 0

http://becomeappsdba.blogspot.com/2006/09/backup-recovery-in-oracle-apps.html

http://wiki.bacula.org/doku.php?id=application_specific_backups:oracle_rdbms

Metalink articles 604683.1 and 270531.1

I would like to thank to following people, not in any particular order:

• Bacula Developer Team for this excellent software.
• Ulrich Leodolter <ulrich.leodolter at obvsg.at> for suggesting the use of strippath in SNAPSHOTS jobs.
• Mike Holden <bacula at mikeholden.org> for correct a small confusion in this text.