Email notification

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
khanzada19Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
k_murli_krishnaConnect With a Mentor Commented:
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
 
hqassapCommented:
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
All Courses

From novice to tech pro — start learning today.