DBA Alert Log

Description

Categories: Enginatics
Repository: Github

V$DIAG_ALERT_EXT shows the contents of the XML-based alert log in the Automatic Diagnostic Repository (ADR) for the current container (PDB).
You could schedule it, for example, in incremental mode to send an email with errors that occured since the last scheduled report run.

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Copy

select
decode(vdae.message_type,1,'Unknown',2,'Incident_Error',3,'Error',4,'Warning',5,'Notification',6,'Trace',vdae.message_type) message_type_desc,
decode(vdae.message_level,1,'Critical',2,'Severe',8,'Important',16,'Normal',vdae.message_level) message_level_desc,
xxen_util.module_type(vdae.module_id) module_type,
xxen_util.module_name(vdae.module_id) module_name,
vdae.*
from
v$diag_alert_ext vdae
where
1=1
order by
vdae.record_id

Parameter Name

SQL text

Validation

Message Text includes

vdae.message_text like '%'||:message_text_includes||'%'

Char

Message Type

vdae.message_type=decode(:message_type,'Unknown',1,'Incident_Error',2,'Error',3,'Warning',4,'Notification',5,'Trace',6,:message_type)

LOV

Message Level

vdae.message_level=decode(:message_level,'Critical',1,'Severe',2,'Important',8,'Normal',16,:message_level)

LOV

History Days

vdae.originating_timestamp>=sysdate-:history_days

Number

Date From

vdae.originating_timestamp>=:date_from

Date

Exceptions and ORA-% only

(
vdae.message_text like 'Exception%' or
vdae.message_text like '%ORA-%' and
vdae.message_text not like 'Non critical error ORA-48913%' and
vdae.message_text not like  '%Result = ORA-31%' and
vdae.message_text not in ('  Result = ORA-0'||chr(10),'  Result = ORA-30')
)

LOV

Incremental Mode

vdae.originating_timestamp>=
(
select
fcr0.actual_start_date
from
fnd_concurrent_requests fcr,
fnd_concurrent_requests fcr0
where
fcr.request_id=fnd_global.conc_request_id and
fcr.parent_request_id=fcr0.request_id
)