ORA error query from sqlplus oracle

Do you need to filter ora errors from sqlplus commandline? Use the below sql query to get it from database itself instead of OS

There you go! Enjoy!

SQL Query:

set lines 200
set pages 1000
col check_error format a20
col message_text format a50
select message_text,to_char(ORIGINATING_TIMESTAMP,'dd-mon-yy hh24:mi:ss'),
case when regexp_like(message_text,'ORA-') then 'ORA errors found'
else 'No errors'
end as check_error
from x$dbgalertext where message_text like '%ORA-%' and ORIGINATING_TIMESTAMP > sysdate - 7 order by 1;

Output:

MESSAGE_TEXT                                       TO_CHAR(ORIGINATING_TIMESTA CHECK_ERROR
-------------------------------------------------- --------------------------- --------------------
Errors in file /data/base/diag/rdbms/exdbx/exdbx1/ 03-may-21 19:04:24          ORA errors found
trace/exdbx1_arc2_13974.trc:
ORA-00338: log 1 of thread 1 is more recent than c
ontrol file
ORA-00312: online log 1 thread 1: '+FRA/exdbx/onli
nelog/group_1.257.1068909749'
ORA-00338: log 1 of thread 1 is more recent than c
ontrol file
ORA-00312: online log 1 thread 1: '+DATA/exdbx/onl
inelog/group_1.261.1068909739'

Errors in file /data/base/diag/rdbms/exdbx/exdbx1/ 03-may-21 19:34:59          ORA errors found
trace/exdbx1_ckpt_13906.trc:
ORA-00206: error in writing (block 3, # blocks 1)
of control file
ORA-00202: control file: '+FRA/exdbx/controlfile/c
urrent.256.1068909735'
ORA-15081: failed to submit an I/O operation to a
disk
ORA-15081: failed to submit an I/O operation to a
disk
ORA-00206: error in writing (block 3, # blocks 1)
of control file
ORA-00202: control file: '+DATA/exdbx/controlfile/
current.260.1068909733'
ORA-15081: failed to submit an I/O operation to a
disk
ORA-15081: failed to submit an I/O operation to a
disk

Errors in file /data/base/diag/rdbms/exdbx/exdbx1/ 03-may-21 19:34:59          ORA errors found
trace/exdbx1_ckpt_13906.trc:
ORA-00221: error on write to control file
ORA-00206: error in writing (block 3, # blocks 1)
of control file
ORA-00202: control file: '+FRA/exdbx/controlfile/c
urrent.256.1068909735'
ORA-15081: failed to submit an I/O operation to a
disk
ORA-15081: failed to submit an I/O operation to a
disk
ORA-00206: error in writing (block 3, # blocks 1)
of control file
ORA-00202: control file: '+DATA/exdbx/controlfile/
current.260.1068909733'
ORA-15081: failed to submit an I/O operation to a
disk
ORA-15081: failed to submit an I/O operation to a
disk

Errors in file /data/base/diag/rdbms/exdbx/exdbx1/ 03-may-21 19:34:59          ORA errors found
trace/exdbx1_ckpt_13906.trc:
ORA-15080: synchronous I/O operation to a disk fai
led

Errors in file /data/base/diag/rdbms/exdbx/exdbx1/ 03-may-21 19:34:59          ORA errors found
trace/exdbx1_ckpt_13906.trc:
ORA-15080: synchronous I/O operation to a disk fai
led

Errors in file /data/base/diag/rdbms/exdbx/exdbx1/ 03-may-21 19:34:59          ORA errors found
trace/exdbx1_ckpt_13906.trc:
ORA-27603: Cell storage I/O error, I/O failed on d
isk o/192.168.56.33/DATA_CD_cell3_stocell at offse
t 247513088 for data length 16384
ORA-27626: Exadata error: 12 (Network error)

Errors in file /data/base/diag/rdbms/exdbx/exdbx1/ 03-may-21 19:34:59          ORA errors found
trace/exdbx1_ckpt_13906.trc:
ORA-27603: Cell storage I/O error, I/O failed on d
isk o/192.168.56.33/FRA_CD_cell09_stocell at offse
t 33603584 for data length 16384
ORA-27626: Exadata error: 12 (Network error)
ORA-27300: OS system dependent operation:rpc updat
e timed out failed with status: 0
ORA-27301: OS failure message: Error 0
ORA-27302: failure occurred at: skgxp_path

Errors in file /data/base/diag/rdbms/exdbx/exdbx1/ 03-may-21 19:34:59          ORA errors found
trace/exdbx1_lmon_13887.trc:
ORA-00202: control file: '+DATA/exdbx/controlfile/
current.260.1068909733'
ORA-15081: failed to submit an I/O operation to a
disk

Errors in file /data/base/diag/rdbms/exdbx/exdbx1/ 03-may-21 19:34:59          ORA errors found
trace/exdbx1_lmon_13887.trc:
ORA-27603: Cell storage I/O error, I/O failed on d
isk o/192.168.56.33/DATA_CD_cell1_stocell at offse
t 264290304 for data length 16384
ORA-27626: Exadata error: 12 (Network error)
ORA-27300: OS system dependent operation:rpc updat
e timed out failed with status: 0
ORA-27301: OS failure message: Error 0
ORA-27302: failure occurred at: skgxp_path

ORA-1092 : opitsk aborting process                 03-may-21 19:35:06          ORA errors found
ORA-1092 : opitsk aborting process                 03-may-21 19:35:03          ORA errors found
ORA-1092 : opitsk aborting process                 03-may-21 19:35:04          ORA errors found

12 rows selected.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s