Re: Backup setup sane?

From: hpuxrac <johnbhurley_at_sbcglobal.net>
Date: Fri, 16 Oct 2009 06:35:13 -0700 (PDT)
Message-ID: <dfa2a5da-6b66-4c27-a429-e711410a0419_at_z2g2000yqm.googlegroups.com>



On Oct 16, 4:39 am, Robert Klemme <shortcut..._at_googlemail.com> wrote:
> Hi,
>
> I set up backups on my test database and would be glad if someone
> could point me to any errors I did or things that are not reasonable
> (details below).  The purpose of this backup setup is (in that order)
>
> - learning RMAN,
> - automated operation with limits to disk size (deletion of archived
> logs and obsolete backups),
> - a certain level of safety against user, software (kill -9 on all
> oracle processes, accidental deletion of DB files and the like) and
> power failure.
>
> My strategy was to not use EM / Grid Control and no catalog to have a
> minimal setup and do as much as possible via configuration settings so
> backup scripts are short.  I wanted to set up a fairly common scheme
> with an incremental backup level 0 once a week and level 1 backups on
> each other day.
>
> Thanks for your feedback!
>
> Kind regards
>
> robert
>
> DB is 10.2.0.4.0 on RHEL 5 (well, really CentOS 5).
>
> I set these non default settings
> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 10 DAYS;
> CONFIGURE BACKUP OPTIMIZATION ON;
> CONFIGURE CONTROLFILE AUTOBACKUP ON;
> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/
> backup/ctl/%F';
> CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO COMPRESSED
> BACKUPSET;
> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/backup/sets/%U';
>
> These stayed default
> CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
> CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
> CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; #
> default
> CONFIGURE MAXSETSIZE TO UNLIMITED; # default
> CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
> CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
> CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
> CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/app/oracle/oracle/product/
> 10.2.0/db_1/dbs/snapcf_opscgold.f'; # default
>
> [oracle]$ crontab -l
> # Oracle backups
> # weekly backup
> 7 3 * * 0   $HOME/bin/backup-base.sh
> # daily backup
> 7 3 * * 1-6 $HOME/bin/backup-incr.sh
>
> And the scripts
>
> [oracle]$ cat $HOME/bin/backup-base.sh
> #! /bin/sh
> set -f
>
> # prevent concurrent running
> lock="/var/run/oracle/`basename "$0"`.pid"
>
> if [ -e "$lock" ]; then
>   pid=`cat "$lock"`
>   kill -0 "$pid" && { echo "running $0 with pid $pid"; exit 1; }
>   rm -f "$lock"
> fi
>
> trap "rm -f '$lock'" 0
> echo $$ >"$lock"
>
> # main code
> ts="`date '+%Y-%m-%d_%H-%M-%S'`"
>
> echo "base incremental backup: $ts"
>
> ORACLE_BASE='/app/oracle'
> export ORACLE_BASE
>
> ORACLE_HOME="${ORACLE_BASE}/oracle/product/10.2.0/db_1"
> export ORACLE_HOME
>
> ORACLE_SID='opscgold'
> export ORACLE_SID
>
> PATH="${ORACLE_HOME}/bin:${PATH}"
> export PATH
>
> rman target / <<EOS
> BACKUP INCREMENTAL LEVEL=0
>   TAG 'backup-cron-$ts'
>   DATABASE
>   PLUS ARCHIVELOG DELETE ALL INPUT;
> DELETE OBSOLETE;
> LIST BACKUP;
> EXIT;
> EOS
>
> The incremental script is the same apart from the RMAN part:
>
> rman target / <<EOS
> BACKUP INCREMENTAL LEVEL=1
>   TAG 'backup-cron-$ts'
>   DATABASE
>   PLUS ARCHIVELOG DELETE ALL INPUT;
> EXIT;
> EOS
I really prefer to do any DELETing in separate scripts under separate schedules. I also prefer to handle archivelogs separate from database backups.

This is what one of my backup scripts looks like:

rman << EOF
connect target
run {
allocate channel d1 type disk format '/u03prod/disk/rman_prod_%M_%D_%Y_ %U.bkp';
backup validate check logical database;
backup incremental level 1
for recover of copy
with tag 'disk_incremental'
database;
recover copy of database with tag 'disk_incremental'; restore database validate;
release channel d1;
}
exit;
EOF I then send the output from this set of commands to a file and then run a custom script that parses the output from each of the steps and makes sure that everything was clean.

  • * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

 Result Summary of RMAN runlog analysis:


 SUCCESS: rman backup validate processing is complete
 SUCCESS: rman backup of database is complete
 SUCCESS: rman recover copy of database disk copy is complete
 SUCCESS: rman restore validate database is complete
 SUCCESS: local file creation via sqlplus is complete

  • * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

If any of the steps has issues it would be noted with a FAILURE and email based escalation.

I am not a big fan of the autobackup of the controlfile and spfile and followup an rman backup with manual steps to get both of those available "where I can control them" instead of rman.

sqlplus / as sysdba << EOF1
create pfile='/home/oracle/prod_pfile.ora' from spfile; create pfile='/u03prod/disk/prod_pfile_`date +"%m_%d_%Y"`_`date +"%T"`.ora' from
 spfile;
alter database backup controlfile to trace as '/u03prod/disk/ prod_controlfile_tr
ace_backup_`date +"%m_%d_%Y"`_`date +"%T"`.ora'; exit;
EOF1 Received on Fri Oct 16 2009 - 08:35:13 CDT

Original text of this message