Categories

[Oracle] Get alert log information from DB instances

Created On
You are here:
  • Main
  • Oracle
  • [Oracle] Get alert log information from DB instances
< All Topics

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
Table of Contents
Tags: