Finding alert.log file in 11g [ID 438148.1]

来源:互联网 发布:linux下编译qt5.7源码 编辑:程序博客网 时间:2024/05/20 14:40

Metalink - ID 438148.1

In this Document
  Purpose
  Scope and Application
  Finding alert.log file in 11g


Applies to:

Oracle Server - Enterprise Edition - Version: 11.1.0.6 and later   [Release: 11.1 and later ]
Information in this document applies to any platform.

Purpose

This bulletin is to find alert log file in its new destination in 11g

Scope and Application

All DBA

Finding alert.log file in 11g

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.

If this parameter is omitted or left null, the database sets DIAGNOSTIC_DEST upon startup as follows:

  • If environment variable ORACLE_BASE is set, DIAGNOSTIC_DEST is set to the directory designated by ORACLE_BASE.
  • If environment variable ORACLE_BASE is not set, DIAGNOSTIC_DEST is set to ORACLE_HOME/log.
    for e.g

    SQL> show parameter diagno

    NAME                          TYPE          VALUE 
    --------------------------- ----------- ------------------------------
    diagnostic_dest             string      /u01/oracle/product/ora11g/log

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,

 for a database with a SID and database name both equal to ora11g, the ADR home would be in the following location:

<ADR_BASE>/diag/rdbms/ora11g/ora11g/

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

Subdirectory NameContentsalertThe XML-formatted alert logtraceBackground and server process trace files and SQL trace files and text alert.log filecdumpCore 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

You can also use the ADR Client Interface called 'adrci' to view the alert.log

% adrci
ADRCI> show alert
       show alert -tail 50

The alert log of a database is a chronological log of messages and errors, including the following:
  • All internal errors (ORA-600), block corruption errors (ORA-1578), and deadlock errors (ORA-60) that occur
  • Administrative operations, such as the SQL statements CREATE/ALTER/DROP DATABASE/TABLESPACE and the Oracle Enterprise Manager or SQL*Plus statements STARTUP, SHUTDOWN, ARCHIVE LOG, and RECOVER
  • Several messages and errors relating to the functions of shared server and dispatcher processes
  • Errors during the automatic refresh of a materialized view


Oracle Database uses the alert log to keep a record of these events as an alternative to displaying the information on an operator's console. (Many systems also display this information on the console.) If an administrative operation is successful, a message is written in the alert log as "completed" along with a time stamp.