• Environment
OS: Sun Solaris 2.8
Oracle version: 9.2.0.6.0
ORACLE_SID: prod
Data volume: /dprod01
/dprod02
/dprod03
• Original backup solution
Oracle manual online backup
Problem: The online backup takes about 6 hours to complete and copies about 600G of data which impacts nightly batch job executions.
• How does Point-in-Time (PIT) copy work
1. For each data volume, a shadow volume is set up with the same size, i.e. /sdprod01 is setup for /dprod01.
2. At one point, with the database shutdown or suspended, all the files on /dprod01 is copied to /sdprod01.
3. A bit map area is setup to track the changes on the data volume.
4. The data file on /dprod01 is modified by Oracle once the database is started up or resumed. However /sdprod01 remains unchanged as at the sync point (step 2).
5. When you need to synchronize the shadow volume with the data volume again, the PIT software checks the bitmap to see which blocks have been modified and brings only those changed blocks from /dprod01 to /sdprod01.
6. The beauty of this software is that you don’t have to wait for the synchronization to complete before resuming the database. As long as you mark the PIT sync point, the software knows what data to flush to the shadow volume. I assume this works the same as the flash back query on Oracle.
• Software setup
For detailed explanation of setting up SUN Availability Suite, please see:
http://docs.sun.com/app/docs/doc/816-4312-10?q=availability+suite+point+in+time
Shadow volume: /sdprod01
/sdprod02
/sdprod03
• Advantage:
1. Shortened backup windows from 6 hours to a few seconds.
2. Increased database availability close to 7/24.
• Disadvantage and problem encountered
1. It is difficult to increase data volume (/dprodxx) once the setup is completed. You will need to break the configuration and redo. So make sure you have reserved enough space on all the data volumes before starting the setup.
2. Space requirement is doubled as the shadow volume takes the same amount of space as the original data volume.
3. The backup from the shadow volume to tape will still take the same amount of time as from the manual online backup. All the data files will be backed up even if you perform an incremental backup as Oracle keeps modifying the timestamp on these data files.
4. I had one incident where one of the data volumes was stuck on one of the backup steps. The database didn’t come up but we didn’t receive email alert as well. I need to tune the script to make sure an alert is sent out if the backup didn’t complete within a few minutes.
• Comparing to other solutions
1. I personally still like the Physical Standby Database solution where two instances are available at the same time and it is much quicker to switch to the standby instance. Backup can also be done on the standby database without impacting the production database.
2. I also like the RMAN solution very much for smaller databases. It is easy to manage and offers a lot of backup options.
3. However from a pure backup solution point of view, I found this is the easiest one.
• Backup steps and scripts
DISCLAIMER: The scripts attached below are for your reference only. The script has been tested and appears to work as intended. Proofread this script prior to running it!
PIT.sh – Master script to wrap step 1-4
1. begin_pit_backup.sh – Put tablespaces on backup mode, suspend database
2. pit_pre_$SID.sh – Copy data to shadow volume
3. end_pit_backup.sh – Resume database, take all tablespaces out of backup mode
4. pit_post_$SID.sh – Check shadow volume to make sure there is no corruption.
sunserv1:/home/oracle/scripts>cat PIT.sh
#!/bin/ksh
SID=$1;export SID
OWNER_ID1=root
OWNER_ID2=oracle
SHELL_SCRIPT=:/home/oracle/scripts
LOGS=/home/oracle/logs
exec >> $LOGS/PIT_$SID.log 2>&1
echo “**************************PIT START*************************
/usr/bin/date
/usr/bin/su – $OWNER_ID2 -c “$SHELL_SCRIPT/begin_pit_backup.sh $SID”
#
echo “*********$SID Database in Suspend Mode************”
/usr/bin/date
/usr/bin/su – $OWNER_ID1 -c “$SHELL_SCRIPT/pit_pre_$SID.sh”
#
echo “*******PIT Copy to Shadow Volumes Completed*******”
/usr/bin/date
/usr/bin/su – $OWNER_ID2 -c “$SHELL_SCRIPT/end_pit_backup.sh $SID”
#
echo “********$SID Database Out of Suspend Mode*********”
/usr/bin/date
/usr/bin/su – $OWNER_ID1 -c “$SHELL_SCRIPT/pit_post_$SID.sh”
#
echo “********Fsck of Shadow Volumes Completed*********”
/usr/bin/date
echo “*************************PIT Completed**********************
exit
sunserv1:/home/oracle/scripts>cat begin_pit_backup.sh
#!/bin/ksh
#———————————————————————————————————-
#
# This script performs the following steps:
# 1. Put all tablespaces in backup mode
# 2. Suspend oracle
#
#———————————————————————————————————-
. ~/.profile
MAILLIST=”dba@mycomp.com”
LOG=/home/oracle/logs
# Check command syntax
if [[ -z $1 ]] then
echo ‘Please specify which instance to backup’
echo ‘exit now…’
exit 1
else
export ORACLE_SID=$1
echo ‘Starting backup for ORACLE SID: ‘ $ORACLE_SID
fi
echo “Checking database status”
ORA_UP=`ps -ef | grep ${ORACLE_SID} | awk ‘$NF ~ /pmon/ {print $NF}’`
if [ -z "$ORA_UP" ]; then
echo “${ORACLE_SID} is DOWN” |mailx -s “Unable to do hotbackup for ${ORACLE_SID}” ${MAILLIST} /tmp/$$.1
echo “${ORACLE_SID} is DOWN”
exit 1
else
echo “${ORACLE_SID} is UP!”
fi
echo ‘Preparing SQL scripts’
$ORACLE_HOME/bin/sqlplus -s ‘/ as sysdba’ < < EOF
set Heading off Verify Off FeedBack Off LineSize 132 PageSize 1000 termout on
WHENEVER OSERROR EXIT FAILURE
WHENEVER SQLERROR EXIT SQL.SQLCODE
spool $LOG/start_pit_backup_$ORACLE_SID.sql
prompt select instance_name,host_name,database_status,version,status,active_state from V\$instance;;
select 'alter tablespace ' || name || ' begin backup;' from V\$tablespace where name not like '%TEMP%';
prompt alter database backup controlfile to trace;;
prompt alter system switch logfile;;
prompt alter system suspend;;
prompt select database_status from v\$instance;;
prompt quit;;
Spool Off
spool $LOG/end_pit_backup_$ORACLE_SID.sql
prompt alter system resume;;
prompt select instance_name,host_name,database_status,version,status,active_state from V\$instance;;
select 'alter tablespace ' || name || ' end backup;' from V\$tablespace where name not like '%TEMP%';
prompt quit;;
Spool Off
exit
EOF
echo "Starting backup process"
$ORACLE_HOME/bin/sqlplus -s '/ as sysdba' @$LOG/start_pit_backup_$ORACLE_SID.sql
echo "Database suspended now."
#exit
sunserv1:/home/oracle/scripts>cat pit_pre_prod.sh
#!/bin/sh
# need to explicit binary path when running pres/post script
PATH=/sbin:/usr/local/bin:/usr/local/sbin:/usr/bin:/usr/sbin:/usr/opt/SUNWesm/sbin
export PATH
# definition of volumes
SHADOW_DISK1=/dev/md/rdsk/d80
SHADOW_DISK2=/dev/md/rdsk/d81
SHADOW_DISK3=/dev/md/rdsk/d82
MASTER_DISK1=/dev/md/rdsk/d37
MASTER_DISK2=/dev/md/rdsk/d38
MASTER_DISK3=/dev/md/rdsk/d39
BITMAP_DISK1=/dev/md/rdsk/d100
BITMAP_DISK2=/dev/md/rdsk/d101
BITMAP_DISK3=/dev/md/rdsk/d102
MASTER_DISK1_MNT=/dprod01
MASTER_DISK2_MNT=/dprod02
MASTER_DISK3_MNT=/dprod03
SHADOW_DISK1_MNT=/sdprod01
SHADOW_DISK2_MNT=/sdprod02
SHADOW_DISK3_MNT=/sdprod03
LOGS=/home/oracle/logs
SID=prod
mailrec=root@mycomp.com
# we re-direct output in a logfile
#exec >> $LOGS/snapshot_$SID.log 2>&1
# We umount the Shadow volumes
umount -a -f $SHADOW_DISK1_MNT $SHADOW_DISK2_MNT $SHADOW_DISK3_MNT
# we flush the Master volumes before doing a “Quick Update”
echo “Quick update started”
lockfs -f -w $MASTER_DISK1_MNT $MASTER_DISK2_MNT $MASTER_DISK3_MNT
# Perform Shadow update
iiadm -u s $SHADOW_DISK1 $SHADOW_DISK2 $SHADOW_DISK3
if [ ! "$?" = 0 ] ; then
echo “failed to update shadow volume” | \
/usr/ucb/mail -s “Alert: Failed to update shadow volume for $SID Database” $mailrec
exit 1
fi
echo “Update finished.”
# we wait for Instant Image to finish its operations
#iiadm -w $SHADOW_DISK1 $SHADOW_DISK2 $SHADOW_DISK3
# Master Volumes can be re-activated and ready for normal operations
lockfs -u $MASTER_DISK1_MNT $MASTER_DISK2_MNT $MASTER_DISK3_MNT
echo “Pre-Script finished”
sunserv1:/home/oracle/scripts>cat end_pit_backup.sh
#!/bin/ksh
#———————————————————————————————————-
#
# This script performs the following steps:
# 1. Resume oracle
# 2. Take tablespaces out of backup mode
#
#———————————————————————————————————-
. ~/.profile
MAILLIST=”dba@mycomp.com”
LOG=/home/oracle/logs
# Check command syntax
if [[ -z $1 ]] then
echo ‘Please specify which instance to backup’
echo ‘exit now…’
exit 1
else
export ORACLE_SID=$1
echo ‘Ending backup for ORACLE SID: ‘ $ORACLE_SID
fi
echo “Checking database status”
ORA_UP=`ps -ef | grep ${ORACLE_SID} | awk ‘$NF ~ /pmon/ {print $NF}’`
if [ -z "$ORA_UP" ]; then
echo “${ORACLE_SID} is DOWN” |mailx -s “Unable to do hotbackup for ${ORACLE_SID}” ${MAILLIST} /tmp/$$.1
echo “${ORACLE_SID} is DOWN”
exit 1
else
echo “${ORACLE_SID} is UP!”
fi
echo “Ending backup process”
$ORACLE_HOME/bin/sqlplus -s ‘/ as sysdba’ @$LOG/end_pit_backup_$ORACLE_SID.sql
echo “Database resumed now.”
#exit
sunserv1:/home/oracle/scripts>cat pit_post_prod.sh
#!/bin/sh
# need to explicit binary path when running pres/post script
PATH=/sbin:/usr/local/bin:/usr/local/sbin:/usr/bin:/usr/sbin:/usr/opt/SUNWesm/sbin
export PATH
# definition of volumes
SHADOW_DISK1=/dev/md/rdsk/d80
SHADOW_DISK2=/dev/md/rdsk/d81
SHADOW_DISK3=/dev/md/rdsk/d82
MASTER_DISK1=/dev/md/rdsk/d37
MASTER_DISK2=/dev/md/rdsk/d38
MASTER_DISK3=/dev/md/rdsk/d39
BITMAP_DISK1=/dev/md/rdsk/d100
BITMAP_DISK2=/dev/md/rdsk/d101
BITMAP_DISK3=/dev/md/rdsk/d102
MASTER_DISK1_MNT=/dprod01
MASTER_DISK2_MNT=/dprod02
MASTER_DISK3_MNT=/dprod03
SHADOW_DISK1_MNT=/sdprod01
SHADOW_DISK2_MNT=/sdprod02
SHADOW_DISK3_MNT=/sdprod03
LOGS=/home/oracle/logs
SID=prod
# we re-direct output in a logfile
#exec >> $LOGS/snapshot_$SID.log 2>&1
# we wait for Instant Image to finish its operations
iiadm -w $SHADOW_DISK1 $SHADOW_DISK2 $SHADOW_DISK3
# when doing PIT of a mounted volume, fsck must be executed on snapshot
fsck -y $SHADOW_DISK1 $SHADOW_DISK2 $SHADOW_DISK3
# we mount the snapshot volume, ready to be backed up
mount -a $SHADOW_DISK1_MNT $SHADOW_DISK2_MNT $SHADOW_DISK3_MNT
echo “Post-Script finished”
0 Comments.