Tuesday, August 5, 2014

1z0-060 Upgrade to 12c: ENABLE_DDL_LOGGING changing behaviour in 12c

ENABLE_DDL_LOGGING enables or disables the writing of a subset of data definition language (DDL) statements to a DDL alert log.

The DDL log is a file that has the same format and basic behavior as the alert log, but it only contains the DDL statements issued by the database. The DDL log is created only for the RDBMS component and only if the ENABLE_DDL_LOGGING initialization parameter is set to true. When this parameter is set to false, DDL statements are not included in any log.

The DDL log contains one log record for each DDL statement issued by the database. The DDL log is included in IPS incident packages.

There are two DDL logs that contain the same information. One is an XML file, and the other is a text file. The DDL log is stored in the log/ddl subdirectory of the ADR home.

SQL> alter system set enable_ddl_logging=true;

System altered.

SQL> create table t1 (id number);
create table t1 (id number)
             *
ERROR at line 1:
ORA-00955: name is already used by an existing object


SQL> drop table t1;

Table dropped.

SQL> create table t1 (id number);

Table created.


oracle@solaris:/u01/app/oracle/diag/rdbms/orcl/orcl/log$ cat ddl_orcl.log
diag_adl:drop table t1
diag_adl:create table t1 (id number)


oracle@solaris:/u01/app/oracle/diag/rdbms/orcl/orcl/log$ cat ddl/log.xml
<msg time='2014-08-05T21:20:08.030+08:00' org_id='oracle' comp_id='rdbms'
msg_id='opiexe:4383:2946163730' type='UNKNOWN' group='diag_adl'
level='16' host_id='solaris' host_addr='::1'
version='1'>
<txt>drop table t1
</txt>
</msg>
<msg time='2014-08-05T21:20:09.390+08:00' org_id='oracle' comp_id='rdbms'
msg_id='opiexe:4383:2946163730' type='UNKNOWN' group='diag_adl'
level='16' host_id='solaris' host_addr='::1'>
<txt>create table t1 (id number)
</txt>
</msg>