Home » RDBMS Server » Server Administration » Mail whenever error registered in alert.log. (Oracle 11g)
Mail whenever error registered in alert.log. [message #561480] Mon, 23 July 2012 04:51 Go to next message
chintan.patel
Messages: 131
Registered: July 2008
Location: Ahmedabad
Senior Member
Hi Friends,

I want to mail any "ora" errors registered into alert.log file. is it possible if yes then how can i do it.

Chintan
Re: Mail whenever error registered in alert.log. [message #561482 is a reply to message #561480] Mon, 23 July 2012 05:08 Go to previous messageGo to next message
Michel Cadot
Messages: 59815
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, it is possible.
I think EM has something built-in for this but I don't use EM.
Otherwise you can have a PL/SQL procedure that read the alert.log and send a mail (using UTL_MAIL) if it encounters an error.
To read the alert.log from SQL you can use an external table.

Regards
Michel

[Edit: typo]

[Updated on: Mon, 23 July 2012 05:16]

Report message to a moderator

Re: Mail whenever error registered in alert.log. [message #561483 is a reply to message #561482] Mon, 23 July 2012 05:10 Go to previous messageGo to next message
John Watson
Messages: 4806
Registered: January 2010
Location: Global Village
Senior Member
x$dbgalertext may be easier than an external table.
Re: Mail whenever error registered in alert.log. [message #561485 is a reply to message #561483] Mon, 23 July 2012 05:26 Go to previous messageGo to next message
Michel Cadot
Messages: 59815
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Or V$DIAG_ALERT_EXT for non-SYS accounts.

Regards
Michel
Re: Mail whenever error registered in alert.log. [message #561487 is a reply to message #561483] Mon, 23 July 2012 05:28 Go to previous messageGo to next message
chintan.patel
Messages: 131
Registered: July 2008
Location: Ahmedabad
Senior Member
Thanks John

can u explain how can i use this view for ora errors.

Chintan
Re: Mail whenever error registered in alert.log. [message #561490 is a reply to message #561487] Mon, 23 July 2012 05:33 Go to previous messageGo to next message
Michel Cadot
Messages: 59815
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select ORIGINATING_TIMESTAMP, MESSAGE_TEXT from V$DIAG_ALERT_EXT where MESSAGE_TEXT like '%ORA-%';

ORIGINATING_TIMESTAMP
---------------------------------------------------------------------------
MESSAGE_TEXT
-------------------------------------------------------------------------------------------------------------
31-JAN-12 01.12.39.441000000 PM +01:00
ORA-7452: resource plan 'SYSTEM_PLAN' does not exist

29-FEB-12 10.15.06.391000000 PM +01:00
Errors in file C:\ORACLE\ADMIN\MIKB2\TRACE\diag\rdbms\mikb2\mikb2\trace\mikb2_j002_2580.trc:
ORA-16957: SQL Analyze time limit interrupt

10-JUN-12 01.46.20.771000000 PM +02:00
Non critical error ORA-48913 caught while writing to trace file "C:\ORACLE\ADMIN\MIKB2\TRACE\diag\rdbms\mikb2
e\mikb2_dbrm_844.trc"
Error message: ORA-48913: Writing into trace file failed, file size limit [5242880] reached
Writing to the above trace file is disabled for now on...

29-JUN-12 11.33.39.818000000 AM +02:00
Errors in file C:\ORACLE\ADMIN\MIKB2\TRACE\diag\rdbms\mikb2\mikb2\trace\mikb2_ora_5304.trc  (incident=68193):
ORA-00600: internal error code, arguments: [4353], [U], [0], [94], [], [], [], [], [], [], [], []

29-JUN-12 11.33.52.318000000 AM +02:00
Errors in file C:\ORACLE\ADMIN\MIKB2\TRACE\diag\rdbms\mikb2\mikb2\trace\mikb2_ora_5304.trc  (incident=68194):
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [4353], [U], [0], [94], [], [], [], [], [], [], [], []

29-JUN-12 11.33.53.630000000 AM +02:00
opiodr aborting process unknown ospid (5304) as a result of ORA-603

Regards
Michel
Re: Mail whenever error registered in alert.log. [message #561491 is a reply to message #561487] Mon, 23 July 2012 05:33 Go to previous messageGo to next message
John Watson
Messages: 4806
Registered: January 2010
Location: Global Village
Senior Member
@Michel, thank you - I didn;t know about that view.

@Chintan, take a look at the view, and I'm sure you'll be able to work it out. You'll want to use the Scheduler.
Re: Mail whenever error registered in alert.log. [message #561508 is a reply to message #561491] Mon, 23 July 2012 06:32 Go to previous messageGo to next message
chintan.patel
Messages: 131
Registered: July 2008
Location: Ahmedabad
Senior Member
Thanks Michel

Your Query solved my problem.

Chintan
Re: Mail whenever error registered in alert.log. [message #561568 is a reply to message #561508] Tue, 24 July 2012 01:18 Go to previous messageGo to next message
chintan.patel
Messages: 131
Registered: July 2008
Location: Ahmedabad
Senior Member
Hi Michel

Query as supplied by u run very slow, how can i run it fast or can u guide me for any base table in which i find above details stored,
so i will use it for putting the trigger to mail latest "ora" error.

Thanks
Chintan
Re: Mail whenever error registered in alert.log. [message #561570 is a reply to message #561568] Tue, 24 July 2012 01:26 Go to previous messageGo to next message
Michel Cadot
Messages: 59815
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This view merges different diagnosis files and aggregates many informat and so is slow.
A faster (but just a bit) is to create an external table on the alert.log itself as I mentioned in my first post.
But anyway, this will scan the file each time you will query it, so make it as small as possible by purging it.
You can make a local copy in a table before purging if you want to archive it for future research.

Regards
Michel

[Updated on: Tue, 24 July 2012 01:28]

Report message to a moderator

Re: Mail whenever error registered in alert.log. [message #561577 is a reply to message #561570] Tue, 24 July 2012 01:37 Go to previous messageGo to next message
chintan.patel
Messages: 131
Registered: July 2008
Location: Ahmedabad
Senior Member
Thanks

Can u guide me how can i read alert.log from external table. i have used external tables for load data from csv files.

Chintan
Re: Mail whenever error registered in alert.log. [message #561584 is a reply to message #561577] Tue, 24 July 2012 02:02 Go to previous messageGo to next message
Michel Cadot
Messages: 59815
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The same way, just you have only one column which is the whole line.

Regards
Michel
Re: Mail whenever error registered in alert.log. [message #561590 is a reply to message #561584] Tue, 24 July 2012 02:12 Go to previous messageGo to next message
chintan.patel
Messages: 131
Registered: July 2008
Location: Ahmedabad
Senior Member
In csv file all the rows have same type of data with comma separated while in alert.log file there are so many other lines, how can i avoid those lines and pick only "ora" error lines.

Chintan
Re: Mail whenever error registered in alert.log. [message #561592 is a reply to message #561590] Tue, 24 July 2012 02:25 Go to previous messageGo to next message
Michel Cadot
Messages: 59815
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You do not pick up the lines with ORA- in the external table definition, you pick up them when you query the external table like in my previous query.

Regards
Michel
Re: Mail whenever error registered in alert.log. [message #561595 is a reply to message #561592] Tue, 24 July 2012 02:27 Go to previous messageGo to next message
chintan.patel
Messages: 131
Registered: July 2008
Location: Ahmedabad
Senior Member
Ok

means i have to read all the lines with where condition. is it right.

Thanks
chintan
Re: Mail whenever error registered in alert.log. [message #561598 is a reply to message #561595] Tue, 24 July 2012 02:36 Go to previous messageGo to next message
Michel Cadot
Messages: 59815
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes.

Regards
Michel
Re: Mail whenever error registered in alert.log. [message #561602 is a reply to message #561598] Tue, 24 July 2012 02:57 Go to previous messageGo to next message
Michel Cadot
Messages: 59815
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Here's an example:
SQL> create or replace directory BDUMP_DIR as 'C:\ORACLE\ADMIN\MIKA\BDUMP';

Directory created.

SQL> drop table alert_log_ext;

Table dropped.

SQL> create table alert_log_ext (line varchar2(2000))
  2  organization external (
  3     type oracle_loader
  4     default directory bdump_dir
  5     access parameters (
  6        records delimited by newline
  7        nobadfile
  8        nologfile
  9        nodiscardfile
 10        fields
 11        missing field values are null
 12        (line position (1:2000))
 13        )
 14     location ('alert_mika.log') 
 15     )
 16  reject limit unlimited
 17  /

Table created.

SQL> select line from alert_log_ext where line like '%ORA-%' and rownum <= 10;
LINE
----------------------------------------------------------------------------------------------------
ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [unable_to_trans_pc] [PC:0x7C911689]
[ADDR:0x0] [UNABLE_TO_READ] []
ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [unable_to_trans_pc] [PC:0x7C9101B3]
[ADDR:0x52005A] [UNABLE_TO_WRITE] []
ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [unable_to_trans_pc] [PC:0x7C911780]
[ADDR:0x8A01C083] [UNABLE_TO_READ] []
ORA-00472: PMON  process terminated with error
ORA-00472: PMON  process terminated with error
ORA-00472: PMON  process terminated with error
ORA-00472: PMON  process terminated with error
ORA-19815: WARNING: db_recovery_file_dest_size of 1073741824 bytes is 92.30% used, and has 82706432
remaining bytes available.
ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [unable_to_trans_pc] [PC:0x7C911689]
[ADDR:0x0] [UNABLE_TO_READ] []
ORA-19815: WARNING: db_recovery_file_dest_size of 1073741824 bytes is 92.30% used, and has 82706432
remaining bytes available.

10 rows selected.

Regards
Michel

[Edit: remove code pasted twice]

[Updated on: Tue, 24 July 2012 03:44]

Report message to a moderator

Re: Mail whenever error registered in alert.log. [message #561604 is a reply to message #561595] Tue, 24 July 2012 02:59 Go to previous messageGo to next message
John Watson
Messages: 4806
Registered: January 2010
Location: Global Village
Senior Member
I think you can discard lines you don't want when populating the external table, by using a WHEN clause. That might be quicker than using a predicate when you query it.
But really, why have you ignored my suggestion? The table I suggested you look at queries the XML log files, which are limited to 10M (if I remember correctly) so should be much faster to query than the entire alert log. Unless you have routines in place to trim the alert log, it is probably huge.

Overall though, I think you are porbably going in the wrong direction. The database has a built in alert system, perhaps you shouild be writing an alert handler to pick up events as they occur.
Re: Mail whenever error registered in alert.log. [message #561608 is a reply to message #561604] Tue, 24 July 2012 03:36 Go to previous messageGo to next message
chintan.patel
Messages: 131
Registered: July 2008
Location: Ahmedabad
Senior Member
Dear John

i am also working on your suggested view x$dbgalertext, it takes less time compare to V$DIAG_ALERT_EXT.
i also interested in reading uneven flat text file from sql.

Thanks John & Michel,
your suggestions reaches me very near to my requirements, i will evaluate the different ways and select the easier one.

Chintan
Re: Mail whenever error registered in alert.log. [message #561718 is a reply to message #561608] Wed, 25 July 2012 00:05 Go to previous messageGo to next message
chintan.patel
Messages: 131
Registered: July 2008
Location: Ahmedabad
Senior Member
Should i grant select on x$dbgalertext to other user.
i tried and facing following error.

SQL> grant select on x$dbgalertext to testuser;
grant select on x$dbgalertext to testuser
                *
ERROR at line 1:
ORA-02030: can only select from fixed tables/views


Chintan
Re: Mail whenever error registered in alert.log. [message #561722 is a reply to message #561718] Wed, 25 July 2012 00:48 Go to previous messageGo to next message
Michel Cadot
Messages: 59815
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, only SYS.

Regards
Michel
Re: Mail whenever error registered in alert.log. [message #561723 is a reply to message #561722] Wed, 25 July 2012 00:52 Go to previous messageGo to next message
chintan.patel
Messages: 131
Registered: July 2008
Location: Ahmedabad
Senior Member
Ok

Thanks
Chintan
Re: Mail whenever error registered in alert.log. [message #561734 is a reply to message #561723] Wed, 25 July 2012 01:32 Go to previous message
John Watson
Messages: 4806
Registered: January 2010
Location: Global Village
Senior Member
Create a view on the X$ table as SYS, and grant select on that.
Previous Topic: Job not running on defined schedule
Next Topic: Profile file
Goto Forum:
  


Current Time: Fri Nov 28 01:16:18 CST 2014

Total time taken to generate the page: 0.06317 seconds