Pavan DBA's Blog

The DBA Knowledge Store

Posts Tagged ‘script for hot backup in windows env’

script for hot backup in windows env

Posted by Pavan DBA on March 26, 2012


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

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

 

Posted in backup and recovery, Scripts | Tagged: , , | Leave a Comment »

 
%d bloggers like this: