Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl-Sensitive Sunglasses
 
PerlMonks  

Oracle DB & Server Backup

by samgold (Scribe)
on Mar 29, 2002 at 02:42 UTC ( #155184=sourcecode: print w/ replies, xml ) Need Help??

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.
Backup script Backup.pl
#!/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.

Comment on Oracle DB & Server Backup
Re: Oracle DB & Server Backup
by gav^ (Curate) on Mar 29, 2002 at 04:13 UTC
    A couple of quick points:
    • Save uppercase functions for constants
    • You don't have to declare you variables at the top of a function. Declare them where you need them
    • use strict and warnings
    • Use ++ and += to simplify
    • Don't use backticks in a void context
    • You can do a delete @hash{qw(key1 key2 key3)}
    • foreach my $var (@list)
    That's all for now.

    gav^

      What do you mean Don't use backticks in a void context? I think I know what you mean, what would be a better way of doing that?
        Instead of using backticks, use system.

        gav^

Re: Oracle DB & Server Backup
by dba (Monk) on Dec 29, 2004 at 04:54 UTC
    I appreciate the effort, but most of the work can be done by rman more efficiently.

Back to Code Catacombs

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: sourcecode [id://155184]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others examining the Monastery: (5)
As of 2015-07-07 06:52 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (87 votes), past polls