Pavan DBA's Blog

The DBA Knowledge Store

Archive for the ‘backup and recovery’ Category

« Previous Entries

script for archives backup in windows

Posted by Pavan DBA on March 26, 2012


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

PURGING ARCHIVE LOGS

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

rem ——————————————————————————————–

rem ——————————————————————————————–

rem -*-

rem -*- Description: Purge old archive logs script for Oracle NT database main program

rem -*-

rem -*- Source file: %ora_utl_dir%\purge_arch_logs.bat

rem -*-

rem — This job:

rem — 1. Removes archived logs from %ora_arch_save_dir% directory

rem — 2. copies archived logs from %ora_arch_dir% directory to %ora_arch_save_dir% directory

rem — 3. Removes archived logs older than %older_than_days% days from %ora_arch_dir% directory

rem -*-

rem -*- Author : Diana Farazi

rem -*- Date Written: 04/20/2000

rem -*-

rem -*- Modification log:

rem -*- Mod Ver Date Who Desciption

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

rem -*-

rem ——————————————————————————————–

rem ——————————————————————————————–

 

set ora_sid=PRD2

set ORACLE_SID=%ora_sid%

set tns=bp1tulap151_%ora_sid%

set ora_system_id=system/rsdbsite

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

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

set ora_vbs_dir=i:\d002\oracle\vbs

 

set ora_arch_dir=i:\d802\oracle\%ORACLE_SID%\arch

set ora_arch_save_dir=i:\d802\oracle\%ORACLE_SID%\arch\save

 

set purge_arch_logs_log=%ora_log_dir%\purge_arch_logs.log

set save_purge_arch_logs_log=%ora_log_dir%\save_purge_arch_logs.log

set older_than_days=2

set email=dba_ora_offshore@bp.com

##set email2=dba_ora_onshore@bp.com

set email_message=

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

set star=*

set error_count=0

 

 

echo %line% > %purge_arch_logs_log%

echo %line% >> %purge_arch_logs_log%

echo %star% >> %purge_arch_logs_log%

 

now purge_arch_logs START for %tns% database >> %purge_arch_logs_log%

echo %star% >> %purge_arch_logs_log%

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

 

echo %star% >> %purge_arch_logs_log%

echo %line% >> %purge_arch_logs_log%

echo %line% >> %purge_arch_logs_log%

echo %star% >> %purge_arch_logs_log%

 

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

rem — . . . Before Purge

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

echo %line% >> %purge_arch_logs_log%

echo . . . Before Purge >> %purge_arch_logs_log%

echo %line% >> %purge_arch_logs_log%

 

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

rem — . . . Before Purge – Listing archive logs in %ora_arch_dir%

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

echo %line% >> %purge_arch_logs_log%

echo . . . Before Purge – Listing archive logs in %ora_arch_dir% >> %purge_arch_logs_log%

echo %line% >> %purge_arch_logs_log%

echo %star% >> %purge_arch_logs_log%

 

dir %ora_arch_dir%\*.* >> %purge_arch_logs_log%

 

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

rem — . . . Before Purge – Listing archive logs in %ora_arch_save_dir%

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

echo %line% >> %purge_arch_logs_log%

echo . . . Before Purge – Listing archive logs in %ora_arch_save_dir% >> %purge_arch_logs_log%

echo %line% >> %purge_arch_logs_log%

echo %star% >> %purge_arch_logs_log%

 

dir %ora_arch_save_dir%\*.* >> %purge_arch_logs_log%

 

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

rem — . . . Removing archive logs from %ora_arch_save_dir%

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

echo %line% >> %purge_arch_logs_log%

echo . . . Removing archive logs from %ora_arch_save_dir% >> %purge_arch_logs_log%

echo %line% >> %purge_arch_logs_log%

echo %star% >> %purge_arch_logs_log%

 

del %ora_arch_save_dir%\arch*.log

 

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

rem — . . . Copying archive logs from %ora_arch_dir% to %ora_arch_save_dir%

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

echo %line% >> %purge_arch_logs_log%

echo . . . Copying archive logs from %ora_arch_dir% to %ora_arch_save_dir% >> %purge_arch_logs_log%

echo %line% >> %purge_arch_logs_log%

echo %star% >> %purge_arch_logs_log%

 

copy %ora_arch_dir%\arch*.* %ora_arch_save_dir%\

 

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

rem — . . . Removing archive logs from %ora_arch_dir% older than %older_than_days%

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

echo %line% >> %purge_arch_logs_log%

echo . . . Removing archive logs from %ora_arch_dir% older than %older_than_days% >> %purge_arch_logs_log%

echo %line% >> %purge_arch_logs_log%

echo %star% >> %purge_arch_logs_log%

 

cscript.exe %ora_vbs_dir%\cleanupoldfiles.vbs -d %ora_arch_dir% -o %older_than_days% >> %purge_arch_logs_log%

 

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

rem — . . . After Purge

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

echo %star% >> %purge_arch_logs_log%

echo %line% >> %purge_arch_logs_log%

echo %line% >> %purge_arch_logs_log%

echo . . . After Purge >> %purge_arch_logs_log%

echo %line% >> %purge_arch_logs_log%

 

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

rem — . . . After Purge – Listing archive logs in %ora_arch_dir%

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

echo %line% >> %purge_arch_logs_log%

echo . . . After Purge – Listing archive logs in %ora_arch_dir% >> %purge_arch_logs_log%

echo %line% >> %purge_arch_logs_log%

echo %star% >> %purge_arch_logs_log%

 

dir %ora_arch_dir%\*.* >> %purge_arch_logs_log%

 

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

rem — . . . After Purge – Listing archive logs in %ora_arch_save_dir%

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

echo %line% >> %purge_arch_logs_log%

echo . . . After Purge – Listing archive logs in %ora_arch_save_dir% >> %purge_arch_logs_log%

echo %line% >> %purge_arch_logs_log%

echo %star% >> %purge_arch_logs_log%

 

dir %ora_arch_save_dir%\*.* >> %purge_arch_logs_log%

 

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

rem — . . . END Purge

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

echo %line% >> %purge_arch_logs_log%

echo %line% >> %purge_arch_logs_log%

echo %star% >> %purge_arch_logs_log%

 

now purge_arch_logs END for %tns% database >> %purge_arch_logs_log%

echo %star% >> %purge_arch_logs_log%

 

echo %line% >> %purge_arch_logs_log%

echo %line% >> %purge_arch_logs_log%

 

type %purge_arch_logs_log% >> %save_purge_arch_logs_log%

 

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

script for cold backup in windows

Posted by Pavan DBA on March 26, 2012


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

COLD BACKUP IN WINDOWS

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

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

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

rem -*-

rem -*- Description: Cold backup script for Oracle NT database main program

rem -*-

rem -*- Source file: %ora_utl_dir%\cb_cold_backup.bat

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 -*- 1.2 09/22/2003 farazid added Net Stop and Net Start SampleManager services

rem -*- sections (must be executed in this order!):

rem -*-

rem -*- Net STOP SampleManager services:

rem -*- (1)smpbackVGLP,(2) smwVGLP,(3)smpVGLP

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 as sysdba”

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%\colddmps

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

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

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 cb_cold_backup_log=%ora_log_dir%\cb_cold_backup.log

set save_cb_cold_backup_log=%ora_log_dir%\save_cb_cold_backup.log

set cb_ping_db_log=%ora_log_dir%\cb_ping_db.log

set cb_recv_log=%ora_log_dir%\cb_recv.log

set cb_recv_dat=%ora_recv_dir%\cb_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=”Statement processed”

set search_for3=”ORACLE instance shut down”

set search_for4=”Database opened”

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

set star=*

set error_count=0

 

 

del %ora_log_dir%\cb*.*

 

echo %line% > %cb_cold_backup_log%

echo %line% >> %cb_cold_backup_log%

echo %star% >> %cb_cold_backup_log%

 

now Cold Backup START for %tns% database >> %cb_cold_backup_log%

echo %star% >> %cb_cold_backup_log%

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

 

echo %star% >> %cb_cold_backup_log%

echo %line% >> %cb_cold_backup_log%

echo %line% >> %cb_cold_backup_log%

echo %star% >> %cb_cold_backup_log%

 

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

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

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

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

echo %star% >> %cb_cold_backup_log%

 

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

 

CALL %ora_vbs_dir%\upper_case.bat %search_for%

set search_for=%RET%

 

type %cb_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 – Cold Backup terminating!

echo ALERT: database %tns% is DOWN – Cold Backup terminating! >> %cb_cold_backup_log%

goto :END

 

 

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

rem — . . . Archive log list

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

 

:SKIP0

 

echo . . . Proceeding with Cold Backup >> %cb_cold_backup_log%

echo %star% >> %cb_cold_backup_log%

echo %line% >> %cb_cold_backup_log%

echo %star% >> %cb_cold_backup_log%

echo . . . Archive log list >> %cb_cold_backup_log%

 

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

 

echo %star% >> %cb_cold_backup_log%

type %ora_log_dir%\cb_archive_log_list.log >> %cb_cold_backup_log%

 

echo %star% >> %cb_cold_backup_log%

echo %line% >> %cb_cold_backup_log%

echo %star% >> %cb_cold_backup_log%

 

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

rem — . . . Building the file list

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

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

echo . . . Building the file list >> %cb_cold_backup_log%

echo %star% >> %cb_cold_backup_log%

type %ora_log_dir%\cb_file_list.log >> %cb_cold_backup_log%

echo %star% >> %cb_cold_backup_log%

echo . . . See %ora_log_dir%\cb_file_list.log >> %cb_cold_backup_log%

 

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

 

 

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

rem — . . . Shutting down database %ORACLE_SID% immediate

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

echo %star% >> %cb_cold_backup_log%

echo %line% >> %cb_cold_backup_log%

echo %star% >> %cb_cold_backup_log%

echo . . . Shutting down database %ORACLE_SID% immediate >> %cb_cold_backup_log%

echo %star% >> %cb_cold_backup_log%

 

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

type %ora_log_dir%\cb_shutdown.log >> %cb_cold_backup_log%

 

type %ora_log_dir%\cb_shutdown.log | find.exe %search_for3% > nul

 

IF %errorlevel% EQU 0 goto :SKIP1

set /a error_count=error_count+1

set email_message=ALERT: Error shutting down %tns% database – Cold Backup terminating!

echo ALERT: Error shutting down %tns% database – Cold Backup terminating! >> %cb_cold_backup_log%

goto :END

 

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

rem — . . . Starting Oracle file backups

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

 

:SKIP1

 

echo %star% >> %cb_cold_backup_log%

echo %line% >> %cb_cold_backup_log%

echo . . . Starting Oracle file backups >> %cb_cold_backup_log%

echo %line% >> %cb_cold_backup_log%

 

FOR /f %%I in (%ora_log_dir%\cb_file_list.log) DO (@CALL %ora_utl_dir%\cb_file_backup.bat %%I) & (echo %%I >> %cb_cold_backup_log%) & (echo %%I >> %cb_recv_log%)

 

IF %error_count% NEQ 0 goto :END

 

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

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

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

echo %star% >> %cb_cold_backup_log%

echo %line% >> %cb_cold_backup_log%

echo %star% >> %cb_cold_backup_log%

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

echo %star% >> %cb_cold_backup_log%

 

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

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

 

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

rem — . . . Startup open database %ORACLE_SID%

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

echo %star% >> %cb_cold_backup_log%

echo %line% >> %cb_cold_backup_log%

echo %star% >> %cb_cold_backup_log%

echo . . . Startup open database %ORACLE_SID% >> %cb_cold_backup_log%

echo %star% >> %cb_cold_backup_log%

 

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

type %ora_log_dir%\cb_startup.log >> %cb_cold_backup_log%

 

type %ora_log_dir%\cb_startup.log | find.exe %search_for4% > nul

 

IF %errorlevel% EQU 0 goto :SKIP2

set /a error_count=error_count+1

set email_message=ALERT: Error starting %tns% database – Cold Backup terminating!

echo ALERT: Error starting %tns% database – Cold Backup terminating! >> %cb_cold_backup_log%

goto :END

 

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

 

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

rem — . . . Backing up the control file

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

:SKIP2

 

echo %star% >> %cb_cold_backup_log%

echo %line% >> %cb_cold_backup_log%

echo %star% >> %cb_cold_backup_log%

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

CALL %ora_bin_dir%\%ora_sqlplus%.exe -s %ora_sys_id% @%ora_utl_dir%\cb_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 — . . . Archive log list

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

echo %star% >> %cb_cold_backup_log%

echo %line% >> %cb_cold_backup_log%

echo %star% >> %cb_cold_backup_log%

echo . . . Archive log list >> %cb_cold_backup_log%

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

echo %star% >> %cb_cold_backup_log%

type %ora_log_dir%\cb_archive_log_list.log >> %cb_cold_backup_log%

 

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

rem — . . . Building the recovery script

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

echo %star% >> %cb_cold_backup_log%

echo %line% >> %cb_cold_backup_log%

echo %star% >> %cb_cold_backup_log%

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

 

echo %line% > %cb_recv_dat%

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

echo %line% >> %cb_recv_dat%

 

FOR /f %%Z in (%cb_recv_log%) DO (@CALL %ora_utl_dir%\cb_build_recv_file.bat %%Z)

 

echo %star% >> %cb_cold_backup_log%

echo . . . See %ora_recv_dir%\cb_recv.dat >> %cb_cold_backup_log%

echo %star% >> %cb_cold_backup_log%

 

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

rem — . . . End Cold Backup

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

 

:END

 

echo %line% >> %cb_cold_backup_log%

echo %line% >> %cb_cold_backup_log%

echo %star% >> %cb_cold_backup_log%

now Error_count=%error_count% %email_message% >> %cb_cold_backup_log%

echo %star% >> %cb_cold_backup_log%

now Cold Backup END >> %cb_cold_backup_log%

echo %star% >> %cb_cold_backup_log%

echo %line% >> %cb_cold_backup_log%

echo %line% >> %cb_cold_backup_log%

type %cb_cold_backup_log% >> %save_cb_cold_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% Cold Backup Failed!” -b ” %email_message%” -a %cb_cold_backup_log%

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

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

:EOF

 

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

script for export in windows

Posted by Pavan DBA on March 26, 2012


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

EXPORT JOB IN WINDOWS

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

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

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

rem -*-

rem -*- Description: Full export script for Oracle NT database main program

rem -*-

rem -*- Source file: %ora_utl_dir%\exp_full.bat

rem -*-

rem -*- Instructions:

rem -*- 1. Create the following directories:

rem -*- ora_exp_dir

rem -*- ora_log_dir

rem -*- ora_utl_dir

rem -*- ora_vbs_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 SMTP service running, cscript.exe and ste50en.exe

rem -*-

rem -*- Author : Diana Farazi

rem -*- Date Written: 09/22/2000

rem -*-

rem -*- Modification log:

rem -*- Mod Ver Date Who Desciption

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_system_id=system/rsdbsite

 

set ora_sqlplus=sqlplus

set ora_export=exp

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

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

set ora_exp_dir=o:\d902\oracle\%ora_sid%\exports3

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

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

set ora_vbs_dir=i:\d002\oracle\vbs

 

set exp_id_password=system/rsdbsite

set exp_parfile=i:\d002\oracle\%ora_sid%\utilitys\export\exp_full.par

 

FOR /f “tokens=2-4 delims=/ ” %%a in (‘DATE/T’) do SET rundate=%%c%%a%%b

FOR /f “tokens=1-2 delims=: ” %%a in (‘TIME/T’) do SET runtime=%%a%%b

 

set exp_log=%ora_exp_dir%\exp_full_%ORACLE_SID%_%rundate%_%runtime%.log

set exp_dmp=%ora_exp_dir%\exp_full_%ORACLE_SID%_%rundate%_%runtime%.dmp

 

set exp_ping_db_log=%ora_log_dir%\exp_ping_db.log

set exp_full_job_log=%ora_log_dir%\exp_full_%ORACLE_SID%_job3.log

set save_exp_full_job_log=%ora_log_dir%\save_exp_full_%ORACLE_SID%_job3.log

 

 

set older_than_days=1

 

set email=dba_ora_offshore@bp.com

rem set email2=dba_ora_onshore@bp.com

rem set email2=dba_ora_offshore@bp.com

set email_message=

set search_for=”%ORACLE_SID%”

 

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

set star=*

set error_count=0

 

 

echo %line% > %exp_full_job_log%

echo %line% >> %exp_full_job_log%

echo %star% >> %exp_full_job_log%

 

now Full Export START for %tns% database >> %exp_full_job_log%

echo %star% >> %exp_full_job_log%

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

 

echo %star% >> %exp_full_job_log%

echo %line% >> %exp_full_job_log%

echo %line% >> %exp_full_job_log%

echo %star% >> %exp_full_job_log%

 

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

rem — . . . Deleting export dump files older than %older_than_days% days

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

 

 

IF %error_count% NEQ 0 goto :EOF1

 

echo %star% >> %exp_full_job_log%

echo %line% >> %exp_full_job_log%

echo %star% >> %exp_full_job_log%

echo . . . Deleting Oracle export dump files older than %older_than_days% days >> %exp_full_job_log%

echo %star% >> %exp_full_job_log%

 

cscript.exe %ora_vbs_dir%\cleanupoldfiles.vbs -d %ora_exp_dir% -o %older_than_days% >> %exp_full_job_log%

 

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

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

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

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

echo %star% >> %exp_full_job_log%

echo %line% >> %exp_full_job_log%

echo %star% >> %exp_full_job_log%

 

CALL %ora_bin_dir%\sqlplus.exe -s %ora_system_id% < %ora_utl_dir%\exp_ping_db.sql >> %exp_full_job_log%

 

echo %star% >> %exp_full_job_log%

echo %line% >> %exp_full_job_log%

echo %star% >> %exp_full_job_log%

 

type %exp_ping_db_log% | find.exe /i %search_for% > nul

 

IF %errorlevel% EQU 0 goto :SKIP0

set /a error_count=error_count+1

set email_message=ALERT: database %tns% is DOWN – Full Export terminating!

echo ALERT: database %tns% is DOWN – Full Export terminating! >> %exp_full_job_log%

goto :END

 

 

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

rem — . . . Full Export

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

 

:SKIP0

 

 

echo . . . Proceeding with Full Export >> %exp_full_job_log%

echo %star% >> %exp_full_job_log%

echo %line% >> %exp_full_job_log%

echo %star% >> %exp_full_job_log%

 

echo . . . Parfile: >> %exp_full_job_log%

echo %star% >> %exp_full_job_log%

type %exp_parfile% >> %exp_full_job_log%

echo %star% >> %exp_full_job_log%

echo %line% >> %exp_full_job_log%

echo %star% >> %exp_full_job_log%

 

CALL %ora_bin_dir%\%ora_export%.exe %exp_id_password% log=%exp_log% file=%exp_dmp% parfile=%exp_parfile% >> %exp_full_job_log%

 

type %exp_log% >> %exp_full_job_log%

 

 

 

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

rem — . . . End Full Export

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

 

:END

 

echo %line% >> %exp_full_job_log%

echo %line% >> %exp_full_job_log%

echo %star% >> %exp_full_job_log%

now Error_count=%error_count% %email_message% >> %exp_full_job_log%

echo %star% >> %exp_full_job_log%

now Full Export END >> %exp_full_job_log%

echo %star% >> %exp_full_job_log%

echo %line% >> %exp_full_job_log%

echo %line% >> %exp_full_job_log%

type %exp_full_job_log% >> %save_exp_full_job_log%

 

 

 

 

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

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

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

 

:EOF1

 

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% Full Export Failed!” -b ” %email_message%” -a %exp_full_job_log%

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

cscript.exe %ora_vbs_dir%\sendmailattach.vbs -t %email2% -f %email2% -s “Alert: %tns% Full Export Failed!” -b ” %email_message%” -a %exp_full_job_log%

:EOF

 

Posted in backup and recovery, Scripts | Tagged: , | 4 Comments »

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 »

Script for Archive backup in Unix

Posted by Pavan DBA on March 8, 2012


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

ARCH BACKUP IN UNIX

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

#!/bin/ksh

# —————————————————————————–

# — Description: Oracle Archived Log Archival Job —

# — —

# — Instructions: —

# — 1) Create the following directories if not already setup: —

# — These directories receive the various output files from —

# — this job.

# — $ORACLE_BASE/backups —

# — $ORACLE_BASE/backups/jobs —

# — $ORACLE_BASE/backups/joblogs —

# — $ORACLE_BASE/backups/adsmlogs —

# — —

# — 2) Create a directory under the directory where the archived —

# — redo logs are kept. Call this sub-directory “save”. —

# — —

# — 3) Change variables below as needed —

# — —

# — —

# —————————————————————————

# ———— variables common to instance ——————————

ORACLE_HOME=/d001/oracle/9.2.0

ORACLE_SID=SDSS # oracle instance

ARCHIVE_DIR=/d002/oracle/$ORACLE_SID/backups # job logs, scripts etc….

COMMON_DIR=/d002/oracle/common/backups # common scripts

UID=”system/systemSDSS” # userid/password for

# sqlplus queries

RETPRD=3 # how many days do you

# want to keep joblogs etc..

ADSM_PROCESS_LIMIT=9 # max adsm processes

#ADSM_SERVER=”-se=mvsosap” # adsm server – leave commented

# for default server

#MGMTCLASS=”-ARCHMc=UNIX_MGT” # adsm management class

# leave commented for default

#FROMNODE=”-fromnode=`hostname`” #adsm fromnode parameter –

# leave commented for default

#FROMOWNER=”-fromowner=`/usr/ucb/whoami`” #adsm fromowner parameter –

# leave commented for default

SQLDBA=”sqlplus” # use svrmgr for v7.3

# ———– sendtrap variables ——————————

SNDTRP_FLG=N # do you want to use

# sendtrap (Y,N)

# if you are using sendtrap

# change the following

# VARx’s to instruct

# operations what to do

VAR1=”….Archive Backup”

VAR2=$ORACLE_SID

VAR3=”Database is down – Qname= – contact oncall DBAORA”

VAR4=”Compress error – Qname= – contact oncall DBAORA”

VAR5=”Copy error – Qname= – contact oncall DBAORA”

VAR6=”ADSM error – Qname= – contact oncall DBAORA”

VAR7=”DB in hot bkup mode – Qname= – contact oncall DBAORA”

export COMMON_DIR ORACLE_HOME ORACLE_SID UID VAR1 VAR2 VAR3 VAR4 VAR5 VAR6 VAR7

export INITDF CONFIGDF ARCHIVE_DIR MGMTCLASS ADSM_SERVER RETPRD

export FROMOWNER FROMNODE ADSM_PROCESS_LIMIT SHUTDOWN_TIME_LIMIT

# ————————————————————————–

# ———– variables common to server ——————————

ARCHIVELOGDIR=/d801/oracle/$ORACLE_SID/archive # archived log dest

ARCHIVELOGSAVEDIR=$ARCHIVELOGDIR/save # dest to save archived

# logs that have been sent

# to TSM

ARCHIVELOGFMT=”arch_*_$ORACLE_SID.log” # archived log format

DSMPATH=/usr/bin # dsmc command path

# try:

# /usr/sbin for Solaris

# /usr/bin for AIX

STPATH=/usr/local/bin # sendtrap command path

TMP=/tmp # temp path

export ARCHIVELOGDIR ARCHIVELOGFMT

export DSMPATH STPATH TMP

# —————————————————————————–

# ————— variables common to archival processes ———————–

ARCH_JOB_LOGS=$ARCHIVE_DIR/joblogs # job log dest

ARCH_ADSM_LOGS=$ARCHIVE_DIR/adsmlogs # adsm log dest

ARCH_COMMON_SCRIPTS=$COMMON_DIR/common_scripts # common script dest

ARCH_RETR_SCRIPTS=$ARCHIVE_DIR/recv # adsm retr script dest

FILELST=$ARCH_JOB_LOGS # file list dest

WAIT_SLEEP=15 # number of seconds to sleep

# waiting for log switch

# to finish

MAX_WAIT_COUNT=20 # maximum number of times

# to wait WAIT_SLEEP seconds

# before terminating the

# archive backup

# with a return code of 8.

# Recommend

# WAIT_SLEEP X MAX_WAIT_COUNT

# less than 1 minute.

ARCH_LOGS_TO_KEEP=15 # number of archived redo

# logs to keep in the

# ARCHIVELOGSAVEDIR directory

# after archiving to ADSM

KEEP_UNITS=count # the meaning of

# ARCH_LOGS_TO_KEEP.

# Possible values are:

# days = number of days

# to keep them.

# count = number of

# files to keep.

VERSION=prod # versions of the script:

# prod = production

# devl = development

# qlty = quality

EXIT_0=”$ARCHIVE_DIR/scripts/exit_0.sh” # exit 0 routine – successful

EXIT_4=”$ARCHIVE_DIR/scripts/exit_4.sh” # exit 4 routine – warning

EXIT_8=”$ARCHIVE_DIR/scripts/exit_8.sh” # exit 8 routine – error

USER_EXIT_1=”$ARCHIVE_DIR/scripts/usrexit1.sh” # user exit script dest

USER_EXIT_2=”$ARCHIVE_DIR/scripts/usrexit2.sh”

USER_EXIT_3=”$ARCHIVE_DIR/scripts/usrexit3.sh”

USER_EXIT_4=”$ARCHIVE_DIR/scripts/usrexit4.sh”

JDATE=`date +%y%j`

CTIME=`date +%HH%MM%SS`

export EXIT_0 EXIT_4 EXIT_8

export USER_EXIT_1 USER_EXIT_2 USER_EXIT_3 USER_EXIT_4

export ARCH_JOB_LOGS ARCH_ADSM_LOGS ARCH_COMMON_SCRIPTS

export FILELST WAIT_SLEEP ARCH_LOGS_TO_KEEP KEEP_UNITS VERSION JDATE CTIME

export MAX_WAIT_COUNT

# ————————————————————————–

# ————– Main Section ——————————————-

. $ARCH_COMMON_SCRIPTS/arch_$VERSION.sh # read in common functions

# ————————————————————————–

# —– Uncomment the functions below to be executed ———————–

param_set # set parameters for function – required

#chk_dup_process # check to see if this process is still running

# required

db_status # check database status

#user_exit_1 # user exit 1

get_archnames # get archived log files to send to ADSM

switch_logfiles # alter system switch logfiles

sleep 10

#user_exit_2 # user exit 2

arch_log_list # record current active log list in joblog

df_to_adsm # archive archived log files directly to ADSM

#user_exit_3 # user exit 3

cleanup_archive_dir # remove old archive logs from save directory

#user_exit_4 # user exit 4

clean_up # clean up directorys

save_to_adsm # archive joblogs and

# filelist to ADSM

create_stats # generate ADSM statistics for this backup

exit_0 # Archive successful routine

 

Posted in backup and recovery, Scripts | Tagged: , , , | 4 Comments »

« Previous Entries
 
%d bloggers like this: