Pavan DBA's Blog

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

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