Read Alert log from x$dbgalertext

select

xmlelement(noentityescaping "msg",

xmlattributes( xalt.originating_timestamp as "time",

xalt.organization_id as "org_id",

xalt.component_id as "comp_id",

xalt.message_id as "msg_id",

xalt.message_type as "type",

xalt.message_group as "group",

xalt.message_level as "level",

xalt.host_id as "host_id",

xalt.host_address as "host_addr",

xalt.process_id as "pid_id",

xalt.version as "version"

),

xmlelement("txt", regexp_replace (message_text, '\s+', ' ') )

) as "mylog.xml"

from

x$dbgalertext xalt

where

originating_timestamp > sysdate-1

and

(

message_text like '%ORA%'

or

message_text like '%Fatal%'

);

Output: