LATEST TOPICS

Oracle 12c: DDL Logging.. will it serve the purpose?

Oracle had introduced a cool feature in version 11g, where we were able to log or track DDL statements executed in the database by means of a parameter called ENABLE_DDL_LOGGING without setting up database auditing. Setting ENABLE_DDL_LOGGING to TRUE results in logging DDL statements in to the instance alert log (s). Since the DDL statements are logged into alert log file, it becomes a hard task to scan through the alert log and find the DDL logs.

Oracle has made a significant change in terms of DDL logging with version 12c. In Oracle 12c, the DDL logs are maintained in dedicated DDL log file (s) unlike the instance alert log file which was the case with Oracle 11g. This makes it easier to track DDL statements executed in a database.

In 12c, Oracle maintains the DDL logs in two files (XML and plain text) under the ADR_HOME as listed below.

  • XML Version: $ADR_BASE/diag/rdbms/${DBNAME}/${ORACLE_SID}/log/ddl/log.xml
  • Text Version: $ADR_BASE/diag/rdbms/${DBNAME}/${ORACLE_SID}/log/ddl_${ORACLE_SID}.log

As per the Oracle documentation, setting ENABLE_DDL_LOGGING to TRUE will log following DDL statements executed in a database.

  • 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

There are some discrepancies while using ENABLE_DDL_LOGGING for tracking DDL statements particularly in the context of multi tenant architecture. In this post, I will go through a quick demonstration to explore how this feature works and what are the discrepancies associated with this feature.

Let’s start with our demonstration. By default, DDL logging is not enabled as we can find by querying the v$parameter view.

---//
---// DDL logging is disabled by default //---
---//
SQL> show con_name

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

SQL> select name,value,default_value from v$parameter where name='enable_ddl_logging';

NAME                      VALUE      DEFAULT_VALUE
------------------------- ---------- ---------------
enable_ddl_logging        FALSE      FALSE

Let’s enable DDL logging in our database by setting ENABLE_DDL_LOGGING to TRUE as shown below. My demonstration is targeted against a Oracle 12c (12.1.0.2) container database to understand how the feature works in a multi tenant environment.

---//
---// Enable DDL logging //---
---//
SQL> show con_name

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

SQL> alter system set enable_ddl_logging=TRUE;

System altered.

---//
---// Validate DDL logging is enabled in the database //---
---//

SQL> select name,value,default_value from v$parameter where name='enable_ddl_logging';

NAME                      VALUE      DEFAULT_VALUE
------------------------- ---------- ---------------
enable_ddl_logging        TRUE       FALSE

At this point, we have enabled DDL logging for our container database. However, there is no log file created yet as we haven’t performed any DDL after enabling the DDL logging. This can be confirmed by looking to the DDL log locations as shown below.

##---
##--- DDL log files are not created yet ---##
##---
[oracle@labserver1 ~]$ cd /app/oracle/diag/rdbms/orpcdb1/orpcdb1/log/ddl/
[oracle@labserver1 ddl]$ ls -lrt
total 0

Let’s perform few DDL statements on both container (CDB$ROOT) and pluggable (CDB1_PDB_1) databases and observe how these statements are logged by Oracle.

---// 
---// executing DDL in root(CDB$ROOT) container //---
---//
16:24:02 SQL> show con_name

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

16:24:16 SQL> create user c##test identified by c##test;

User created.

16:24:29 SQL> drop user c##test cascade;

User dropped.


---//
---// connecting to PDB CDB1_PDB_1 //---
---//
16:24:36 SQL> conn myapp@cdb1_pdb_1
Enter password:
Connected.

---//
---// executing DDL in pluggable database CDB1_PDB_1 //---
---//
16:24:49 SQL> show con_name

CON_NAME
------------------------------
CDB1_PDB_1

16:25:34 SQL> create table test as select * from all_users;

Table created.

16:25:48 SQL> create table test1 as select * from test;

Table created.

16:26:04 SQL> truncate table test1;

Table truncated.

16:26:13 SQL> drop table test purge;

Table dropped.

We have performed a number of DDL statements in both root (CDB$ROOT) and pluggable (CDB1_PDB_1) databases. We can now see the existence of respective DDL log files as shown below.

##---
##--- DDL logs are created after DDL execution ---##
##---
[oracle@labserver1 ddl]$ pwd
/app/oracle/diag/rdbms/orpcdb1/orpcdb1/log/ddl
[oracle@labserver1 ddl]$ ls -lrt
total 4
-rw-r----- 1 oracle dba 1650 May  8 16:26 log.xml

[oracle@labserver1 log]$ pwd
/app/oracle/diag/rdbms/orpcdb1/orpcdb1/log
[oracle@labserver1 log]$ ls -lrt ddl_${ORACLE_SID}.log
-rw-r----- 1 oracle dba 225 May  8 16:26 ddl_orpcdb1.log

Let’s see what is logged in the DDL log files for these DDL statements. First, let’s take a look into the text version of the logs

##---
##--- DDL logs from text version of log file ---##
##---
[oracle@labserver1 log]$ cat ddl_orpcdb1.log
diag_adl:drop user c##test cascade
Sun May 08 16:25:48 2016
diag_adl:create table test as select * from all_users
diag_adl:create table test1 as select * from test
diag_adl:truncate table test1
diag_adl:drop table test purge

We can see that all the DDL statements are logged (except the “create user” statement as that is not supported) into the log file. However, looks like the log file is just a sequence of DDL statements without any context of those statements. There are some vital information missing from this log file. If you observe it closely, you will find that TIMESTAMP information is missing for most of the statements. I was also expecting the container information to be associated with each of the logged DDL statement. However, that information is not there in the log file. Since, we are dealing with multi tenant database; the container information is very much required to be able to determine in which container a particularly DDL statement was executed. Without the container details, these DDL logging could not server any purpose.

Now let’s take a look into the XML version of the DDL log file to see if we can find any missing information there.

##---
##--- DDL logs from XML version of log file ---##
##---
[oracle@labserver1 ddl]$ cat log.xml

 drop user c##test cascade
 


 create table test as select * from all_users
 


 create table test1 as select * from test
 


 truncate table test1
 


 drop table test purge
 

The XML version of the log file looks to be more informative than the text version. We have now additional details available for each of the DDL statements executed like the TIMESTAMP of the DDL along with some session specific details like host_id and host_addr

However, the logs are still not sufficient for a multi tenant container database. The container information is still missing from the logs and we can’t rely on it to track on which container a particular DDL statement was executed

Footnote:

ENABLE_DDL_LOGGING feature can be considered for use in Oracle 11g or Oracle 12c non-CDB database. We must query the XML version of the log file for detailed information related to a DDL statement rather than querying the text version as the text version seems to miss the TIMESTAMP and session specific information for most DDL statements. Moreover, ENABLE_DDL_LOGGING feature seems not to be an ideal option for DDL tracking in a multi tenant container database considering the fact that it doesn’t log the container details of DDL statements and in turn serves no purpose for tracking.

The last thing to mention is that you need additional license to use the DDL logging feature. I would recommend to evaluate it thoroughly before actually implementing it, especially if you are dealing with container databases.

3 Comments
  1. Venkat
  2. venkatesh
%d bloggers like this:
Visit Us On LinkedinVisit Us On TwitterVisit Us On Google PlusCheck Our Feed