Pavan DBA's Blog

#################################

HOT BACKUP IN WINDOWS

#################################

rem ——————————————————————————————-

rem ——————————————————————————————-

rem -*-

rem -*- Description: Hot (warm) backup script for Oracle NT database main program

rem -*-

rem -*- Source: Hot backup of Oracle instance %ora_sid%

rem -*-

rem -*- Instructions:

rem -*- 1. Create the following directories:

rem -*- ora_back_dir

rem -*- ora_log_dir

rem -*- ora_utl_dir

rem -*- ora_vbs_dir

rem -*- ora_recv_dir

rem -*-

rem -*- 2. Change variables below as needed

rem -*-

rem -*- 3. In order to execute the cscript.exe %ora_vbs_dir%\sendmailattach.vbs

rem -*- you must have the SNMP service running, cscript.exe and ste50en.exe

rem -*-

rem -*- Author : Diana Farazi

rem -*- Date Written: 03/24/2000

rem -*-

rem -*- Modification log:

rem -*- Mod Ver Date Who Desciption

rem -*- ——- ———- ——– ——————————————————

rem -*- 1.1 09/08/2000 farazid added CALL %ora_vbs_dir%\upper_case.bat %search_for%

rem -*- set search_for=%RET%

rem -*- created upper_case.bat in the %ora_vbs_dir%

rem -*-

rem -*- 2.0 01/29/2004 farazid Oracle9i modified and tested

rem ——————————————————————————————-

rem ——————————————————————————————-

 

set ora_sid=PRD2

set ORACLE_SID=%ora_sid%

set tns=bp1tulap151_%ora_sid%

set ora_sys_id=”sys/rsdbsite as sysdba”

set ora_system_id=system/rsdbsite

set ora_sqlplus=sqlplus

set ora_ocopy=ocopy

set ora_bin_dir=i:\d001\oracle\920\bin

set ora_home=i:\d001\oracle\920\database

set ora_back_dir=o:\d902\oracle\%ora_sid%\hotdumps

set ora_log_dir=i:\d002\oracle\%ora_sid%\utillogs\hb

set ora_utl_dir=i:\d002\oracle\%ora_sid%\utilitys\hb

set ora_vbs_dir=i:\d002\oracle\vbs

set ora_recv_dir=i:\d002\oracle\%ora_sid%\utilitys\recv

 

##set init_ora=%ora_home%\spfile%ORACLE_SID%.ora

set init_ora=%ora_home%\init%ORACLE_SID%.ora

set pwd_ora=%ora_home%\pwd%ORACLE_SID%.ora

 

set hb_hot_backup_log=%ora_log_dir%\hb_hot_backup.log

set save_hb_hot_backup_log=%ora_log_dir%\save_hb_hot_backup.log

set hb_ping_db_log=%ora_log_dir%\hb_ping_db.log

set hb_recv_log=%ora_log_dir%\hb_recv.log

set hb_recv_dat=%ora_recv_dir%\hb_recv.dat

set email=dba_ora_offshore@bp.com

rem set email2=dba_ora_offshore@bp.com

rem set email2=dba_ora_onshore@bp.com

set email_message=

set search_for=”%ORACLE_SID%”

set search_for2=”Tablespace altered”

rem ### changed in 9i ### set search_for2=”Statement processed”

 

set line=—————————————————————————

set star=*

set ts_in_hot_backup_count=0

set error_count=0

 

 

del %ora_log_dir%\hb*.*

 

echo %line% > %hb_hot_backup_log%

echo %line% >> %hb_hot_backup_log%

echo %star% >> %hb_hot_backup_log%

 

now Hot Backup START for %tns% database >> %hb_hot_backup_log%

echo %star% >> %hb_hot_backup_log%

echo This is: %0 script >> %hb_hot_backup_log%

 

echo %star% >> %hb_hot_backup_log%

echo %line% >> %hb_hot_backup_log%

echo %line% >> %hb_hot_backup_log%

echo %star% >> %hb_hot_backup_log%

 

rem ———————————————————————————————

rem — . . . Checking if database %ORACLE_SID% is up

rem ———————————————————————————————

echo . . . Checking if database %ORACLE_SID% is up >> %hb_hot_backup_log%

echo %star% >> %hb_hot_backup_log%

 

CALL %ora_bin_dir%\%ora_sqlplus%.exe -s %ora_system_id% @%ora_utl_dir%\hb_ping_db.sql

 

CALL %ora_vbs_dir%\upper_case.bat %search_for%

set search_for=%RET%

 

type %hb_ping_db_log% | find.exe %search_for% > nul

 

IF %errorlevel% EQU 0 goto :SKIP0

set /a error_count=error_count+1

set email_message=ALERT: database %tns% is DOWN – Hot Backup terminating!

echo ALERT: database %tns% is DOWN – Hot Backup terminating! >> %hb_hot_backup_log%

goto :END

 

rem ———————————————————————————————

rem — . . . Checking for TS already in backup mode

rem ———————————————————————————————

 

:SKIP0

 

echo . . . Proceeding with Hot Backup >> %hb_hot_backup_log%

echo %star% >> %hb_hot_backup_log%

echo . . . Checking for TS already in backup mode >> %hb_hot_backup_log%

echo %star% >> %hb_hot_backup_log%

 

CALL %ora_bin_dir%\%ora_sqlplus%.exe -s %ora_system_id% @%ora_utl_dir%\hb_ts_already_in_backup.sql

FOR /f %%X in (%ora_log_dir%\hb_ts_already_in_backup.log) DO (set /a ts_in_hot_backup_count=ts_in_hot_backup_count+1)

 

rem ———————————————————————————————

rem — . . . Tablespaces already in backup mode list

rem ———————————————————————————————

IF %ts_in_hot_backup_count% EQU 0 goto :SKIP1

echo . . . Tablespaces already in backup mode list >> %hb_hot_backup_log%

FOR /f %%Y in (%ora_log_dir%\hb_ts_already_in_backup.log) DO (echo %%Y >> %hb_hot_backup_log%)

type %hb_hot_backup_log% >> %save_hb_hot_backup_log%

set /a error_count=error_count+1

set email_message=ALERT: Tablespaces already in backup mode – Hot Backup terminating!

goto :END

 

rem ———————————————————————————————

rem — . . . Archive log list

rem ———————————————————————————————

 

:SKIP1

 

echo . . . Proceeding with Hot Backup >> %hb_hot_backup_log%

echo %star% >> %hb_hot_backup_log%

echo %line% >> %hb_hot_backup_log%

echo %star% >> %hb_hot_backup_log%

echo . . . Archive log list >> %hb_hot_backup_log%

 

CALL %ora_bin_dir%\%ora_sqlplus%.exe -s %ora_sys_id% @%ora_utl_dir%\hb_archive_log_list.sql

echo %star% >> %hb_hot_backup_log%

type %ora_log_dir%\hb_archive_log_list.log >> %hb_hot_backup_log%

 

echo %star% >> %hb_hot_backup_log%

echo %line% >> %hb_hot_backup_log%

echo %star% >> %hb_hot_backup_log%

 

rem ———————————————————————————————

rem — . . . Tablespace list

rem ———————————————————————————————

CALL %ora_bin_dir%\%ora_sqlplus%.exe -s %ora_system_id% @%ora_utl_dir%\hb_ts_list.sql

echo . . . Tablespace list >> %hb_hot_backup_log%

echo %star% >> %hb_hot_backup_log%

type %ora_log_dir%\hb_ts_list.log >> %hb_hot_backup_log%

echo %star% >> %hb_hot_backup_log%

echo . . . See %ora_log_dir%\hb_ts_list.log >> %hb_hot_backup_log%

 

rem ———————————————————————————————

rem — . . . Starting tablespace backups

rem ———————————————————————————————

echo %star% >> %hb_hot_backup_log%

echo %line% >> %hb_hot_backup_log%

echo . . . Starting tablespace backups >> %hb_hot_backup_log%

echo %line% >> %hb_hot_backup_log%

 

FOR /f %%I in (%ora_log_dir%\hb_ts_list.log) DO (@CALL %ora_utl_dir%\hb_ts_backup.bat %%I)

 

IF %error_count% NEQ 0 goto :END

 

rem ———————————————————————————————

rem — . . . Archive log list

rem ———————————————————————————————

echo %star% >> %hb_hot_backup_log%

echo %line% >> %hb_hot_backup_log%

echo %star% >> %hb_hot_backup_log%

echo . . . Archive log list >> %hb_hot_backup_log%

CALL %ora_bin_dir%\%ora_sqlplus%.exe -s %ora_sys_id% @%ora_utl_dir%\hb_archive_log_list.sql

echo %star% >> %hb_hot_backup_log%

type %ora_log_dir%\hb_archive_log_list.log >> %hb_hot_backup_log%

 

rem ———————————————————————————————

rem — . . . Backing up the control file

rem ———————————————————————————————

echo %star% >> %hb_hot_backup_log%

echo %line% >> %hb_hot_backup_log%

echo %star% >> %hb_hot_backup_log%

echo . . . Backing up the control file >> %hb_hot_backup_log%

 

CALL %ora_bin_dir%\%ora_sqlplus%.exe -s %ora_sys_id% @%ora_utl_dir%\hb_backup_control_file.sql

erase %ora_back_dir%\prev_backup_control_file.ctl

move %ora_back_dir%\backup_control_file.ctl %ora_back_dir%\prev_backup_control_file.ctl

move %ora_home%\backup_control_file.ctl %ora_back_dir%\backup_control_file.ctl

 

rem ———————————————————————————————

rem — . . . Backing up the %init_ora% and %pwd_ora% files

rem ———————————————————————————————

echo %star% >> %hb_hot_backup_log%

echo %line% >> %hb_hot_backup_log%

echo %star% >> %hb_hot_backup_log%

echo . . . Backing up the init%ORACLE_SID%.ora and pwd%ORACLE_SID%.ora files >> %hb_hot_backup_log%

echo %star% >> %hb_hot_backup_log%

 

xcopy %init_ora% %ora_back_dir%\ /h/f/y >> %hb_hot_backup_log%

xcopy %pwd_ora% %ora_back_dir%\ /h/f/y >> %hb_hot_backup_log%

 

rem ———————————————————————————————

rem — . . . Building the recovery script

rem ———————————————————————————————

echo %star% >> %hb_hot_backup_log%

echo %line% >> %hb_hot_backup_log%

echo . . . Building the recovery script >> %hb_hot_backup_log%

echo %line% >> %hb_hot_backup_log%

 

echo %line% > %hb_recv_dat%

echo — . . . Remove the datasets below that you do not want to restore >> %hb_recv_dat%

echo %line% >> %hb_recv_dat%

 

FOR /f %%Z in (%hb_recv_log%) DO (@CALL %ora_utl_dir%\hb_build_recv_file.bat %%Z)

 

echo %star% >> %hb_hot_backup_log%

echo . . . See %ora_recv_dir%\hb_recv.dat >> %hb_hot_backup_log%

echo %star% >> %hb_hot_backup_log%

 

rem ———————————————————————————————

rem — . . . End hot backup

rem ———————————————————————————————

 

:END

 

echo %line% >> %hb_hot_backup_log%

echo %line% >> %hb_hot_backup_log%

echo %star% >> %hb_hot_backup_log%

now Error_count=%error_count% %email_message% >> %hb_hot_backup_log%

echo %star% >> %hb_hot_backup_log%

now Hot Backup END >> %hb_hot_backup_log%

echo %star% >> %hb_hot_backup_log%

echo %line% >> %hb_hot_backup_log%

echo %line% >> %hb_hot_backup_log%

type %hb_hot_backup_log% >> %save_hb_hot_backup_log%

 

rem ———————————————————————————————

rem — . . . If an error was detected, send an e-mail

rem ———————————————————————————————

 

IF %error_count% EQU 0 goto :EOF

cscript.exe %ora_vbs_dir%\sendmailattach.vbs -t %email% -f %email% -s “Alert: %tns% Hot Backup Failed!” -b ” %email_message%” -a %hb_hot_backup_log%

If {%email2%}=={} goto :EOF

cscript.exe %ora_vbs_dir%\sendmailattach.vbs -t %email2% -f %email2% -s “Alert: %tns% Hot Backup Failed!” -b ” %email_message%” -a %hb_hot_backup_log%

:EOF