How to read Alert and Listener logs from SQLPLUS

Before 11g, alert logs were found using the below option

show parameter BACKGROUND_DUMP_DEST

and from 11g, Location of alert logs are found by the below SQL, 

select * from   V$DIAG_INFO where name ='Diag Trace'
   INST_ID NAME                     VALUE
---------- ------------------------ ------------------------------------------
         1 Diag Trace               /database/diag/rdbms/orcl/ORCL/trace

Oracle 11G had made that a lot easier to read alert_log from SQLPLUS using X$DBGALERTEXT, which picks from XML version of the Alert log.
It contains vital information of the database in chronological order.

in 11gR2 and later

# Selecting from the Alert log :-
select ORIGINATING_TIMESTAMP, MESSAGE_TEXT
from V$DIAG_ALERT_EXT
WHERE ORIGINATING_TIMESTAMP > sysdate -1/24
and trim(COMPONENT_ID)='rdbms';

-- MESSAGE_LEVEL - 1: CRITICAL

select *

from   v$diag_alert_ext 

where  message_level = 1

order by ORIGINATING_TIMESTAMP desc;

Level the message belongs to. Lower level values imply higher severity for errors. Possible values include:

• 1: CRITICAL: critical errors

• 2: SEVERE: severe errors

• 8: IMPORTANT: important message

• 16: NORMAL: normal message

-- MESSAGE_TYPE - 2: INCIDENT_ERROR, 3: ERROR

select * 

--message_type, message_level, message_text

from   v$diag_alert_ext

where  message_type in (2, 3);

Type of the message, indicating that a different type of response is required. Possible values include:

• 1: UNKNOWN - Essentially the NULL type

• 2: INCIDENT_ERROR - The program has encountered an error for some internal or unexpected reason, and it must be reported to Oracle Support

• 3: ERROR - An error of some kind has occurred

• 4: WARNING: An action occurred or a condition was discovered that should be reviewed and may require action

• 5: NOTIFICATION: reports a normal action or event. This could be a user action such as "logon completed"

• 6: TRACE: Output of a diagnostic trace

-- list the trace fies

SELECT * FROM TABLE(rdsadmin.rds_file_util.listdir('BDUMP')) order by mtime desc;

-- read the contents of a trace file

SELECT text FROM TABLE(rdsadmin.rds_file_util.read_text_file('BDUMP','ORCL_ora_2163.trc'));

in 11gR1

Please note is
In our example, DBA_USER is a Read only user.
Steps to provide the access to read alert_log from SQLPLUS are as below:-

create view v_$alert_log as select * from x$dbgalertext;
create public synonym v$alert_log for sys.v_$alert_log;
grant select on v$alert_log to DBA_USER;


--Use the query below to find the error, timestamp and the criticality.
define time_min=60
select  ORIGINATING_TIMESTAMP , MESSAGE_LEVEL,message_text from v$alert_log where rownum <= 20
and MESSAGE_LEVEL =1
and cast(ORIGINATING_TIMESTAMP as DATE) > sysdate - &&time_min/1440
order by 1 desc;

--to check the count of such errors in a given time
define time_min=60
select substr(MESSAGE_TEXT, 1, 300) message_text, count(*) cnt
from X$DBGALERTEXT
where MESSAGE_LEVEL =1
and cast(ORIGINATING_TIMESTAMP as DATE) > sysdate - &&time_min/1440
group by substr(MESSAGE_TEXT, 1, 300);


Structure of the newly created view - v$alert_log
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