[Oracle] Get alert log information from DB instances
Hi! today we’ll see a simple query to get alert log information throug a fixed table.
ENVIRONMENT
- Oracle Linux 6.10 [OS]
- Oracle RDBMS 11.2.0.4 [DATABASE]
- Oracle sqlplus 11.2.0.4 [CLIENT]
The fixed table x$dbgalertext permits to read alert log information but, unfortunately, does not exist a “global” version of it. You have to connect to all instances to get logs from all nodes.
In the following example, the query permits to get all “ORA-” errors in last 3 days. You can try to modify the parameter “text” and “days” to tune your own results. The query returns the TIMESTAMP of entry in alert log, the related SNAP_ID and the MESSAGE_TEXT.
set lines 1000
set pages 300
col TIMESTAMP for a20
col MESSAGE_TEXT for a110
DEFINE text = 'ORA-'
DEFINE days = '3'
select
to_char(alert.originating_timestamp,'YYYY-MM-DD HH24:MI:SS') TIMESTAMP
,snap.snap_id
,alert.message_text
from x$dbgalertext alert, dba_hist_snapshot snap
where alert.originating_timestamp between snap.begin_interval_time and snap.end_interval_time
and upper(alert.message_text) like upper('%&text%')
and originating_timestamp > (sysdate-&days)
order by alert.record_id;
UNDEFINE text
UNDEFINE days
Following an example of result
TIMESTAMP SNAP_ID MESSAGE_TEXT
-------------------- ---------- --------------------------------------------------------------------------------------------------------------
2020-05-04 21:58:31 19878 Errors in file /u01/app/oracle/diag/rdbms/database/DATABASE1/trace/DATABASE1_ora_23450.trc:
ORA-19698: /zfssa/database/database-1/snp/snapshot/S-202005041958/data_D-database_I-1928307788_TS-USERS_FNO-5_3espd9dr
is from different database: id=1928307788, db_name=database
2020-05-04 21:58:31 19878 Errors in file /u01/app/oracle/diag/rdbms/database/DATABASE1/trace/DATABASE1_ora_23450.trc:
ORA-19698: /zfssa/database/database-1/snp/snapshot/S-202005041958/data_D-database_I-1928307788_TS-USERS_FNO-5_3espd9dr
is from different database: id=1928307788, db_name=database
2020-05-04 21:58:32 19878 Errors in file /u01/app/oracle/diag/rdbms/database/DATABASE1/trace/DATABASE1_ora_23450.trc:
ORA-19698: /zfssa/database/database-1/snp/snapshot/S-202005041958/data_D-database_I-1928307788_TS-UNDOTBS1_FNO-3_38spd9
9k is from different database: id=1928307788, db_name=database
2020-05-04 21:58:32 19878 Errors in file /u01/app/oracle/diag/rdbms/database/DATABASE1/trace/DATABASE1_ora_23450.trc:
ORA-19698: /zfssa/database/database-1/snp/snapshot/S-202005041958/data_D-database_I-1928307788_TS-UNDOTBS1_FNO-3_38spd9
9k is from different database: id=1928307788, db_name=database
2020-05-04 21:58:32 19878 Errors in file /u01/app/oracle/diag/rdbms/database/DATABASE1/trace/DATABASE1_ora_23450.trc:
ORA-19698: /zfssa/database/database-1/snp/snapshot/S-202005041958/data_D-database_I-1928307788_TS-DATI_FNO-4_36spd93r i
s from different database: id=1928307788, db_name=database
2020-05-04 21:58:32 19878 Errors in file /u01/app/oracle/diag/rdbms/database/DATABASE1/trace/DATABASE1_ora_23450.trc:
ORA-19698: /zfssa/database/database-1/snp/snapshot/S-202005041958/data_D-database_I-1928307788_TS-DATI_FNO-4_36spd93r i
s from different database: id=1928307788, db_name=database
2020-05-04 21:58:32 19878 Errors in file /u01/app/oracle/diag/rdbms/database/DATABASE1/trace/DATABASE1_ora_23450.trc:
ORA-19698: /zfssa/database/database-1/snp/snapshot/S-202005041958/data_D-database_I-1928307788_TS-SYSAUX_FNO-2_3bspd9dd
is from different database: id=1928307788, db_name=database
2020-05-04 21:58:32 19878 Errors in file /u01/app/oracle/diag/rdbms/database/DATABASE1/trace/DATABASE1_ora_23450.trc:
ORA-19698: /zfssa/database/database-1/snp/snapshot/S-202005041958/data_D-database_I-1928307788_TS-SYSAUX_FNO-2_3bspd9dd
is from different database: id=1928307788, db_name=database