Oracle 12c New Features - Change in DDL log location

Unlike Oracle 11g the DDL statement is not logged to alert log file in Oracle 12c. The ddl statement is logged into another directory log/ddl in diagnostic_dest location.

 Enable DDL logging in Oracle 11g 

In Oracle 11g the ENABLE_DDL_LOGGING parameter logs the DDL statements into the database instance alert log.

Let us test it.

Set the ENABLE_DDL_LOGGING parameter to TRUE.

SQL> SHOW PARAMETER ENABLE_DDL_LOGGING

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

SQL> ALTER SYSTEM SET ENABLE_DDL_LOGGING=TRUE SCOPE=MEMORY;

System altered.

SQL> SHOW PARAMETER ENABLE_DDL_LOGGING

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------
enable_ddl_logging                   boolean     TRUE

Let us execute some DDL statements here

SQL>
SQL> CONN test@ddb
Enter password:
Connected.
SQL> CREATE TABLE T1(eid NUMBER,ENAME VARCHAR2(30));

Table created.

SQL> ALTER TABLE T1 DROP COLUMN ENAME;

Table altered.

SQL>

The alert log shows the record of DDL statments executed in database.

-- Snippet of alert log --
...
....
..
Mon Jan 12 19:23:50 2015
ALTER SYSTEM SET enable_ddl_logging=TRUE SCOPE=MEMORY;
Mon Jan 12 19:24:54 2015
CREATE TABLE T1(eid NUMBER,ENAME VARCHAR2(30))
Mon Jan 12 19:25:13 2015
ALTER TABLE T1 DROP COLUMN ENAME
....
..

---

 Enable DDL logging in Oracle 12c 

There is a change in Oracle 12c how DDL statement is recorded when ddl logging is enabled.The DDL statement is recorded in DDL log (not in alert log)


SQL> show parameter enable_ddl_logging

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

SQL> ALTER SYSTEM SET enable_ddl_logging=TRUE SCOPE=MEMORY;

System altered.

SQL> show parameter enable_ddl_logging

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
enable_ddl_logging     boolean TRUE

SQL> DROP TABLE T1 PURGE;

Table dropped.

SQL> CREATE TABLE T1(eid NUMBER, ename VARCHAR2(30)) TABLESPACE users;

Table created.

SQL> ALTER TABLE T1 DROP COLUMN ename;

Table altered.

SQL>

The DDL statement in recorded in DIAG_DEST/rdbms/<db name>/<instance_name>/ddl

[oracle@norac ddl]$ pwd
/u01/app/oracle/diag/rdbms/db12c/DB12C/log/ddl
[oracle@norac ddl]$ cat log.xml
<msg time='2015-01-12T23:41:22.286+05:30' org_id='oracle' comp_id='rdbms'
 msg_id='opiexe:4383:2946163730' type='UNKNOWN' group='diag_adl'
 level='16' host_id='norac.localdomain' host_addr='192.168.56.101'
 version='1'>
 <txt>DROP TABLE T1 PURGE
 </txt>
</msg>
<msg time='2015-01-12T23:43:11.838+05:30' org_id='oracle' comp_id='rdbms'
 msg_id='opiexe:4383:2946163730' type='UNKNOWN' group='diag_adl'
 level='16' host_id='norac.localdomain' host_addr='192.168.56.101'>
 <txt>CREATE TABLE T1(eid NUMBER, ename VARCHAR2(30)) TABLESPACE users
 </txt>
</msg>
<msg time='2015-01-12T23:43:45.599+05:30' org_id='oracle' comp_id='rdbms'
 msg_id='opiexe:4383:2946163730' type='UNKNOWN' group='diag_adl'
 level='16' host_id='norac.localdomain' host_addr='192.168.56.101'>
 <txt>ALTER TABLE T1 DROP COLUMN ename
 </txt>
</msg>
[oracle@norac ddl]$

- 0 -