Category: | Utility Scripts |
Author/Contact Info | Sam Gold sgold@tampabay.rr.com www.GoldDesignInc.com |
Description: | Script to backup 4 Oracle databases and then backup the server using ufsdump. It backs up 2 databases a day and then the other 2 the next day. This was written for a Sun Box backing up to a DLT tape drive. Look for places where lines will need to be edited noted by #!! If you have questions or comments please let me know. |
#!/usr/local/bin/perl # # SCRIPT: backup.pl # CREATED: 6-8-01 Sam Gold. # sgold@tampabay.rr.com # www.GoldDesignInc.com # # use DBI; use Env; use Date::Manip; use strict; use File::Find; sub BACKUP($); sub ORA_BACKUP; sub RM_OLD_ARCH; sub GZIP_ARCH; my @fs; # Array of the file systems to be backed up. my $i = 0; # counter. my $ORA_HOME; # Oracle home directory. my $ORACLE_HOME; # "" my $PATH; # path. my $LD_LIBRARY_PATH; # the library path for oracle. my $ORA_SID; # Unique identifer of an oracle database. my $ORACLE_SID; # "" my $BACKUP_HOME; # Where all of the backup files go. my $ARCHIVE_HOME; # where all of the archive files are stored. my $LogFile; # a log file on disk of each file system that is backed up. # Collect date information. my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime; my $day = ('SUNDAY', 'MONDAY', 'TUESDAY', 'WEDNESDAY', 'THURSDAY', 'FRIDAY', 'SATURDAY')[$wday]; my $month = ('January','February','March','April','May','June','July','August','September','October','November','December')[$mon]; my $bkup_day = $day; my $bkup_wday = $wday; my $bkup_mday = $mday; $year += 1900; my $FirstFriday = UnixDate(ParseDate("first friday in $month $year"),"%e"); my $today = UnixDate(ParseDate("today"),"%y%m%d"); #get arguments to make the LogFile name. if ($#ARGV != 0) { die "\nUsage: $0 <date>\n\n"; } #!! You will need to modify this line. #!! it is a log of the whole backup process. #!! $ARGV[0] is the date. $LogFile = sprintf "/logs/Backup_%s", $ARGV[0]; # ----------------------------------------- # Redirecting Standard Error # ----------------------------------------- open (STDERR, ">>$LogFile") or die "Could not open $LogFile: $!\n"; # ----------------------------------------- # Redirecting Standard Out. # ----------------------------------------- open (STDOUT, ">>$LogFile") or die "Could not open $LogFile: $!\n"; eval { # Catch _any_ Kind of failures from the code within. # Make sure that the environmental variables are undefined. undef $ENV{ORA_SID}; undef $ENV{ORACLE_SID}; undef $BACKUP_HOME; undef $ARCHIVE_HOME; undef $ENV{ORA_HOME}; undef $ENV{ORACLE_HOME}; undef $ENV{LD_LIBRARY_PATH}; #!! You will need to set up all of the Oracle #!! environment variables below. #!! You may need to remove some of the lines #!! depending on how many database you are backing up. # setup Env for first backup. SWITCH: { if ($bkup_wday == 1 || $bkup_wday == 3 || $bkup_wday == 5) { $ENV{ORA_SID} =''; $ENV{ORACLE_SID} =$ENV{ORA_SID}; $BACKUP_HOME =''; $ARCHIVE_HOME =''; last SWITCH; } if ($bkup_wday == 2 || $bkup_wday == 4 || $bkup_wday == 6 ) { $ENV{ORA_SID} =''; $ENV{ORACLE_SID} =$ENV{ORA_SID}; $BACKUP_HOME =''; $ARCHIVE_HOME =''; last SWITCH; } } #End SWITCH. $ENV{ORA_HOME} = ''; $ENV{ORACLE_HOME} = $ENV{ORA_HOME}; #!! Enter PATH info here. $ENV{PATH} = sprintf "%",$ENV{ORACLE_HOME}; #!! More PATH stuff. $ENV{LD_LIBRARY_PATH} = sprintf "%", $ENV{ORACLE_HOME}; # 1) Run backup on Database 1 or Database 2. ORA_BACKUP() if (defined $ENV{ORA_SID}); # Undefine the environmental variables undef $ENV{ORA_SID}; undef $ENV{ORACLE_SID}; undef $BACKUP_HOME; undef $ARCHIVE_HOME; undef $ENV{ORA_HOME}; undef $ENV{ORACLE_HOME}; undef $ENV{LD_LIBRARY_PATH}; # setup Env for second backup. SWITCH: { if ($bkup_wday == 1 || $bkup_wday == 3 || $bkup_wday == 5) { $ENV{ORA_SID} =''; $ENV{ORACLE_SID} =$ENV{ORA_SID}; $BACKUP_HOME =''; $ARCHIVE_HOME =''; last SWITCH; } if ($bkup_wday == 2 || $bkup_wday == 4 || $bkup_wday == 6 ) { #$ENV{ORA_SID} =''; #$ENV{ORACLE_SID} =$ENV{ORA_SID}; #$BACKUP_HOME =''; #$ARCHIVE_HOME =''; last SWITCH; } } #End SWITCH. $ENV{ORA_HOME} = ''; $ENV{ORACLE_HOME} = $ENV{ORA_HOME}; $ENV{LD_LIBRARY_PATH} = sprintf "%s/lib:/opt/SUNWspro/lib:/usr/openwin/lib:/usr/dt/lib:/usr/lib", $ENV{ORACLE_HOME}; $ENV{PATH} = sprintf "%s/bin:/bin:/sbin:/usr/sbin:/usr/bin:/usr/local/bin",$ENV{ORACLE_HOME}; # 2) Run backup on Database 3 or Database 4. ORA_BACKUP() if (defined $ENV{ORA_SID}); # 3) Now for the Unix backup. # read the vfstab to get all of the mounted ufs file systems. open VFS, "< /etc/vfstab" || die "Cannot open /etc/vfstab\n"; while (<VFS$gt;) { chop; if (substr($_,0,1) eq "/") { my ($device2mnt,$device2fsck,$mnt,$fstype,$fsckpass,$mntboot,$mntopt) = split /\t/, $_; if (uc($fstype) eq "UFS") { $fs[$i] = ($mnt); $i++; } } } close VFS; # Monday thru Thursday we will be doing an incremental backup. # Levels 1-3 & 5. On Friday we will be doing 1 of 2 things, # a level 4 backup.The order will be 1,3,2,5,4, and on the # first Friday of the month it will be a level 0. SWITCH: { if ( $bkup_day eq 'MONDAY') { &BACKUP(1); last SWITCH; } if ( $bkup_day eq 'TUESDAY' ) { &BACKUP(3); last SWITCH; } if ( $bkup_day eq 'WEDNESDAY' ) { &BACKUP(2); last SWITCH; } if ( $bkup_day eq 'THURSDAY' ) { &BACKUP(5); last SWITCH; } if ( $bkup_day eq 'FRIDAY' ) { if ( $bkup_mday == $FirstFriday) { &BACKUP(0); last SWITCH; } else { &BACKUP(4); last SWITCH; } } } # End SWITCH. }; # End eval. warn "Error in backup, error: $@\n" if $@; # Close the logfile so I can mail it. close STDERR; close STDOUT; exit; ############################################################################################################## ############################################## Subroutines ################################################# ############################################################################################################## sub ORA_BACKUP { # declair variables. Statement handles that return data start with get, # statement handles that don't return data start with sth. my $dbh; # Database handle to connect to oracle databases. my %attr; # Hash for the database handle attributes. my $ERR_NO; # The DBI error number. ORA-xxxxx my $ERR_TXT; # The text that goes along with $ERR_NO. my @files; # the old backup files that are about to be deleted. my $file; # the file that is about to be deleted. my $get_tablespace; # statement handle for the names of the tablespaces. my @tablespace_name; # all of the tablespaces. my $tablespace_name; # the name of the tablespace that is being backed up. my $sth_tbs_mode; # statement handle to change the mode of the tablespace. my $get_datafile; # statement handle for the names of the datafiles. my @datafile_name; # an array that has all of the datafile names in a tablespace. my $datafile_name; # the name of the datafile that is being backup including the path. my @dbf; # contains the names of the directories and the name of the datafile. my $sth_control_file; # statement handle to backup control file to trace. my $ctlfl; # controlfile. my $quoted_ctlfl; # quoted controlfile. ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime; ### Attributes to disable automatic error checking. %attr= ( PrintError => 0, RaiseError => 0, AutoCommit => 0, ); $dbh = DBI->connect( "dbi:Oracle:$ENV{ORACLE_SID}","bkup","bkup", \%attr ); ($ERR_NO,$ERR_TXT) = split /:/, $DBI::errstr, 2; warn "Error contact the Database Administrator!\nError Number: $ERR_NO\nError Text: $ERR_TXT\n" if (defined $ERR_NO); # Check to see if there was an error if so, print message and continue on to the backup of the file system. # If there were no errors backup the database. if (defined $ERR_NO) { print"Error trying to connect to $ENV{ORACLE_SID}. Continuing with the rest of the backup.\n"; } else { printf STDOUT "BEGIN BACKUP OF $ENV{ORACLE_SID} AT %02d:%02d %02d/%02d/%04d.\n",$hour,$min,$mon+1,$mday,1900+$year; # removing old backup files. opendir DIRH, $BACKUP_HOME or die "Could not read $BACKUP_HOME. $!\n"; @files = readdir DIRH; while (<@files>) { unless ($_ eq "." || $_ eq "..") { $file = join '/', $BACKUP_HOME,$_; unlink $file or die "Could not unlink $file. $!\n"; } } closedir DIRH; $get_tablespace = $dbh->prepare( "SELECT tablespace_name FROM DBA_TABLESPACES" ); $get_tablespace->execute() or warn "Could not get tablespace name: $DBI::errstr.\n"; while ( @tablespace_name = $get_tablespace->fetchrow_array() ) { $tablespace_name = uc(sprintf "%s", @tablespace_name); print "Tablespace Name: $tablespace_name.\n"; $sth_tbs_mode = $dbh->prepare("ALTER TABLESPACE $tablespace_name BEGIN BACKUP"); $sth_tbs_mode->execute() or warn "Could not alter tablespace: $DBI::errstr.\n"; $get_datafile = $dbh->prepare("select file_name from dba_data_files where tablespace_name = ?"); $get_datafile->bind_param(1,$tablespace_name); $get_datafile->execute() or warn "Could not get datafile name: $DBI::errstr.\n"; while ( @datafile_name = $get_datafile->fetchrow_array() ) { # This will compress the datafile to the backup home directory # without having to copy it first. foreach $datafile_name (@datafile_name) { @dbf = split /\//, $datafile_name; # the last element becomes the first element. # the last element is the name of the datafile. unshift(@dbf, pop(@dbf)); `/usr/local/bin/gzip -c $datafile_name > $BACKUP_HOME/$dbf[0].gz`; print STDOUT "Compressed $datafile_name to $BACKUP_HOME.\n"; } } $sth_tbs_mode = $dbh->prepare("ALTER TABLESPACE $tablespace_name END BACKUP"); $sth_tbs_mode->execute() or warn "Could not alter tablespace: $DBI::errstr.\n"; } $sth_control_file = $dbh->prepare("ALTER DATABASE BACKUP CONTROLFILE TO TRACE"); $sth_control_file->execute() or warn "Could not backup controlfile. $DBI::errstr.\n"; print STDOUT "Backed up controlfile to trace.\n" unless ($DBI::errstr); $ctlfl = join '/', $BACKUP_HOME,"controlfile.bkp"; $quoted_ctlfl = $dbh->quote($ctlfl); $sth_control_file = $dbh->do("ALTER DATABASE BACKUP CONTROLFILE TO $quoted_ctlfl") or warn "Could not backup controlfile to $quoted_ctlfl.\n $DBI::errstr\n"; print STDOUT "Backed up controlfile to $BACKUP_HOME.\n" unless ($DBI::errstr); print STDOUT "Removing archive logs older than 2 days.\n"; find (\&RM_OLD_ARCH, $ARCHIVE_HOME); print STDOUT "Compressing archive logs older than 12 hours.\n"; find (\&GZIP_ARCH, $ARCHIVE_HOME); ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime; printf STDOUT "END BACKUP OF $ENV{ORACLE_SID} AT %02d:%02d %02d/%02d/%04d.\n",$hour,$min,$mon+1,$mday,1900+$year; $dbh->disconnect or warn "Disconnection Failed! $DBI::errstr\n"; } } # End ORA_BACKUP. ######################################################################### sub RM_OLD_ARCH { unlink $_ if -M > 2; } # Needed for the find command. ######################################################################### sub GZIP_ARCH { `/usr/local/bin/gzip $_` if -M > .5; } # Needed for the find command. ######################################################################### sub BACKUP($) { # variables my $level; # todays backup level. my $status; # the status of the tape dive. my $args; # the arguments for ufsdump. my $archive_log; # a log file on disk of each file system that is backed up. my @bkup; # array to hold what file systems were backed up. my $bk; # used to print each file system. my @files; # the name of the directories on /u01. my $nfs; # the name of the file system with the "/" removed. my $fs; # the name of the file system. my $dir; # the directory that is being backup on /u01. i.e. /u01/home. my $k = 0; # a counter. my $block = 128; # the blocking factor for ufsdump. bf * OS Block size: 128*512=64K my $device = "/dev/rmt/0ubn"; # tape device. my $PATH = '/bin:/sbin:/usr/sbin:/usr/bin:/usr/local/bin'; $level = shift; $year = $year+1900; $mon = $mon+1; print "\tDAY\t\t:=$day\n\tDATE\t\t:=$mday\n\tTIME\t\t:=$hour:$min\n\t"; print "MONTH\t:=$mon\n\tYEAR\t\t:=$year\n\tLEVEL\t:=$level\n"; print "Today is scheduled for a level $level backup.\n"; system (`/bin/mt -t $device fsf 2 2>/dev/null`) == 0 or die "No tape in drive. Aborting backup!\n"; system (`/bin/mt -t $device rewind`) == 0 or warn "Could not rewind tape.\n"; } $args = join '', $level,"unbaf"; #!! You don't want to back up drives with datafiles #!! so include only the drives you want backed up here. foreach $fs (@fs) { if ( $fs eq "/u15" || $fs eq "/" || $fs eq "/var" || $fs eq "/opt" ) { unless ( $fs eq "/") { $nfs = substr($fs,1); } else { $nfs = "root"; } $archive_log = "/logs/$level\_$nfs\_$today.log"; system "/usr/sbin/ufsdump $args $block $archive_log $device $fs"; $bkup[$k++] = $fs; } if ($fs eq "/u01" ) { $nfs = substr($fs,1); opendir DIRH, $fs or die "Could not read $fs. $!\n"; @files = readdir DIRH; while (<@files>) { unless ($_ eq 'oradata' || $_ eq "." || $_ eq "..") { my $dir_name = join '_', $nfs,$_; $dir = join'/', $fs,$_; $archive_log = "/logs/ level\_$dir_name\_$today.log"; system "/usr/sbin/ufsdump $args $block $archive_log $device $dir"; $bkup[$k++] = $dir; } } close DIRH; } } print "\nFinished level $level backup of\n"; foreach $bk (@bkup) { print "$bk "; } print "\nat: "; print UnixDate(ParseDate("today"),"%I:%M:%S %p"),"\n"; print "Rewinding tape.\n"; system (`/bin/mt -t $device rewind`) == 0 or warn "Could not rewind tape!"; print "Finished Rewinding tape at: ", UnixDate(ParseDate("today"),"%I:%M:%S %p"),"\n"; print "Ejecting tape. End of backup.\n"; system (`/bin/mt -t $device offline`) == 0 or warn "Problem ejecting tape!"; } # End BACKUP. |
|
---|
Replies are listed 'Best First'. | |
---|---|
Re: Oracle DB & Server Backup
by gav^ (Curate) on Mar 29, 2002 at 04:13 UTC | |
by samgold (Scribe) on Mar 29, 2002 at 04:57 UTC | |
by gav^ (Curate) on Mar 29, 2002 at 14:34 UTC | |
by samgold (Scribe) on Mar 30, 2002 at 02:07 UTC | |
Re: Oracle DB & Server Backup
by dba (Monk) on Dec 29, 2004 at 04:54 UTC |
Back to
Code Catacombs