11g Alert log new features



Last updated on November 28th, 2015 at 11:58 am

11g Alert log new features

Beginning with Release 11g of Oracle Database, the alert log is written as both an XML-formatted file and as a text file, as in earlier releases.Both these log files are stored inside the ADR home.The ADR root directory is known as ADR BASE.The Automatic Diagnostic Repository (ADR) is a directory structure that is stored outside of the database.This parameter is set by DIAGNOSTIC_DEST initialization parameter.

SQL> show parameter diagno

NAME TYPE VALUE
————————— ———– ——————————
diagnostic_dest string /u001/oracle/product/XPROD11g/diag
The location of an ADR home is given by the following path, which starts at the ADR base directory:

ADR_BASE/diag/product_type/product_id/instance_id

For example,

So for RDBMS oracle Home of Database name XPROD

ADR_base/diag/rdbms/XPROD/XPROD

Within the ADR home directory are subdirectories where the database instance stores diagnostic data.

alert Log file :
The XML-formatted alert log
trace Background and server process trace files and SQL trace files and text alert.log file
cdump Core files

XML formatted alert.log
The alert log is named log.xml and is stored in the alert subdirectory of ADR home.

To get the log.xml path

ADR_BASE/diag/product_type/product_id/instance_id/alert

from sqlplus

SQL> select value from v$diag_info where name =’Diag Alert’;

ADRCI utility to view a text version of the alert log (with XML tags stripped)

Text formatted alert.log

The alert.log is named alertSID.log and is stored in the trace subdirectory of ADR home.

To view the text only alert.log file

ADR_BASE/diag/product_type/product_id/instance_id/trace

from sqlplus

SQL> select value from v$diag_info where name =’Diag Trace’;
or
SQL>show parameter background_dump_dest

Open file alert_SID.log with a text editor

With 11g ,Oracle provides a way to look the alert log file from database also. There is a fixed table X$DBGALERTEXT, when you query it, Oracle reads the log.xml from alert directory (which contains all the data what alert.log does), parses it and returns the details back as rows:

SQL> select message_text from X$DBGALERTEXT where rownum desc X$DBGALERTEXT
Name Null? Type
——————————- ——– —————————-
1 ADDR RAW(4)
2 INDX NUMBER
3 INST_ID NUMBER
4 ORIGINATING_TIMESTAMP TIMESTAMP(3) WITH TIME ZONE
5 NORMALIZED_TIMESTAMP TIMESTAMP(3) WITH TIME ZONE
6 ORGANIZATION_ID VARCHAR2(64)
7 COMPONENT_ID VARCHAR2(64)
8 HOST_ID VARCHAR2(64)
9 HOST_ADDRESS VARCHAR2(16)
10 MESSAGE_TYPE NUMBER
11 MESSAGE_LEVEL NUMBER
12 MESSAGE_ID VARCHAR2(64)
13 MESSAGE_GROUP VARCHAR2(64)
14 CLIENT_ID VARCHAR2(64)
15 MODULE_ID VARCHAR2(64)
16 PROCESS_ID VARCHAR2(32)
17 THREAD_ID VARCHAR2(64)
18 USER_ID VARCHAR2(64)
19 INSTANCE_ID VARCHAR2(64)
20 DETAILED_LOCATION VARCHAR2(160)
21 PROBLEM_KEY VARCHAR2(64)
22 UPSTREAM_COMP_ID VARCHAR2(100)
23 DOWNSTREAM_COMP_ID VARCHAR2(100)
24 EXECUTION_CONTEXT_ID VARCHAR2(100)
25 EXECUTION_CONTEXT_SEQUENCE NUMBER
26 ERROR_INSTANCE_ID NUMBER
27 ERROR_INSTANCE_SEQUENCE NUMBER
28 VERSION NUMBER
29 MESSAGE_TEXT VARCHAR2(2048)
30 MESSAGE_ARGUMENTS VARCHAR2(128)
31 SUPPLEMENTAL_ATTRIBUTES VARCHAR2(128)
32 SUPPLEMENTAL_DETAILS VARCHAR2(128)
33 PARTITION NUMBER
34 RECORD_ID NUMBER

There’s also a fixed table X$DBGDIREXT, which returns all file and directory names under [diagnostic_dest]/diag directory:

SQL> select lpad(‘ ‘,lvl,’ ‘)||logical_file file_name
from X$DBGDIREXT
where rownum


Leave a Reply