Oracle Database Monitoring and Alerting scripts for Windows

Windows Monitoring and Alerting, and Backup Scripts

I searched the net for a set of Windows Oracle monitoring and alerting scripts and was astounded to find that there were none out there (that I could find anyway) so I wrote my own. Except for a host space one which I did find and edited for my needs.

Feel free to adapt these as you see fit.

You will obviously need to change the email details and the host details, I have created dummy entries here.

Job Schedules

The scripts detailed in this document were scheduled using Windows Task Scheduler. Note that when implementing, the user that runs them needs to be ‘SYSTEM’ in order to have the correct permissions.

All the monitoring scripts are in “D:\scripts”.

The backup jobs are in “D:\scripts\backups”.

The log files are in the “D:\scripts\logs” and “D:\scripts\backup\logs” directories.

Backup log files and error log files are also mailed to the support team.

The scripts are called by ‘bat’ files.

Job	Schedule	SMS
Alert Log Check	Every 30 minutes	No
Tablespace Space Check	Every 1 hour	On Critical
Tablespace Space Report	Every Week (Mon 0800)	No
Host Space Check	Every 1 hour	On Critical
Host Space Report	Every Week (Mon 0830)	No
Services Check	Every 5 minutes	Yes
Full Database Backup (on standby host)	Every day at 0200	No
Archived Log Backup	Every 2 hours	No
Data Guard Check	Every 2 hours	No

Only the Services check, data guard check and the full backup jobs are enabled on a standby host, the rest are installed and scheduled but disabled. They should be enabled for a switchover, and the corresponding jobs disabled /enabled on the new standby.

Script Details

Alert Log monitor

This script copies the alert log to a local directory and scans it for ORA- errors and puts the results to a file. At this stage there is no difference between a critical and a warning. Note that some ORA- errors have been filtered out, you can add others there to ensure there are no false alerts.

It compares these results with the previous error log file to avoid duplicating the alerts.

If it finds a difference, i.e. new errors, it will send a mail.

A batch file calls a Powershell script:

alert_log_check.bat

@ECHO OFF
Powershell.exe -Command "& 'D:\scripts\alert_log_check.ps1'"

Powershell script:

alert_log_check.ps1

# Script to check the alert log and email if an entry is found

# It calls the sendmail_alertlog_att.ps1 script which needs the subject line

# hard-coded as per our standards (client:hostname:dbname:metric:level) –

# MGA:andy-pc:ANDY:Alert Log:WARNING

#

# Note you need to pre-create the old_errors.txt and errors.txt files or it will error.

#

# Andy Horne    31/07/2019

#

cd D:\scripts

Remove-Item -Path "D:\scripts\logs\old_errors.txt"

Rename-Item -Path "D:\scripts\logs\errors.txt" -NewName "D:\scripts\logs\old_errors.txt"

Copy-Item -path "D:\app\oracle\diag\rdbms\sid\trace\alert_sid.log" -Destination "D:\scripts\logs"

Get-Content .\logs\alert_sid.log | Select-String -Pattern ORA- -Context 1,1 >.\logs\ora_errors.txt

Get-Content .\logs\ora_errors.txt | Select-String -Pattern "ORA-2000|ORA-12609" -NotMatch >.\logs\errors_temp.txt

Get-Content .\logs\errors_temp.txt | Select-String -Pattern ORA -Context 1,1 >.\logs\errors.txt

Compare-Object (Get-Content "D:\scripts\logs\old_errors.txt") (Get-Content "D:\scripts\logs\errors.txt") | format-list | Out-File "D:\scripts\logs\new_errors.txt"

if ($(get-item -path  D:\scripts\logs\new_errors.txt).length -gt 2) {invoke-expression -Command .\sendmail_alert_log_att.ps1}

If you want to add filters to prevent known issues sending an alert, add the string to this line inside the quotes separated by ‘|’:

Get-Content .\logs\ora_errors.txt | Select-String -Pattern "ORA-2000|ORA-12609" -NotMatch >.\logs\errors_temp.txt

So this is currently filtering ORA-2000 and ORA-12609 errors.

You can test this by creating a dummy entry in the alert log. Run the powershell or bat script manually once to create a new error file, then run it again to clear out the existing entries. Next, run the code below to generate a dummy entry.

SQL> begin

  2  sys.dbms_system.ksdwrt(2, to_char(sysdate)||'ORA-99999 Alert Log Test. Please ignore.');

  3  end;

  4  /

PL/SQL procedure successfully completed.

Finally run the bat or powershell script for a third time and an alert should be generated and an email sent.

The actual alert log entry is in the ‘new_errors.txt’ file –it also sends it as an attachment in the email.

new_errors.txt contents:

InputObject   :     2020-01-07T08:38:57.103852+11:00

SideIndicator : =>

InputObject   : > > 07-JAN-20ORA-99999 Alert Log Test. Please ignore.

SideIndicator : =>

InputObject   :  

SideIndicator : =>

Troubleshooting

Sometimes the script may start detecting old entries – not sure why this happens. The fix is to delete all the log files created by the script from the ‘D:\scripts\logs’ directory and manually run the job 3 times to recreate the files. It should then start to work again. Alternatively, rotate the alert log.

This is the sendmail script – note the username / password need changing, and the subject etc. This sends the new_errors.txt as an attachment.

sendmail_alert_log_att.ps1

# Script to send a mail and attach the error log if there is a full backup error. Called by full_backup_log_check.ps1

# It needs the subject line hard-coded as per our standards (client:hostname:dbname:metric:level) –

# MGA:andy-pc:ANDY:Alert Log:WARNING

#

# Andy Horne    31/07/2019

#

$EmailTo = "yourmail@you.com.au"

$EmailFrom = "hostname@client.com.au"

$Subject = "CLIENT:PROD HOST:SID:RMAN Backup:WARNING"

$Body = "RMAN Backup Error Detected"

$SMTPServer = "client.smtp.com.au"

$filenameAndPath = "D:\scripts\backups\logs\full_backup_errors.txt"

$SMTPMessage = New-Object System.Net.Mail.MailMessage($EmailFrom,$EmailTo,$Subject,$Body)

$attachment = New-Object System.Net.Mail.Attachment($filenameAndPath)

$SMTPMessage.Attachments.Add($attachment)

$SMTPClient = New-Object Net.Mail.SmtpClient($SmtpServer, 25)

$SMTPClient.Credentials = New-Object System.Net.NetworkCredential("mail_username", "password");

$SMTPClient.Send($SMTPMessage)

DB and Listener Services Check

The services need to be hard-coded in the powershell script. Open the ‘Control Panel / Services’ in Windows, locate the database and listener services and had-code the names into the powershell script.

The batch file calls the Powershell script:

db_services_check.bat

@ECHO OFF

Powershell.exe -Command "& 'D:\scripts\db_services_check.ps1'"

Powershell script:

#

# Script to check if services are running - need to be hard-coded

#

# Andy Horne 31/07/2019

#

$servicename = "OracleServiceSID"

if(!((Get-Service $servicename).status -eq 'running')){invoke-expression -Command .\sendmail_dbservice.ps1}

$servicename = "OracleOraDB12Home1TNSListener"

if(!((Get-Service $servicename).status -eq 'running')){invoke-expression -Command .\sendmail_lsnrservice.ps1} 

To test, stop the listener (not in an environment that is being used though), then run it. This was performed in a test environment.

These are the sendmail scripts. They also send an SMS:

# Script to send a mail and SMS if the database service is down. Called by db_services_check.ps1

# It needs the subject line hard-coded as per our standards (client:hostname:dbname:metric:level) –

# MGA:andy-pc:ANDY:Alert Log:WARNING

#

# Andy Horne    31/07/2019

#

$EmailFrom = “hostname@client.com.au”

$EmailToAddresses = @(“someone@somewhere.com.au”,"phone_no_for_sms@messagenet.com.au")

$Subject = “CLIENT:PROD HOST:SID:DB Service:CRITICAL”

$Body = “The SID Windows Service on the CLIENT PROD Host is down”

$SMTPServer = “client.smtp.com.au”

$SMTPClient = New-Object Net.Mail.SmtpClient($SmtpServer, 25)

$SMTPClient.Credentials = New-Object System.Net.NetworkCredential(“mail_username”, “password”);

foreach ($EmailTo in $EmailToAddresses)

{

    $SMTPClient.Send($EmailFrom, $EmailTo, $Subject, $Body)

}

# Script to send a mail and SMS if the listener service is down. Called by db_services_check.ps1

# It needs the subject line hard-coded as per our standards (client:hostname:dbname:metric:level) –

# MGA:andy-pc:ANDY:Alert Log:WARNING

#

# Andy Horne    31/07/2019

#

$EmailFrom = “hostname@client.com.au”

$EmailToAddresses = @(“someone@somewhere.com.au”,"phone_num_for_sms@messagenet.com.au")

$Subject = “CLIENT:PROD HOST:SID:Listener Service:CRITICAL”

$Body = “The Listener Windows Service on the PROD Host is down”

$SMTPServer = “client.smtp.com.au”

$SMTPClient = New-Object Net.Mail.SmtpClient($SmtpServer, 25)

$SMTPClient.Credentials = New-Object System.Net.NetworkCredential("mail_username", "password");

foreach ($EmailTo in $EmailToAddresses)

{

    $SMTPClient.Send($EmailFrom, $EmailTo, $Subject, $Body)

}

Host Space Check

I copied the Powershell scipt. There are 2, because the disks are different sizes and the script only has one threshold.

This is the batch job that calls them:

@ECHO OFF
Powershell.exe -Command "& 'D:\scripts\big_host_space_check.ps1'"

And

@ECHO OFF
Powershell.exe -Command "& 'D:\scripts\small_host_space_check.ps1'"

These are the Powershell scripts – the sendmail script is embedded. Change the $minSize value as appropriate. These scripts also send an SMS

big_host_space_check.ps1

#

# Script to check on disk space of large disks.

# Copied from http://www.ryadel.com/RunningLow

#

# Drives to check: set to $null or empty to check all local (non-network) drives

# $drives = @("C","D");

$drives = @("E","R");

# The minimum disk size to check for raising the warning

$minSize = 100GB;

# SMTP configuration: username, password & so on

$email_username = "username";

$email_password = "password";

$email_smtp_host = "client.smtp.com.au";

$email_smtp_port = 25;

$email_from_address = "hostname@client.com.au";

$email_to_addressArray = @("you@youremail.com.au","phone_no_for_sms@messagenet.com.au");

if ($drives -eq $null -Or $drives -lt 1) {

       $localVolumes = Get-WMIObject win32_volume;

       $drives = @();

    foreach ($vol in $localVolumes) {

           if ($vol.DriveType -eq 3 -And $vol.DriveLetter -ne $null ) {

                 $drives += $vol.DriveLetter[0];

             }

       }

}

foreach ($d in $drives) {

       Write-Host ("`r`n");

       Write-Host ("Checking drive " + $d + " ...");

       $disk = Get-PSDrive $d;

       if ($disk.Free -lt $minSize) {

             Write-Host ("Drive " + $d + " has less than " + $minSize `

                    + " bytes free (" + $disk.free + "): sending e-mail...");

            

             $message = new-object Net.Mail.MailMessage;

             $message.From = $email_from_address;

             foreach ($to in $email_to_addressArray) {

                    $message.To.Add($to);

             }

#            $message.Subject = ("[RunningLow] WARNING: " + $env:computername + " drive " + $d);

#            $message.Subject += (" has less than " + $minSize + " bytes free ");

#            $message.Subject += ("(" + $disk.Free + ")");

        $message.Subject = ("CLIENT:Hostname:NA:Host Space:WARNING");

#            $message.Body =           "Hello there, `r`n`r`n";

#            $message.Body +=    "this is an automatic e-mail message ";

#            $message.Body +=    "sent by RunningLow Powershell script ";

             $message.Body =     ("Note that " + $env:computername + " drive " + $d + " ");

             $message.Body +=    "is running low on free space. `r`n`r`n";

             $message.Body +=    "--------------------------------------------------------------";

             $message.Body +=    "`r`n";

             $message.Body +=    ("Machine HostName: " + $env:computername + " `r`n");

#            $message.Body +=    "Machine IP Address(es): ";

#            $ipAddresses = Get-NetIPAddress -AddressFamily IPv4;

#            foreach ($ip in $ipAddresses) {

#                if ($ip.IPAddress -like "127.0.0.1") {

#                       continue;

#                   }

#                $message.Body += ($ip.IPAddress + " ");

#            }

             $message.Body +=    "`r`n";

             $message.Body +=    ("Used space on drive " + $d + ": " + $disk.Used + " bytes. `r`n");

             $message.Body +=    ("Free space on drive " + $d + ": " + $disk.Free + " bytes. `r`n");

             $message.Body +=    "--------------------------------------------------------------";

             $message.Body +=    "`r`n`r`n";

             $message.Body +=    "This warning will fire when the free space is lower ";

             $message.Body +=    ("than " + $minSize + " bytes `r`n`r`n");

#            $message.Body +=    "Sincerely, `r`n`r`n";

#            $message.Body +=    "-- `r`n";

#            $message.Body +=    "RunningLow`r`n";

#                $message.Body +=        "http://www.ryadel.com/RunningLow";

             $smtp = new-object Net.Mail.SmtpClient($email_smtp_host, $email_smtp_port);

             $smtp.Credentials = New-Object System.Net.NetworkCredential($email_username, $email_password);

             $smtp.send($message);

             $message.Dispose();

#            write-host "... E-Mail sent!" ;

       }

       else {

             Write-Host ("Drive " + $d + " has more than " + $minSize + " bytes free: nothing to do.");

       }

} 

And small_host_space_check.ps1:

#

# Script to check on disk space of small disks.

# Copied from http://www.ryadel.com/RunningLow

#

# Drives to check: set to $null or empty to check all local (non-network) drives

# $drives = @("C","D");

$drives = @("C","D","F");

# The minimum disk size to check for raising the warning

$minSize = 5GB;

# SMTP configuration: username, password & so on

$email_username = "username";

$email_password = "password_goes_here";

$email_smtp_host = "client.smtp.com.au";

$email_smtp_port = 25;

$email_from_address = "hostname@client.com.au";

$email_to_addressArray = @("someone@somewhere.com.au","phone_num_for_sms@messagenet.com.au");

if ($drives -eq $null -Or $drives -lt 1) {

       $localVolumes = Get-WMIObject win32_volume;

       $drives = @();

    foreach ($vol in $localVolumes) {

           if ($vol.DriveType -eq 3 -And $vol.DriveLetter -ne $null ) {

                 $drives += $vol.DriveLetter[0];

             }

       }

}

foreach ($d in $drives) {

       Write-Host ("`r`n");

       Write-Host ("Checking drive " + $d + " ...");

       $disk = Get-PSDrive $d;

       if ($disk.Free -lt $minSize) {

             Write-Host ("Drive " + $d + " has less than " + $minSize `

                    + " bytes free (" + $disk.free + "): sending e-mail...");

            

             $message = new-object Net.Mail.MailMessage;

             $message.From = $email_from_address;

             foreach ($to in $email_to_addressArray) {

                    $message.To.Add($to);

             }

#            $message.Subject = ("[RunningLow] WARNING: " + $env:computername + " drive " + $d);

#            $message.Subject += (" has less than " + $minSize + " bytes free ");

#            $message.Subject += ("(" + $disk.Free + ")");

        $message.Subject = ("CLIENT:Prod Host:NA:Host Space:WARNING");

#            $message.Body =           "Hello there, `r`n`r`n";

#            $message.Body +=    "this is an automatic e-mail message ";

#            $message.Body +=    "sent by RunningLow Powershell script ";

             $message.Body =     ("Note that " + $env:computername + " drive " + $d + " ");

             $message.Body +=    "is running low on free space. `r`n`r`n";

             $message.Body +=    "--------------------------------------------------------------";

             $message.Body +=    "`r`n";

             $message.Body +=    ("Machine HostName: " + $env:computername + " `r`n");

#            $message.Body +=    "Machine IP Address(es): ";

#            $ipAddresses = Get-NetIPAddress -AddressFamily IPv4;

#            foreach ($ip in $ipAddresses) {

#                if ($ip.IPAddress -like "127.0.0.1") {

#                       continue;

#                   }

#                $message.Body += ($ip.IPAddress + " ");

#            }

             $message.Body +=    "`r`n";

             $message.Body +=    ("Used space on drive " + $d + ": " + $disk.Used + " bytes. `r`n");

             $message.Body +=    ("Free space on drive " + $d + ": " + $disk.Free + " bytes. `r`n");

             $message.Body +=    "--------------------------------------------------------------";

             $message.Body +=    "`r`n`r`n";

             $message.Body +=    "This warning will fire when the free space is lower ";

             $message.Body +=    ("than " + $minSize + " bytes `r`n`r`n");

#            $message.Body +=    "Sincerely, `r`n`r`n";

#            $message.Body +=    "-- `r`n";

#            $message.Body +=    "RunningLow`r`n";

#                $message.Body +=        "http://www.ryadel.com/RunningLow";

             $smtp = new-object Net.Mail.SmtpClient($email_smtp_host, $email_smtp_port);

             $smtp.Credentials = New-Object System.Net.NetworkCredential($email_username, $email_password);

             $smtp.send($message);

             $message.Dispose();

#            write-host "... E-Mail sent!" ;

       }

       else {

             Write-Host ("Drive " + $d + " has more than " + $minSize + " bytes free: nothing to do.");

       }

} 

Test this by seeing how much space is free on one of the disks and changing the threshold in the script.

If the drive(s) have less than the specified space free, it will send a mail

The initial mail is overly verbose – I edited the script to reduce the text.

There is also a weekly host space report to assist with capacity planning

Batch file

@ECHO OFF

Powershell.exe -Command "& 'D:\scripts\weekly_host_space_report.ps1'"

Powershell

# Script to produce a weekly host space report which is emailed. Schedule to run once a week.

#

# Andy Horne    31/07/2019

#

cd D:\scripts

Get-WmiObject -Class Win32_logicaldisk | Select-Object -Property DeviceID, DriveType, VolumeName, @{L='FreeSpaceGB';E={"{0:N2}" -f ($_.FreeSpace /1GB)}},@{L="Capacity";E={"{0:N2}" -f ($_.Size/1GB)}} | out-file 'D:\scripts\logs\host_space_report.txt'

if ($(get-item -path  D:\scripts\logs\host_space_report.txt).length -gt 2) {invoke-expression -Command .\sendmail_host_space_report.ps1} 

Script that sends the mail

# Script to send a mail and attach the host space report. Called by weekly_host_space_report.ps1

# It needs the subject line hard-coded as per our standards (client:hostname:dbname:metric:level) –

# MGA:andy-pc:ANDY:Alert Log:WARNING

#

# Andy Horne    31/07/2019

#

$EmailTo = "yourmail@you.com.au"

$EmailFrom = "hostname@client.com.au"

$Subject = "CLIENT:PROD HOST:SID:Weekly Host Space Report"

$Body = "PROD Weekly Host Space Report"

$SMTPServer = "client.smtp.com.au"

$filenameAndPath = "D:\scripts\logs\host_space_report.txt"

$SMTPMessage = New-Object System.Net.Mail.MailMessage($EmailFrom,$EmailTo,$Subject,$Body)

$attachment = New-Object System.Net.Mail.Attachment($filenameAndPath)

$SMTPMessage.Attachments.Add($attachment)

$SMTPClient = New-Object Net.Mail.SmtpClient($SmtpServer, 25)

$SMTPClient.Credentials = New-Object System.Net.NetworkCredential("mail_username", "password");

$SMTPClient.Send($SMTPMessage)

Tablespace check

This script calls a couple of SQL scripts and generates warning and critical alerts as required. It puts the output to files, and if there are entries in the files it means there is an issue and it sends an email. 

It logs in as the mga_admin user, so either create this user on the database or use another one that has permission to query the views.

You can test this by finding a tablespace that is over a certain percentage used and change the threshold in the SQL.

Batch job:

@ECHO OFF

Powershell.exe -Command "& 'D:\scripts\tablespace_check.ps1'"

Powershell script

# Script to check tablespace space. Sends an email if a tablespace is > 80%, an SMS if > 90% used.

# It needs a connection to the database with access to the dba_tablespaces and dba_freespace views.

# It needs the subject line hard-coded as per our standards (client:hostname:dbname:metric:level) –

# MGA:andy-pc:ANDY:Alert Log:WARNING

#

# Andy Horne    31/07/2019

#

$env:PATH += ";D:\app\oracle\product\12.2.0\dbhome_1\network\admin"

cd D:\scripts

Remove-Item -Path "D:\scripts\logs\tbs_check_warn.txt"

Remove-Item -Path "D:\scripts\logs\tbs_check_crit.txt"

D:\app\oracle\product\12.2.0\dbhome_1\bin\sqlplus -s 'username/password@SID' '@D:\scripts\tbs_check_warn.sql' | out-file 'D:\scripts\logs\tbs_check_warn.txt'

D:\app\oracle\product\12.2.0\dbhome_1\bin\sqlplus -s 'username/password@SID' '@D:\scripts\tbs_check_crit.sql' | out-file 'D:\scripts\logs\tbs_check_crit.txt'

if ($(get-item -path  D:\scripts\logs\tbs_check_crit.txt).length -gt 2) {invoke-expression -Command .\sendmail_tablespace_crit.ps1}

if ($(get-item -path  D:\scripts\logs\tbs_check_warn.txt).length -gt 2) {invoke-expression -Command .\sendmail_tablespace_warn.ps1}

Warning threshold SQL

set feed off

set lines 120 pages 1000

col tablespace_name format a20

col allocated format 9999990.99

col freespace format 9999990.99

col maxsize format 9999990.99

col pctused format 90.99

select tablespace_name, allocated, freespace,

  (allocated-freespace)/maxsize*100 as pctused, maxsize

  from

  (

    select a.tablespace_name,

      sum(a.bytes)/1024/1024 as allocated,

      (

        select nvl(sum(b.bytes)/1024/1024,0)

        from dba_free_space b

        where b.tablespace_name = a.tablespace_name

      ) as freespace,

      sum(decode(a.maxbytes,0,a.bytes,a.maxbytes))/1024/1024 as maxsize

    from dba_data_files a

    group by a.tablespace_name

  )

where (allocated-freespace)/maxsize*100 > 80

  order by 1;

exit

Critical Threshold SQL:

set feed off

set lines 120 pages 1000

col tablespace_name format a20

col allocated format 9999990.99

col freespace format 9999990.99

col maxsize format 9999990.99

col pctused format 90.99

select tablespace_name, allocated, freespace,

  (allocated-freespace)/maxsize*100 as pctused, maxsize

  from

  (

    select a.tablespace_name,

      sum(a.bytes)/1024/1024 as allocated,

      (

        select nvl(sum(b.bytes)/1024/1024,0)

        from dba_free_space b

        where b.tablespace_name = a.tablespace_name

      ) as freespace,

      sum(decode(a.maxbytes,0,a.bytes,a.maxbytes))/1024/1024 as maxsize

    from dba_data_files a

    group by a.tablespace_name

  )

where (allocated-freespace)/maxsize*100 > 90

  order by 1;

exit

Sendmail scripts. The critical also sends an SMS:

# Script to send a mail if a tablespace is > 80% Used. Called by tablespace_check.ps1

# It needs the subject line hard-coded as per our standards (client:hostname:dbname:metric:level) –

# MGA:andy-pc:ANDY:Alert Log:WARNING

#

# Andy Horne    31/07/2019

#

$EmailFrom = “hostname@client.com.au”

$EmailTo = “someone@somewhere.com.au”

$Subject = “CLIENT:PROD HOST:SID:Tablespace Space:WARNING”

$Body = “A Tablespace in SID on the PROD Host is over 80% full”

$SMTPServer = “client.smtp.com.au”

$SMTPClient = New-Object Net.Mail.SmtpClient($SmtpServer, 25)

$SMTPClient.Credentials = New-Object System.Net.NetworkCredential("mail_username", "password");

$SMTPClient.Send($EmailFrom, $EmailTo, $Subject, $Body)

# Script to send a mail and SMS if a tablespace is > 90% Used. Called by tablespace_check.ps1

# It needs the subject line hard-coded as per our standards (client:hostname:dbname:metric:level) –

# MGA:andy-pc:ANDY:Alert Log:WARNING

#

# Andy Horne    31/07/2019

#

$EmailFrom = “hostname@client.com.au”

$EmailToAddresses = @(“someone@somewhere.com.au”,"phone_num_for_sms@messagenet.com.au")

$Subject = “CLIENT:PROD HOST:SID:Tablespace Space:CRITICAL”

$Body = “A Tablespace in SID on the PROD Host is over 90% full”

$SMTPServer = “client.smtp.com.au”

$SMTPClient = New-Object Net.Mail.SmtpClient($SmtpServer, 25)

$SMTPClient.Credentials = New-Object System.Net.NetworkCredential("mail_username", "password");

foreach ($EmailTo in $EmailToAddresses)

{

    $SMTPClient.Send($EmailFrom, $EmailTo, $Subject, $Body)

}

There is also a script that runs at 0800 every Monday that sends a report on the tablespace usage which can be used for capacity planning.

The batch job:

@ECHO OFF

Powershell.exe -Command "& 'D:\scripts\weekly_tablespace_report.ps1'"

This calls the powershell script:

# Script to produce a weekly tablespace space report which is emailed. Schedule to run once a week.

# It needs a connection to the database with access to the dba_tablespaces and dba_freespace views.

#

# Andy Horne    31/07/2019

#

$env:PATH += ";D:\app\oracle\product\12.2.0\dbhome_1\network\admin"

cd D:\scripts

D:\app\oracle\product\12.2.0\dbhome_1\bin\sqlplus -s 'username/password@SID' '@D:\scripts\tbs_check.sql' | out-file 'D:\scripts\logs\tbs_report.txt'

if ($(get-item -path  D:\scripts\logs\tbs_report.txt).length -gt 2) {invoke-expression -Command .\sendmail_tablespace_report.ps1} 

and sends the mail

# Script to send a mail with a weekly tablespace space report. Called by weekly_tablespace_report.ps1

# It needs the subject line hard-coded as per our standards (client:hostname:dbname:metric:level) –

# MGA:andy-pc:ANDY:Alert Log:WARNING

#

# Andy Horne    31/07/2019

#

$EmailTo = "yourmail@you.com.au"

$EmailFrom = "hostname@client.com.au"

$Subject = "CLIENT:PROD HOST:SID:Weekly Tablespace Space Report"

$Body = "PROD Weekly Tablespace Space Report"

$SMTPServer = "client.smtp.com.au"

$filenameAndPath = "D:\scripts\logs\tablespace_space_report.txt"

$SMTPMessage = New-Object System.Net.Mail.MailMessage($EmailFrom,$EmailTo,$Subject,$Body)

$attachment = New-Object System.Net.Mail.Attachment($filenameAndPath)

$SMTPMessage.Attachments.Add($attachment)

$SMTPClient = New-Object Net.Mail.SmtpClient($SmtpServer, 25)

$SMTPClient.Credentials = New-Object System.Net.NetworkCredential("mail_username", "password");

$SMTPClient.Send($SMTPMessage)

Data Guard Checks

These scripts check for data guard status and lag, and run on the standby host. Note that at this stage it only detects for any lag – ie it looks for “0 seconds” and alerts if it doesn’t find it.

Bat file

@ECHO OFF

Powershell.exe -Command "& 'D:\scripts\data_guard_check.ps1'"

Powershell

# Script to check Data Guard status and Lag - runs on the standby.

# It needs a connection to the standby database using the dgmgrl command.

# Andy Horne

$env:PATH += ";D:\app\oracle\product\12.2.0\dbhome_1\network\admin"

cd D:\scripts

D:\app\oracle\product\12.2.0\dbhome_1\bin\dgmgrl -silent username/password@sid "show database SID"| out-file 'D:\scripts\logs\dg_lag.txt'

D:\app\oracle\product\12.2.0\dbhome_1\bin\dgmgrl -silent username/password@sid "show configuration"| out-file 'D:\scripts\logs\dg_check.txt'

Get-Content .\logs\dg_lag.txt | Select-String -Pattern " 0 seconds" >.\logs\dg_lag_detected.txt

Get-Content .\logs\dg_check.txt | Select-String -Pattern ERROR >.\logs\dg_error.txt

if ($(get-item -path  D:\scripts\logs\dg_error.txt).length -gt 2) {invoke-expression -Command .\sendmail_dg_error.ps1}

if ($(get-item -path  D:\scripts\logs\dg_lag_detected.txt).length -lt 2) {invoke-expression -Command .\sendmail_dg_lag.ps1}

Mail scripts

If an Error is detected:

# Script to send a mail if there is a data guard error. Called by data_guard_check.ps1

# It needs the subject line hard-coded as per our standards (client:hostname:dbname:metric:level) –

# MGA:andy-pc:ANDY:Alert Log:WARNING

#

# Andy Horne    31/07/2019

#

$EmailTo = "youremail@you.com.au"

$EmailFrom = "hostname@client.com.au"

$Subject = "CLIENT:STANDBY HOST:SID:Data Guard:WARNING"

$Body = "Data Guard Error Detected on the STANDBY host"

$SMTPServer = "client.smtp.com.au"

$filenameAndPath = "D:\scripts\logs\dg_error.txt"

$SMTPMessage = New-Object System.Net.Mail.MailMessage($EmailFrom,$EmailTo,$Subject,$Body)

$attachment = New-Object System.Net.Mail.Attachment($filenameAndPath)

$SMTPMessage.Attachments.Add($attachment)

$SMTPClient = New-Object Net.Mail.SmtpClient($SmtpServer, 25)

$SMTPClient.Credentials = New-Object System.Net.NetworkCredential("mail_username", "password");

$SMTPClient.Send($SMTPMessage)

If a lag is detected

# Script to send a mail if there is a data guard lag. Called by data_guard_check.ps1

# It needs the subject line hard-coded as per our standards (client:hostname:dbname:metric:level) –

# MGA:andy-pc:ANDY:Alert Log:WARNING

#

# Andy Horne    31/07/2019

#

$EmailTo = "yourmail@you.com.au"

$EmailFrom = "hostname@client.com.au"

$Subject = "CLIENT:STANDBY HOST:SID:Data Guard Lag:WARNING"

$Body = "Data Guard Lag on the STANDBY host"

$SMTPServer = "client.smtp.com.au"

$filenameAndPath = "D:\scripts\logs\dg_lag_detected.txt"

$SMTPMessage = New-Object System.Net.Mail.MailMessage($EmailFrom,$EmailTo,$Subject,$Body)

$attachment = New-Object System.Net.Mail.Attachment($filenameAndPath)

$SMTPMessage.Attachments.Add($attachment)

$SMTPClient = New-Object Net.Mail.SmtpClient($SmtpServer, 25)

$SMTPClient.Credentials = New-Object System.Net.NetworkCredential("mail_username", "password");

$SMTPClient.Send($SMTPMessage)

Backups

There are 2 backup scripts, a full and an archived log script. These are in ‘D:\scripts\backups’

The Full is called by this bat file and runs on the standby host.

Echo on

set ORACLE_SID=SID

RMAN @D:\scripts\backups\RMAN.rcv log D:\scripts\backups\logs\backup.log

This is the rcv file

connect target /

run {

       crosscheck archivelog all;

       allocate channel d1 type disk;

       backup (DATABASE filesperset 10      format 'R:\oracle\backups\SID\database\df_%d_U%U_%T');

        SQL'ALTER SYSTEM SWITCH LOGFILE';

       backup (ARCHIVELOG all not backed up 1 times format 'R:\oracle\backups\SID\archivelog\af_%d_U%U_%T');

       backup (CURRENT CONTROLFILE format 'R:\oracle\backups\SID\database\cf_%d_U%U_%T');

       delete noprompt ARCHIVELOG all completed before 'sysdate-1';

        delete noprompt obsolete;

    }

These are the scripts that check the logs (full is commented out on the primary as the backup runs on the standby)

Bat file:

@ECHO OFF

Powershell.exe -Command "& 'D:\scripts\full_backup_log_check.ps1'"

Powershell

# Script to check the RMAN Full backup log and email the log files for an error or a success

# It calls the sendmail scripts which need the subject line

# hard-coded as per our standards (client:hostname:dbname:metric:level) –

# MGA:andy-pc:ANDY:Alert Log:WARNING

#

# Schedule this to run after the backup finishes.

#

# Andy Horne    31/07/2019

#

cd D:\scripts\backups

Get-Content .\logs\backup.log | Select-String -Pattern "ORA-|Error|Failed|failed" -Context 1,1 >.\logs\full_backup_errors.txt

if ($(get-item -path  D:\scripts\backups\logs\full_backup_errors.txt).length -gt 2) {invoke-expression -Command D:\scripts\sendmail_full_rman_log_att.ps1}

if ($(get-item -path  D:\scripts\backups\logs\full_backup_errors.txt).length -lt 2) {invoke-expression -Command D:\scripts\sendmail_full_rman_log_ok_att.ps1} 

Sends a mail with the error as an attachment, or the log if OK

Error detected:

# Script to send a mail and attach the error log if there is a full backup error. Called by full_backup_log_check.ps1

# It needs the subject line hard-coded as per our standards (client:hostname:dbname:metric:level) –

# MGA:andy-pc:ANDY:Alert Log:WARNING

#

# Andy Horne    31/07/2019

#

$EmailTo = "yourmail@you.com.au"

$EmailFrom = "hostname@client.com.au"

$Subject = "CLIENT:PROD HOST:SID:RMAN Backup:WARNING"

$Body = "RMAN Backup Error Detected"

$SMTPServer = "client.smtp.com.au"

$filenameAndPath = "D:\scripts\backups\logs\full_backup_errors.txt"

$SMTPMessage = New-Object System.Net.Mail.MailMessage($EmailFrom,$EmailTo,$Subject,$Body)

$attachment = New-Object System.Net.Mail.Attachment($filenameAndPath)

$SMTPMessage.Attachments.Add($attachment)

$SMTPClient = New-Object Net.Mail.SmtpClient($SmtpServer, 25)

$SMTPClient.Credentials = New-Object System.Net.NetworkCredential("mail_username", "password");

$SMTPClient.Send($SMTPMessage)

No error, full log

# Script to send a mail and attach the log of the full backup. Called by full_backup_log_check.ps1

# It needs the subject line hard-coded as per our standards (client:hostname:dbname:metric:level) –

# MGA:andy-pc:ANDY:Alert Log:WARNING

#

# Andy Horne    31/07/2019

#

$EmailTo = "yourmail@you.com.au"

$EmailFrom = "hostname@client.com.au"

$Subject = "CLIENT:PROD HOST:SID:RMAN Backup:SUCCESS"

$Body = "RMAN Full Backup Complete"

$SMTPServer = "client.smtp.com.au"

$filenameAndPath = "D:\scripts\backups\logs\backup_SID.log"

$SMTPMessage = New-Object System.Net.Mail.MailMessage($EmailFrom,$EmailTo,$Subject,$Body)

$attachment = New-Object System.Net.Mail.Attachment($filenameAndPath)

$SMTPMessage.Attachments.Add($attachment)

$SMTPClient = New-Object Net.Mail.SmtpClient($SmtpServer, 25)

$SMTPClient.Credentials = New-Object System.Net.NetworkCredential("mail_username", "password");

$SMTPClient.Send($SMTPMessage)

These are for the archived log backup

Bat file

Echo on

set ORACLE_SID=SID

RMAN @D:\scripts\backups\RMAN_ARCH.rcv log D:\scripts\backups\logs\backup_arch.log

Rcv file

connect target /

run {

       crosscheck archivelog all;

       allocate channel d1 type disk;

       SQL'ALTER SYSTEM SWITCH LOGFILE';

       backup (ARCHIVELOG all not backed up 1 times format 'R:\oracle\backups\SID\archivelog\af_%d_U%U_%T');

       backup (CURRENT CONTROLFILE format 'R:\oracle\backups\SID\database\cf_%d_U%U_%T');

       delete noprompt ARCHIVELOG all completed before 'sysdate-1';

        delete noprompt obsolete;

      }

Bat file to check the log

@ECHO OFF

Powershell.exe -Command "& 'D:\scripts\arch_backup_log_check.ps1'"

Powershell

# Script to check the RMAN Archive log backup log and email if an error is found

# It calls the sendmail_arch_rman_log.ps1 script which needs the subject line

# hard-coded as per our standards (client:hostname:dbname:metric:level) –

# MGA:andy-pc:ANDY:Alert Log:WARNING

#

# Andy Horne    31/07/2019

#

cd D:\scripts\backups

Get-Content .\logs\backup_arch_SID.log | Select-String -Pattern "ORA-|Error|Failed|failed" -Context 1,1 >.\logs\arch_backup_log.txt

if ($(get-item -path  D:\scripts\backups\logs\arch_backup_log.txt).length -gt 2) {invoke-expression -Command D:\scripts\sendmail_arch_rman_log_att.ps1}

if ($(get-item -path  D:\scripts\backups\logs\arch_backup_log.txt).length -lt 2) {invoke-expression -Command D:\scripts\sendmail_arch_rman_log_ok_att.ps1} 

Send the mail with the errors as an attachment, or the log if OK

Errors detected

# Script to email the archived log error file if it finds an error, called by arch_backup_log_check.ps1

# It needs the subject line hard-coded as per our standards (client:hostname:dbname:metric:level) –

# MGA:andy-pc:ANDY:Alert Log:WARNING

#

# Andy Horne    31/07/2019

#

$EmailTo = "username@youremail.com.au"

$EmailFrom = "hostname@client.com.au"

$Subject = "CLIENT:PROD HOST:SID:RMAN ARCH Backup:WARNING"

$Body = "RMAN ARCH Backup Error Detected"

$SMTPServer = "client.smtp.com.au"

$filenameAndPath = "D:\scripts\backups\logs\arch_backup_errors.txt"

$SMTPMessage = New-Object System.Net.Mail.MailMessage($EmailFrom,$EmailTo,$Subject,$Body)

$attachment = New-Object System.Net.Mail.Attachment($filenameAndPath)

$SMTPMessage.Attachments.Add($attachment)

$SMTPClient = New-Object Net.Mail.SmtpClient($SmtpServer, 25)

$SMTPClient.Credentials = New-Object System.Net.NetworkCredential("username_for_email", "password_for_email");

$SMTPClient.Send($SMTPMessage)

No errors

# Script to email the archived log file, called by arch_backup_log_check.ps1

# It needs the subject line hard-coded as per our standards (client:hostname:dbname:metric:level) –

# MGA:andy-pc:ANDY:Alert Log:WARNING

#

# Andy Horne    31/07/2019

#

$EmailTo = "username@youremail.com.au"

$EmailFrom = "hostname@client.com.au"

$Subject = "CLIENT:PROD HOST:SID:RMAN ARCH Backup:SUCCESS"

$Body = "RMAN ARCH Backup Complete"

$SMTPServer = "client.smtp.com.au"

$filenameAndPath = "D:\scripts\backups\logs\backup_arch.log"

$SMTPMessage = New-Object System.Net.Mail.MailMessage($EmailFrom,$EmailTo,$Subject,$Body)

$attachment = New-Object System.Net.Mail.Attachment($filenameAndPath)

$SMTPMessage.Attachments.Add($attachment)

$SMTPClient = New-Object Net.Mail.SmtpClient($SmtpServer, 25)

$SMTPClient.Credentials = New-Object System.Net.NetworkCredential("username_for_email", "password_for_email");

$SMTPClient.Send($SMTPMessage)

These scripts should be enough to monitor and alert on Windows.