RE: Centralized Alert Log Monitoring

I like your plan a lot. I'd like to hear about you accomplish step 2.

I already use a centralized monitoring database to store statistical data and use Excel to graph the results (or I was doing this last time I worked with Oracle - still looking for my next position!).

Randy

-----Original Message-----
Michael (TEM)
Sent: Monday, January 07, 2002 3:20 PM
To: Multiple recipients of list ORACLE-L

Dennis:

I read an article in Oracle Magazine about a technique called 'Do-it-yourself
Views' (called DIY$ views in the article). The article is at http://www.oracle.com/oramag/webcolumns/2001/index.html?diy_dynamic.html. If you have 8i or above this would work for you. Basically, it allows you to read flat files with a 'SELECT' statement.

I've been thinking about a tool like this, too. My plan is something like this:

1. Create a 'DBAMON' database instance. This instance needs current database links to each instance you want to monitor.
2. Write the scanning algorithm in PL/SQL, and store it in the DBAMON instance.
3. Create a DIY$ view in each database. A DIY$ view is kinda like but not quite the flat-file select in 9i. A DIY$ view is file-specific. So you create a view and call it something like DIY$ALERT_LOG in each database instance, and each instance knows where the alertSID.log file is and how to read it.
4. Create a job using DBMS_JOB in the DBAMON database to periodically scan each instance's DIY$ALERT_LOG view and look for errors.
5. If the job finds any errors, use the UTL_SMTP package to send you an e-mail, or beep your pager, or whatever.

The beauty of this method is that it's all done from inside Oracle, and doesn't rely on outside factors such as WinBatch, ksh, csh, sh, Kix, bash, or Perl (sorry, Jared). My DBAMON database is on my NT workstation, and I can read alert_SID.log files from both NT and UNIX operating systems.

So far, I've got step 1 done, step 3 done on a test database, and am working on step #2. Film at 11:00 if you're interested.

Remember, YMMV!

Cheers,
Mike

---
===========================================================================
Michael P. Vergara
Oracle DBA
Guidant Corporation


(909) 914-2304



-----Original Message-----
Sent: Monday, January 07, 2002 10:58 AM
To: Multiple recipients of list ORACLE-L


Hi All,
Currently I am the only DBA for the 15 productin databases in our company.
I have written shell scripts on all database servers to parse the alert.log
file and look for Oracle errors and send page/E-mail if problems are found.
While this works it is sometimes painful to maintain - if I want to change
something I have to go to different servers to change the scripts one by
one. I figure it would be nice if we can have a centralized solution where
I can monitor alert logs remotely and don't have to hop from server to
server.  Are there any tools/scripts that enable DBAs to do this? I know
OEM can do that with the Advanced Events but now you have to buy the
Diagnostics pack for that.
TIA
Dennis Meng
Database Administrator
Focal Communications
847-954-8328
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Vergara, Michael (TEM)
  INET: mvergara_at_guidant.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Randy Kirkpatrick
  INET: randywk_at_usa.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).