1 - Ajustar variáveis de ambiente no script, tais como ORACLE_HOME, ORACLE BASE e por ai vai.
2 - /etc/oratab configurado.
3 - Path '/u01/orabkp' deve existir ou deve ser alterado.
4 - Subdiretórios dentro do path devem existir: 'db', 'archives', 'spfile'.
5 - RMAN deve ser configurado para excluir archives não mais em uso para possível recovery.
6 - RMAN deve ser configurado para usar Flash Recovery Area.
7 - Ter um Postfix ou Sendmail com relay configurado para um servidor de e-mail.
Exemplo de uso no crontab:
# --- BKP ORA --- #
01 00 * * * $path/bin/ora-bkp-db-cold -m teste@teste.com > /dev/null 2>&1
Testado em: RedHat Enterprise 5 update 4 x86-64
#!/usr/bin/perl
#
# Description: Backup Oracle Database (Cold backup)
#
#
#Author:
# Gabriel Prestes (helkmut@gmail.com)
#
#04-30-2010 : Created
#05-25-2010 : Modified
# Modules
use strict;
use POSIX;
use Getopt::Long;
use File::Basename;
use DBI;
# ENVs
$ENV{"USER"}="oracle";
$ENV{"HOME"}="/u01";
$ENV{"ORACLE_HOME"}="/u01/app/oracle/product/10.2.0/db_1";
$ENV{"ORACLE_BASE"}="/u01/app/oracle";
$ENV{"ORACLE_SID"}="orcl";
$ENV{"LD_LIBRARY_PATH"}="/u01/app/oracle/product/10.2.0/db_1/lib:/lib:/usr/lib:/usr/local/lib";
# Global variables
our $name = basename($0, ".pl");
our $version="1.0";
our $date=strftime("%m-%d-%Y",localtime);
our $path = "/u01/orabkp";
our $log= "$path/log/backup-$date.log";
our ($opt_help, $opt_verbose, $opt_version, $opt_mailto);
# Program variables
our $flagmailover=0;
our $flagerror=0;
our $host=`/bin/hostname`;
sub main {
# --- Get Options --- #
getoption();
# --- My vars --- #
my @cmd;
my $rsyncfull;
my $finishdate;
# --- INFO --- #
#
# 1-> Verify oratab, if ok next
# 1-> Run dbshut, if ok next
# 2-> Make backup db, if ok next
# 3-> Make backup archives, if ok next
# 4-> Make backup controlfiles, if ok next
# 5-> Startup db, if ok end exit (OK) and send mail
#
# --- END INFO --- #
# --- Verify ORATAB --- #
@cmd=`cat /etc/oratab | grep :Y `;
if($cmd[0] !~ ":Y"){
$flagerror=5;
logger("ERROR - Setting your ORATAB");
}
# --- Shutdown dababase --- #
@cmd=`$ENV{'ORACLE_HOME'}/bin/dbshut`;
# --- Test return --- #
unless ($? == 0){
$flagerror=5;
logger("ERROR - Agent can't shutdown instance");
}
logger("OK - Instance down");
# --- Test destiny --- #
opendir(DIR, "$path/db") or do {logger("ERROR - Can't opendir $path/db : $!"); $flagerror=5;};
closedir DIR;
opendir(DIR, "$path/archives") or do {logger("ERROR - Can't opendir $path/archives : $!"); $flagerror=5;};
closedir DIR;
opendir(DIR, "$path/spfile") or do {logger("ERROR - Can't opendir $path/spfile : $!"); $flagerror=5;};
closedir DIR;
# --- Init backup --- #
if($flagerror==0){
# --- Database backup --- #
$rsyncfull=`/usr/bin/rsync -arvt \$ORACLE_BASE/product/10.2.0/oradata/orcl $path/db >> $log 2>&1`;
if($?!=0){
$flagerror++;
$finishdate=localtime();
logger("ERROR - Can't sync database files : $finishdate");
} else {
$finishdate=localtime();
logger("OK - Database files sync : $finishdate");
}
# --- SPFile Backup --- #
$rsyncfull=`/usr/bin/rsync -arvt \$ORACLE_HOME/dbs/spfileorcl.ora $path/spfile >> $log 2>&1`;
if($?!=0){
$flagerror++;
$finishdate=localtime();
logger("ERROR - Can't sync spfile : $finishdate");
} else {
$finishdate=localtime();
logger("OK - spfile sync : $finishdate");
}
# --- Archives Backup --- #
$rsyncfull=`/bin/tar cvfj $path/archives/archives_orcl_$date.tar.bz2 $ENV{'ORACLE_BASE'}/product/10.2.0/flash_recovery_area/ORCL/backupset >> $log 2>&1`;
if($?!=0){
$flagerror++;
$finishdate=localtime();
logger("ERROR - Can't backup ARCHIVES : $finishdate");
} else {
@cmd=`/bin/rm -rvf $ENV{'ORACLE_BASE'}/product/10.2.0/flash_recovery_area/ORCL/backupset/* >> $log 2>&1`;
$finishdate=localtime();
logger("OK - ARCHIVES saved and removed RMAN backup files: $finishdate");
}
}
# --- Start database --- #
@cmd=`$ENV{'ORACLE_HOME'}/bin/dbstart`;
# --- Test return --- #
unless ($? == 0){
$flagerror=5;
logger("ERROR - Agent can't startup Database");
}
# --- Returns --- #
if($flagerror==5){$flagmailover=1; logger("ERROR - Backup fail"); exit 1;}
if($flagerror==1){$flagmailover=1; logger("ERROR - Backup finished with errors"); exit 1;}
if($flagerror==0){$flagmailover=1; logger("OK - Backup finished without errors"); exit 0;}
# --- Unexpected return --- #
$flagmailover=1; logger("ERROR - Unexpected return"); exit 1;
}
sub getoption {
Getopt::Long::Configure('bundling');
GetOptions(
'V|version' => \$opt_version,
'h|help' => \$opt_help,
'm|mail=s' => \$opt_mailto,
'v|verbose=i' => \$opt_verbose,
);
if($opt_help){
printHelp();
exit;
}
if($opt_version){
print "$name - '$version'\n";
exit;
}
if(!$opt_verbose){
$opt_verbose = 0;
}
if(!$opt_mailto){
printUsage();
exit;
}
}
sub logger {
return (0) if (not defined $opt_verbose);
my $msg = shift (@_);
my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst)=localtime(time);
$wday++;
$yday++;
$mon++;
$year+=1900;
$isdst++;
if ($opt_verbose == 0){
print "$msg\n";
}
else {
open(LOG, ">>$log") or do error();
printf LOG ("%02i/%02i/%i - %02i:%02i:%02i => %s\n",$mday,$mon,$year,$hour,$min,$sec,$msg);
close(LOG);
}
# --- Call mail function --- #
if($flagmailover==1){mail();}
}
sub mail {
# --- Sendmail --- #
system("/bin/cat \"$log\" | mail -s \"JOB $host (ORACLE) - report $date\" '$opt_mailto'");
# --- Clear logs --- #
system("/bin/rm $log >> /dev/null 2>&1");
}
#--------------------------------------------------------------------------------------
sub printHelp {
my $help = <<'HELP';
This is a agent to Cold Backup Oracle instance
Requirements:
-> Scripts DBSTART and DBSHUT work
-> Backup archives with RMAN job with destination FLASH_RECOVERY_AREA/backupset
Arguments:
-V : Version
-h : Help
-m : Mail to
-v 1: Send to log
-v 0: Show log in console
Required APIs:
use DBI;
use strict;
use Getopt::Long;
use POSIX;
use File::Basename;
HELP
system("clear");
print $help;
}
&main
