Solved

Email notification

Posted on 2009-04-03
2
572 Views
Last Modified: 2013-12-18
Hi,

I have set up TNSPing on server1 which pings server2 every miniute.  Log file is generated on server1; when it connects it logs 'Yes' when can't connect logs 'No'.

Is there a way to set up some sorta process on server1 to browse through the log file and when it sees 'No' then sends an email notification.  
log file is a text file by the way.

thx
0
Comment
Question by:khanzada19
2 Comments
 
LVL 17

Accepted Solution

by:
k_murli_krishna earned 500 total points
ID: 24065913
You can use UTL_FILE PL/SQL package to read/write from/to files. We wrote a procedure to spit out INSERT statements for all tables in a schema in the correct ascending relational order. So, you can do the read part of it as well. Then in the same procedure, you can use UTL_MAIL/UTL_SMTP PL/SQL packages to send the mail triggered when 'Yes'/'No' is found logged at a particular place due to tnsping from server 1 to server 2. You can pin point the 'Yes'/'No' based on row, standard text inside the row w.r.t. 'Yes'/'No' OR based on cell by (row,column) coordinates. Here are a few references which can help you out.

Oracle utl_file Package
http://www.adp-gmbh.ch/ora/plsql/utl_file.html
http://www.cs.umbc.edu/help/oracle8/server.815/a68001/utl_file.htm
http://www.dba-oracle.com/t_utl_file_package.htm
http://download.oracle.com/docs/cd/B10500_01/appdev.920/a96612/u_file.htm
http://www.databasejournal.com/features/oracle/article.php/3295321/Manipulating-Oracle-Files-with-UTLFILE.htm

Send email within Oracle 10g with the UTL_MAIL package.
http://searchoracle.techtarget.com/tip/0,289483,sid41_gci1195514,00.html
http://articles.techrepublic.com.com/5100-10878_11-6078020.html

Send email within Oracle with the UTL_SMTP package.
http://www.lv2000.com/articles/utlsmtp.htm

General
http://www.tutorialized.com/tutorial/Oracle-Procedures-Send-Email/36707
0
 
LVL 4

Expert Comment

by:hqassap
ID: 24072960
Hello,
You can do what you want by creating an external table on the log file, then every couple of minutes, for example, you can run a stored procedure (scheduled run) to check the table does contain 'NO' then send an email encluding the incident information.

You can use Oracle built in package UTL_MAIL to send emails (see attached file for more details on how to install and use this package procedures:
Oracle-Send-Mail--UTL-MAIL-.txt
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question