How To View Alert.log From Database

Traditionally examine the alert.log of a database from the operating system Or using an editor like vi, ed, edit, etc. depending on the operating system where we are. A way less known is from the database itself. The advantages of this method is that we independence from OS and can make statements SQLs or scripts  multi-platform..

The following query we can see all the specific messages between a certain date or time;

 -- Look for a period of date/times -------
SELECT  
  TO_CHAR(ORIGINATING_TIMESTAMP, 'DD-MON-YYYY HH24:MI:SSXFF') AS "timestamp",
  SUBSTR(MESSAGE_TEXT, 0, 75) AS "Message"
FROM X$DBGALERTEXT
  WHERE  
  ORIGINATING_TIMESTAMP BETWEEN 
  TO_DATE ('20-APR-2015 07:00:00', 'DD-MON-YYYY HH24:MI:SS') AND
  TO_DATE ('20-APR-2015 07:10:00', 'DD-MON-YYYY HH24:MI:SS')     
/

In the following example we can see all ORA- errors in the alert.log

 -- Look for all ORA- errors -------
SELECT  
  TO_CHAR(ORIGINATING_TIMESTAMP, 'DD-MON-YYYY HH24:MI:SSXFF') AS "timestamp",
  SUBSTR(MESSAGE_TEXT, 0, 75) AS "Message"
FROM X$DBGALERTEXT
WHERE  
   MESSAGE_TEXT like '%ORA-%'
/

HTH – Antonio NAVARRO

Advertisements