BeepityBop: report

Showing posts with label report. Show all posts

Thursday, 30 June 2016

Daily Drive Space Report

This post aims to setup an automatic space report email script for Windows. In Linux, the function df -m is used to display the amount of space on drives in clear readable manner.

/* Snippet taken from MOBAXTERM */
[2016-06-29 15:01.14] /drives/c/mine/sql
[$$$$] ➤ df -m
Filesystem 1M-blocks Used Available Use% Mounted on
C:/mine/home 304893 108067 196826 35% /home/mobaxterm
C: 304893 108067 196826 35% /drives/c
J: 215040 177142 37898 82% /drives/j
S: 112635 87374 25261 78% /drives/s
U: 572412 438530 133882 77% /drives/u
V: 153597 22683 130914 15% /drives/v
W: 153597 22683 130914 15% /drives/w
X: 215040 177142 37898 82% /drives/x
Y: 153597 22683 130914 15% /drives/y
Z: 572412 438530 133882 77% /drives/z

───────────────────────────────────────────────────────────────────
[2016-06-30 08:57.54] /drives/c/mine/sql
[$$$$] ➤

In Windows, there is no df -m nor does the UnxUtils df.exe seem to want to work as shown below.
C:\mine\Programs\unixutils\df -m
C:\mine\Programs\unixutils\df: cannot read table of mounted filesystems

To help keep tabs on a development environment and the disk space the backups consume, I've resorted a home made solution.

REQUIREMENTS:

1. Access to Task Scheduler
2. A from address from a recognized mail server within your domain.
3. A home folder to deploy the scripts

Step 1
Create the directories
mkdir E:\dba\script\backupfile_report

Step 2
Create the file backupfile_report.bat in the above working directory. Once created, load it with the following script:
SET WORKING_DIR=E:\dba\script\backupfile_report
SET PATH=%PATH%;%WORKING_DIR%
SET MONITOR_DIRECTORY=G:\RMAN_Backups

cd %WORKING_DIR%
E:

SETLOCAL EnableDelayedExpansion

REM change code page 1252 is used to remove the Unicode characters of the dir command output
chcp 1252

REM ^ is used to escape characters like greater or less than sybols from the output
REM The pre tag is used so that the email spaces the output at fixed spacing
echo ^<pre^> > %WORKING_DIR%\backupfile_report.log

echo FILES IN BACKUP DIR >> %WORKING_DIR%\backupfile_report.log
echo =================== >> %WORKING_DIR%\backupfile_report.log

dir /O:D %MONITOR_DIRECTORY% | find "7z" >> %WORKING_DIR%\backupfile_report.log

echo ^<br^> >> %WORKING_DIR%\backupfile_report.log
echo FREE SPACE >> %WORKING_DIR%\backupfile_report.log
echo ========== >> %WORKING_DIR%\backupfile_report.log
dir G:\ | find "bytes free" >> %WORKING_DIR%\backupfile_report.log

echo ^</pre^> >> %WORKING_DIR%\backupfile_report.log

mailhtml.vbs <FROM_ADDRESS> <TO_ADDRESS> "<SERVER> Backup Location Report" %WORKING_DIR%\backupfile_report.log

Step 3
Create the emailing script mailhtml.vbs in the same directory as the batch file.
Load the file up with the following script.

'Grab the parameters
from_address = WScript.Arguments(0)
to_address = WScript.Arguments(1)
subject = WScript.Arguments(2)
file = WScript.Arguments(3)

'Set the file reading variables
Const ForReading = 1
Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")

'Set the mail properties
SMTPServer = "<SMTP_RELAY_SERVER>"
Recipient = to_address
From = from_address
Subject = subject

'Create the mail object
GenericSendmail SMTPserver, From, Recipient, Subject, Message
Sub GenericSendmail (SMTPserver, From, Recipient, Subject, Message)
Message = fso.OpenTextFile(file,ForReading).ReadAll

'Assisgn the values to our mail object and send the mail off
set msg = WScript.CreateObject("CDO.Message")
msg.From = From
msg.To = Recipient
msg.Subject = Subject
msg.HTMLBody = Message
msg.Configuration.Fields ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = SMTPServer
msg.Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
msg.Configuration.Fields.Update
msg.Send
End Sub

Step 4
Create the task scheduler for the batch script. To see how this is done, see Step 4 in the "Cluster Health Report" post. Alternatively, import the following task scheduler export:

<?xml version="1.0" encoding="UTF-16"?>
<Task version="1.2" xmlns="http://schemas.microsoft.com/windows/2004/02/mit/task">
<RegistrationInfo>
<Date>2016-06-29T14:36:45.1784573</Date>
<Author>ADMIN</Author>
<Description>This reports to us the G:\RMAN_Backup location as well as the space left on the G drive.</Description>
</RegistrationInfo>
<Triggers>
<CalendarTrigger>
<StartBoundary>2016-06-29T05:30:00</StartBoundary>
<Enabled>true</Enabled>
<ScheduleByDay>
<DaysInterval>1</DaysInterval>
</ScheduleByDay>
</CalendarTrigger>
</Triggers>
<Principals>
<Principal id="Author">
<UserId>ADMIN</UserId>
<LogonType>S4U</LogonType>
<RunLevel>HighestAvailable</RunLevel>
</Principal>
</Principals>
<Settings>
<MultipleInstancesPolicy>IgnoreNew</MultipleInstancesPolicy>
<DisallowStartIfOnBatteries>true</DisallowStartIfOnBatteries>
<StopIfGoingOnBatteries>true</StopIfGoingOnBatteries>
<AllowHardTerminate>true</AllowHardTerminate>
<StartWhenAvailable>false</StartWhenAvailable>
<RunOnlyIfNetworkAvailable>false</RunOnlyIfNetworkAvailable>
<IdleSettings>
<StopOnIdleEnd>true</StopOnIdleEnd>
<RestartOnIdle>false</RestartOnIdle>
</IdleSettings>
<AllowStartOnDemand>true</AllowStartOnDemand>
<Enabled>true</Enabled>
<Hidden>false</Hidden>
<RunOnlyIfIdle>false</RunOnlyIfIdle>
<WakeToRun>false</WakeToRun>
<ExecutionTimeLimit>P3D</ExecutionTimeLimit>
<Priority>7</Priority>
</Settings>
<Actions Context="Author">
<Exec>
<Command>E:\dba\script\backupfile_report\backupfile_report.bat</Command>
<WorkingDirectory>E:\dba\script\backupfile_report\</WorkingDirectory>
</Exec>
</Actions>
</Task>

Here is the contents of the email sent.

OUTPUT:

FILES IN BACKUP DIR
===================
2016/06/27 05:00 PM 694 DB1_2016_06_27.7z
2016/06/27 05:12 PM 566 DB2_2016_06_27.7z
2016/06/27 05:33 PM 13 397 DB3_2016_06_27.7z
2016/06/27 05:38 PM 897 066 419 DB4_2016_06_27.7z
2016/06/27 05:38 PM 694 DB5_2016_06_27.7z
2016/06/27 05:47 PM 1 728 202 632 DB6_2016_06_27.7z
2016/06/27 05:56 PM 1 216 270 881 DB7_2016_06_27.7z
2016/06/27 06:06 PM 1 927 492 151 DB8_2016_06_27.7z
2016/06/27 06:14 PM 1 733 744 322 DB9_2016_06_27.7z
2016/06/28 05:00 PM 694 DB1_2016_06_28.7z
2016/06/28 05:24 PM 566 DB2_2016_06_28.7z
2016/06/28 05:54 PM 13 397 DB3_2016_06_28.7z
2016/06/28 05:58 PM 892 120 199 DB4_2016_06_28.7z
2016/06/28 05:58 PM 694 DB5_2016_06_28.7z
2016/06/28 06:07 PM 1 720 066 139 DB6_2016_06_28.7z
2016/06/28 06:16 PM 1 213 519 663 DB7_2016_06_28.7z
2016/06/28 06:39 PM 1 906 291 500 DB8_2016_06_28.7z
2016/06/28 06:54 PM 1 721 172 646 DB9_2016_06_28.7z
2016/06/29 05:00 PM 694 DB1_2016_06_29.7z
2016/06/29 06:10 PM 13 577 939 773 DB2_2016_06_29.7z
2016/06/29 07:00 PM 10 541 191 641 DB3_2016_06_29.7z
2016/06/29 07:16 PM 857 023 418 DB4_2016_06_29.7z
2016/06/29 07:29 PM 694 DB5_2016_06_29.7z
2016/06/29 08:13 PM 1 666 050 028 DB6_2016_06_29.7z
2016/06/29 08:49 PM 1 160 641 332 DB7_2016_06_29.7z
2016/06/29 09:16 PM 1 878 692 682 DB8_2016_06_29.7z
2016/06/29 09:43 PM 1 720 452 716 DB9_2016_06_29.7z
2016/06/29 10:18 PM 5 990 226 449 DB0_2016_06_29.7z

FREE SPACE
==========
13 Dir(s) 48 683 343 872 bytes free

Thursday, 10 March 2016

Daily Cluster Health Report

This post will show you how to setup a RAC component report: an email that sends the state of the cluster components. This helps a DBA keep an eye on the cluster and act preemptively if any component appears offline.

REQUIREMENTS:

1. Access to Task Scheduler (or cron for Linux)
2. A from address from a recognized mail server within your domain.
3. A home folder to deploy the scripts

INSTALL

1. Open up command prompt (cmd) and execute the following – adjust root location if need be
e:
mkdir E:\dba\script\clusterhealthreport
cd E:\dba\script\clusterhealthreport

2. Create a file called mail.vbs inside E:\oracle\scripts\LogReport. Copy the following and paste into the file
'Grab the parameters

from_address = WScript.Arguments(0)

to_address = WScript.Arguments(1)

subject = WScript.Arguments(2)

file = WScript.Arguments(3)

'Set the file reading variables

Const ForReading = 1

Dim fso

Set fso = CreateObject("Scripting.FileSystemObject")

'Set the mail properties

SMTPServer = "smtprelay.inter.domain.net"

Recipient = to_address

From = from_address

Subject = subject

'Create the mail object

GenericSendmail SMTPserver, From, Recipient, Subject, Message

Sub GenericSendmail (SMTPserver, From, Recipient, Subject, Message)

Message = fso.OpenTextFile(file,ForReading).ReadAll

'Assisgn the values to our mail object and send the mail off

set msg = WScript.CreateObject("CDO.Message")

msg.From = From

msg.To = Recipient

msg.Subject = Subject

msg.TextBody = Message

msg.Configuration.Fields ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = SMTPServer

msg.Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

msg.Configuration.Fields.Update

msg.Send

End Sub

3. Create a file called clusterhealthreport.bat and paste the following into it. Set the variables in yellow to what you want:

SET CLUSTERNAME=CLUSTERNAME

SET WORKING_DIR=E:\dba\script\clusterhealthreport
SET PATH=%PATH%;%WORKING_DIR%

SET ORACLE_BASE=E:\Oracle

SET ORACLE_HOME=E:\OracleGrid\11.2.0.4

cd %WORKING_DIR%

SETLOCAL EnableDelayedExpansion

echo CLUSTER HEALTH REPORT > clusterhealthreport.log

echo --------------------- >> clusterhealthreport.log

echo . >> clusterhealthreport.log

echo crsctl stat res -t >> clusterhealthreport.log

crsctl stat res -t >> clusterhealthreport.log

mail.vbs serveraddress@servername.net emailaddress@domain.net "%CLUSTERNAME% Cluster Health Report" %WORKING_DIR%\clusterhealthreport.log

4. Create a task scheduler to run once a day – I usually set it for 5:30am:

Click start, open task scheduler

Right click task scheduler library

Under the general tab, ensure the task executes with the highest privileges and runs while the user in not logged into the system. Enter a description for the server administrator.

Create a schedule – for details on their configuration see the screenshot below.

In the action tab, browse to the bat file location created in step 3

Your email should look like the following:

CLUSTER HEALTH REPORT

---------------------

crsctl stat res -t

--------------------------------------------------------------------------------

NAME TARGET STATE SERVER STATE_DETAILS

--------------------------------------------------------------------------------

Local Resources

--------------------------------------------------------------------------------

ora.DATA.dg

ONLINE ONLINE yournodedb01

ONLINE ONLINE yournodedb02

ora.H_DRIVE.dg

ONLINE ONLINE yournodedb01

ONLINE ONLINE yournodedb02

ora.LISTENER.lsnr

ONLINE ONLINE yournodedb01

ONLINE ONLINE yournodedb02

ora.asm

ONLINE ONLINE yournodedb01 Started

ONLINE ONLINE yournodedb02 Started

ora.gsd

OFFLINE OFFLINE yournodedb01

OFFLINE OFFLINE yournodedb02

ora.net1.network

ONLINE ONLINE yournodedb01

ONLINE ONLINE yournodedb02

ora.ons

ONLINE ONLINE yournodedb01

ONLINE ONLINE yournodedb02

ora.registry.acfs

ONLINE ONLINE yournodedb01

ONLINE ONLINE yournodedb02

--------------------------------------------------------------------------------

Cluster Resources

--------------------------------------------------------------------------------

ora.LISTENER_SCAN1.lsnr

1 ONLINE ONLINE yournodedb02

ora.LISTENER_SCAN2.lsnr

1 ONLINE ONLINE yournodedb01

ora.LISTENER_SCAN3.lsnr

1 ONLINE ONLINE yournodedb01

ora.cvu

1 ONLINE ONLINE yournodedb01

ora.oc4j

1 ONLINE ONLINE yournodedb01

ora.scan1.vip

1 ONLINE ONLINE yournodedb02

ora.scan2.vip

1 ONLINE ONLINE yournodedb01

ora.scan3.vip

1 ONLINE ONLINE yournodedb01

ora.database.db

1 ONLINE ONLINE yournodedb01 Open

2 ONLINE ONLINE yournodedb02 Open

ora.yournodedb01.vip

1 ONLINE ONLINE yournodedb01

ora.yournodedb02.vip

1 ONLINE ONLINE yournodedb02

Wednesday, 9 March 2016

Alert Log Daily Report

This document will show you how to setup an alert log report: an email that sends the alert log trail since the last report was sent. This helps a DBA keep an eye on the alert log of a database and helps the administrators spot any issues pre-emptively

REQUIREMENTS:

1. Access to Task Scheduler (or cron for Linux)
2. A from address from a recognized mail server within your domain.
3. A home folder to deploy the scripts
4. Download UnixUtils – the 20+ unix utilities adapted for windows-in particular: wc.exe and tail.exe

INSTALL

1. Open up command prompt (cmd) and execute the following – adjust root location if need be
e:
mkdir E:\oracle\scripts\LogReport
cd E:\oracle\scripts\LogReport

2. Create a file called mail.vbs inside E:\oracle\scripts\LogReport. Copy the following and paste into the file
'Grab the parameters

from_address = WScript.Arguments(0)

to_address = WScript.Arguments(1)

subject = WScript.Arguments(2)

file = WScript.Arguments(3)

'Set the file reading variables

Const ForReading = 1

Dim fso

Set fso = CreateObject("Scripting.FileSystemObject")

'Set the mail properties

SMTPServer = "smtprelay.inter.domain.net"

Recipient = to_address

From = from_address

Subject = subject

'Create the mail object

GenericSendmail SMTPserver, From, Recipient, Subject, Message

Sub GenericSendmail (SMTPserver, From, Recipient, Subject, Message)

Message = fso.OpenTextFile(file,ForReading).ReadAll

'Assisgn the values to our mail object and send the mail off

set msg = WScript.CreateObject("CDO.Message")

msg.From = From

msg.To = Recipient

msg.Subject = Subject

msg.TextBody = Message

msg.Configuration.Fields ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = SMTPServer

msg.Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

msg.Configuration.Fields.Update

msg.Send

End Sub

3. Create a file called logreport_DBNAME.bat and paste the following into it. Set the variables in yellow to what you want:
SET ORACLE_SID=DBNAME

SET ORACLE_BASE=E:\Oracle

SET ALERT_LOG=%ORACLE_BASE%\diag\rdbms\%ORACLE_SID%\%ORACLE_SID%\trace\alert_%ORACLE_SID%.log

SET WORKING_DIR=E:\oracle\scripts\LogReport

SET PATH=%PATH%;%WORKING_DIR%

cd %WORKING_DIR%

SETLOCAL EnableDelayedExpansion

echo Alert Log Difference

`echo --------------------

echo .

echo Load the last line count taken into a variable...

set /p old_linecount=<linecount.txt

echo Get the current line count of the database alert log...

wc -l < %ALERT_LOG% > linecount.txt

set /p new_linecount=<linecount.txt

echo Subtract the latest from the previous...

set /a difference=%new_linecount%-%old_linecount%

echo %difference%

if %difference% geq 3000 set difference=3000

if %difference% geq 1 tail -n %difference% %ALERT_LOG% > alert_%ORACLE_SID%.out

if %difference% equ 0 echo The alert log has not change or is empty, if you receive this message twice, investigate the log file %ALERT_LOG%. > alert_%ORACLE_SID%.out

if %difference% lss 0 echo The alert log %ALERT_LOG% has changed in a way where yesterdays file has a greater line count than todays, if you receive this message twice, investigate the log file. > alert_%ORACLE_SID%.out

mail.vbs servername@domainname.net emailaddress@domainname.net "%ORACLE_SID% DailyAlert" %WORKING_DIR%\alert_%ORACLE_SID%.out

4. Copy cat.exe (unixutils) into E:\oracle\scripts\LogReport or unix utils in your %PATH% location

5. Create a task scheduler to run once a day – I usually set it for 5:30am:

Click start, open task scheduler

Right click task scheduler library

Under the general tab, ensure the task executes with the highest privileges and runs while the user in not logged into the system. Enter a description for the server administrator.

In the action tab, browse to the bat file location created in step 3

Create a schedule – for details on their configuration see the screenshot below.

Schedule the job to run at 2 times – create 2 schedules on daily repeat