Results 1 to 2 of 2
  1. #1
    Join Date
    May 2004
    Posts
    40

    Unanswered: hotbackup script doesn't work at all

    i tried to put this script to a file called "hotbackup.bat" and
    schedule tasks on the windows task scheduler. But it doesn't work
    Any problem of this script that I already define everything.
    thanks for help!
    __________________________________________________ ______


    @echo off
    rem----------------------------------------------------------------------------------
    rem
    rem Name: hotbackup.bat
    rem
    rem Date: 30 May, 2006
    rem
    rem Purpose:
    rem This script performs hot backup of the database on Windows NT
    rem
    rem Prepared/
    rem CopyRighted By: theOracleResourceStop Script Archive
    rem http://www.orsweb.com
    rem
    rem Dependancies:
    rem It is necessary to edit this file and to replace the following:
    rem {userid} --> Change to the user name to use connect to your database instance
    rem {password} --> Change to the password of userid
    rem {tools directory} --> Change to the destination directory for
    rem log files
    rem {oracle home} --> Change to the oracle home designation
    rem {initfile} --> Change to the initfile location
    rem {archive directory} --> Change to the source directory for
    rem archive log files
    rem
    rem Assumptions: NONE
    rem
    rem Parameters: SID name
    rem
    rem Output: SID.log
    rem
    rem Exceptions: No handled errors.
    rem
    rem Usage: c:\>hotbackup.bat SID
    rem
    rem----------------------------------------------------------------------------------

    REM ::::::::::::::::::: Begin Declare Variables Section

    set ORA_HOME=d:\oracle\Ora81
    set CONNECT_USER="sys/manager"
    set ORACLE_SID=%1
    set BACKUP_DIR=d:\backups\hotbks\%ORACLE_SID%
    set INIT_FILE=e:\createdb\IS21
    set ARC_DEST=c:\is21bak\Archive\

    set TOOLS=d:\backups
    set LOGDIR=%TOOLS%\logs

    set HFILE=%BACKUP_DIR%\log\hotbackup.sql
    set ERR_FILE=%BACKUP_DIR%\log\herrors.log
    set LOG_FILE=%BACKUP_DIR%\log\hbackup.log
    set BKP_DIR=%BACKUP_DIR%
    REM :::::::::::::::::::: End Declare Variables Section

    REM :::::::::::::::::::: Begin Parameter Checking Section

    if "%1" == "" goto usage

    REM Create backup directories if already not exist
    if not exist %BACKUP_DIR%\data mkdir %BACKUP_DIR%\data
    if not exist %BACKUP_DIR%\control mkdir %BACKUP_DIR%\control
    if not exist %BACKUP_DIR%\arch mkdir %BACKUP_DIR%\arch
    if not exist %BACKUP_DIR%\log mkdir %BACKUP_DIR%\log
    if not exist %LOGDIR% mkdir %LOGDIR%

    REM Check to see that there were no create errors
    if not exist %BACKUP_DIR%\data goto backupdir
    if not exist %BACKUP_DIR%\control goto backupdir
    if not exist %BACKUP_DIR%\arch goto backupdir
    if not exist %BACKUP_DIR%\log goto backupdir

    REM Deletes previous backup. Make sure you have it on tape.
    del/q %BACKUP_DIR%\data\*
    del/q %BACKUP_DIR%\control\*
    del/q %BACKUP_DIR%\arch\*
    del/q %BACKUP_DIR%\log\*

    echo. > %ERR_FILE%
    echo. > %LOG_FILE%
    (echo Hot Backup started & date/T & time/T) >> %LOG_FILE%
    echo Parameter Checking Completed >> %LOG_FILE%
    REM :::::::::::::::::::: End Parameter Checking Section

    REM :::::::::::::::::::: Begin Create Dynamic files Section
    echo. >%HFILE%
    echo set termout off heading off feedback off >>%HFILE%
    echo set linesize 250 pagesize 0 >>%HFILE%
    echo set serveroutput on size 1000000 >>%HFILE%
    echo spool %BACKUP_DIR%\log\backup_command.sql >>%HFILE%

    echo Declare >>%HFILE%
    echo cursor cur1 is >>%HFILE%
    echo select distinct tablespace_name from dba_data_files >>%HFILE%
    echo order by tablespace_name; >>%HFILE%
    echo cursor cur2(tbl varchar2) is >>%HFILE%
    echo select file_name from dba_data_files >>%HFILE%
    echo where tablespace_name = tbl >>%HFILE%
    echo order by file_name; >>%HFILE%
    echo Begin >>%HFILE%
    echo dbms_output.put_line('set termout off heading off feedback off'); >>%HFILE%
    echo. >>%HFILE%
    echo dbms_output.put_line(chr(10) ); >>%HFILE%
    echo dbms_output.put_line('/******Data files******/' ); >>%HFILE%
    echo for tbs in cur1 loop >>%HFILE%
    echo dbms_output.put_line('alter tablespace '^|^|tbs.tablespace_name ^|^|' begin backup;'); >>%HFILE%
    echo for dbf in cur2(tbs.tablespace_name) loop >>%HFILE%
    echo dbms_output.put_line('host copy '^|^|dbf.file_name^|^|' %BKP_DIR%\data 1^>^> %LOG_FILE% 2^>^> %ERR_FILE%'); >>%HFILE%
    echo end loop; >>%HFILE%
    echo dbms_output.put_line('alter tablespace '^|^| >>%HFILE%
    echo tbs.tablespace_name ^|^|' end backup;'); >>%HFILE%
    echo end loop; >>%HFILE%

    echo. >>%HFILE%
    echo dbms_output.put_line(chr(10) ); >>%HFILE%
    echo dbms_output.put_line('/******Control file******/' ); >>%HFILE%
    echo dbms_output.put_line('alter database backup controlfile to '^|^|''''^|^|'%BKP_DIR%\control\%ORACLE_SID%_contr ol_01.ctl'^|^|''''^|^|';'); >>%HFILE%
    echo dbms_output.put_line('alter database backup controlfile to trace;'); >>%HFILE%

    echo. >>%HFILE%
    echo dbms_output.put_line(chr(10) ); >>%HFILE%
    echo dbms_output.put_line('/******Initialization file******/' ); >>%HFILE%
    echo dbms_output.put_line('host copy %INIT_FILE% %BKP_DIR%\control 1^>^> %LOG_FILE% 2^>^> %ERR_FILE%'); >>%HFILE%

    echo. >>%HFILE%
    echo dbms_output.put_line(chr(10) ); >>%HFILE%
    echo dbms_output.put_line('/******Archivelog files******/' ); >>%HFILE%
    echo dbms_output.put_line('alter system switch logfile;'); >>%HFILE%
    echo dbms_output.put_line('alter system archive log stop;'); >>%HFILE%
    echo dbms_output.put_line('host move %ARC_DEST%\* %BKP_DIR%\arch 1^>^> %LOG_FILE% 2^>^> %ERR_FILE%' ); >>%HFILE%
    echo dbms_output.put_line('alter system archive log start;'); >>%HFILE%

    echo dbms_output.put_line('exit;'); >>%HFILE%
    echo End; >>%HFILE%
    echo / >>%HFILE%
    echo spool off >>%HFILE%
    echo exit; >>%HFILE%

    echo Dynamic files Section Completed >> %LOG_FILE%
    REM :::::::::::::::::::: End Create Dynamic files Section

    REM :::::::::::::::::::: Begin HotBackup Section

    %ORA_HOME%\bin\sqlplus -s %CONNECT_USER%@%ORACLE_SID% @%HFILE%
    REM Copy the files to backup location
    %ORA_HOME%\bin\sqlplus -s %CONNECT_USER%@%ORACLE_SID% @%BACKUP_DIR%\log\backup_command.sql
    (echo HotBackup Completed Successfully & date/T & time/T) >> %LOG_FILE%
    goto end

    REM :::::::::::::::::::: End HotBackup Section


    REM :::::::::::::::::::::::::::: Begin Error handling section

    :usage
    echo Error, Usage: hotbackup.bat SID
    goto end

    :backupdir
    echo Error creating Backup directory structure >> %ERR_FILE%
    (echo HOTBACKUP_FAIL:Error creating Backup directory structure & date/T & time/T) >> %LOG_FILE%
    REM :::::::::::::::::::::::::::: End Error handling section

    REM :::::::::::::::::::::::::::: Cleanup Section
    :end
    set ORA_HOME=
    set ORACLE_SID=
    set CONNECT_USER=
    set BACKUP_DIR=
    set INIT_FILE=
    set ARC_DEST=
    set HFILE=
    set ERR_FILE=
    set LOG_FILE=
    set BKP_DIR=

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    If you run the .bat file by hand, does it run ???
    If so, then what kind of priv's are you associating the at job?
    Under the AT scheduler, you will find a place where a log is written. That
    may give you some clues as to what is happening.

    If have the job under the system account and have changed the password
    for the system account, the job wont run under any circumstances until
    the machine is rebooted.

    HTH
    Gregg

Posting Permissions

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