SQL> Select * From Alert_XML;

By mapping an external table to some text file, you can view the file contents as if it were
data in a database table. External tables are available since Oracle 9i Database, and from Oracle
11gR2 Database on, it is even possible to do some inline preprocessing on the file.

The following example of this feature picks up on standard output of shell script “get_alert_xml.sh”.
It isn’t referencing any file, but take notice of the fact that an empty “dummyfile” must still be
present and readable by oracle. By pre-executing some ADRCI commands and redirecting output to screen,
external table Alert_XML will show the last 7 days of entries of the xml version of the alert log.

su - oracle
. oraenv [ orcl ]

$ cd /u01/app/oracle/admin/scripts
$ touch dummyfile
$ echo '#!/bin/sh'                                                                     > get_alert_xml.sh
$ echo 'ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1'                          >> get_alert_xml.sh
$ echo 'DIAG_HOME=diag/rdbms/orcl/orcl'                                               >> get_alert_xml.sh
$ echo 'DAYS=\\"originating_timestamp > systimestamp-7\\"'                            >> get_alert_xml.sh
$ echo '$ORACLE_HOME/bin/adrci exec="set home $DIAG_HOME;show alert -p $DAYS -term;"' >> get_alert_xml.sh
$ chmod 744 get_alert_xml.sh

$ sqlplus / as sysdba
SQL> create directory exec_dir as '/u01/app/oracle/admin/scripts';
SQL> grant read,execute on directory exec_dir to harry;
SQL> connect harry/****
SQL> CREATE TABLE ALERT_XML ( TEXT VARCHAR2(400 CHAR) )
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY EXEC_DIR
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE
PREPROCESSOR EXEC_DIR:'get_alert_xml.sh'
nobadfile
nodiscardfile
nologfile
)
LOCATION ('dummyfile')
)
REJECT LIMIT UNLIMITED
NOPARALLEL
NOMONITORING;
SQL> select * from alert_xml;

TEXT
--------------------------------------------------------------------------------

ADR Home = /u01/app/oracle/diag/rdbms/orcl/orcl:
*************************************************************************
2014-08-26 10:21:19.018000 +02:00
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST
2014-08-26 10:21:20.066000 +02:00

> snip <

SQL>

Check out Alert_XML_Errors here.

—–Add-on 5 September 2014——
if confronted with more than 1 database instance on the database server, you can either use 1 dummyfile for all instances and a different shell script for each instance in order to set the correct DIAG HOME, or you use a different LOCATION file for each instance, and reference this in one shell script. I’d like to opt for the latter, because the LOCATION file only has to contain the instance name, so it’s lesser code in total.

For instance, with LOCATION (‘orcl.txt’), and file orcl.txt just containing the instance name orcl, the following shell script code:

 
ORACLE_SID=`/bin/cat $1`         
ORACLE_DBS=`/usr/bin/expr $ORACLE_SID | /usr/bin/tr '[:lower:]' '[:upper:]' `
DIAG_HOME=diag/rdbms/$ORACLE_DBS/$ORACLE_SID

generates this DIAG_HOME: diag/rdbms/ORCL/orcl