How to backup the Datafile in the hot method
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: How to backup the Datafile in the hot method

Hybrid View

  1. #1
    Join Date
    Nov 2000
    Posts
    8
    Hi All,

    In the hot method,I write the the script code as below:
    Step 1) Alter tablespace tab_name begin backup
    Step 2) oCopy Data file to target location
    Step 3) Alter tablespace tab_name end backup

    But,When step two isn't finished, step three is already executed,My Oracle Server is install in the NT mode,How to deal with it?

    Thanks!

    Best Regards!
    X.M.Tan

  2. #2
    Join Date
    Sep 2000
    Posts
    14
    How are you running these commands?
    Is the OCOPY run as a HOST command?

  3. #3
    Join Date
    Nov 2000
    Posts
    34

    Wink

    This is a script for performing an online backup of the Oracle database. The script can be run after setting the site-specific values of the different environment variables listed below. It has been tested on all versions of Oracle, up to 8.0.6, installed on Unix platforms.

    This script is generic and can be customized to suit your environment.

    ---------------------------------------------------------------------------
    #!/bin/ksh
    #Filename: hotbkp.ksh
    #Author: Sandeep Sahore
    #---------------------------------------------------------------------------
    # Initialize the environment variables to be used in the script here.
    #
    set -a
    #
    ORACLE_SID=ORCL
    ORACLE_HOME=/u01/app/oracle/product/8.0.4
    ARCHIVE_DEST=/archive/$ORACLE_SID/hotbackups
    ARCHIVE_DISK=/archive
    CFILE=$ARCHIVE_DEST/control_bkp
    INITORA_LOC=/u01/app/oracle/admin/$ORACLE_SID/pfile/init$ORACLE_SID.ora
    CONFIGORA_LOC=/u01/app/oracle/admin/$ORACLE_SID/pfile/config$ORACLE_SID.ora
    ARCLOGS_LOC=/archive/$ORACLE_SID/archlogs
    ARCLIST_LOC=/oradba/list
    HOSTN=`uname -n`
    DBA='your_login_name@some_company.com'
    SPOOLF=/oradba/hotbackup_${ORACLE_SID}.sql
    LOGF=/oradba/hotbackup_${ORACLE_SID}.log
    #
    # Pre-processing check to see if ORACLE is available.
    #
    DBUP=`ps -ef | grep $ORACLE_SID | awk '$NF ~ /pmon/ {print $NF}'`
    #
    if [ -z "$DBUP" ]
    then
    echo Oracle instance $ORACLE_SID on $HOSTN is unavailable > instance$$.err
    mailx -s 'ERROR!!!......in hotbackup procedure' $DBA <instance$$.err
    rm instance$$.err
    exit 1
    fi
    #
    # Pre-processing check to see if there is sufficient disk space for hot backup.
    #
    integer DISK_MB=`df -k | grep $ARCHIVE_DISK | awk '{print int($4/1024)}'`
    #
    integer DFILE_MB=`sqlplus -s system/blank <<-!!
    set echo off heading off feedback off pages 0
    select ceil((sum(bytes/1024)/1024)) from v\\$datafile;
    !!`
    #
    if [ $DISK_MB -le $DFILE_MB ]
    then
    echo Insufficient disk space on $HOSTN for
    hot backup of $ORACLE_SID >dskspace$$.err
    mailx -s 'ERROR!!!...in hot backup
    procedure'$DBA <dskspace$$.err && rm dskspace$$.err
    exit 1
    fi
    #
    # After disk space & instance up verification checks generate hot backup script
    #
    sqlplus -s internal <<-%%
    whenever oserror exit sql.oscode
    whenever sqlerror exit sql.sqlcode
    set serveroutput on size 10000
    set echo off heading off feedback off lines 132
    spool $SPOOLF
    rem
    declare
    target_dir varchar2(100) := '$ARCHIVE_DEST';
    cf_dest varchar2(100) := '$CFILE';
    counter integer;
    ts_name varchar2(100);
    df_name varchar2(100);
    v_fname varchar2(100);
    v_dbname varchar2(100);
    cursor c_ts is
    select tablespace_name, count(file_name)
    from dba_data_files
    group by tablespace_name;
    cursor c_df is
    select file_name, substr(file_name, instr(file_name, '/', -1) + 1)
    from dba_data_files
    order by tablespace_name;
    cursor c_dbname is
    select name from v\$database;
    begin
    open c_ts;
    open c_df;
    open c_dbname;
    fetch c_dbname into v_dbname;
    dbms_output.put_line
    ('connect internal');
    dbms_output.put_line
    ('spool $LOGF');
    dbms_output.put_line
    ('archive log list');
    dbms_output.put_line
    ('! echo "***********************************************"');
    dbms_output.put_line
    ('! echo Starting HOT BACKUP of ' || v_dbname || ' database...');
    dbms_output.put_line
    ('! echo "***********************************************"');
    loop
    fetch c_ts into ts_name, counter;
    exit when c_ts%notfound;
    dbms_output.put_line
    ('! echo Backing up tablespace ' || ts_name || '......');
    dbms_output.put_line
    ('alter tablespace ' || ts_name || ' begin backup;');
    for i in 1..counter loop
    fetch c_df into df_name, v_fname;
    dbms_output.put_line
    ('!cp ' || df_name || ' ' || target_dir || '/' || v_fname);
    end loop;
    dbms_output.put_line
    ('alter tablespace ' || ts_name || ' end backup;');
    dbms_output.put_line
    ('alter system checkpoint;');
    end loop;
    dbms_output.put_line
    ('! echo Backing up the CONTROLFILE......');
    dbms_output.put_line
    ('alter database backup controlfile to ' ||''''|| cf_dest ||''''||'reuse;');
    dbms_output.put_line
    ('! echo Backing up CONTROLFILE to TRACE......');
    dbms_output.put_line
    ('alter database backup controlfile to trace;');
    dbms_output.put_line
    ('! echo Copying the CONFIGORA file......');
    dbms_output.put_line
    ('!cp $CONFIGORA_LOC ' || target_dir);
    dbms_output.put_line
    ('! echo Copying the INITORA file......');
    dbms_output.put_line
    ('!cp $INITORA_LOC ' || target_dir);
    dbms_output.put_line
    ('! echo "**********************************************"');
    dbms_output.put_line
    ('! echo HOT BACKUP of ' || v_dbname || ' database ends....');
    dbms_output.put_line
    ('! echo "**********************************************"');
    close c_ts;
    close c_df;
    close c_dbname;
    dbms_output.put_line ('spool off');
    dbms_output.put_line ('exit');
    end;
    /
    spool off
    exit
    %%
    #
    # Check if the PL/SQL block completed successfully.
    #
    if [ $? != 0 ]
    then
    echo PL/SQL block exits abnormally in hotbackup
    of $ORACLE_SID on $HOSTN >genrate$$.err
    mailx -s "ERROR!!!...in hotbackup procedure" $DBA <
    genrate$$.err && rm genrate$$.err
    if [ -f $SPOOLF ]; then
    rm $SPOOLF
    fi
    exit 1
    fi
    #
    # Log into server manager and run the hotbackup script generated above.
    #
    svrmgrl command=@$SPOOLF
    #
    # Verify whether the hot backup went well or not.
    #
    if [ ! -f $LOGF ]
    then
    echo "svrmgr -> $LOGF file I/O error in hotbackup
    of $ORACLE_SID on $HOSTN" > spool$$.err
    mailx -s 'ERROR!!!......in hot backup procedure'
    $DBA < spool$$.err && rm spool$$.err
    exit 1
    else
    sed -n '/^ORA-/ p' $LOGF > ora$$.err
    if [ -s ora$$.err ]; then
    echo ORA-errors while doing hotbackup
    of $ORACLE_SID on $HOSTN >> ora$$.err
    mailx -s 'ERROR!!!....in hotbackup procedure'
    $DBA <ora$$.err && rm ora$$.err
    exit 1
    else
    mailx -s "Log file of hotbackup of $ORACLE_SID on
    $HOSTN" $DBA <$LOGF && rm ora$$.err
    fi
    fi
    #
    # Cleanup the archived redo logs directory.
    #
    LOGSEQ=`awk '/^Oldest online log sequence/ {print $5}' $LOGF`
    ls $ARCLOGS_LOC > $ARCLIST_LOC
    #
    if [ -s $ARCLIST_LOC ]
    then
    csplit $ARCLIST_LOC "/$LOGSEQ/"
    cat xx00 | xargs -i rm $ARCLOGS_LOC/{} && rm /oradba/xx??
    fi
    #
    trap 'rm $SPOOLF $LOGF $ARCLIST_LOC' 0 1 2 15 ERR
    #
    # end-of-script
    #


    Try this out

  4. #4
    Join Date
    Nov 2000
    Posts
    8
    Hi,Tony and Admala

    My Oracle Server is running under the NT enviroment , and my script is executed in the host machine.

    Thanks!


    ------------------------------------------------------------------------
    In the hot method,I write the the script code as below:
    Step 1) Alter tablespace tab_name begin backup
    Step 2) oCopy Data file to target location
    Step 3) Alter tablespace tab_name end backup

    But,When step two isn't finished, step three is already executed,My Oracle Server is install in the NT mode,How to deal with it?
    Best Regards!
    X.M.Tan

  5. #5
    Join Date
    Sep 2000
    Posts
    14
    Are you running everything from a single command file?
    I have just tested running from a single SQLPLUS script with multiple HOST OCOPY infile.dat copy.dat commands and each waited for the previous to finish.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width