Re: 9i Hot Backup Script
Date: Mon, 13 Jan 2003 19:28:03 -0500
Message-ID: <v26mcjr3pp3hf0_at_corp.supernews.com>
Matt,
[Quoted] Found this script on the net. I have updated it to use DBI/DBD and Zlib compression. I have tested it a little hope it helps you.
Frank P.
--------------- begin -----
#!/usr/bin/perl
# ------------------------------------------------------------------------
# hotarch -- hotarchive backup for Microsoft copied from ye olde shell
script
# G. Patterson, Nov 2001
#
use File::Basename;
use File::Copy;
use DBI;
use Compress::Zlib;
use File::Slurp;
$MyPath = dirname($0);
# ------------------------------------------------------------------------
sub LogMsg{
my _at_t = localtime(time);
printf LOG "%04d-%02d-%02d %02d:%02d %s\n",
$t[5]+1900,$t[4]+1,$t[3],$t[2],$t[1],$_[0];
}
sub Compress {
$in_file=$_[0];
$out_file=$_[1];
$status=0;
# open(INFILE,'<',$in_file) or die "could not open $in_file";
open(OUTFILE,'>',$out_file) or die "could not open $out_file";
binmode INFILE;
binmode OUTFILE;
my $gz = gzopen(\*OUTFILE, "wb") or die "Cannot open stdout: $gzerrno\n" ;
# while (read(INFILE, $input, 32768)) {
# $gz->gzwrite($input) ;
# last if $status != Z_OK ;
# }
$input = read_file($in_file);
$gz->gzwrite($input) ;
$gz->gzclose ;
#close INFILE;
close OUTFILE;
return $status;
}
# ------------------------------------------------------------------------
sub OraDo {
($thesql) = $_[0];
$status=0;
print "OraDo $thesql\n";
$sth2 = $dbh->prepare($thesql) or wrapup();
if ($DBI::errstr) {
LogMsg "$thesql\nParse Error $DBI::errstr\n";
$status=-1;
}
$sth2->execute or wrapup();
if ($DBI::errstr) {
LogMsg "$thesql\nExec Error $DBI::errstr\n";
$status=-1;
}
$sth2->finish;
return $status;
}
# ------------------------------------------------------------------------
sub OraQry {
($thesql) = $_[0];
$status=0;
print "OraQry $thesql\n";
$sth1 = $dbh->prepare($thesql) or wrapup();
if ($DBI::errstr) {
LogMsg "$thesql\nParse Error $DBI::errstr\n";
$status=-1;
}
$sth1->execute or wrapup{};
if ($DBI::errstr) {
LogMsg "$thesql\nExec Error $DBI::errstr\n";
$status=-1;
}
return $status;
}
# ------------------------------------------------------------------------
sub stop_backup{
#
# just in case previous hotarch died an untidy death
#
local($SQL)=<<EOF;
declare
my_exception exception;
cursor thecsr is
select 'alter tablespace ' || TABLESPACE_NAME || ' end
backup' the_ts
from dba_tablespaces;
begin
for therec in thecsr loop
begin
execute immediate therec.the_ts;
exception when others then null;
--raise_application_error(-20001,'Exception
occured.' || sqlcode || sqlerrm);
end;
end loop;
end;
EOF
$rtn=OraDo($SQL);
}
# ------------------------------------------------------------------------
sub problem{
# Err Houston? ... Houston ... We have a problem ...
LogMsg "ERROR: $_[0]";
# make a last ditch effort to end backup
stop_backup();
wrapup();
die "$_[0]\n";
}
# ------------------------------------------------------------------------
sub wrapup {
$dbh->disconnect;
close LOG;
unlink $lockfile;
unlink $stopfile;
}
# ------------------------------------------------------------------------
sub get_log_info{
$SQL = 'select log_mode from v_$database';
$rtn=OraQry($SQL);
($arch_mode) = $sth1->fetchrow_array;
$sth1->finish;
$rtn=OraQry('select archiver from v_$instance');
($archiver) = $sth1->fetchrow_array;
$sth1->finish;
$rtn=OraQry('select value from v_$parameter where name = ' . "'" .
'log_archive_dest' . "'");
($arcdir) = $sth1->fetchrow_array;
$sth1->finish;
$rtn=OraQry('select max(recid) from v_$archived_log where ' . "
archived = 'YES'");
($log_cur_seq) = $sth1->fetchrow_array;
$sth1->finish;
$rtn=OraQry('select sequence# from v_$log where ' . "archived =
'NO'");
($next_archive) =$sth1->fetchrow_array;
$sth1->finish;
}
# ------------------------------------------------------------------------
sub get_oraparm{
# retrieve parameter values from v$parameter
$SQL = 'select name from v_$database';
$rtn=&OraQry($SQL);
($orasid) = $sth1->fetchrow_array;
$sth1->finish;
}
# ------------------------------------------------------------------------
sub backup_mode{
# alter tablespace begin/end backup
$SQL="alter tablespace $_[0] $_[1] backup";
$rtn=OraDo($SQL);
}
# ------------------------------------------------------------------------
sub backup_init{
$BKUPDIR= $ENV{BKUPDIR} or die "Environment missing BKUPDIR can not run!";
get_oraparm();
get_log_info();
_at_start = localtime(time);
$start_str = sprintf "%04d-%02d-%02d %02d:%02d:%02d",$start[5]+1900,
$start[4]+1,$start[3],$start[2],$start[1],$start[0];
$logfile = sprintf
"%s\/%s_%s_%02d%02d%02d.log",$BKUPDIR,$orasid,$MyName,
$start[5]%100,$start[4]+1,$start[3];
open(LOG,">$logfile") || die "error opening $logfile\n";
LogMsg "$MyName started at $start_str ORACLE_SID:
$orasid ORACLE_HOME: $ENV{ORACLE_HOME} BLOCK_SIZE:
$db_block_size START_SEQNO: $log_cur_seq Archive Mode:
$arch_mode";
$lockfile = "$BKUPDIR\/hotarch.$orasid.lock";
$stopfile = "$BKUPDIR\/hotarch.$orasid.stop";
if ((-f $lockfile) && (-M $lockfile < 1)) {
LogMsg "$MyName is already running";
wrapup();
exit(5);
}
open (LOCK,">$lockfile") || die "Cannot write to $lockfile";
print LOCK "$MyName started $start_str\n";
close (LOCK) || die "$!";
#
if ($arch_mode =~ "ARCHIVELOG") {}
else {
LogMsg "Error! No Archive Mode";
wrapup();
exit(5);
}
if ($archiver =~ "STARTED") {}
else {
LogMsg "Error! Archive disabled";
wrapup();
exit(5);
}
if ($arcdir) {}
else {
LogMsg "Error! No archive log destination";
wrapup();
exit(5);
}
stop_backup();
}
# ------------------------------------------------------------------------
sub backup_control_file{
# create a backup copy of the control file
my $backup_ctrl = "$BKUPDIR" . '/' . uc($orasid) . "_CTRL.FILE";
unlink "$backup_ctrl";
$SQL = "alter database backup controlfile to '$backup_ctrl'";
$rtn=OraDo($SQL);
}
# ------------------------------------------------------------------------
sub backup_dbfs {
local($SQL)=<<EOF;
select tablespace_name,file_name
from dba_data_files where status = 'AVAILABLE'
order by tablespace_name, file_name
EOF
$rtn=OraQry($SQL);
my $j = 0;
$df_cnt = 0;
$last_tsname="";
while( ($ts_name,$file_name) = $sth1->fetchrow_array ) {
$df_cnt++;
LogMsg "copying $ts_name, $file_name";
LogMsg "$ts_name $last_tsname";
if (index($ts_name,$last_tsname) > 0 ) {}
else {
if ($last_tsname) {
backup_mode $last_tsname,"END";
}
backup_mode $ts_name,"BEGIN";
$last_tsname = $ts_name;
}
($base_name)=basename($file_name);
$rtn=Compress( $file_name,$BKUPDIR . '/' . $base_name .
'.gz');
if ($rtn != Z_OK ) {
LogMsg "error copying $file_name";
}
}
backup_mode $last_tsname,"END";
problem "no data files available" unless ($df_cnt > 0);
$sth1->finish;
}
# ------------------------------------------------------------------------
sub switch_log{
# switch_log files ... then wait for the archiver to catch up
$SQL = "alter system switch logfile";
$rtn=OraDo($SQL);
$SQL = "alter system switch logfile";
$rtn=OraDo($SQL);
do {
OraQry('select max(recid) from v_$archived_log where ' . "
archived = 'YES'");
($log_nxt_seq) = $sth1->fetchrow_array;
$sth1->finish;
sleep(60);
} until $log_nxt_seq > $log_cur_seq;
}
# ------------------------------------------------------------------------
sub copy_log_files{
my $i;
my $next_log = $next_archive;
$SQL='select name from v$archived_log where ' . " ARCHIVED = 'YES'";
&OraQry($SQL);
$df_cnt = 0;
while( ($file_name) = $sth1->fetchrow_array ) {
($base_name)=basename($file_name);
if (-r "$arcdir\/$base_name") {
$df_cnt++;
LogMsg "compressing & moving $file_name";
$rtn=Compress($file_name,$BKUPDIR . '/' . $base_name
. '.gz');
if ($rtn != Z_OK ) {
LogMsg "error copying $file_name";
}
else {
unlink "$file_name";
}
}
else {
LogMsg "archive log $file_name not here";
}
}
$sth1->finish;
}
# ------------------------------------------------------------------------
# Main ... start here:
$MyName = basename $0,"\.pl";
$dbh = DBI->connect("dbi:Oracle:", '', '',{ ora_session_mode => 2})
or die "Login error to db";
backup_init();
backup_dbfs();
stop_backup();
backup_control_file();
copy ($ENV{ORACLE_HOME} . '/dbs/init' . $orasid . '.ora',$BKUPDIR);
if ($!) {
LogMsg "Error copy " . $ORACLE_HOME . '/dbs/init' . $orasid
. '.ora' . " $!. Return code was $? ";
}
switch_log();
copy_log_files();
LogMsg "$MyName completed normal";
wrapup();
exit(0);
----- end ----
"Matt Isleb" <misleb_at_NO.SPAMonshore.com> wrote in message news:3e22f398$0$55612$9a6e19ea_at_news.newshosting.com...
> Hello, > > I'm looking for a comprehensive online (hot) backup script for 9i/Linux. Ihave
> found several scripts but most of them seem to be for versions 7 and 8. > > Thanks, > -matthew >Received on Tue Jan 14 2003 - 01:28:03 CET