Oracle Database Diagnostics: About Trace and Log Files

The Oracle DBA’s swiss-knife for fixing an issue is first knowing where and how to find diagnostic information needed to trouble-shoot defects and warnings on a database and its instance.

Oracle has rightly provided a centralised means of tracking down log and trace info about all its vendor products (rdbms, app_servers etc) in a single location called the Automatic Diagnostic Repository(ADR).

a.    It is found in the diag folder of the base-location of oracle - <ORACLE_BASE>/diag.

e.g.  /home/install/Orabase/diag/rdbms/annang_db/sample_sid/trace/alert_log

b.   At the database level, the diagnostic info can be stated by displaying info from running a select statement on the dynamic performance view v$diag_info.

c.    The location of diagnostic files can be customised for an oracle database by setting the DIAGNOSTIC_DEST parameter in the parameter file (PFILE/SPFILE) usually when the database is not mounted.

d.    alert logs usually contains DDL statements, system level activities and non-standard parameters configured or modified.