Batch script to monitor ORDS service and send mail notification it goes down

 

Hi Everyone,

 

Recently I came across an issue where ords service on windows was getting stopped due to some reason. It was hard to monitor as they had no mechanism to monitor this.

 

To mitigate the problem, I wrote a batch script which will monitor ords service’s uptime on windows . The script will send a mail once notifying service has gone down.

 

Save the following script in .bat format and schedule it as per your requirement in task scheduler.

 

@echo off setlocal enabledelayedexpansion set my_date=%date% set my_time=%TIME% set my_hours=%my_time:~0,2% set my_minutes=%my_time:~3,2% set my_seconds=%my_time:~6,2%   REM Define variables set APEX_URL=http://server1.example.com:7799/ords set MAIL_RECIPIENT='soumya.das@testmail.com' set SMTP_SERVER=smtp.office365.com set SMTP_PORT=587 set MAIL_SENDER=notification@example.com set MAIL_SUBJECT="Oracle APEX URL Status" set SMTP_USER=emr-notification@example.com set SMTP_PASSWORD=xyz123# set SUBJECT="Apex down Alert" set HOSTNAME=%COMPUTERNAME%   REM Check if the URL is accessible curl -s -o NUL -w "%%{http_code}" %APEX_URL% > D:\response.txt   REM Check if D:\response.txt exists if not exist D:\response.txt (     REM If D:\response.txt is not present, send email notification                powershell.exe -command "Send-MailMessage -From '%MAIL_SENDER%' -To %MAIL_RECIPIENT% -Subject '%SUBJECT%' -Body 'Oracle APEX URL is not accessible at %HOSTNAME% .Apex Service is down.' -SmtpServer '%SMTP_SERVER%' -Port %SMTP_PORT% -UseSsl -Credential (New-Object PSCredential('%SMTP_USER%', (ConvertTo-SecureString '%SMTP_PASSWORD%' -AsPlainText -Force)))" )   REM Read the HTTP status code set /p HTTP_STATUS=<D:\response.txt   REM Check if HTTP status code is 000, indicating a failure if "%HTTP_STATUS%" equ "503" (     powershell.exe -command "Send-MailMessage -From '%MAIL_SENDER%' -To %MAIL_RECIPIENT% -Subject '%SUBJECT%' -Body 'Oracle APEX URL is not accessible at %HOSTNAME%.Apex Service is down.' -SmtpServer '%SMTP_SERVER%' -Port %SMTP_PORT% -UseSsl -Credential (New-Object PSCredential('%SMTP_USER%', (ConvertTo-SecureString '%SMTP_PASSWORD%' -AsPlainText -Force)))" ) else if "%HTTP_STATUS%" equ "000" (               powershell.exe -command "Send-MailMessage -From '%MAIL_SENDER%' -To %MAIL_RECIPIENT% -Subject '%SUBJECT%' -Body 'Oracle APEX URL is not accessible at %HOSTNAME%.Apex Service is down.' -SmtpServer '%SMTP_SERVER%' -Port %SMTP_PORT% -UseSsl -Credential (New-Object PSCredential('%SMTP_USER%', (ConvertTo-SecureString '%SMTP_PASSWORD%' -AsPlainText -Force)))" )   REM Cleanup temporary files del D:\response.txt   endlocal

 

Batch script to kill blocked session

An oracle database administrator often face a situation where a deadlock is observed in database. A deadlock occurs when two or more sessions are waiting for data locked by each other, resulting in all the sessions being blocked.

As a DBA, the only way you can resolve a lock by killing either the blocking session or blocked session.

To automate this process, I have prepared a batch script for windows which will kill the session after 10 mins of getting locked.

Step 1. Lets prepare the sql first which will find out the session that are locked more than 10mins

SET LINESIZE 1000 SET PAGESIZE 1000 SET FEEDBACK OFF   SELECT s.sid, s.serial#, ROUND((SYSDATE - s.logon_time) * 24 * 60) AS logon_time_minutes FROM v$session s, v$session_wait w WHERE s.sid = w.sid AND w.event = 'enq: TX - row lock contention' AND s.status = 'ACTIVE' AND (SYSDATE - s.logon_time) * 24 * 60 >= 10; exit;

Lets save the above sql as blocked_sessions_query.sql

Step 2. I will now prepare the batch script which will kill the session based on output retrieved from above sql

@echo on   REM Oracle database connection details   SET ORACLE_USER=sys SET ORACLE_PASSWORD=welcome123# SET ORACLE_SID=ORCL SET ORACLE_HOME=D:\oracle\product\19.3.0\dbhome_1   REM Define log file path SET LOG_FILE=F:\script_log.txt   REM SQL file containing the query to identify blocked sessions SET SQL_FILE=F:\blocked_sessions_query.sql   %ORACLE_HOME%\bin\sqlplus -S %ORACLE_USER%/%ORACLE_PASSWORD%@%ORACLE_SID% as sysdba @%SQL_FILE% > F: \session_id.txt     REM Execute SQL query using SQL*Plus and fetch results FOR /F "tokens=1,2,3" %%a IN (F:\session_id.txt) DO (     REM Set session details     set sid=%%a     set serial#=%%b     set logon_time_minutes=%%c )     REM If session has been active for at least 15 minutes, kill the session     if %logon_time_minutes% GEQ 15 (         echo Killing session %sid% (serial#: %serial#%)         echo ALTER SYSTEM KILL SESSION '%sid%,%serial#%' immediate; | %ORACLE_HOME%\bin\sqlplus -S %ORACLE_USER%/%ORACLE_PASSWORD%@%ORACLE_SID% as sysdba         REM Call email notification function here     )     exit /b

Save the above batch file as deadlock_kill.bat

Step 3. Now schedule the above script in task scheduler

Click on and provide password to set it.

Disclaimer:- This script should be tested properly in uat/test environment and it is not advised to run it directly on production database. The script automatically selects the session and kills it. However before killing the session, it is recommended you must send the information to application team to get their approval on which session to be killed.

Batch script to check tablespace usage and send email notification

 

Issue: We had a requirement for a customer, where oracle 19c database was running on Windows server 2019. Customer wanted to monitor tablespace’s datafile usage and get email alert incase the used space is less than 80%

To mitigate this , we developed a batch script leveraging powershell’s Send-Maillmessage command to achieve this.

 

Step 1.

First we create the sql which will be used to monitor the tablespace usage. Lets save it as E:\BackupScripts\Tbs_size_check.sql

Set lines 222 SELECT d.tablespace_name "TS Name", d.num_files "Num Files",        d.asize "Size MB", NVL (f.freebytes, 0) "Free MB",        (d.BYTES - NVL (f.freebytes, 0)) "Used MB",        ((d.BYTES - NVL (f.freebytes, 0)) * 100) / d.asize "Percent Used"   FROM (SELECT   tablespace_name, SUM (BYTES) / 1024 / 1024 freebytes             FROM dba_free_space         GROUP BY tablespace_name) f,        (SELECT   tablespace_name, COUNT (DISTINCT (file_id)) num_files,                  sum(greatest(maxbytes,bytes))/1024/1024 asize,                  SUM (BYTES) / 1024 / 1024 BYTES             FROM dba_data_files         GROUP BY tablespace_name) d  WHERE d.tablespace_name = f.tablespace_name(+)    AND d.tablespace_name not like '%UNDO%'    AND ROUND (((d.BYTES - NVL (f.freebytes, 0)) * 100) / d.asize) > 80;    exit;

Step 2.

Now lets create the batch script that will be used to monitor the tablespace usage and send an alert if the usage hits a predefined threshold value. In our case we have set threshold value to 80%. This can be modified as per the requirement.

@echo on REM Database credentials set DB_USER=sys set DB_PASS=passfordb set DB_SID=orcl REM Email configuration set EMAIL_RECIPIENT=soumya.das@testmail.com,usertwo@testmail.com set EMAIL_SUBJECT=Tablespace Usage Alert set FROM_EMAIL=database.alerts@testmail.com set HOSTNAME=%COMPUTERNAME% set EMAIL_BODY=Tablespace usage is  LOW at %HOSTNAME%. Please take appropriate action. set ATTACHMENTS=E:\TABLESPACE_USAGE.txt REM Execute SQL queries using SQL*Plus REM Execute SQL query and send email if threshold is met E:\app\oracle\product\19.3.0\dbhome_1\bin\sqlplus -S %DB_USER%/%DB_PASS%@%DB_SID% as sysdba @E:\BackupScripts\TS_SPACE_CHECK1.SQL > E:\TABLESPACE_USAGE.txt cd E:\ REM Read usage percent and tablespace name from TABLESPACE_USAGE.txt for /f "tokens=1,6" %%a in (E:\TABLESPACE_USAGE.txt) do (     set tablespace_name=%%a     set usage_percent=%%b ) rem echo %usage_percent%     REM Check if usage_percent is greater than or equal to the threshold if %usage_percent% GEQ 80 (         REM Send email alert with tablespace name and usage percentage powershell -ExecutionPolicy Bypass -Command "Send-MailMessage -From 'database.alerts@testmail.com' -To %EMAIL_RECIPIENT% -Subject '%EMAIL_SUBJECT%' -Body '%EMAIL_BODY%' -SmtpServer '192.24.1.40' -Attachments '%ATTACHMENTS%'" ) del E:\TABLESPACE_USAGE.txt

Open task scheduler and follow the following steps to schedule the batch script to run every 1 hour.

Done…

batch script to send email notification once if any service goes down and sends mail once service is up

Hi Everyone,

Recently I came across an issue where oracle service on windows was getting stopped due to some reason. Since customer was not using any monitoring tool, it was very hard to track when the service was going down.

To mitigate the problem , I wrote a batch script which will monitor oracleservice and listener service on windows . The script will send a mail once notifying service has gone down and once service comes back it will send another notification on email with service is running message.

Save the following script in .bat format and schedule it as per your requirement in task scheduler.

@echo off setlocal enabledelayedexpansion set my_date=%date% set my_time=%TIME% set my_hours=%my_time:~0,2% set my_minutes=%my_time:~3,2% set my_seconds=%my_time:~6,2%   rem Configure email settings   set TO_EMAIL=soumya.das@email.com set FROM_EMAIL=noreply@alerts.com set SMTP_SERVER=smtp.gmail.com set SMTP_PORT=587  set SMTP_USER=noreply@alerts.com set SMTP_PASSWORD=Password set SUBJECT="Service Monitor Alert"   rem List of services to monitor (add more as needed) set "SERVICES=OracleServiceORCL OracleOraDB19Home1TNSListener"   rem Create a temporary file to store service status set STATUS_FILE=status.txt   rem Loop through the services and check their status using PowerShell for %%s in (%SERVICES%) do (     sc query "%%s" | find "STATE" | find "RUNNING" >nul     if errorlevel 1 (         echo %%s service is not running.         if not exist !STATUS_FILE! (             echo Sending email notification.             powershell -command "Send-MailMessage -From '%FROM_EMAIL%' -To '%TO_EMAIL%' -Subject '%SUBJECT%' -Body 'The %%s service is not running.' -SmtpServer '%SMTP_SERVER%' -Port %SMTP_PORT% -UseSsl -Credential (New-Object PSCredential('%SMTP_USER%', (ConvertTo-SecureString '%SMTP_PASSWORD%' -AsPlainText -Force)))"             rem Create the status file to indicate that an alert has been sent             echo DOWN > !STATUS_FILE!         )     ) else (         echo %%s service is running.         if exist !STATUS_FILE! (             echo Sending email notification.             powershell -command "Send-MailMessage -From '%FROM_EMAIL%' -To '%TO_EMAIL%' -Subject 'Service Monitor Alert' -Body 'The %%s service is now running.' -SmtpServer '%SMTP_SERVER%' -Port %SMTP_PORT% -UseSsl -Credential (New-Object PSCredential('%SMTP_USER%', (ConvertTo-SecureString '%SMTP_PASSWORD%' -AsPlainText -Force)))"             rem Delete the status file to indicate that the service is running             del !STATUS_FILE!         )     ) )   endlocal

Batch script to take database users backup in sql server

SQL Server Version:- SQL 2014

Create a batch file which will be scheduled on task scheduler.

set backuplogfilename=%date:~-7,2%-%date:~-10,2%-%date:~-4,4%-0%time:~1,1%%time:~3,2%%time:~6,2%
SQLCMD.EXE -S localhost -U sa -P "sa@123" -i "E:\users.sql"  >> "E:\users_%backuplogfilename%.log"
-- save and exit

Now schedule the above .bat file in task scheduler


Create a sql file lets say users.sql
Content of users.sql

USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
  DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
    @binvalue varbinary(256),
    @hexvalue varchar (514) OUTPUT
AS
DECLARE @charvalue varchar (514)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
  DECLARE @tempint int
  DECLARE @firstint int
  DECLARE @secondint int
  SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
  SELECT @firstint = FLOOR(@tempint/16)
  SELECT @secondint = @tempint - (@firstint*16)
  SELECT @charvalue = @charvalue +
    SUBSTRING(@hexstring, @firstint+1, 1) +
    SUBSTRING(@hexstring, @secondint+1, 1)
  SELECT @i = @i + 1
END

SELECT @hexvalue = @charvalue
GO

IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
  DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @type varchar (1)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary  varbinary (256)
DECLARE @PWD_string  varchar (514)
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string varchar (514)
DECLARE @tmpstr  varchar (1024)
DECLARE @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)

DECLARE @defaultdb sysname

IF (@login_name IS NULL)
  DECLARE login_curs CURSOR FOR

      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
      ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
ELSE
  DECLARE login_curs CURSOR FOR


      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
      ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
OPEN login_curs

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
IF (@@fetch_status = -1)
BEGIN
  PRINT 'No login(s) found.'
  CLOSE login_curs
  DEALLOCATE login_curs
  RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
WHILE (@@fetch_status <> -1)
BEGIN
  IF (@@fetch_status <> -2)
  BEGIN
    PRINT ''
    SET @tmpstr = '-- Login: ' + @name
    PRINT @tmpstr
    IF (@type IN ( 'G', 'U'))
    BEGIN -- NT authenticated account/group

      SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
    END
    ELSE BEGIN -- SQL Server authentication
        -- obtain password and sid
            SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
        EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
        EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

        -- obtain password policy state
        SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
        SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name

            SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'

        IF ( @is_policy_checked IS NOT NULL )
        BEGIN
          SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
        END
        IF ( @is_expiration_checked IS NOT NULL )
        BEGIN
          SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
        END
    END
    IF (@denylogin = 1)
    BEGIN -- login is denied access
      SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
    END
    ELSE IF (@hasaccess = 0)
    BEGIN -- login exists but does not have access
      SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
    END
    IF (@is_disabled = 1)
    BEGIN -- login is disabled
      SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
    END
    PRINT @tmpstr
  END

  FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
   END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO

exec dbo.sp_help_revlogin
go