Uptime vs Database Backups
Backups of your database can be an enemy to your system uptime. Doing a filesystem dump of your database tablespace files will require that it occur while your database is down so that you don’t have problems with the database locking the files and changing them while you are backing it up. If the database is down, then your uptime is suffering, so you need a way to minimize the time that your database is down.
Linux Snapshot Backups
Linux snapshot backups allow you to backup your database files with the database up and running as if the database were down. Snapshots cache all of the changes and when you make the backup the cache is used to give you the state of the file when the snapshot was created.
Let’s Do It!
Here is the list of my prerequisites all ready for usage:
Snapshot Logical Volume: /dev/snapshots/datasnapshot
Snapshot Mount Point: /snapshotmount
Source Filesystem: /data
Source Logical Volume: /dev/leadsdb/data
Backup Path: /backup/mydatabasebackup.tar.gz
OK, we’re ready to start.
Step 1 – Shutdown any Databases on the File System
Most active running databases need to be shutdown for a filesystem backup to be successful in recovery. This is the whole point of the snapshot backup. We quickly take down the database, then we create the snapshot, then we restart the database again. Then we backup the snapshot. This allows us to backup for hours while the database is operational.
Step 2 – Create the Snapshot Logical Volume
At this point, we create our snapshot logical volume. This snapshot logical volume will store all of the changes that occur in the target logical volume while the logical volume exists. This allows us to backup the target filesystem as it was at the time of the snapshot logical volume creation.
# lvcreate –size 100G –snapshot –name datasnapshot /dev/leadsdb/data Logical volume “datasnapshot” created |
Step 3 – Mount the Snapshot
Now, we mount the filesystem on the snapshot logical volume.
# mount /dev/leadsdb/datasnapshot /snapshotmount |
Step 4 – Restart Your Database
It’s all about uptime after all and that is the whole point of backing up using lvm snapshots. Since the filesystem now has a snapshot, we can backup the snapshot logical volume without worrying about database tablespace files changing even with the database up and running. So start your database up at this point.
Step 5 – Perform the Backup
Use tar, cpio, cp, rsync, dump, etc… to backup the files from the
/bin/tar -zcvf /backup/mydatabasebackup.tar.gz /snapshotmount |
Step 6 – Unmount and Remove the Snapshot Logical Volume
Once the backup is complete, you can unmount and remove the snapshot logical volume. It is very important that we do this. If the logical volume remains, it will continue to hold the changes in the target logical volume and will eventually fill up. Once there is no more space in the snapshot logical volume, then the target logical volume will at some point stop allowing changes and this will be a huge problem for your database. So, we will unmount the snapshot filesystem and delete the snapshot logical volume.
# umount /snapshotmount # /usr/sbin/lvremove -f /dev/leadsdb/datasnapshot |
Step 7 – Done
Once that you verify that the snapshot logical volume is no longer mounted and that it has been removed, the whole task is complete.
Example Oracle Snapshot Backup Script
Now that we’ve gone through how it all works, you are ready to look at a complete example script. Just like in the example above, you need to get all of your homework done. You will need to get define the snapshot logical volume and mount point, as well as the source logical volume, and Oracle variables.
Do not rely on this script without testing it out first and verifying that you understand completely what it is doing. Be sure to have also tested a successful restore of your database that was backed up using this script.
Oracle Backup Snapshot Script:
#!/bin/bash # This backup script will attempt to shutdown oracle # snapshot the filesystem, restart oracle, backup the # filesystem, and then unmount the snapshot. # # Prior to running this script, a logical volume has # to be created using text similar to: # lvcreate –size 40G –snapshot –name u01snap /dev/u01/u01 SYSTEM=`hostname` ORACLE_SID=oraprd2 ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 SNAPLV=/dev/VolGroup00/u01snap SNAPFS=/orasnap SOURCELV=/dev/VolGroup00/LogVol02 LOG=/backup/logs/backup_oracle_snap_`/bin/date ‘+%y%m%d_%H%M%S’`.log . /etc/profile.d/oracle.shverify_fs_mounted() {MOUNTED=”NULL” grep ${SNAPFS} /proc/mounts RESULT=$? if [ $RESULT -eq 0 ] then MOUNTED=”TRUE” echo “${SNAPFS} is mounted” else MOUNTED=”FALSE” echo “${SNAPFS} is NOT mounted” fi}get_oracle_status() {# Need to verify that Oracle is down and not running ORACLE_UP=0 ORACLE_STATUS=”NULL” ORACLE_UP=`ps -ef | grep ${ORACLE_SID} | grep pmon | wc -l` if [ $ORACLE_UP -eq 1 ] then ORACLE_STATUS=UP echo “Oracle SID ${ORACLE_SID} is still running!” else ORACLE_STATUS=DOWN echo “ORACLE SID ${ORACLE_SID} is verified down for backup!” fi } startup_oracle() { shutdown_oracle() { backup_oracle() { create_snapshot() { remove_snapshot() { verify_fs_mounted >> ${LOG} # 2. Create and mount the snapshot # 3. Startup Oracle again # 4. Perform the backup # 5. Remove the Snapshot |
Latest posts by Jeff Staten (see all)
- Configure Your HP Procurve Switch with SNTP - May 5, 2015
- Configuring HP Procurve 2920 Switches - May 1, 2015
- Troubleshooting Sendmail - November 28, 2014