Trace and Alert log files on Oracle Database Server (11g)
Trace and Alert log files
Root ‘log dump’ location:
[sql]select value from v$parameter where name = ‘background_dump_dest’;[/sql]
[text]/u01/app/oracle/diag/rdbms/db_name/trace[/text]
Find the trace file path of current session use the following PL/SQL snippet:
[sql]select u_dump.value || ‘/’ || db_name.value || ‘_ora_’ || v$process.spid ||
nvl2(v$process.traceid, ‘_’ || v$process.traceid, null) || ‘.trc’ “Trace File”
from v$parameter u_dump
cross join v$parameter db_name
cross join v$process
join v$session on v$process.addr = v$session.paddr
where u_dump.name = ‘user_dump_dest’
and db_name.name = ‘db_name’
and v$session.audsid = sys_context(‘userenv’, ‘sessionid’);[/sql]
This would output something similar to:
[text]/u01/app/oracle/diag/rdbms/db_name/trace[/text]
The ALERT file would be:
[text]/u01/app/oracle/diag/rdbms/db_name/trace/alert_<db_name>.log[/text]
You could also create a DB table that would keep the alert log file and you do a select
to view a list of the logged items.
Credits to: Rene Nyffenegger
Comments