home

Cribbage Game - By my brother Craig.

Moving Redo Logs - Mark Hessel

Key Words: System move, moving redo logs, moving undo logs, moving database change logs

I divised the following to move change logs from one system to another in this case an SAP system with an oracle database.

Notes:
- Assumes ssh connection is setup from origin to destination db servers.  scp can change to rcp, if that is setup.  scp and rcp formats are the same.
 
- Origin and destinations have to be changed  for new servers.
 
- A db duplex directory has to be setup to hold at least the max size of two days of redo logs on the origin.
 
- The destination directory should be able to hold at least 7 days of max redo log usage.
 
- The gzip origin needs to be setup in this script.
   * The gzip version needs to be the same on both servers for gzip to work
   * The script can send the unzipped file and the gzipped files or just the gzipped files.  the script is currently set to send just gzipped files.
        - I did this for testing purposes on the DEV and TEST systems.
 
- The script assumes all files to move are named *.dbf.  It never sends the newest dbf.  That has to be done manually when the cutover happens.
  * or I guess you can create a fake redo log in the duplex directory to send the last dbf file -- just thought of that one.
 
- The redo log script should be in the redo log duplex directory on the origin server
 
- I ran the script via crontab every 5 minutes with a command script in the <sid>adm's home directory.
redo_logs.cmd script contents
perl /<full origin duplex path/redo_logs.pl
I find it easier to turn the process off and on that way.  To stop the process just put a # in front of the perl command in the redo_logs.cmd script
 
- Test the script before the duplex directory is running, by running the commands in the origin duplex directory.
touch 1.dbf
touch 2.dbf
touch 3.dbf
3.dbf should remain and the other files should be sent.
 
You can test it in the duplex directory first by simply running the script "perl redo_logs.pl" and then again using crontab.
 
- the following log files are created in the duplex directory
#process files
$error_file = $redo_log_path."error.log";                          # errors during transfer (should initially exist)
$skipped_process_file = $redo_log_path."skipped_processing.log";   # file notes time of skipped processing because  busy
$process_history = $redo_log_path."process_history.log";           # date|ls entry|transfer time seconds
$stdout = $redo_log_path."stdout";                                 # stdout for system commands
$errout = $redo_log_path."errout";                                 # errout for system commands
note that the file error.log should exist in the duplex directory - touch error.log.   The script always appends to this file.
I could have had the script create it initially, but I was just lazy.
 
- the status file redo_process_status must be created with "0" in the file in the duplex directory -- again just lazy
$status_file = $redo_log_path."redo_process_status";               # this file must initially exist (with 0) for process to work
echo 0 > redo_process_status
in the duplex directory
This file is changed to 1 when the redo_logs.pl script is running.  This is checked in the script, so if the script is still
running after 5 minutes, it just stops.   The script changes the status file to 0 when the process is done.
 
- error checking emails
sends emails/pager notification to
#notification
$adm_email = "mark_hessel\@somecompany.com";                              
$pager_email = "5419171194\@archwireless.net";
$adm_email if there are any send errors.
 
If the redo_process_status file is over 1 hour old, a pager message will go to the $pager_email.  I put this in to do
immediate notification of a stuck process.  There's only 2 days of redo logs storage on the origin.
 
That's about it.  If you have any question let me know.  I think that the transfer rate from city1 to city2 was about
2 gig per hour for a single stream send.  When you have to do this, you should test the send rate from origin to destination
to see what you get.
 
Multiple scp commands could probably send at a higher rate, but since the production zipped redo logs were under that rate
so I didn't try to write that code. 

perl script

# Mark Hessel 20070317
# this process assumes that ssh is setup between the source system and the destination system.
# varibles to change for each system
# the last file *.dbf file will have to be moved manually

$redo_log_path = "/oracle/SID/saparch_DCC/";                                     # source system
$redo_log_destination = "sidadm\@sid_name.xyz.com:/oracle/SIS/sapdata1/";   # destination system scp style

 

# gzip directory
$gzip_dir = "/usr/contrib/bin/";

# turn off moving of unzipped files; 1 send both files, 0 send just gzipped files
$send_non_gzipped = 0;

#notification
$adm_email = "mark_hessel\@somecompany.com";
$pager_email = "5419171194\@archwireless.net";

#process files
$status_file = $redo_log_path."redo_process_status";               # this file must initially exist (with 0) for process to work
$error_file = $redo_log_path."error.log";                          # errors during transfer (should initally exist)
$skipped_process_file = $redo_log_path."skipped_processing.log";   # file notes time of skipped processing because  busy
$process_history = $redo_log_path."process_history.log";           # date|ls entry|transfer time seconds
$stdout = $redo_log_path."stdout";                                 # stdout for system commands
$errout = $redo_log_path."errout";                                 # errout for system commands

# check if another redo_log process is running.
# open file processing if 1 is 0 if not.


if (open(INPUT, "<$status_file")){}else{`mailx -s"$status_file doesn't exist!" $adm_email < /dev/null`; exit;}


while (<INPUT>)
{
   # strip leading and ending whitespace
   $_ =~ s/^\s+//;
   $_ = ~ s/\s+$//;

   # if process file is 0, continue to process
   if (! $_)
   {
      last;
   }
   else # log date and time in skipped_processing, if status file is over 1 hour old, page me and exit
   {
      if (open(OUTPUT, ">>$skipped_process_file")){}else{`mailx -s"$skipped_process_file problem!" $adm_email < /dev/null`; exit;}
      $date = `date`;
      print OUTPUT $date;
      close OUTPUT;
      close INPUT;
      # now check if the status file is over 1 hour, if true page me
      if (-M $status_file > 1/24)
      {
         `mailx -s"$process_file over 1 hour old!"  $pager_email < /dev/null`;
      }
      exit;
   }
}

close INPUT;

#begin processing  put 1 in the status file.
if (open(OUTPUT, "> $status_file")){}else{`mailx -s"problem writing to $status_file!" $adm_email < /dev/null`; exit;}
print OUTPUT "1";
close OUTPUT;


# do ls -ltr into array, move oldest files and leave the newest - just to make sure newest file is not created when process to move
# is being done

chdir ($redo_log_path);


@files_to_move = (); # initialize
@files_to_move = `ls -ltr *.dbf`;  # get output of newest to oldest files into array

#             $#files_to_move = 1 less then items in array
$process_to = $#files_to_move;
$i= 0;

$file_transmission_error = 0;
$gzip_error = 0;
foreach $record (@files_to_move)
{
   if ($i == $process_to){next;} # skip processing of newest file

# ***********************************************************************************************  
# ******************* this is where all the work is being done **********************************
# ***********************************************************************************************  
   ($permissions, $access_num, $owner, $group, $size, $month, $day, $time, $file) = split(/[ \t\n]+/, $record); #split on white space

   $i++; # processed on file, error or not


   if ($send_non_gzipped)
   {
      $start_time = time;
      # copy the file
      $error = system"(scp -pq $file $redo_log_destination$file  > $stdout) 2> $errout";
      # if the copy fails, skip  write to error_file
      if ($error) # system returns non 0 for error
      {
         $file_transmission_error = 1;
         if (open(ERR, ">> $error_file")){}else{`mailx -s"problem writing to $error_file!" $adm_email < /dev/null`; exit;}
         $date =  `date`;
         print ERR $date;
         print ERR $record;
         `cat $errout >> $error_file`;
         close ERR;
         next;
      }
  
      $end_time = time;
      $current_run_time = $end_time - $start_time;
   #   system"rm -f $file"; 
      if (open(LOG, ">> $process_history")){}else{`mailx -s"problem writing to $process_history!" $adm_email < /dev/null`; exit;}
      chop $record;
      $date = `date`;
      chop $date;
      print LOG "$date|$record|$current_run_time\n";
      close LOG;
   } # if ($send_non_gzipped)

# **************** send gzipped files *********************
   $gzfile = $file.".gz";
   $gzip_exe = $gzip_dir."gzip";
   $error = system "($gzip_exe $file > $stdout) 2> $errout";
   if ($error) # system returns non 0 for error
   {
      $gzip_error = 1;
      if (open(ERR, ">> $error_file")){}else{`mailx -s"problem writing to $error_file!" $adm_email < /dev/null`; exit;}
      $date =  `date`;
      print ERR $date;
      print ERR $gzfile;
      `cat $errout >> $error_file`;
      close ERR;
      next;
   }
  
   $start_time = time;
   $error = system"(scp -pq $gzfile $redo_log_destination$gzfile  > $stdout) 2> $errout";
   # if the copy fails, skip  write to error_file
   if ($error) # system returns non 0 for error
   {
      $file_transmission_error = 1;
      if (open(ERR, ">> $error_file")){}else{`mailx -s"problem writing to $error_file!" $adm_email < /dev/null`; exit;}
      $date =  `date`;
      print ERR $date;
      print ERR $record;
      `cat $errout >> $error_file`;
      close ERR;
      next;
   }
  
   $end_time = time;
   $current_run_time = $end_time - $start_time;
   $record = `ll $gzfile`;
   system"rm -f $gzfile";

   if (open(LOG, ">> $process_history")){}else{`mailx -s"problem writing to $process_history!" $adm_email < /dev/null`; exit;}
   chop $record;
   $date = `date`;
   chop $date;
   print LOG "$date|$record|$current_run_time\n";
   close LOG;
# **************** test sending gzipped files *********************

 


} # foreach record in list of files to send

if ($file_transmission_error)  # this just emails the error type.  Look at the logs for errors
{
   `mailx -s"problem scping file!" $adm_email < /dev/null`; 
}

if ($gzip_error)               # this just emails the error type.  Look at the logs for errors
{
   `mailx -s"problem gzipping file!" $adm_email < /dev/null`;
}

 


# done processing put 0 in the status file.
if (open(OUTPUT, "> $status_file")){}else{`mailx -s"problem writing to $status_file!" $adm_email < /dev/null`; exit;}
print OUTPUT "0";
close OUTPUT;

 


# history report daily