Home » RDBMS Server » Server Administration » Mail whenever error registered in alert.log. (Oracle 11g)
|
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: 68758 Registered: March 2007 Location: Saint-Maur, France, https...
|
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: 68758 Registered: March 2007 Location: Saint-Maur, France, https...
|
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: 68758 Registered: March 2007 Location: Saint-Maur, France, https...
|
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: 68758 Registered: March 2007 Location: Saint-Maur, France, https...
|
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
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Jun 12 14:52:47 CDT 2025
|