Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> perl script for hot backups

perl script for hot backups

From: <aaronurbain_at_bigfoot.com>
Date: Thu, 11 Nov 1999 16:42:28 GMT
Message-ID: <80erlj$10n$1@nnrp1.deja.com> 







I'd like to offer the group my perl script for doing hot backups.
Its been working well for a month, and is my first attempt at a perl
script.


The script calls files which perform the action indicated in the called
file name.  It should be simple to recreate them; they are not included
here.  If you're stuck, send me your email address on the back of a
twenty dollar bill........



# -------------------------------------------------------------


# This script handles doing a hot backup of the Oracle instance.


# Magic contained within.


# -------------------------------------------------------------





use Time::localtime;


use diagnostics;


use strict;




# -----------------------------------------------------


# Note: required files for this script


#  need any unix style grep for NT, in the path, called 'grep'


#  need plus33 from oracle client software installation


# -----------------------------------------------------






# -------------------------------------------


# Alter these parameters to suit the site


# These params MUST be adjusted at setup time


# -------------------------------------------



my $ora_userid =        "sys";                                       #
Oracle userid
my $ora_password =      "smr2pass";                                  #
Oracle userid password
my $ora_servicename =   "mp2";                                   #
SQL*NET connect string
my $working_dir =       "e:\\working";                               #
every file here gets erased at the start of every run
my $backup_dir =        "e:\\backup\\full_backup";                   #
where we are backing everything up to
my $init_ora_file =     "d:\\orant\\admin\\smr2\\pfile\\initsmr2.ora";


# where is the fucking init.ora?


my $user_dump_dest_dir= "d:\\orant\\admin\\smr2\\udump";    	     #


value lives in init.ora


# note: due to Oracle on NT bug #381394, you must


# use log_archive_format = ARC%s.%T in your init.ora


my $archive_log_dir =   "d:\\ARCHIVE_LOG_DEST_DIR";


# value lives in init.ora, where we copy the archive logs from


my $oracle_script_dir = "d:\\orant\\admin\\smr2\\scripts\\hot_backup";


# the dir where the oracle scripts live, need to make that directory the


current dir


my $archive_log_dest_dir="e:\\backup\\archive_logs";

my $keepdays            = 10;                                        #


how many days do we keep archived log files around?





# ---------------------------------------


# build some filenames we will need


# no adjustment needed here at setup time


# ---------------------------------------


my $sqlplus =           "plus33 -s


$ora_userid/$ora_password\@$ora_servicename" ;

my $log_fn =            "$working_dir\\logfile.log";
my $tracefile_location_fn = "$working_dir\\tracefile_location.txt" ;
my $cf_sql_fn =         "$backup_dir\\controlfile.sql";
my $cf_binary_fn =      "$backup_dir\\controlfile.bin";
my $redologfiles_fn =   "$working_dir\\redologfiles.txt" ;
my $datafiles_fn =      "$working_dir\\datafiles.txt" ;
my $controlfiles_fn =   "$working_dir\\controlfiles.txt" ;
my $tablespaces_fn =    "$working_dir\\tablespaces.txt" ;
my $test_mode =         0;    #set to nonzero for testing.







# -----------------------------------------------------




# first, we need to make the directory where the oracle


# scripts live the current directory so we dont have to


# explicitly path to them when we invoke them later on.


# -----------------------------------------------------


if (!chdir($oracle_script_dir))


   {die "Cannot make '$oracle_script_dir' the working dir.\n please
check setup. $!" ; }




# --------------------------------------------


# erase the files from the working dir


# from the last run


# --------------------------------------------


&clear_working_directory();





# --------------------------------------------


# tag the start of the error log with the time


# --------------------------------------------


my $current_time = "current time = " . ctime();
system("echo $current_time >> $log_fn") ;





# ---------------------------------------------------


# create the files which define what files need to be


# backed up


# ---------------------------------------------------


&create_datafiles() ;





# -------------------------------------


#  we have to backup the:


#     control files


#     init.ora file


#     log files


#     archive log files


#     all datafiles in each tablespace.


# -------------------------------------



&backup_controlfiles();
&backup_initora();
&backup_redo_log_files();
&backup_archive_log_files();
&backup_tablespace_datafiles();





exit;






sub backup_tablespace_datafiles()


{


   print "Backing up tablespaces: \n" ;




   # make an array containing our tablespace names
   open(TSFILE, $tablespaces_fn)


      or errorhandle("Error opening $tablespaces_fn. $!\n") ;



   my @tablespaces = <TSFILE>;


   foreach my $tablespace (@tablespaces)
   {

      $tablespace =~ s/^\s+//;	#get rid of control chars
      $tablespace =~ s/\s+$//;	#get rid of spaces
      print "  $tablespace\n" ;

      # ------------------------------------------
      # need to put the tablespace in backup mode.
      # ------------------------------------------
      my $cmd = "$sqlplus \@begin_backup.sql $tablespace >> $log_fn" ;
      if (!$test_mode) { system($cmd) && errorhandle("Error begin
backup. $cmd"); }
      else { print "  cmd = '$cmd'\n";}


      # ----------------------------
      # go get the list of datafiles
      # ----------------------------
      my $tsdatafile_fn = "$working_dir\\$tablespace.datafile" ;

      open(TSDATAFILE, $tsdatafile_fn)
         or errorhandle("Error opening $tsdatafile_fn. $!\n") ;

      my @tsdatafiles = <TSDATAFILE> ;
      foreach my $tsdata_fn (@tsdatafiles)
      {
         $tsdata_fn =~ s/^\s+//;	#get rid of control chars
         $tsdata_fn =~ s/\s+$//;	#get rid of spaces
         my $cmd = "copy /v $tsdata_fn $backup_dir" ;
         if (!$test_mode) { system($cmd) && errorhandle("Error copying
init.ora file. $cmd ");}
         else { print "  cmd = '$cmd'\n";}
      }


      # ---------------------------------------
      # put the tablespace back in regular mode
      # ---------------------------------------
      $cmd = "$sqlplus \@end_backup.sql $tablespace >> $log_fn" ;
      if (!$test_mode) { system($cmd) && errorhandle("Error begin
backup. $cmd"); }
      else { print "  cmd = '$cmd'\n";}


   }


}




sub backup_initora()


{


   print "Backing up init.ora file\n" ;
   my $cmd = "copy /v $init_ora_file $backup_dir" ;
   if (!$test_mode) { system($cmd) && errorhandle("Error copying
init.ora file. $cmd ");}


   else { print "  cmd = '$cmd'\n";}


}





sub backup_archive_log_files()


{

   # ---------------------------------------------------------------
   # erase all files in $archive_log_dest dir that are over
   # are over $KEEP_DAYS old.
   # ---------------------------------------------------------------


   # read file names into array @filenames, exclude . & ..
   opendir(ARCHDIR,"$archive_log_dir")


      or errorhandle("unable to open $archive_log_dir for reading.
$!\n");


   my @filenames = grep (!/^\.\.?$/, readdir (ARCHDIR));
   closedir ARCHDIR;



   # ---------------------------------------------


   # check modification dates against $time_limit.

   # ---------------------------------------------
   my $file;
   foreach $file (@filenames)          # work through the array of names
   {
      if (-M "$archive_log_dir/$file" > $keepdays)
      {
         print "need to delete $archive_log_dir/$file\n";
         unlink "$archive_log_dir/$file"
            or errorhandle("error deleting $archive_log_dir/$file,
$!\n") ;
      }




   }




   # --------------------




   # switch the log twice

   # --------------------




   print "Alter system switch logfile\n";
   my $cmd = "$sqlplus \@switch_logfile.sql >> $log_fn" ;
   if (!$test_mode) { system($cmd) && errorhandle("Error switching log
$cmd "); }


   else { print "  cmd = '$cmd'\n";}




   # ---------------------------------------------------------------
   # Copy all files in $archive_log_dir to archive_log_dest_dir
   # Verify all files have copied correctly
   # ---------------------------------------------------------------


   # read file names into array @filenames, exclude . & ..

   # ---------------------------------------------------------------
   opendir(EXPDIR,"$archive_log_dir")
      or error_handler("unable to open $archive_log_dir for reading.


$!\n");


   @filenames = grep (!/^\.\.?$/, readdir (EXPDIR));
   closedir EXPDIR;



   # ---------------------------------------------


   # check modification dates against $time_limit.

   # ---------------------------------------------
   print "Moving archive log files:\n" ;
   foreach $file (@filenames)          # work through the array of names
   {
      # -----------------------------------------------------
      # windoze likes to show a zero filesize for
      # files that are not closed.
      # If we run across one, that is most likely because the
      # archiver is moving a redo log file to arch directory.
      # loop until he is done
      # -----------------------------------------------------
      my $archfilename = "$archive_log_dir\\$file";
      while (!-s $archfilename)
      {
         print "Encountered an open file $archfilename, waiting 1
second\n";
         sleep 1;
      }


      # ------------------------------------
      # copy the file to the destination dir
      # ------------------------------------
      print "  $archfilename copied to $archive_log_dest_dir" ;
      my $cmd = "copy /v $archfilename $archive_log_dest_dir" ;
      if (!$test_mode) { system($cmd) && errorhandle("Error copying
init.ora file. $cmd ");}
      else { print "  cmd = '$cmd'\n";}


      # ---------------------------------
      # third, did it make it there there
      # ---------------------------------
      my $archdestfn = "$archive_log_dest_dir\\$file" ;
      if ((! -e $archdestfn) && (!$test_mode))
      {
         error_handle("$archdestfn does not exist.") ;
      }


      # ---------------------------
      # lastly, delete the original
      # ---------------------------
      print "    and deleted from archive_log_dir\n" ;
      if (!$test_mode)
      {
         unlink($archfilename)
            or errorhandler("Error deleting $archfilename. $!\n");
      }




   }


}





sub backup_redo_log_files()


{


   # the log file locations live in the file $working_dir .
"logfiles.txt"


   # we will open that file and for each line, copy the file to the
   # backup dir.



   print "Backing up Redo Log Files\n";



   open(LOGFILES, $redologfiles_fn)


      or	errorhandle ("could not open $redologfiles_fn") ;



   while (<LOGFILES>)


   {

   	my $logfile = $_ ;
      $logfile =~ s/^\s+//;	#get rid of control chars
      $logfile =~ s/\s+$//;	#get rid of spaces
   	print "  $logfile \n";
   	my $cmd = "copy /v $logfile $backup_dir" ;
   	if (!$test_mode) { system($cmd) && errorhandle ("Error copying


log file $logfile. $cmd "); }


   }


}






sub backup_controlfiles ()


{


   # This is a two step process.
   #  1) have oracle make a text control file in the backup dir
   #  2) backup the binary control file to the backup dir.


   # ----------------------------------------------


   # get oracle to backup our control file to trace

   # ----------------------------------------------


   print "Backing up text control file to trace\n";
   my $cmd = "$sqlplus \@cf_to_trace.sql >> $log_fn" ;
   if (!$test_mode) { system($cmd) && errorhandle("SEE ERROR LOG $cmd
"); }


   else { print "  cmd = '$cmd'\n";}




   # --------------------------------------------------------------


   # lets make sure that the $user dump dest directory exists first

   # --------------------------------------------------------------


   if (!-e $user_dump_dest_dir)


   {


      errorhandle("Please verify setting of user_dump_dest_dir in setup
section of this script\n") ;


   }



   # -------------------------------------------------------------
   # since Oracle puts the file it just created in USER_DUMP_DEST,
   # we have to go find it and move it to the backup area by hand.


   # use grep to put the name of our trace file into a holder file

   # -------------------------------------------------------------


   $cmd = "grep -l \"CREATE CONTROLFILE\" $user_dump_dest_dir\\*.trc >
$tracefile_location_fn";


   if (!$test_mode) { system($cmd) && errorhandle( "Did trace file get
created? $cmd $!"); }


   else { print "  cmd = '$cmd'\n";}


   if (! -e $tracefile_location_fn)


   {


      error_handler("tracefile_location_fn $tracefile_location_fn does
not exist.") ;


   }




   # -----------------------------------------------------------


   # now read that holder file to get the name of our trace file

   # -----------------------------------------------------------
   open(FILE, $tracefile_location_fn)
      or error_handler("tracefile_location_fn $tracefile_location_fn


does not exist.") ;

   my $tracefile = <FILE>;
   $tracefile =~ s/^\s+//;	#get rid of control chars
   $tracefile =~ s/\s+$//;	#get rid of spaces


   print "  tracefile = $tracefile\n";




   # ---------------------------------------------------


   # perform the copy of our tracefile to the backup_dir

   # ---------------------------------------------------


   print "Copying text control file $tracefile to backup dir\n" ;
   $cmd = "copy /V $tracefile $cf_sql_fn";
   if (!$test_mode) { system($cmd) && errorhandle("Error copying the
text controlfile $cmd"); }


   else { print "  cmd = '$cmd'\n";}


   if ((!$test_mode) && (! -e $cf_sql_fn))
   {


      errorhandle("text controlfile $cf_sql_fn does not exist.") ;
   }




   # ------------------------------------------


   # delete the trace file file we just created

   # ------------------------------------------


   if (!$test_mode)


   {

      unlink($tracefile)
         or errorhandle("Error unlinking $tracefile. $!\n") ;


   }




   #




   # Oracle will refuse to overwrite the binary control file
   # if it already exists, so we have to delete it first should it
exist.


   #




   if (-e $cf_binary_fn)


   {

      print("  deleting existing binary control file first\n");
      unlink($cf_binary_fn)
         or errormessage("Error unlinking binary control file


$cf_binary_fn $!\n") ;


   }



   # -----------------------------------------


   # now we can backup the binary control file

   # -----------------------------------------


   print "Backing up binary control file to $backup_dir\n";
   $cmd = "$sqlplus \@cf_to_location.sql $cf_binary_fn >> $log_fn";
   if (!$test_mode) { system($cmd) && errorhandle("Error backing up
control file $cmd"); }


   else { print "  cmd = '$cmd'\n";}


   if ((!$test_mode) && (! -e $cf_binary_fn))
   {


      errorhandle("text controlfile $cf_binary_fn does not exist.") ;
   }


}







sub create_datafiles()


{

   # ----------------------------------------------------


   # create a file containing a list of Oracle data files

   # ----------------------------------------------------


   print "Getting list of datafiles from Oracle\n";
   my $cmd = "$sqlplus \@gen_datafiles.sql $datafiles_fn >> $log_fn";
   if (!$test_mode) { system($cmd) && errorhandle("Error getting
datafile list. $cmd $!"); }


   else { print "  cmd = '$cmd'\n";}


   if ((!$test_mode) && (! -e $datafiles_fn))
   {


      errorhandle("$datafiles_fn not found $cmd $!") ;
   }



   # ------------------------------------------------


   # create a file containing a list of control files

   # ------------------------------------------------


   print "Getting list of control files from Oracle\n";
   $cmd = "$sqlplus \@gen_controlfiles.sql $controlfiles_fn >> $log_fn"
;


   if (!$test_mode) { system($cmd) && errorhandle("Error getting
controlfile list. $cmd $!"); }


   else { print "  cmd = '$cmd'\n";}


   if ((!$test_mode) && (! -e $controlfiles_fn))
   {


      errorhandle("$controlfiles_fn not found $cmd $!") ;
   }


   # ------------------------------------------------


   # create a file containing a list of redo log files

   # ------------------------------------------------


   print "Getting redo log files from Oracle\n";
   $cmd = "$sqlplus \@gen_logfiles.sql $redologfiles_fn >> $log_fn" ;
   if (!$test_mode) { system($cmd) && errorhandle("Error getting
redologfiles list. $cmd $!"); }


   else { print "  cmd = '$cmd'\n";}


   if ((!$test_mode) && (! -e $redologfiles_fn))
   {


      errorhandle("$redologfiles_fn not found $cmd $!") ;
   }



   # ----------------------------------------------


   # create a file containing a list of tablespaces

   # ----------------------------------------------


   print "Getting tablespaces from Oracle\n";
   $cmd = "$sqlplus \@gen_tbs.sql $tablespaces_fn >> $log_fn";
   if (!$test_mode) { system($cmd) && errorhandle("Error getting
tablespaces list. $cmd $!"); }


   else { print "  cmd = '$cmd'\n";}


   if ((!$test_mode) && (! -e $tablespaces_fn))
   {


      errorhandle("$tablespaces_fn not found $cmd $!") ;
   }



   # ------------------------------------------------------------
   # work throught the tablespace datafile and


   # get the datafiles associated with each one into its own file

   # ------------------------------------------------------------


   if (!$test_mode)


   {

      open(TABLESPACES, $tablespaces_fn) or
      	errorhandle("could not open $tablespaces_fn") ;

      while (<TABLESPACES>)
      {
         my $tablespace = $_ ;
         $tablespace =~ s/^\s+//;	#get rid of control chars
         $tablespace =~ s/\s+$//;	#get rid of spaces
      	print "Getting datafiles for tablespace $tablespace from
Oracle\n";
      	my $tablespace_datafiles_fn =
"$working_dir\\$tablespace.datafile" ;
      	# This stored proc takes two parameters
      	#     1) the tablespace name which we need a listing of
datafiles.
   	   #     2) the filename of the file which will hold the above
listing.
      	$cmd = "$sqlplus \@gen_tbs_files.sql $tablespace
$tablespace_datafiles_fn >> $log_fn" ;
         if (!$test_mode) { system($cmd) && errorhandle("Error getting
datafile list for TS $tablespace. $cmd $!"); }
         else { print "  cmd = '$cmd'\n";}
         if ((!$test_mode) && (! -e $tablespace_datafiles_fn))
         {
            errorhandle("$tablespace_datafiles_fn not found. $cmd $!") ;
         }
      }




   }


}






sub clear_working_directory()


{


   print "Deleting files from working directory $working_dir:\n" ;


   # --------------------------------------------------------------
   # erase the files in the working directory from the previous run
   # NOTE: I cant get a variable to expand inside the glob so


   # it is hard coded for the moment.

   # --------------------------------------------------------------

   opendir(ARCHDIR, "$working_dir")  ;
      #or errorhandle("unable to open $working_dir for reading. $!\n");


   my @filenames = grep (!/^\.\.?$/, readdir (ARCHDIR));
   closedir ARCHDIR;



   # ----------------




   # delete the files

   # ----------------




   my $file;


   my $name;


   foreach $file (@filenames)          # work through the array of names
   {

      $name = "$working_dir\\$file";
      print "  $name\n" ;
      if (!$test_mode)
      {
         unlink($name);
         if (-e $name)
         {
            errorhandle("Error unlinking $name, $!") ;
         }
      }




   }


}




sub errorhandle()


{


   # need to see if we can come up with something more
   # intelligent to do here, like notify someone via email.
   die "\n$_[0]\n" ;


}




Sent via Deja.com http://www.deja.com/



Before you buy.
Received on Thu Nov 11 1999 - 10:42:28 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US