Oracle 12c ADR enhancements

Introduction

Oracle 11g introduced new locations for storing the diagnostics-related logs (e.g. alert log, background trace files, foreground trace files, core dumps, etc.). This feature named ADR (Automatic Diagnostic Repository). The base directory for the ADR is defined by the DIAGNOSTIC_DEST parameter. You can also obtain the specific location of each ADR file type via V$DIAG_INFO as follows:

SQL> show parameter diagnostic
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
diagnostic_dest                      string      C:\APP\ORACLE

SQL> select name,value from v$diag_info;
NAME                           VALUE
------------------------------ ------------------------------------------------------------
Diag Enabled                   TRUE
ADR Base                       C:\APP\ORACLE
ADR Home                       C:\APP\ORACLE\diag\rdbms\orcl11\orcl11
Diag Trace                     C:\APP\ORACLE\diag\rdbms\orcl11\orcl11\trace
Diag Alert                     C:\APP\ORACLE\diag\rdbms\orcl11\orcl11\alert
Diag Incident                  C:\APP\ORACLE\diag\rdbms\orcl11\orcl11\incident
Diag Cdump                     C:\app\oracle\diag\rdbms\orcl11\orcl11\cdump
Health Monitor                 C:\APP\ORACLE\diag\rdbms\orcl11\orcl11\hm
Default Trace File             C:\APP\ORACLE\diag\rdbms\orcl11\orcl11\trace\orcl11_ora_1010

The ADRCI (ADR Command Interpreter) that was introduced in Oracle 11gR1 is a command line tool that is being installed as part of the Oracle software installation (similar to SQL*PLUS) and its purpose is to view and manage ADR diagnostic data needed for troubleshooting issues. More informatin about the ADRCI is available here.

DDL Logging

In Oracle 12c you can enable DDL logging which will audit all the DDL statements. Oracle Database 12c turns DDL logging off by default. The parameter ENABLE_DDL_LOGGING must be set to TRUE to activate DDL logging.​ DDL logs are stored in separate files and directories. DDL logs can be found in the $ADR_HOME/log/ddl. Let's see a quick demonstration of this feature:
SQL> show parameter enable_ddl_logging

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------
enable_ddl_logging                   boolean     FALSE

SQL> alter system set enable_ddl_logging=true;
System altered.

SQL> create table test_ddl_logging (id number);
Table created.

SQL> drop table test_ddl_logging;
Table dropped.

SQL> alter system set enable_ddl_logging=false;
System altered.

Now I will connect to the machine via putty in order to view the DDL log file:





As you can see the log file prints the CREATE TABLE and DROP TABLE commands.
It's also possible to view the contents of the DDL log file via the ADRCI "SHOW LOG" command which will show you the actual statements and the execution date for each statements that was executed.

DEBUG Logging

Some low-severity issues that don't require immediate action can trigger a warning which will be recorded in a debug log file (instead of the alert log). These messages might be needed to diagnose a future problem and the debug information is included as part of the incident Packaging Service (IPS) which can be shipped to the Oracle support when needed.

The location of the debug log file is $ADR_BASE/diag/rdbms/{DB-name}/{SID}/log/debug

Useful Links

• Oracle Documentation - Managing Diagnostic Data
• Oracle Reference - ENABLE_DDL_LOGGING parameter