x$dbgalertext

SQL> conn / as sysdba

Connected.

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

PL/SQL Release 11.2.0.2.0 - Production

CORE    11.2.0.2.0      Production

TNS for Linux: Version 11.2.0.2.0 - Production

NLSRTL Version 11.2.0.2.0 - Production

SQL> create view v$alert_log as select * from x$dbgalertext;

View created.

SQL> create public synonym v$alert_log for v$alert_log;

Synonym created.

SQL> grant select on v$alert_log to jay;

Grant succeeded.

SQL> conn jay/magic

Connected.

SQL> desc v$alert_log

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 ADDR                                               RAW(8)

 INDX                                               NUMBER

 INST_ID                                            NUMBER

 ORIGINATING_TIMESTAMP                              TIMESTAMP(3) WITH TIME ZONE

 NORMALIZED_TIMESTAMP                               TIMESTAMP(3) WITH TIME ZONE

 ORGANIZATION_ID                                    VARCHAR2(64)

 COMPONENT_ID                                       VARCHAR2(64)

 HOST_ID                                            VARCHAR2(64)

 HOST_ADDRESS                                       VARCHAR2(46)

 MESSAGE_TYPE                                       NUMBER

 MESSAGE_LEVEL                                      NUMBER

 MESSAGE_ID                                         VARCHAR2(64)

 MESSAGE_GROUP                                      VARCHAR2(64)

 CLIENT_ID                                          VARCHAR2(64)

 MODULE_ID                                          VARCHAR2(64)

 PROCESS_ID                                         VARCHAR2(32)

 THREAD_ID                                          VARCHAR2(64)

 USER_ID                                            VARCHAR2(64)

 INSTANCE_ID                                        VARCHAR2(64)

 DETAILED_LOCATION                                  VARCHAR2(160)

 PROBLEM_KEY                                        VARCHAR2(64)

 UPSTREAM_COMP_ID                                   VARCHAR2(100)

 DOWNSTREAM_COMP_ID                                 VARCHAR2(100)

 EXECUTION_CONTEXT_ID                               VARCHAR2(100)

 EXECUTION_CONTEXT_SEQUENCE                         NUMBER

 ERROR_INSTANCE_ID                                  NUMBER

 ERROR_INSTANCE_SEQUENCE                            NUMBER

 VERSION                                            NUMBER

 MESSAGE_TEXT                                       VARCHAR2(2048)

 MESSAGE_ARGUMENTS                                  VARCHAR2(128)

 SUPPLEMENTAL_ATTRIBUTES                            VARCHAR2(128)

 SUPPLEMENTAL_DETAILS                               VARCHAR2(128)

 PARTITION                                          NUMBER

 RECORD_ID                                          NUMBER

SQL> set lin 180

SQL> col ORIGINATING_TIMESTAMP format A20

SQL> col MESSAGE_TEXT format A90

SQL> select MESSAGE_TEXT from v$alert_log WHERE MESSAGE_TEXT LIKE 'ORA-%' ;

MESSAGE_TEXT

------------------------------------------------------------------------------------------

ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...

ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...

ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...

ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...

ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...

ORA-1589 signalled during: alter database open...

ORA-16038: log 1 sequence# 169 cannot be archived

ORA-19502: write error on file "", block number  (block size=)

ORA-00312: online log 1 thread 1:

Some more that can be used to read alert log:

select DISTINCT ORIGINATING_TIMESTAMP,MESSAGE_TEXT from v$alert_log WHERE MESSAGE_TEXT LIKE 'ORA-%' or MESSAGE_TEXT LIKE '%Fatal%' AND ORIGINATING_TIMESTAMP > SYSDATE-4; 

select rownum “line”, message_text “error” from v$alert_log where originating_timestamp > (sysdate – 5/1440) and message_text like ‘%ORA-%’ order by originating_timestamp;