Read Alert log from x$dbgalertext in Oracle

This post is small in content but most useful for all DBAs. We are going to have look at the most important part of Oracle RDBMS. Being Oracle DBA we all are well known about alert logs but few of us experts know how to read it. We re being a part of IT basic thing taught us that compiler used by the computer to understand some codes. In Database life, we have some queries which we use to read alert logs. 

Let’s have a look at the query.

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>

“Spread Knowledge, Spread Help, Spread Smile”

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Telegram Channel: https://t.me/helporacle

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp

Tagged:

About The Author

"The fragrance always remains in the hand that gives the rose." I am a person who believes in sharing knowledge among all. My Oracle rewards are OCI, OCP, OCA.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.