Reading the alert.log as a local table.

Reading the alert.log file is a must for the DBA. This is the primary source of information about the Database. The traditional way to read it is by means of any text based tool that can open the file and lets you take a look at it and look for specific text patterns. This requires access to the Operating System, which probably is something the DBA can take for granted at most shops. However, it happens that because of security issues, access to the Operating System is restricted ... sounds familiar? ... either you have someone to send you the file on a regular basis, you have a link to the file (assuming proper permissions) or you have to create your own routines to access it. Another natural choice is to access it by means of either the EM Control Console or the Grid Console, and the of course an External Table.

In this example I will create an externa table to access the alert.log file

create table ALERT_LOG (
text_line varchar2( 512)
)
organization external (
type ORACLE_LOADER
default directory BACKGROUND_DUMP_DEST_DIR
access parameters (
records delimited by newline
nobadfile
nodiscardfile
nologfile
)
location( 'alertORCL.log')
);

This example assumes the BACKGROUND_DUMP_DEST_DIR exists and you have access to it. It also assumes your Oracle instance is the classical ORCL instance.

Once the external table has been created just access it as a regular Oracle table with select only privileges.


SELECT * FROM ALERT_LOG;

TEXT_LINE
--------------------------------------------------------------------------------
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Shared memory segment for instance monitoring created
Picked latch-free SCN scheme 3
Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
IMODE=BR
ILAT =18
LICENSE_MAX_USERS = 0
SYS auditing is disabled

Starting up ORACLE RDBMS Version: 11.1.0.7.0.
Using parameter settings in client-side pfile C:\ORACLE\APP\ADMIN\ORCL\PFILE\I
NIT.ORA on machine HECTOR

System parameters with non-default values:
processes = 150
memory_target = 1648M
control_files = "C:\ORACLE\APP\ORADATA\ORCL\CONTROL01.CTL"
control_files = "C:\ORACLE\APP\ORADATA\ORCL\CONTROL02.CTL"
control_files = "C:\ORACLE\APP\ORADATA\ORCL\CONTROL03.CTL"

19 rows selected.


This works for 9i, 10g and 11g, (Rel. 1 and Rel. 2). In the particular case of 11g Oracle created a new X$ view which can be accessed just like any other X$ view to read the alert.log file, the X$DBGALERTEXT view. You must have proper privileges to access it.


Reference: Oracle Data and Database Management Utilities. Chapter 3