In 12c, the location of DDL logs has changed from alert log to DDL log.

Oracle Database 11g

If instance parameter ENABLE_DDL_LOGGING = TRUE

DDL statements are logged into the alert log.

Oracle Database 12c

If instance parameter ENABLE_DDL_LOGGING = TRUE

DDL statement logging has been moved from alert log to DDL log
i.e. $ADR_HOME/log/ddl/log.xml
This file  contains only the DDL statements and dates.
– Let’s demonstrate

–Check that by default DDL logging is disabled

SQL> sho parameter ddl

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
ddl_lock_timeout                     integer     0
enable_ddl_logging                   boolean     FALSE

– Enable DDL logging

SQL> alter system set enable_ddl_logging=true;

sho parameter ddl

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
ddl_lock_timeout                     integer     0
enable_ddl_logging                   boolean     TRUE

– Remove all the trace files including alert log

[oracle@em12 ~]$ cd /u01/app/oracle/diag/rdbms/cdb1/cdb1/trace/

rm -rf *

ls

– check that there is no DDL log file in /u01/app/oracle/diag/rdbms/cdb1/cdb1/log/ddl

[oracle@em12 ddl]$ cd /u01/app/oracle/diag/rdbms/cdb1/cdb1/log/ddl
ls

– Issue a DDL while connected to CDB1

SQL> sho con_name

CON_NAME
------------------------------
CDB$ROOT

SQL>  create table test(x number);

– check that alert log has not been created as DDL’s are no longer logged to alert log

[oracle@em12 log]$ ls /u01/app/oracle/diag/rdbms/cdb1/cdb1/trace/

– check that ddl log file log.xml has been created in $ORACLE_BASE/diag/rdbms/cdb1/cdb1/log/ddl($ADR_HOME/log/ddl)

[oracle@em12 ddl]$ ls /u01/app/oracle/diag/rdbms/cdb1/cdb1/log/ddl/

log.xml

– check that the contents of the file give info about the DDL that was issued
Note that name of the database issuing the DDL (CDB$ROOT) is not there in the log

[oracle@em12 ddl]$ cat log.xml

<msg time='2014-03-05T13:08:33.207+05:30' org_id='oracle' comp_id='rdbms'
msg_id='opiexe:4181:2946163730' type='UNKNOWN' group='diag_adl'
level='16' host_id='em12.example.com' host_addr='192.9.201.150'
version='1'>
<txt>create table test(x number)
</txt>
</msg>

– Issue another DDL from PDB1

SQL> alter session set container=pdb1;
create table test(x number);

– check that the contents of the file give info about this DDL also
   Again note that name of the database issuing the DDL(PDB1) is not there in the log

[oracle@em12 ddl]$ cat log.xml

<msg time='2014-03-05T13:08:33.207+05:30' org_id='oracle' comp_id='rdbms'
msg_id='opiexe:4181:2946163730' type='UNKNOWN' group='diag_adl'
level='16' host_id='em12.example.com' host_addr='192.9.201.150'
version='1'>
<txt>create table test(x number)
</txt>
</msg>
<msg time='2014-03-05T13:09:11.104+05:30' org_id='oracle' comp_id='rdbms'
msg_id='kpdbLogDDL:15115:2946163730' type='UNKNOWN' group='diag_adl'
level='16' host_id='em12.example.com' host_addr='192.9.201.150'>
<txt> create table test(x number)
</txt>
</msg>

It can be seen that it is not possible to identify  from this log whether the  DDL was issued from CDB$ROOT or which PDB (in case CDB has multiple PDB’s)

In 11g or 12c non-CDB, this issue is not there since the alert log belongs to one database only.

References:

Oracle Documentation

Oracle 12c: DDL Logging : Will it serve the purpose?

————————————————————————————

Related Links:

Home

Database 12c