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:

mylog.xml

<msg time="2017-01-10T17:58:02.705+01:00" org_id="oracle" comp_id="rdbms" type="1" level="16" host_id="empdb.test.com" host_addr="10.0.84.33" pid_id="33965" version="0"><txt>Errors in file /opt/oracle/diag/rdbms/sas2/sas2/trace/sas2_ora_33965.trc: ORA-01013: user requested cancel of current operation </txt></msg>

<msg time="2017-01-10T17:59:33.866+01:00" org_id="oracle" comp_id="rdbms" type="1" level="16" host_id="empdb.test.com" host_addr="10.0.84.33" pid_id="33965" version="0"><txt>Errors in file /opt/oracle/diag/rdbms/sas2/sas2/trace/sas2_ora_33965.trc: ORA-01013: user requested cancel of current operation </txt></msg>

<msg time="2017-01-10T18:00:14.147+01:00" org_id="oracle" comp_id="rdbms" type="1" level="16" host_id="empdb.test.com" host_addr="10.0.84.33" pid_id="33965" version="0"><txt>Errors in file /opt/oracle/diag/rdbms/sas2/sas2/trace/sas2_ora_33965.trc: ORA-01013: user requested cancel of current operation </txt></msg>

<msg time="2017-01-10T18:17:38.306+01:00" org_id="oracle" comp_id="rdbms" type="1" level="16" host_id="empdb.test.com" host_addr="10.0.84.33" version="0"><txt> Fatal NI connect error 12170. </txt></msg>

<msg time="2017-01-10T19:16:03.765+01:00" org_id="oracle" comp_id="rdbms" type="1" level="16" host_id="empdb.test.com" host_addr="10.0.84.33" version="0"><txt> Fatal NI connect error 12170. </txt></msg>

<msg time="2017-01-11T09:21:36.353+01:00" org_id="oracle" comp_id="rdbms" type="1" level="16" host_id="empdb.test.com" host_addr="10.0.84.33" pid_id="37407" version="0"><txt>Errors in file /opt/oracle/diag/rdbms/sas2/sas2/trace/sas2_ora_37407.trc: ORA-01013: user requested cancel of current operation </txt></msg>

<msg time="2017-01-11T09:30:25.405+01:00" org_id="oracle" comp_id="rdbms" type="1" level="16" host_id="empdb.test.com" host_addr="10.0.84.33" pid_id="5450" version="0"><txt>Errors in file /opt/oracle/diag/rdbms/sas2/sas2/trace/sas2_ora_5450.trc: ORA-01013: user requested cancel of current operation </txt></msg>

<msg time="2017-01-11T09:36:55.220+01:00" org_id="oracle" comp_id="rdbms" type="1" level="16" host_id="empdb.test.com" host_addr="10.0.84.33" pid_id="13786" version="0"><txt>Errors in file /opt/oracle/diag/rdbms/sas2/sas2/trace/sas2_ora_13786.trc: ORA-01013: user requested cancel of current operation </txt></msg>

<msg time="2017-01-11T10:15:47.153+01:00" org_id="oracle" comp_id="rdbms" type="1" level="16" host_id="empdb.test.com" host_addr="10.0.84.33" version="0"><txt> Fatal NI connect error 12170. </txt></msg>

<msg time="2017-01-11T10:20:33.686+01:00" org_id="oracle" comp_id="rdbms" type="1" level="16" host_id="empdb.test.com" host_addr="10.0.84.33" pid_id="17918" version="0"><txt>Errors in file /opt/oracle/diag/rdbms/sas2/sas2/trace/sas2_ora_17918.trc: ORA-01013: user requested cancel of current operation </txt></msg>

<msg time="2017-01-11T10:31:07.892+01:00" org_id="oracle" comp_id="rdbms" type="1" level="16" host_id="empdb.test.com" host_addr="10.0.84.33" pid_id="1458" version="0"><txt>Errors in file /opt/oracle/diag/rdbms/sas2/sas2/trace/sas2_ora_1458.trc: ORA-01013: user requested cancel of current operation </txt></msg>

<msg time="2017-01-11T10:49:25.522+01:00" org_id="oracle" comp_id="rdbms" type="1" level="16" host_id="empdb.test.com" host_addr="10.0.84.33" pid_id="33475" version="0"><txt>Errors in file /opt/oracle/diag/rdbms/sas2/sas2/trace/sas2_ora_33475.trc: ORA-01013: user requested cancel of current operation </txt></msg>

<msg time="2017-01-11T10:50:22.204+01:00" org_id="oracle" comp_id="rdbms" type="1" level="16" host_id="empdb.test.com" host_addr="10.0.84.33" pid_id="33475" version="0"><txt>Errors in file /opt/oracle/diag/rdbms/sas2/sas2/trace/sas2_ora_33475.trc: ORA-01013: user requested cancel of current operation </txt></msg>

<msg time="2017-01-11T11:22:28.263+01:00" org_id="oracle" comp_id="rdbms" type="1" level="16" host_id="empdb.test.com" host_addr="10.0.84.33" pid_id="34149" version="0"><txt>Errors in file /opt/oracle/diag/rdbms/sas2/sas2/trace/sas2_ora_34149.trc: ORA-01013: user requested cancel of current operation </txt></msg>

<msg time="2017-01-11T11:23:33.017+01:00" org_id="oracle" comp_id="rdbms" type="1" level="16" host_id="empdb.test.com" host_addr="10.0.84.33" version="0"><txt> Fatal NI connect error 12170. </txt></msg>

<msg time="2017-01-11T11:32:20.810+01:00" org_id="oracle" comp_id="rdbms" type="1" level="16" host_id="empdb.test.com" host_addr="10.0.84.33" version="0"><txt> Fatal NI connect error 12170. </txt></msg>

<msg time="2017-01-11T11:32:20.810+01:00" org_id="oracle" comp_id="rdbms" type="1" level="16" host_id="empdb.test.com" host_addr="10.0.84.33" version="0"><txt> Fatal NI connect error 12170. </txt></msg>

<msg time="2017-01-11T11:32:33.495+01:00" org_id="oracle" comp_id="rdbms" type="1" level="16" host_id="empdb.test.com" host_addr="10.0.84.33" version="0"><txt> Fatal NI connect error 12170. </txt></msg>

<msg time="2017-01-11T11:53:51.275+01:00" org_id="oracle" comp_id="rdbms" type="1" level="16" host_id="empdb.test.com" host_addr="10.0.84.33" pid_id="39294" version="0"><txt>Errors in file /opt/oracle/diag/rdbms/sas2/sas2/trace/sas2_ora_39294.trc: ORA-01013: user requested cancel of current operation </txt></msg>

<msg time="2017-01-11T12:00:33.940+01:00" org_id="oracle" comp_id="rdbms" type="1" level="16" host_id="empdb.test.com" host_addr="10.0.84.33" pid_id="47314" version="0"><txt>Errors in file /opt/oracle/diag/rdbms/sas2/sas2/trace/sas2_ora_47314.trc: ORA-01013: user requested cancel of current operation </txt></msg>

<msg time="2017-01-11T12:24:01.557+01:00" org_id="oracle" comp_id="rdbms" type="1" level="16" host_id="empdb.test.com" host_addr="10.0.84.33" version="0"><txt> Fatal NI connect error 12170. </txt></msg>

<msg time="2017-01-11T12:44:08.619+01:00" org_id="oracle" comp_id="rdbms" type="1" level="16" host_id="empdb.test.com" host_addr="10.0.84.33" version="0"><txt> Fatal NI connect error 12170. </txt></msg>

<msg time="2017-01-11T14:02:24.872+01:00" org_id="oracle" comp_id="rdbms" type="1" level="16" host_id="empdb.test.com" host_addr="10.0.84.33" version="0"><txt> Fatal NI connect error 12170. </txt></msg>

<msg time="2017-01-11T14:49:06.257+01:00" org_id="oracle" comp_id="rdbms" type="1" level="16" host_id="empdb.test.com" host_addr="10.0.84.33" pid_id="24775" version="0"><txt>Errors in file /opt/oracle/diag/rdbms/sas2/sas2/trace/sas2_ora_24775.trc: ORA-01013: user requested cancel of current operation </txt></msg>

<msg time="2017-01-11T14:50:01.676+01:00" org_id="oracle" comp_id="rdbms" type="1" level="16" host_id="empdb.test.com" host_addr="10.0.84.33" pid_id="24775" version="0"><txt>Errors in file /opt/oracle/diag/rdbms/sas2/sas2/trace/sas2_ora_24775.trc: ORA-01013: user requested cancel of current operation </txt></msg>

<msg time="2017-01-11T15:15:37.677+01:00" org_id="oracle" comp_id="rdbms" type="1" level="16" host_id="empdb.test.com" host_addr="10.0.84.33" pid_id="10846" version="0"><txt>Errors in file /opt/oracle/diag/rdbms/sas2/sas2/trace/sas2_ora_10846.trc: ORA-01013: user requested cancel of current operation </txt></msg>

<msg time="2017-01-11T15:16:47.632+01:00" org_id="oracle" comp_id="rdbms" type="1" level="16" host_id="empdb.test.com" host_addr="10.0.84.33" pid_id="10846" version="0"><txt>Errors in file /opt/oracle/diag/rdbms/sas2/sas2/trace/sas2_ora_10846.trc: ORA-01013: user requested cancel of current operation </txt></msg>

<msg time="2017-01-11T15:26:41.202+01:00" org_id="oracle" comp_id="rdbms" type="1" level="16" host_id="empdb.test.com" host_addr="10.0.84.33" pid_id="10846" version="0"><txt>Errors in file /opt/oracle/diag/rdbms/sas2/sas2/trace/sas2_ora_10846.trc: ORA-01013: user requested cancel of current operation </txt></msg>

<msg time="2017-01-11T15:46:09.594+01:00" org_id="oracle" comp_id="rdbms" type="1" level="16" host_id="empdb.test.com" host_addr="10.0.84.33" pid_id="9566" version="0"><txt>Errors in file /opt/oracle/diag/rdbms/sas2/sas2/trace/sas2_ora_9566.trc: ORA-01013: user requested cancel of current operation </txt></msg>

 

Comments