Re: [Bacula-users] Howto Backup Oracle with Bacula

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 454-5900

Hello,

optionally use strippath=2 for snapshot fileset
it's easier to restore to original location.

On Fri, 2009-02-27 at 16:50 -0300, Victor Hugo dos Santos wrote:
> Hello Everyone ,
> 
> I have writing/working in a howto to backup Oracle DB... and this is
> the first draft
> you can read it and send me yours comment and corrections ??
> and if possible test this procedure ??? :-)
> 
> thanks
> 
> ********************************
> ********************************
> This Howto describe how run a backup Oracle Databases with Bacula
> without downtime.
> 
> the principle of idea:
> 
> 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 - Run a backup from files with bacula
> 	4 - Configure Oracle DB in backup mode off.
> 	5 - Destroy the Snapshot
> 
> Our scenery is:
> 
> Linux ;-)
> Bacula
> Oracle DB (version 10.2.0.0 or superior)
> 
> In Linux we have:
> 	* 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/respaldo/oracle/arch
> 	* and two folders for snapshots in /mnt/snapshots/
> 		- /mnt/snapshots/var/oradata/
> 		- /mnt/snapshots/opt/oraappl/
> 	* Bacula client
> 	* Snapshots
> 
> In Bacula we have:
> 	* RunBeforeJob and RunAfterJob directives that run commands/scripts
> before and after execute jobs.
> 
> In Oracle we have:
> 	* Archive Log modes that "freeze" DBF files and send changes in
> database for "others" files (/var/backup/oracle/arch), allowed copy
> DBF files without data lost.
> 	* "ALTER DATABASE [BEGIN|END] BACKUP" command that configure "ALL"
> database in backup mode (before version 9i was necessary configure the
> tablespace individually).
> 
> 
> Well, now to work
> 
> 
> STEP 1 - Setup Oracle DB in backup and preparing files for backup
> ---------------------------------------------------------------
> 
> 1.1 - Archive Log Mode
> First step is configure your Oracle DB for work in Archive Log modes,
> you can read this document:
> http://lbdwww.epfl.ch/f/teaching/courses/oracle9i/server.920/a96521/archredo.htm#6768
> In my case, I send archive logs to folder /var/backup/oracle/arch
> 
> 1.2 - Oracle DB backup mode
> 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.
> 
> 
> 1.3 - Create a Snapshot from files
> 
> 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.
> 
> 1.3 - Unset Oracle DB backup mode
> 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 final "start-backup-mode.sh" 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
> =======================
> 
> 
> STEP 2 - Run a backup with bacula
> ---------------------------------
> 
> 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.
> 2.1 - Configure sudoers
> 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:
> 
> 	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
> 
> 2.2 - Configure Bacula Jobs
> 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=GZIP9
			strippath=2    # strip /mnt/snap path part
		}
> 	                File = /mnt/snap/var/oradata
> 	                File = /mnt/snap/opt/oraappl
# why this ?              File = /usr/local/bin/
>         	}
> 	}
> 
> 	FileSet {
> 	        Name = "Linux-Oracle-ARCs"
> 	        Include {
> 	                Options { signature = SHA1; compression=GZIP9 }
> 	                File = /var/backup/oracle/arch/
> 	        }
> 	}
> 
> 	Schedule {
> 	        Name = "Oradata-Cycle"
> 	        Run = Full sun at 04:30
> 	        Run = Incremental mon-sat at 04: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 = server-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 = server-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:
> 	- one backup all content of snapshots volumes (mnt/snap/var/oradata
> and /mnt/snap/opt/oraappl) one time for day
> 	- other backup all archive logs (/var/backup/oracle/arch/) each 4 hours.
> 
> In the first jobs (oracle-hotbackup) we use directives:
> 		- RunBeforeJob that configure DB in backup mode before backup
> 		- RunAfterJob that configure Oracle DB in backup mode off.
> 
> 
> 
> STEP 3 - Destroy a Snapshot
> ---------------------------
> 
> Well, in this moment we have a backup of all data and application of
> Oracle and the Snapshot isn't more need, so we can remove it..mmm..
> the true is that Bacula automatic remove it because we configured
> RunAfterJob directive in it 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
> =======================
> 
> References:
> http://becomeappsdba.blogspot.com/2006/09/backup-recovery-in-oracle-apps.html
> http://wiki.bacula.org/doku.php?id=application_specific_backups:oracle_rdbms
> and Metalink 604683.1 and 270531.1 articles
> 
> ********************************
> ********************************
> 
> 
> 
> -- 
> -- 
> Victor Hugo dos Santos
> Linux Counter #224399
> 
> ------------------------------------------------------------------------------
> Open Source Business Conference (OSBC), March 24-25, 2009, San Francisco, CA
> -OSBC tackles the biggest issue in open source: Open Sourcing the Enterprise
> -Strategies to boost innovation and cut costs with open source participation
> -Receive a $600 discount off the registration fee with the source code: SFAD
> http://p.sf.net/sfu/XcvMzF8H
> _______________________________________________
> Bacula-users mailing list
> Bac...@li...
> https://lists.sourceforge.net/lists/listinfo/bacula-users
> 