Monday, April 18, 2011

Alert log & Trace file location in Oracle 11gR2


Oracle 11gR2 is a major release upgrade from 10gR2 and 11gR1 both. Many previous parameters like user_dump_dest etc have been deprecated. There is a single parameter called diagnostic_dest.  Many DBA’s are so used to the location of alert log and trace files , that it becomes quite inconvenient when you start using Oracle 11g . But Oracle 11g has a powerful new utility called ADR CLI to navigate around the alert log and other trace files. I will demonstrate a few ways to check the alert log and trace files using “adrci” command.
C:\Documents and Settings\test>adrci
ADRCI: Release 11.2.0.1.0 – Production on Thu Mar 31 11:56:54 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
ADR base = “e:\app\testdb”
adrci> help
HELP [topic]
Available Topics:
CREATE REPORT
ECHO
EXIT
HELP
HOST
IPS
PURGE
RUN
SET BASE
SET BROWSER
SET CONTROL
SET ECHO
SET EDITOR
SET HOMES | HOME | HOMEPATH
SET TERMOUT
SHOW ALERT
SHOW BASE
SHOW CONTROL
SHOW HM_RUN
SHOW HOMES | HOME | HOMEPATH
SHOW INCDIR
SHOW INCIDENT
SHOW PROBLEM
SHOW REPORT
SHOW TRACEFILE
SPOOL
There are other commands intended to be used directly by Oracle, type
“HELP EXTENDED” to see the list
adrci> show alert
1: diag\clients\user_system\host_3726408541_76
2: diag\rdbms\testdb\testdb
3: diag\tnslsnr\test\listener
Q: to quit
Please select option:
Many of the adrci commands are very similar to the standard Unix vi commands. For example
SHOW ALERT -TAIL (to view the last 10 entries)
SHOW ALERT -TAIL 50 (to view the last 50 entries)
SHOW ALERT -TAIL -F (keeps the alert log open to view as entries arrive until Control C is pressed)
show alert -p “message_text like ‘%ORA-07445%’” (to show all occurences of the string ‘ORA-07445′ in the alert log.)
Purging Alert Log Content
The adrci command ‘purge’ can be used to purge entries from the alert log. Note that this purge will only apply to the XML based alert log and not the text file based alert log which still has to be maintained using OS commands.
The purge command takes the input in minutes and specifies the number of minutes for which records should be retained.
So to purge all alert log entries older than 7 days the following command will be used:
adrci > purge -age 10080 -type ALERT
It is a powerful utility with much more functionality.
P.S: The general location of alert log on windows is : E:\app\<hostname>\diag\rdbms\<sid>\<sid>\trace


Finding Trace Files

Trace files are stored in the Automatic Diagnostic Repository (ADR), in the trace directory under each ADR home. To help you locate individual trace files within this directory, you can use data dictionary views. For example, you can find the path to your current session's trace file or to the trace file for each Oracle Database process.

To find the trace file for your current session:
  • Submit the following query:

    SELECT VALUE FROM V$DIAG_INFO WHERE NAME = 'Default Trace File';
    

    The full path to the trace file is returned.
To find all trace files for the current instance:
  • Submit the following query:

    SELECT VALUE FROM V$DIAG_INFO WHERE NAME = 'Diag Trace';
    

    The path to the ADR trace directory for the current instance is returned.
To determine the trace file for each Oracle Database process:
  • Submit the following query:

    SELECT PID, PROGRAM, TRACEFILE FROM V$PROCESS;
Step by step understanding of ADRCI can be found here : http://uhesse.wordpress.com/2011/06/01/adrci-a-survival-guide-for-the-dba/

See also :   11g NEW FEATURES
     

0 comments:

Post a Comment