Check Database ALERT LOG Output

Onur ARDAHANLI

2 weeks ago

In today’s article, we will be learning how to Check ALERT LOG Output in Database.

We can query the errors encountered in the database directly without accessing the ALERT LOG file on the server as shown below.

The following query will retrieve entries from the alert log file containing “ORA-“, “shutdown”, and “Checkpoint not complete”.

	SQL>
	SELECT INST_ID ınstd, ORIGINATING_TIMESTAMP otsmp, MESSAGE_TEXT mssg FROM (
	select INST_ID, ORIGINATING_TIMESTAMP, MESSAGE_TEXT from V_$DIAG_ALERT_EXT where MESSAGE_TEXT like 'ORA-%' AND ORIGINATING_TIMESTAMP>SYSDATE-2
	UNION ALL
	select INST_ID, ORIGINATING_TIMESTAMP, MESSAGE_TEXT from V_$DIAG_ALERT_EXT where MESSAGE_GROUP ='shutdown' AND ORIGINATING_TIMESTAMP>SYSDATE-2
	UNION ALL
	select INST_ID, ORIGINATING_TIMESTAMP, MESSAGE_TEXT from V_$DIAG_ALERT_EXT where MESSAGE_GROUP ='Checkpoint not complete' AND ORIGINATING_TIMESTAMP>SYSDATE-2
	UNION ALL
	select INSTANCE_NUMBER INST_ID, CREATION_TIME ORIGINATING_TIMESTAMP, REASON MESSAGE_TEXT from DBA_OUTSTANDING_ALERTS where CREATION_TIME>SYSDATE-2)
	order by ORIGINATING_TIMESTAMP desc;

SQL>

SELECT INST_ID ınstd, ORIGINATING_TIMESTAMP otsmp, MESSAGE_TEXT mssg FROM (

select INST_ID, ORIGINATING_TIMESTAMP, MESSAGE_TEXT from V_$DIAG_ALERT_EXT where MESSAGE_TEXT like 'ORA-%' AND ORIGINATING_TIMESTAMP>SYSDATE-2

UNION ALL

select INST_ID, ORIGINATING_TIMESTAMP, MESSAGE_TEXT from V_$DIAG_ALERT_EXT where MESSAGE_GROUP ='shutdown' AND ORIGINATING_TIMESTAMP>SYSDATE-2

UNION ALL

select INST_ID, ORIGINATING_TIMESTAMP, MESSAGE_TEXT from V_$DIAG_ALERT_EXT where MESSAGE_GROUP ='Checkpoint not complete' AND ORIGINATING_TIMESTAMP>SYSDATE-2

UNION ALL

select INSTANCE_NUMBER INST_ID, CREATION_TIME ORIGINATING_TIMESTAMP, REASON MESSAGE_TEXT from DBA_OUTSTANDING_ALERTS where CREATION_TIME>SYSDATE-2)

order by ORIGINATING_TIMESTAMP desc;

2. In addition, the ALERT_LOG file should be examined on all NODEs in the database as follows.

$ tail -100f /u01/app/oracle/diag/rdbms/primary/primary1/trace/alert_primary1.log

1	$ tail -100f /u01/app/oracle/diag/rdbms/primary/primary1/trace/alert_primary1.log