Oracle 12c:New feature ------- Logging DDL data..
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@test1 ~]$ cd /app/oracle/diag/rdbms/OCDB1/OCDB1/log/ddl/
[oracle@test1 ddl]$ ls -lrt
total 0
Let’s perform few DDL statements on both container (CDB$ROOT) and pluggable (CPDB1) 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 utest identified by utest;
User created.
16:24:29 SQL> drop user utest cascade;
User dropped.
---//
---// connecting to PDB CPDB1 //---
---//
16:24:36 SQL> conn ktest@CPDB1
Enter password:
Connected.
---//
---// executing DDL in pluggable database CPDB1 //---
---//
16:24:49 SQL> show con_name
CON_NAME
------------------------------
CPDB1
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 (CPDB1) databases. We can now see the existence of respective DDL log files as shown below.
##---
##--- DDL logs are created after DDL execution ---##
##---
[oracle@test1 ddl]$ pwd
/app/oracle/diag/rdbms/OCDB1/OCDB1/log/ddl
[oracle@test1 ddl]$ ls -lrt
total 4
-rw-r----- 1 oracle dba 1650 May 8 16:26 log.xml
[oracle@test1 log]$ pwd
/app/oracle/diag/rdbms/OCDB1/OCDB1/log
[oracle@test1 log]$ ls -lrt ddl_${ORACLE_SID}.log
-rw-r----- 1 oracle dba 225 May 8 16:26 ddl_OCDB1.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@test1 log]$ cat ddl_OCDB1.log
diag_adl:drop user utest 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@test1 ddl]$ cat log.xml
<msg time="2016-05-08T16:24:36.772+05:30" org_id="oracle" comp_id="rdbms" msg_id="opiexe:4409:2946163730" type="UNKNOWN" group="diag_adl" level="16" host_id="test1.oraclebuffer.com" host_addr="192.168.230.15" version="1">
<txt>drop user utest cascade
</txt>
</msg>
<msg time="2016-05-08T16:25:48.587+05:30" org_id="oracle" comp_id="rdbms" msg_id="kpdbLogDDL:18891:2946163730" type="UNKNOWN" group="diag_adl" level="16" host_id="test1.oraclebuffer.com" host_addr="192.168.230.15">
<txt>create table test as select * from all_users
</txt>
</msg>
<msg time="2016-05-08T16:26:04.508+05:30" org_id="oracle" comp_id="rdbms" msg_id="kpdbLogDDL:18891:2946163730" type="UNKNOWN" group="diag_adl" level="16" host_id="test1.oraclebuffer.com" host_addr="192.168.230.15">
<txt>create table test1 as select * from test
</txt>
</msg>
<msg time="2016-05-08T16:26:13.874+05:30" org_id="oracle" comp_id="rdbms" msg_id="kpdbLogDDL:18891:2946163730" type="UNKNOWN" group="diag_adl" level="16" host_id="test1.oraclebuffer.com" host_addr="192.168.230.15">
<txt>truncate table test1
</txt>
</msg>
<msg time="2016-05-08T16:26:25.462+05:30" org_id="oracle" comp_id="rdbms" msg_id="kpdbLogDDL:18891:2946163730" type="UNKNOWN" group="diag_adl" level="16" host_id="test1.oraclebuffer.com" host_addr="192.168.230.15">
<txt>drop table test purge
</txt>
</msg>
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.
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@test1 ~]$ cd /app/oracle/diag/rdbms/OCDB1/OCDB1/log/ddl/
[oracle@test1 ddl]$ ls -lrt
total 0
Let’s perform few DDL statements on both container (CDB$ROOT) and pluggable (CPDB1) 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 utest identified by utest;
User created.
16:24:29 SQL> drop user utest cascade;
User dropped.
---//
---// connecting to PDB CPDB1 //---
---//
16:24:36 SQL> conn ktest@CPDB1
Enter password:
Connected.
---//
---// executing DDL in pluggable database CPDB1 //---
---//
16:24:49 SQL> show con_name
CON_NAME
------------------------------
CPDB1
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 (CPDB1) databases. We can now see the existence of respective DDL log files as shown below.
##---
##--- DDL logs are created after DDL execution ---##
##---
[oracle@test1 ddl]$ pwd
/app/oracle/diag/rdbms/OCDB1/OCDB1/log/ddl
[oracle@test1 ddl]$ ls -lrt
total 4
-rw-r----- 1 oracle dba 1650 May 8 16:26 log.xml
[oracle@test1 log]$ pwd
/app/oracle/diag/rdbms/OCDB1/OCDB1/log
[oracle@test1 log]$ ls -lrt ddl_${ORACLE_SID}.log
-rw-r----- 1 oracle dba 225 May 8 16:26 ddl_OCDB1.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@test1 log]$ cat ddl_OCDB1.log
diag_adl:drop user utest 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@test1 ddl]$ cat log.xml
<msg time="2016-05-08T16:24:36.772+05:30" org_id="oracle" comp_id="rdbms" msg_id="opiexe:4409:2946163730" type="UNKNOWN" group="diag_adl" level="16" host_id="test1.oraclebuffer.com" host_addr="192.168.230.15" version="1">
<txt>drop user utest cascade
</txt>
</msg>
<msg time="2016-05-08T16:25:48.587+05:30" org_id="oracle" comp_id="rdbms" msg_id="kpdbLogDDL:18891:2946163730" type="UNKNOWN" group="diag_adl" level="16" host_id="test1.oraclebuffer.com" host_addr="192.168.230.15">
<txt>create table test as select * from all_users
</txt>
</msg>
<msg time="2016-05-08T16:26:04.508+05:30" org_id="oracle" comp_id="rdbms" msg_id="kpdbLogDDL:18891:2946163730" type="UNKNOWN" group="diag_adl" level="16" host_id="test1.oraclebuffer.com" host_addr="192.168.230.15">
<txt>create table test1 as select * from test
</txt>
</msg>
<msg time="2016-05-08T16:26:13.874+05:30" org_id="oracle" comp_id="rdbms" msg_id="kpdbLogDDL:18891:2946163730" type="UNKNOWN" group="diag_adl" level="16" host_id="test1.oraclebuffer.com" host_addr="192.168.230.15">
<txt>truncate table test1
</txt>
</msg>
<msg time="2016-05-08T16:26:25.462+05:30" org_id="oracle" comp_id="rdbms" msg_id="kpdbLogDDL:18891:2946163730" type="UNKNOWN" group="diag_adl" level="16" host_id="test1.oraclebuffer.com" host_addr="192.168.230.15">
<txt>drop table test purge
</txt>
</msg>
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.
No comments:
Post a Comment