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

Home -> Community -> Usenet -> c.d.o.misc -> Re: Back up Oracle database with Legato Light

Re: Back up Oracle database with Legato Light

From: frank pantaleo <moles_at_frontiernet.net>
Date: Tue, 7 Jan 2003 22:05:18 -0500
Message-ID: <v1n5be8cst5sb1@corp.supernews.com> 





Here is a perl script that I found that originally used sqlplus. I have
updated it to use DBI.



#!/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 @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();
        @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);





HTH



Frank P.


"Karsten Farrell" <kfarrell_at_belgariad.com> wrote in message
news:ZHES9.708$Qk5.50370665_at_newssvr21.news.prodigy.com...
> Craig S. Ledbetter wrote:


> > You don't say what OS you are using, but if it is a Unix, try looking at
the

> > oraback.sh (a Bourne shell script). Info at
> > http://www.backupcentral.com/oraback.html
> > It takes some configuration, but it will do anything you want it to.
> > CSL
> >
> > "John Wood" <jwood_at_hotmail.com> wrote in message
> > news:uJ9Q9.70904$xp4.2626319_at_news1.telusplanet.net...
> >
> >>As Orcle 9i comes with Legato light vesion, I would like to find out


info


> >>for setting it up.  I am a new oracle user, please advise where I can
get

> >>procedures in setting it up to back up to tape drive.
> >>
> >>By the way, is there any other good Oracle Backup utility I can use.
> >
> > Please
> >
> >>advise.
> >>
> >>Thanks.
> >>
> >>EW
> >>
> I agree, oraback.sh is a great shell script. However, the last time I
> looked at it, calls to 'svrmgrl' were scattered all over it ... and
> since the OP is using 9i, that won't fly. Have they updated the script?
> I started to do that myself ... but like many open-source scripts, this
> one had way too many "cooks in the kitchen" (spagetti code, some of
> which is never executed).
>


Received on Tue Jan 07 2003 - 21:05:18 CST












Original text of this message













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