How to view Database alert Log Files

1. view the operating system version
SQL> select * from v $ version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Prod
PL/SQL Release 10.2.0.1.0-Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0-Production
NLSRTL Version 10.2.0.1.0-Production
SQL>

2. view the location of the Alert Log File (alert_sid.log)
SQL> show parameter dump
NAME TYPE VALUE
-----------------------------------------------------------------------------
Background_core_dump string partial
Background_dump_dest string/u01/oracle/admin/bdump
Core_dump_dest string/u01/oracle/admin/cdump
Max_dump_file_size string 10240
Shadow_core_dump string partial
User_dump_dest string/u01/oracle/admin/udump

3. Create the directory alert
Note: directory is not an entity, but a point, pointing to a path in the OS
SQL> create or replace directory alert as '/u01/oracle/admin/bdump ';
Directory created.
SQL>

4. Create an External table alert
SQL> create table alert
1 (log varchar2 (1000 ))
2 organization external
3 (type oracle_loader
4 default directory alert
5 access parameters
6 (records delimited by newline)
7 location ('alert _ PROD. log '))
8 reject limit unlimited;
Table created.

5. View alert content
SQL> select * from alert where rownum <20;
LOG
Certificate --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Wed Feb 27 11:05:11 2013
Starting ORACLE instance (normal)
Cannot determine all dependent dynamic libraries for/proc/self/exe
Unable to find dynamic library libocr10.so in search paths
RPATH =/ade/aimetricbuild2101/oracle/has/lib/:/ade/aimetricbuild2101/oracle/lib/:/ade/aimetricbuild2101/oracle/has/lib /:
LD_LIBRARY_PATH is not set!
The default library directories are/lib and/usr/lib
Unable to find dynamic library libocrb10.so in search paths
Unable to find dynamic library libocrutl10.so in search paths
Unable to find dynamic library libocrutl10.so in search paths
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_1 parameter default value as/u01/oracle/product/10.2.0/db_1/dbs/arch
Autotune of undo retention is turned on.
IMODE = BR
ILAT = 10
LICENSE_MAX_USERS = 0
SYS auditing is disabled
19 rows selected.
SQL>

6. Check the cute ORA in the database.-error.
SQL> select * from alert where log like '% ORA-% ';
LOG
Certificate --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ORA-959 signalled during: alter database default tablespace users...
ORA-959 signalled during: drop tablespace uses...
ORA-12919 signalled during: drop tablespace users...
ORA-1549 signalled during: drop tablespace users...
ORA-1505 signalled during: alter database add logfile group 1
ORA-1184 signalled during: alter database add logfile group 1
ORA-1013 signalled during: alter tablespace tts read only...
ORA-1013 signalled during: alter tablespace tts read only...
ORA-1013 signalled during: alter tablespace users read only...
ORA-1539 signalled during: alter tablespace users read only...