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