@echo off & setlocal ENABLEDELAYEDEXPANSION
:: -----------------------------------------------------------------------------------
:: Purpose:       Bounce Oracle database services.
:: Version:       2.1
:: OS:            Windows 200n
:: Oracle:        10g or later.
:: Dependencies:  None (only requires standard Windows OS installation).
:: Prerequisites: Oracle version must be 10.2 or later.
:: -----------------------------------------------------------------------------------
:: Usage: Change user variables as required to match your environment.
:: 
:: Comments
:: - Can be run using the Windows scheduler.
:: - Assumes ...Microsoft\Command Processor\EnableExtensions enabled (default).
:: ===================================================================================


:::::::::::::::::::::::::::::::::::::::::::
:: Init User Variables
:::::::::::::::::::::::::::::::::::::::::::
:: Init Oracle Env
set ORACLE_SID=DB01
set ORACLE_BASE=C:\oracle
set ORACLE_HOME=%ORACLE_BASE%\product\10.2.0\db_1
set SCRIPTS_DIR=c:\oracle\scripts
set PW=mypassword

set SVC_DB=OracleServiceDB01
set SVC_LISTENER=OracleOraDb10g_home1TNSListener
set SVC_OEM=OracleDBConsoleDB01

:: Email Alert
set EMAIL_SMTP_SRV=192.168.1.42
set EMAIL_PORT=25
set EMAIL_DOMAIN=mycompany.net
set EMAIL_FROM=%COMPUTERNAME%@mycompany.com
set EMAIL_TO=scott.tiger@mycompany.com
set EMAIL_SUBJECT=%COMPUTERNAME% Database Services Bounced 
set EMAIL_MESSAGE=Database services bounced on %COMPUTERNAME%.



:::::::::::::::::::::::::::::::::::::::::::::::
:: Init API 2.6
:::::::::::::::::::::::::::::::::::::::::::::::
mkdir %SCRIPTS_DIR%\logs > NUL 2>&1
mkdir %SCRIPTS_DIR%\tmp  > NUL 2>&1
for /f "tokens=2-8 delims=/:. " %%A in ("%date%:%time: =0%") do set "SCRIPTID=%%C%%A%%B%%D%%E"
set SCRIPTNAME=%~n0
set LOGDIR=%SCRIPTS_DIR%\logs
set TMPDIR=%SCRIPTS_DIR%\tmp
set SLOG=%SCRIPTS_DIR%\logs\%SCRIPTNAME%.session.log
set HLOG=%SCRIPTS_DIR%\logs\%SCRIPTNAME%.hist.log
set DATE_TIME_START=%date% %time%

:: Script Specific System Variables and Actions


:::::::::::::::::::::::::::::::::::::::::::
:: Heading
:::::::::::::::::::::::::::::::::::::::::::
cls
echo %SCRIPTNAME% - Started: %DATE_TIME_START%
echo =========================================================
echo ORACLE_BASE:    %ORACLE_BASE%
echo ORACLE_HOME:    %ORACLE_HOME%
echo ORACLE_SID:     %ORACLE_SID%
echo SCRIPTS_DIR:    %SCRIPTS_DIR%
echo ---------------------------------------------------------
echo SVC_DB:         %SVC_DB% 
echo SVC_LISTENER:   %SVC_LISTENER%
echo SVC_OEM:        %SVC_OEM% 
echo =========================================================
ping 127.0.0.1 -n 5 -w 1000 > NUL
echo.&&echo.


:::::::::::::::::::::::::::::::::::::::::::
:: Process Start (Init Logs)
:::::::::::::::::::::::::::::::::::::::::::
echo %SCRIPTNAME% Started: %DATE_TIME_START% >> %HLOG%
echo %DATE_TIME_START% > %SLOG%
echo %DATE_TIME_START% > %TMPDIR%\%SCRIPTNAME%.runSQL.spool


:::::::::::::::::::::::::::::::::::::::::::::
: Process
:::::::::::::::::::::::::::::::::::::::::::::

call:writeSessionLog Stopping Services
NET STOP %SVC_OEM%      >> %SLOG% 2>&1
NET STOP %SVC_DB%       >> %SLOG% 2>&1
NET STOP %SVC_LISTENER% >> %SLOG% 2>&1

call:writeSessionLog Starting Services
NET START %SVC_DB%       >> %SLOG% 2>&1
NET START %SVC_LISTENER% >> %SLOG% 2>&1
NET START %SVC_OEM%      >> %SLOG% 2>&1

call:sendMail %EMAIL_SMTP_SRV% %EMAIL_PORT% %EMAIL_FROM% %EMAIL_TO% "%EMAIL_SUBJECT%" "%EMAIL_MESSAGE%"


:::::::::::::::::::::::::::::::::::::::::::
:: End
:::::::::::::::::::::::::::::::::::::::::::
set DATE_TIME_END=!date! !time!

:: Log
echo %SCRIPTNAME% Ended:   %DATE_TIME_END% >> %HLOG%
echo %DATE_TIME_END% - %SCRIPTNAME% Ended  >> %SLOG%

:: Screen
echo.
echo %SCRIPTNAME% Ended
echo ========================================
echo Started: %DATE_TIME_START%
echo Ended:   %DATE_TIME_END%
echo.&&echo.
exit /b


::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
:: COMMON FUNCTIONS
::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

:writeSessionLog
   echo.            >> %SLOG% 2>&1
   echo %time% - %* >> %SLOG% 2>&1
   echo.            >> %SLOG% 2>&1
   echo %time% - %*
   exit /b

:runSQL
   set SQL_SCRIPT=%TMPDIR%\%SCRIPTNAME%.runSQL.sql
   (
   echo set pagesize 9999 linesize 240
   echo spool %TMPDIR%\%SCRIPTNAME%.runSQL.spool APPEND
   echo %*
   echo spool off
   ) > %SQL_SCRIPT%
   echo exit | %ORACLE_HOME%\bin\sqlplus -s "sys/%PW% as sysdba" @%SQL_SCRIPT% >> %SLOG%  2>&1
   exit /b

:sendMail
   rem Usage: call:sendMail %EMAIL_SMTP_SRV% %EMAIL_PORT% %EMAIL_FROM% %EMAIL_TO% "%EMAIL_SUBJECT%" "%EMAIL_MESSAGE%"
   set VBSCRIPT=%TMPDIR%\%SCRIPTNAME%.sendMail.vbs

   :: Create VBSCript
   (
   echo set objEmail      = CreateObject^("CDO.Message"^)
   echo objEmail.From     = "%3"
   echo objEmail.To       = "%4"
   echo objEmail.Subject  = %5
   echo objEmail.Textbody = %6
   echo objEmail.Configuration.Fields.Item ^("http://schemas.microsoft.com/cdo/configuration/sendusing"^) = 2
   echo objEmail.Configuration.Fields.Item ^("http://schemas.microsoft.com/cdo/configuration/smtpserver"^) = "%1"
   echo objEmail.Configuration.Fields.Item ^("http://schemas.microsoft.com/cdo/configuration/smtpserverport"^) = %2
   echo objEmail.Configuration.Fields.Update
   echo objEmail.Send
   ) > %VBSCRIPT%

   :: Send Email
   cscript %VBSCRIPT%
   exit /b