Preventing ORA-00376 Errors in Oracle Alert Log

This is probably as complete a way of killing an Oracle database as you could wish for. The sys tables contain all the metadata about every object in the database -- objects, segments, extents ... so the database now contains no information on what user tables it stores, including the tables that store the data about that.

New database, I think.

And no more sys connection accidents.

1) If you are using Oracle 11g or later:

Starting from 11g Oracle, was been introduced a fixed table, X$DBGALERTEXT, from which you can retrieve all the data what alert.log does. Here is a simple query that retrieves all errors which occured today, like you want.

SET linesize 200 pagesize 200
col RECORD_ID FOR 9999999 head ID
col MESSAGE_TEXT FOR a120 head Message

SELECT record_id, message_text
FROM X$DBGALERTEXT
WHERE originating_timestamp > systimestamp - 1 AND regexp_like(message_text, '(ORA-|error)')

As Twinkles mentioned in his answer, you can take advantage of ADRCI utility to select alert filters that you need (in fact, this utility queries against the X$DBGALERTEXT table). For example, you can build a simple function and call it from OS prompt:

vealert() {
  adrci exec="set home diag\/rdbms\/$DB_UNIQUE_NAME/$ORACLE_SID\; \
   show alert -p \\\"message_text like '%ORA-%' \
     and originating_timestamp > systimestamp - $1\\\""
}

in file .bashrc, for example:

export DB_UNIQUE_NAME=<unique_name>
. $HOME/bin/vealert

Then:

bash
vealert 1

..
Tue Dec 24 17:46:05 2013
ORA-28 signalled during: ALTER TABLESPACE DATA_APP_199903 READ ONLY...
..

2) Otherwise:

Go and have a look at this web page. There is a script that can be used to check any ORA- errors are there in the alertlog file for the present day.

To schedule this job, you can add a line in your cron table specifying the time you want the job to execute. Alternatively, you can take advantage of the Oracle Scheduler.