Beefy Boxes and Bandwidth Generously Provided by pair Networks
Syntactic Confectionery Delight
 
PerlMonks  

Oracle DB & Server Backup

by samgold (Scribe)
on Mar 29, 2002 at 02:42 UTC ( [id://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.
Replies are listed 'Best First'.
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.

Log In?
Username:
Password:

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

How do I use this?Last hourOther CB clients
Other Users?
Others examining the Monastery: (2)
As of 2024-11-10 07:38 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    chatterbot is...






    Results (37 votes). Check out past polls.