This article describes new directories that have appeared in Automatic Diagnostic Repository in database release version 12.1

• DDL Log – $ADR_HOME/log/ddl
• Debug Log – $ADR_HOME/log/debug

DDL Log

In Oracle 11G oracle has introduced new parameter ENABLE_DDL_LOGGING. This parameter enables or disables the writing of a subset of data definition language (DDL) statements into a log.

In previous release DDLs were written into alter.log in new release the information is written to dedicated log log.xml stored in new directory directory

$ADR_HOME/log/ddl

To find out where is “ADR Home” you can check view $DIAG_INFO on your instance.

select name, value
from v$diag_info
where name='ADR Home';

NAME      VALUE                                  
--------- --------------------------------------
ADR Home  D:\APP\ORACLE\diag\rdbms\ora12c\ora12c

so in my case $ADR_HOME/log/ddl is

d:\app\oracle\diag\rdbms\ora12c\ora12c\log\ddl

Example of turning on DDL logging

alter session set enable_ddl_logging=true;

create table test_tbl
(
  id number
);

drop table test_tbl;

now it’s time to check XML result in log file

"d:\app\oracle\diag\rdbms\ora12c\ora12c\log\ddl\log.xml"

<msg time='2014-06-16T11:31:50.104' org_id='oracle' comp_id='rdbms'
 msg_id='kpdbLogDDL:15115:2946163730' type='UNKNOWN' group='diag_adl'
 level='16' host_id='LAPTOP' host_addr='fe80::69b3:fbc3:29c6:c65f%18'
 version='1'>
 <txt>create table test_tbl
(
  id number
)
 </txt>
</msg>
<msg time='2014-06-16T11:31:51.503' org_id='oracle' comp_id='rdbms'
 msg_id='kpdbLogDDL:15115:2946163730' type='UNKNOWN' group='diag_adl'
 level='16' host_id='LAPTOP' host_addr='fe80::69b3:fbc3:29c6:c65f%18'>
 <txt>drop table test_tbl
 </txt>
</msg>

The same you can get using ADRCI and simple command

show log -l ddl

adrci> show log -l ddl

ADR Home = D:\app\oracle\diag\rdbms\ora12c\ora12c:
**********************************************************************
Output the results to file: 
C:\Users\tomasz\AppData\Local\Temp\utsout_2880_3828_1.ado
adrci>

and you should see following output

2014-06-16 11:31:50.104000 +02:00
create table test_tbl
(
  id number
)
2014-06-16 11:31:51.503000 +02:00
drop table test_tbl

Debug Log

The debug log directory is stored in

$ADR_HOME/log/debug

It stores unusual events, warnings, states in database. Most of them are not serious enough to write them to the alert.log.

Because of that altert.log is more compact and easier to read. Data from debug log are intended for Oracle support. DBAs should not use the debug log directly. The debug log is included in IPS incident packages.

You can check debug log using ADRCI and simple command

show log -l debug

adrci> show log -l debug

ADR Home = D:\app\oracle\diag\rdbms\ora12c\ora12c:
********************************************************

No diagnostic log in selected home
adrci>

Have a fun 🙂

Tomasz