Wednesday, November 7, 2007

Oracle 11g : Alert Log file

Print this post

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. 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.

alert ( The XML-formatted alert log )

trace ( Background and server process trace files and SQL trace files and text alert.logfiles )

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_HOME/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_HOME/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

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 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


5 comments:

yaseen said...

Hi famy good day to you,

I have applied patch 3480000 while applying I got some jobs failed using
adctrl utility 8 hidden option i skipped the failed worker.How to complete the failed worker manually

advance thanks & regards
yaseen.a.r

Famy Rasheed said...

Hi yaseen,

you can see the worker logfile, for which action you have got the error, and that sql script can be ran from that source location, which can be identified from the logfile itself. Open the script file and see what action its doing and as which user its to be executed. Usually most of the tasks are done as apps user itself

Regards
Famy

yaseen said...

thanks for relpy famy ,

There is any possibility to reduce the no jobs in workers . for me while applying patch 8500 jobs is there .
There is any way to reduce the no of jobs.my workers is default 2 only i am asking about jobs if there is any wrong in the query ? sorry for that i am new to apps


thanks & regards
yaseen.a.r

yaseen said...

thanks for relpy famy ,

There is any possibility to reduce the no jobs in workers . for me while applying patch 8500 jobs is there .
There is any way to reduce the no of jobs.my workers is default 2 only i am asking about jobs if there is any wrong in the query ? sorry for that i am new to apps


thanks & regards
yaseen.a.r

Famy Rasheed said...

Hi yaseen,

Jobs are hard coded in the unified
patch driver file. If you skipping you jobs for any reasons, the patch will not be successfull and you will not get the required results. All the jobs has to be completed successfully. Let everything run to complete. If you need it to run faster, increase your CPUs and memory so that more workers can handle it together.

Regards
Famy