Using the extended trace facility via the event

Chapter 3 ■ Files

among others. Instrumentation is vital in application design and development, and the Oracle database becomes better instrumented with each release. In fact, the amount of additional instrumentation in the database between Oracle9i Release 2 and Oracle 11g, and now Oracle 12c, is phenomenal. Oracle 10g took code instrumentation in the kernel to a whole new level with the introduction of the Automatic Workload Repository (AWR) and Active Session History (ASH) features. Oracle 11g took that further with options such as the Automatic Diagnostic Repository (ADR) and the SQL Performance Analyzer (SPA). Oracle 12c advanced even further with the addition of a DDL log to track all DDL operations in a database (something that shouldn’t be happening in many typical production databases day to day) and the debug log to track exceptional conditions in the database.

Requested Trace Files

The trace files you typically expect to be generated as the result of enabling trace via DBMS_MONITOR (ALTER SESSION SET SQL_TRACE=TRUE in Oracle9i Release 2 and earlier), or using the extended trace facility via the 10046 event, might be as follows:

•� If you are using a dedicated server connection, the trace file will be generated in the directory specified by the user_dump_dest parameter.

•� If you are using a shared server connection, the trace file will be generated in the directory specified by the background_dump_dest parameter.

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------background_dump_dest string /home/ora12cr1/app/ora12cr1/di ag/rdbms/ora12cr1/ora12cr1/tra ce

core_dump_dest string /home/ora12cr1/app/ora12cr1/di ag/rdbms/ora12cr1/ora12cr1/cdu mp

NAME VALUE
------------------------------ ------------------------------background_dump_dest /home/ora12cr1/app/ora12cr1/di ag/rdbms/ora12cr1/ora12cr1/tra ce

user_dump_dest /home/ora12cr1/app/ora12cr1/di ag/rdbms/ora12cr1/ora12cr1/tra ce

In Oracle 11g, a new facility, the ADR, was added. As part of this new facility, there’s a new V$ view—V$DIAG_INFO.