Enable DDL Logging

Enable DDL Logging

DDL logging is really very good feature to monitor changes done in DB design.
One can monitor or audit all DB changes by viewing log file.

One can set enable_ddl_logging=true to enable DDL logging.
Oracle will start recording all DDL statements in log.xml file. This file has the same format and basic behavior as the alert log, but it only contains the DDL statements.

The DDL log file is created in $ADR_HOME/log/ddl directory; it contains DDL statements that are extracted from alertl log file.
Log file Path
$ADR_BASE/diag/rdbms/{DB-name}/{SID}/log/ddl/log.xml

NOTE:- Oracle license "Oracle Change Management Pack" is require to use this feature.
Parameter enable_ddl_logging is licensed as part of the Change Management Pack.

Following DDL statements are written to the log:
•    ALTER/CREATE/DROP/TRUNCATE CLUSTER
•    ALTER/CREATE/DROP FUNCTION
•    ALTER/CREATE/DROP INDEX
•    ALTER/CREATE/DROP OUTLINE
•    ALTER/CREATE/DROP PACKAGE
•    ALTER/CREATE/DROP PACKAGE BODY
•    ALTER/CREATE/DROP PROCEDURE
•    ALTER/CREATE/DROP PROFILE
•    ALTER/CREATE/DROP SEQUENCE
•    CREATE/DROP SYNONYM
•    ALTER/CREATE/DROP/RENAME/TRUNCATE TABLE
•    ALTER/CREATE/DROP TRIGGER
•    ALTER/CREATE/DROP TYPE
•    ALTER/CREATE/DROP TYPE BODY
•    DROP USER
•    ALTER/CREATE/DROP VIEW


Following is the example of DDL logging,

-bash-4.1$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Jun 7 10:24:39 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>
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>
SQL> show parameter enable_ddl_logging;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
enable_ddl_logging                   boolean     TRUE

SQL> conn hr/hr
Connected.
SQL>
SQL> create table ddltest (id number, name varchar2(50));
Table created.

SQL>
SQL> alter table ddltest add ( address varchar2(100));
Table altered.

SQL>
SQL> insert into ddltest values (1,'CBT','XYZ');
1 row created.

SQL>
SQL> commit;
Commit complete.

SQL> drop table ddltest;
Table dropped.
SQL>

Check the log file for all DDL commands that was run by user.


-bash-4.1$ pwd
/mnt/Oracle/diag/rdbms/orclnew/orclnew/log/ddl


-bash-4.1$ cat log.xml

 msg_id='opiexe:4383:2946163730' type='UNKNOWN' group='diag_adl'
 level='16' host_id='OraLinuxNode' host_addr='10.184.150.107'
 version='1'>
 create table ddltest (id number, name varchar2(50))
 
 msg_id='opiexe:4383:2946163730' type='UNKNOWN' group='diag_adl'
 level='16' host_id='OraLinuxNode' host_addr='10.184.150.107'>
 alter table ddltest add ( address varchar2(100))
 
 msg_id='opiexe:4383:2946163730' type='UNKNOWN' group='diag_adl'
 level='16' host_id='OraLinuxNode' host_addr='10.184.150.107'>
 drop table ddltest
 
-bash-4.1$

If you want to see clear and readable DDL log file, Just invoke ADRCI utility and issue show log command as shown below,

-bash-4.1$ adrci
ADRCI: Release 12.1.0.2.0 - Production on Tue Jun 7 12:33:02 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

ADR base = "/mnt/Oracle"
adrci> show log -l ddl
ADR Home = /mnt/Oracle/diag/rdbms/orclnew/orclnew:
*************************************************************************
Output the results to file: /tmp/utsout_19321_13991_1.ado
adrci>


When you issue the command "show log", this will open the log.xml file in edtor (i.e. vi in linux/unix) and show the contents, in following format :

2016-06-07 12:23:22.395000 +05:30
create table ddltest (id number, name varchar2(50))
2016-06-07 12:24:05.301000 +05:30
alter table ddltest add ( address varchar2(100))
2016-06-07 12:25:14.003000 +05:30
drop table ddltest
2016-06-07 12:30:33.272000 +05:30
truncate table wri$_adv_addm_pdbs

Also you can see the debug log using adrci show log command i.e. show log -l debug.


For more information on adrci utility visit Oracle Doc
http://docs.oracle.com/cd/B28359_01/server.111/b28319/adrci.htm

Thanks & Regards,

Chandan Tanwani

Oracle Performance Tuning Certified Expert