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.