|
Re: Mail whenever error registered in alert.log. [message #561482 is a reply to message #561480] |
Mon, 23 July 2012 05:08 |
|
Michel Cadot
Messages: 68672 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 #561490 is a reply to message #561487] |
Mon, 23 July 2012 05:33 |
|
Michel Cadot
Messages: 68672 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 #561570 is a reply to message #561568] |
Tue, 24 July 2012 01:26 |
|
Michel Cadot
Messages: 68672 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 #561602 is a reply to message #561598] |
Tue, 24 July 2012 02:57 |
|
Michel Cadot
Messages: 68672 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 |
John Watson
Messages: 8941 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 |
chintan.patel
Messages: 162 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 |
chintan.patel
Messages: 162 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
|
|
|
|
|
|