G
grant.september
Hi All, I'm a total newbie to perl but I found a script form the web
that would automate the cloning of Oracle DB's on NT so I thought i'd
give it a go, the problem is whenever i run it i get a badfile error, (
found in the section that backs up Db files - think )which i'm assuming
is becuase my path of some of my db files have a space in them, i'd
appreciate it if anybody can give me a workaround to this
TIA
Grant
=========================================================================
#!/usr/bin/perl
#
------------------------------------------------------------------------
# coldarch -- cold backup for Microsoft
# G. Patterson, June 2002
# WARNING ** This script SHUTS DOWN THE DATABASE! ********
# This is the only warning!
# Set the common parameters (contained in common.pl)
use File::Basename;
# extract the path from $0
$MyPath = dirname($0);
# This file contains important site definitions
require "$MyPath\\common.pl";
# This determines how the database will be re-opened
$OPEN_TYPE = 0; # 0 = Don't open, 1 = oradim, 2 = svrmgrl, 3 =
both
#
------------------------------------------------------------------------
# Write a log message to the logfile
sub LogMsg{
my @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];
}
#
------------------------------------------------------------------------
# We have a problem ... cleanup and exit
sub problem{
LogMsg "ERROR: $_[0]";
unlink "$lockfile";
unlink "$stopfile";
# hopefully we can start the database
opendb();
die "$_[0]\n";
}
# -------------------------------------------------------------------
# Run the given SQL and spool to temp.LST, because we
# cannot redirect output from version 7 sqlplus in NT
sub run_sql {
# first, write the given SQL to a temp file
open SQL,">$sqltmp" || die "cannot open $sqltmp";
print SQL "set term off\n";
print SQL "set echo off\n";
print SQL "set trimspool on\n";
print SQL "set pages 0\n";
print SQL "set feedback off\n";
print SQL "set verify off\n";
print SQL "set lines 1024\n";
print SQL "spool $tmpdir\\temp\n";
print SQL "$_[0]\n";
print SQL "exit\n";
close SQL || die "$!";
# now run it with the SQLPLUS utility
system "$sqlplus -s $userid/$passwd\@$orasid \@$sqltmp";
# read back the spool file and return the contents in list
context
open (TMP,"$tmpdir\\temp.LST") || problem "opening temp.LST";
my @t = (<TMP>);
close TMP || problem "closing temp.LST";
return( @t);
}
#
------------------------------------------------------------------------
# Run the given SQL in svrmgrl
sub run_svrmgr {
# construct an SQL file
open SQL,">$sqltmp" || die "cannot open $sqltmp";
print SQL "connect internal/$passwd\n";
print SQL "$_[0]\n";
print SQL "exit\n";
close SQL || die "$!";
# EXEC as a backticks block and return the output in list
context
my @t = `$svrmgrl \@$sqltmp`;
return( @t);
}
#
------------------------------------------------------------------------
# get list of redo logs from v_$logfile
sub get_redo_info{
# this should return a list of the redo logs
my @t = run_sql "select member from v_\$logfile;";
foreach $x (@t){
# remove any crud from the end of the lines
$x =~ s/\s+$//;
push (@REDOLIST,$x) if (length($x) )
}
}
#
------------------------------------------------------------------------
# shutdown the database and get a copy of SQL to create control file
sub shutdb{
my $t; my $f; my $x; my $a;
# run a BACKUP TO TRACE command
my $backup_ctrl = "$coldarc\\" . uc($orasid) .
"_CTRL.FILE.trc";
my @t = run_svrmgr "alter system switch logfile;
alter database backup controlfile to trace resetlogs;";
@t = run_svrmgr "shutdown immediate;";
LogMsg $t = `$oradim -shutdown -sid $orasid -usrpwd $passwd
-shuttype inst,srvc -shutmode i`;
# examine the files in USER_DUMP_DEST
opendir (TRACE,$ORAPARM{user_dump_dest}) ||
problem "Cannot open $ORAPARM{user_dump_dest}";
$a = 99;
# we want the most recent trace file ...
while ($t = readdir TRACE){
$x = $ORAPARM{user_dump_dest} . "\\" . $t;
next unless ( -f $x);
next unless ( (-M $x ) < $a);
$f = $x;
$a = ( -M $x )
}
# save the trace file for later (the coldclone procedure)
rename $f,$backup_ctrl || problem "Cannot move $f to
$backup_ctrl";
}
#
-----------------------------------------------------------------------
# retrieve parameter values from v$parameter
sub get_oraparm{
# extract the parameters from v$parameter
my @t = run_sql "col name format a40 trunc\ncol value format
a512 trunc
select name,value from v\$parameter;";
for my $i ( 0 .. $#t){
$t[$i] =~ s/\s+$//;
# parse them into %ORAPARM (using parameter as a hash
key)
if ( $t[$i] =~ /^(\w+)/){
$ORAPARM{$1} = $';
$ORAPARM{$1} =~ s/^\s+//;
}
}
}
#
------------------------------------------------------------------------
# Get the list of datafiles to be backed up
sub get_datafiles{
my @t = run_sql "select tablespace_name, file_name, blocks+1
from dba_data_files where status = 'AVAILABLE'
order by tablespace_name, file_name;";
my $j = 0;
for my $i ( 0 .. $#t){
chomp($t[$i]);
my @s = split ' ',$t[$i];
if (@s == 3){
$DATAFILE[$j] = \@s;
my @b = split /\\/,$DATAFILE[$j][1];
$DATAFILE[$j++][3] = $b[$#b];
}
elsif (@s > 3){
# spit the dummy if filename contains embedded
spaces
problem "bad file: $s[1] $s[2]";
}
}
problem "no data files available" unless (@DATAFILE > 0);
LogMsg sprintf "$MyName found %d data files to
backup",@DATAFILE+0;
}
# Create a header file.
# This is a text file that contains information about the cold archive
# which can be used by scripts that restore from the cold archive.
sub write_header{
my $x;
# gather all the necessary information
get_redo_info();
get_datafiles();
@CTRLFILES=split /, /,$ORAPARM{control_files};
# clobber previous backup
unlink glob "$coldarc\\*";
open (HDR,">$coldarc\\0header") || die "Cannot open
$coldarc\\0header\n";
# store the information in the header file
print HDR "$MyName started at $start_str
ORACLE_SID: $orasid
ORACLE_HOME: $ENV{ORACLE_HOME}
BLOCK_SIZE: $ORAPARM{db_block_size}
PARAM_FILE: $pfile{$orasid}\n";
if ($ORAPARM{ifile}){
print HDR " I FILES:\n";
foreach $x(split (/, /,$ORAPARM{ifile})){
print HDR "\t$x\n";
}
}
print HDR " CTRL FILES:\n";
foreach $x (@CTRLFILES){
print HDR "\t$x\n";
}
}
#
------------------------------------------------------------------------
# create SQL for a backup control (backup to trace)
sub backup_control_file{
my $backup_ctrl = "$coldarc\\" . uc($orasid) . "_CTRL.FILE";
# clobber any old versions of this file
unlink "$backup_ctrl";
my @t = run_sql "set feedback on
alter database backup controlfile to '$backup_ctrl';";
problem "Cannot backup controlfile"
unless ( grep(/Database altered$/i,@t) );
# use the $zipview command to verify that the backup is successful
# this reads through the contents of the zip archive and checks
# that the sizes match specified system size.
# NB: This may vary from site to site. If you have actually created
# a data file that is larger than the allocated size in the database
# this check should be disabled.
sub verify_backup{
$blksz = $ORAPARM{db_block_size};
for my $i ( 0 .. $#DATAFILE){
my @t = `$zipview $coldarc\\$DATAFILE[$i][3].zip`;
foreach my $x (@t){
$x =~ s/\s+$//;
if ($x =~ /$DATAFILE[$i][3]$/){
$size = (split ' ',$x)[0];
problem "suspect backup
$DATAFILE[$i][3].zip"
unless ($size == $DATAFILE[$i][2] *
$blksz);
}
}
}
}
#
------------------------------------------------------------------------
#make the output from the zipadd more pretty
sub pretty {
my $t = $_[0];
$t =~ s/Deflating .*%\), done./Deflated/;
return($t);
}
#
------------------------------------------------------------------------
# backup the control_file, redo logs, pfiles and ifiles
sub backup_config(){
my $x; my $b;
my $dest = "$coldarc\\config.$orasid.zip";
# first copy the pfile to the archive directory
my $t = `copy $pfile{$orasid} $coldarc`;
$t =~ s/\s+$//;
problem "pfile: $t" unless ($t =~ /1 file\(s\) copied/);
# add the control_file to the zip archive
LogMsg pretty $t = `$zipadd $dest $CTRLFILES[0]`;
foreach my $x (@REDOLIST){
LogMsg pretty $t = `$zipadd $dest $x`;
}
# copy each of the ifiles to the archive directory
foreach $x(split (/, /,$ORAPARM{ifile})){
$b = basename( $x);
problem "Duplicate ifile: $b" if ( -f "$coldarc\\$b");
$t = `copy $x $coldarc`;
$t =~ s/\s+$//;
problem "ifile: $t" unless ($t =~ /1 file\(s\)
copied/);
}
}
#
------------------------------------------------------------------------
# reopen database -- called from problem(), so we must not call
problem()
sub opendb{
LogMsg "WARNING: Database is shutdown!" unless $OPEN_TYPE;
LogMsg $t =
`$oradim -startup -sid $orasid -usrpwd $passwd -starttype
srvc,inst`
if ( $OPEN_TYPE & 1);
run_svrmgr "startup" if ( $OPEN_TYPE & 2);
}
#
------------------------------------------------------------------------
# Main ... start here:
# which program am I?
$MyName = basename $0,"\.pl";
# sanity check for command line
die "usage: $MyName intrnl_passwd db_name\n" unless (@ARGV == 2);
$userid = "sys";
$passwd = $ARGV[0];
$orasid = $ARGV[1];
# check the environment
chk_env();
# init strings for logfile
@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",$logdir,$orasid,$MyName,
$start[5]%100,$start[4]+1,$start[3];
$lockfile = "$logdir\\coldarch.$orasid.lock";
$stopfile = "$logdir\\coldarch.$orasid.stop";
die "$MyName is already running\n" if ((-f $lockfile) && (-M $lockfile
< 1));
# create the lockfile
open (LOCK,">$lockfile") || die "Cannot write to $lockfile";
print LOCK "$MyName started $start_str\n";
close (LOCK) || die "$!";
open(LOG,">>$logfile") || die "error opening $logfile\n";
get_oraparm();
problem "$ORAPARM{db_name} does not match $orasid"
unless (uc($ORAPARM{db_name}) eq uc($orasid));
$coldarc = "$coldarc\\$ORAPARM{db_name}";
# setup the header
write_header();
# shutdown the database, backup control_file, pfile, ifiles
shutdb();
# write information about datafiles to the HEADER
print HDR "\n DATA FILES:\n";
for my $i ( 0 .. $#DATAFILE){
# loop through the datafiles
problem "$MyName Stopped!" if ( -f $stopfile);
sleep(10);
LogMsg "copying $DATAFILE[$i][1], $DATAFILE[$i][2] blocks";
print HDR "\t$DATAFILE[$i][1] $DATAFILE[$i][2] blocks\n";
LogMsg pretty $t = `$zipadd $coldarc\\$DATAFILE[$i][3].zip
$DATAFILE[$i][1]`;
}
backup_config();
opendb();
LogMsg "$MyName completed normal";
unlink "$lockfile";
that would automate the cloning of Oracle DB's on NT so I thought i'd
give it a go, the problem is whenever i run it i get a badfile error, (
found in the section that backs up Db files - think )which i'm assuming
is becuase my path of some of my db files have a space in them, i'd
appreciate it if anybody can give me a workaround to this
TIA
Grant
=========================================================================
#!/usr/bin/perl
#
------------------------------------------------------------------------
# coldarch -- cold backup for Microsoft
# G. Patterson, June 2002
# WARNING ** This script SHUTS DOWN THE DATABASE! ********
# This is the only warning!
# Set the common parameters (contained in common.pl)
use File::Basename;
# extract the path from $0
$MyPath = dirname($0);
# This file contains important site definitions
require "$MyPath\\common.pl";
# This determines how the database will be re-opened
$OPEN_TYPE = 0; # 0 = Don't open, 1 = oradim, 2 = svrmgrl, 3 =
both
#
------------------------------------------------------------------------
# Write a log message to the logfile
sub LogMsg{
my @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];
}
#
------------------------------------------------------------------------
# We have a problem ... cleanup and exit
sub problem{
LogMsg "ERROR: $_[0]";
unlink "$lockfile";
unlink "$stopfile";
# hopefully we can start the database
opendb();
die "$_[0]\n";
}
# -------------------------------------------------------------------
# Run the given SQL and spool to temp.LST, because we
# cannot redirect output from version 7 sqlplus in NT
sub run_sql {
# first, write the given SQL to a temp file
open SQL,">$sqltmp" || die "cannot open $sqltmp";
print SQL "set term off\n";
print SQL "set echo off\n";
print SQL "set trimspool on\n";
print SQL "set pages 0\n";
print SQL "set feedback off\n";
print SQL "set verify off\n";
print SQL "set lines 1024\n";
print SQL "spool $tmpdir\\temp\n";
print SQL "$_[0]\n";
print SQL "exit\n";
close SQL || die "$!";
# now run it with the SQLPLUS utility
system "$sqlplus -s $userid/$passwd\@$orasid \@$sqltmp";
# read back the spool file and return the contents in list
context
open (TMP,"$tmpdir\\temp.LST") || problem "opening temp.LST";
my @t = (<TMP>);
close TMP || problem "closing temp.LST";
return( @t);
}
#
------------------------------------------------------------------------
# Run the given SQL in svrmgrl
sub run_svrmgr {
# construct an SQL file
open SQL,">$sqltmp" || die "cannot open $sqltmp";
print SQL "connect internal/$passwd\n";
print SQL "$_[0]\n";
print SQL "exit\n";
close SQL || die "$!";
# EXEC as a backticks block and return the output in list
context
my @t = `$svrmgrl \@$sqltmp`;
return( @t);
}
#
------------------------------------------------------------------------
# get list of redo logs from v_$logfile
sub get_redo_info{
# this should return a list of the redo logs
my @t = run_sql "select member from v_\$logfile;";
foreach $x (@t){
# remove any crud from the end of the lines
$x =~ s/\s+$//;
push (@REDOLIST,$x) if (length($x) )
}
}
#
------------------------------------------------------------------------
# shutdown the database and get a copy of SQL to create control file
sub shutdb{
my $t; my $f; my $x; my $a;
# run a BACKUP TO TRACE command
my $backup_ctrl = "$coldarc\\" . uc($orasid) .
"_CTRL.FILE.trc";
my @t = run_svrmgr "alter system switch logfile;
alter database backup controlfile to trace resetlogs;";
@t = run_svrmgr "shutdown immediate;";
LogMsg $t = `$oradim -shutdown -sid $orasid -usrpwd $passwd
-shuttype inst,srvc -shutmode i`;
# examine the files in USER_DUMP_DEST
opendir (TRACE,$ORAPARM{user_dump_dest}) ||
problem "Cannot open $ORAPARM{user_dump_dest}";
$a = 99;
# we want the most recent trace file ...
while ($t = readdir TRACE){
$x = $ORAPARM{user_dump_dest} . "\\" . $t;
next unless ( -f $x);
next unless ( (-M $x ) < $a);
$f = $x;
$a = ( -M $x )
}
# save the trace file for later (the coldclone procedure)
rename $f,$backup_ctrl || problem "Cannot move $f to
$backup_ctrl";
}
#
-----------------------------------------------------------------------
# retrieve parameter values from v$parameter
sub get_oraparm{
# extract the parameters from v$parameter
my @t = run_sql "col name format a40 trunc\ncol value format
a512 trunc
select name,value from v\$parameter;";
for my $i ( 0 .. $#t){
$t[$i] =~ s/\s+$//;
# parse them into %ORAPARM (using parameter as a hash
key)
if ( $t[$i] =~ /^(\w+)/){
$ORAPARM{$1} = $';
$ORAPARM{$1} =~ s/^\s+//;
}
}
}
#
------------------------------------------------------------------------
# Get the list of datafiles to be backed up
sub get_datafiles{
my @t = run_sql "select tablespace_name, file_name, blocks+1
from dba_data_files where status = 'AVAILABLE'
order by tablespace_name, file_name;";
my $j = 0;
for my $i ( 0 .. $#t){
chomp($t[$i]);
my @s = split ' ',$t[$i];
if (@s == 3){
$DATAFILE[$j] = \@s;
my @b = split /\\/,$DATAFILE[$j][1];
$DATAFILE[$j++][3] = $b[$#b];
}
elsif (@s > 3){
# spit the dummy if filename contains embedded
spaces
problem "bad file: $s[1] $s[2]";
}
}
problem "no data files available" unless (@DATAFILE > 0);
LogMsg sprintf "$MyName found %d data files to
backup",@DATAFILE+0;
}
# Create a header file.
# This is a text file that contains information about the cold archive
# which can be used by scripts that restore from the cold archive.
sub write_header{
my $x;
# gather all the necessary information
get_redo_info();
get_datafiles();
@CTRLFILES=split /, /,$ORAPARM{control_files};
# clobber previous backup
unlink glob "$coldarc\\*";
open (HDR,">$coldarc\\0header") || die "Cannot open
$coldarc\\0header\n";
# store the information in the header file
print HDR "$MyName started at $start_str
ORACLE_SID: $orasid
ORACLE_HOME: $ENV{ORACLE_HOME}
BLOCK_SIZE: $ORAPARM{db_block_size}
PARAM_FILE: $pfile{$orasid}\n";
if ($ORAPARM{ifile}){
print HDR " I FILES:\n";
foreach $x(split (/, /,$ORAPARM{ifile})){
print HDR "\t$x\n";
}
}
print HDR " CTRL FILES:\n";
foreach $x (@CTRLFILES){
print HDR "\t$x\n";
}
}
#
------------------------------------------------------------------------
# create SQL for a backup control (backup to trace)
sub backup_control_file{
my $backup_ctrl = "$coldarc\\" . uc($orasid) . "_CTRL.FILE";
# clobber any old versions of this file
unlink "$backup_ctrl";
my @t = run_sql "set feedback on
alter database backup controlfile to '$backup_ctrl';";
problem "Cannot backup controlfile"
unless ( grep(/Database altered$/i,@t) );
# use the $zipview command to verify that the backup is successful
# this reads through the contents of the zip archive and checks
# that the sizes match specified system size.
# NB: This may vary from site to site. If you have actually created
# a data file that is larger than the allocated size in the database
# this check should be disabled.
sub verify_backup{
$blksz = $ORAPARM{db_block_size};
for my $i ( 0 .. $#DATAFILE){
my @t = `$zipview $coldarc\\$DATAFILE[$i][3].zip`;
foreach my $x (@t){
$x =~ s/\s+$//;
if ($x =~ /$DATAFILE[$i][3]$/){
$size = (split ' ',$x)[0];
problem "suspect backup
$DATAFILE[$i][3].zip"
unless ($size == $DATAFILE[$i][2] *
$blksz);
}
}
}
}
#
------------------------------------------------------------------------
#make the output from the zipadd more pretty
sub pretty {
my $t = $_[0];
$t =~ s/Deflating .*%\), done./Deflated/;
return($t);
}
#
------------------------------------------------------------------------
# backup the control_file, redo logs, pfiles and ifiles
sub backup_config(){
my $x; my $b;
my $dest = "$coldarc\\config.$orasid.zip";
# first copy the pfile to the archive directory
my $t = `copy $pfile{$orasid} $coldarc`;
$t =~ s/\s+$//;
problem "pfile: $t" unless ($t =~ /1 file\(s\) copied/);
# add the control_file to the zip archive
LogMsg pretty $t = `$zipadd $dest $CTRLFILES[0]`;
foreach my $x (@REDOLIST){
LogMsg pretty $t = `$zipadd $dest $x`;
}
# copy each of the ifiles to the archive directory
foreach $x(split (/, /,$ORAPARM{ifile})){
$b = basename( $x);
problem "Duplicate ifile: $b" if ( -f "$coldarc\\$b");
$t = `copy $x $coldarc`;
$t =~ s/\s+$//;
problem "ifile: $t" unless ($t =~ /1 file\(s\)
copied/);
}
}
#
------------------------------------------------------------------------
# reopen database -- called from problem(), so we must not call
problem()
sub opendb{
LogMsg "WARNING: Database is shutdown!" unless $OPEN_TYPE;
LogMsg $t =
`$oradim -startup -sid $orasid -usrpwd $passwd -starttype
srvc,inst`
if ( $OPEN_TYPE & 1);
run_svrmgr "startup" if ( $OPEN_TYPE & 2);
}
#
------------------------------------------------------------------------
# Main ... start here:
# which program am I?
$MyName = basename $0,"\.pl";
# sanity check for command line
die "usage: $MyName intrnl_passwd db_name\n" unless (@ARGV == 2);
$userid = "sys";
$passwd = $ARGV[0];
$orasid = $ARGV[1];
# check the environment
chk_env();
# init strings for logfile
@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",$logdir,$orasid,$MyName,
$start[5]%100,$start[4]+1,$start[3];
$lockfile = "$logdir\\coldarch.$orasid.lock";
$stopfile = "$logdir\\coldarch.$orasid.stop";
die "$MyName is already running\n" if ((-f $lockfile) && (-M $lockfile
< 1));
# create the lockfile
open (LOCK,">$lockfile") || die "Cannot write to $lockfile";
print LOCK "$MyName started $start_str\n";
close (LOCK) || die "$!";
open(LOG,">>$logfile") || die "error opening $logfile\n";
get_oraparm();
problem "$ORAPARM{db_name} does not match $orasid"
unless (uc($ORAPARM{db_name}) eq uc($orasid));
$coldarc = "$coldarc\\$ORAPARM{db_name}";
# setup the header
write_header();
# shutdown the database, backup control_file, pfile, ifiles
shutdb();
# write information about datafiles to the HEADER
print HDR "\n DATA FILES:\n";
for my $i ( 0 .. $#DATAFILE){
# loop through the datafiles
problem "$MyName Stopped!" if ( -f $stopfile);
sleep(10);
LogMsg "copying $DATAFILE[$i][1], $DATAFILE[$i][2] blocks";
print HDR "\t$DATAFILE[$i][1] $DATAFILE[$i][2] blocks\n";
LogMsg pretty $t = `$zipadd $coldarc\\$DATAFILE[$i][3].zip
$DATAFILE[$i][1]`;
}
backup_config();
opendb();
LogMsg "$MyName completed normal";
unlink "$lockfile";