Dear Readers,
In this article, we will see the following DDL Logging in Oracle 12c.
What is DDL Logging ?
Concerning the Data Definition Language log, or DDL log, in previous releases, there wasn’t any direct option available to log the DDL command/action. Like an example we can says that if any user perform any DDL operation then it will not log by default in oracle database.
Though, In oracle database 11g added some support for DDL logging of RDBMS DDL statements into the alert log. If we turned on DDL logging in oracle database 12c then RDBMS DDL logging will write to a new ADR file type that has the same format and basics behavior as the alert log, but it contains only DDL statements and Timestamp.
How to enable?
Lets’ look at how to enable this 12c new feature as part of enhancement for monitoring 12c information.
Check the DDL is Enabled or Disabled.
|
1 2 3 4 |
SQL> show parameter ENABLE_DDL_LOGGING NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ enable_ddl_logging boolean FALSE |
Now enable by using following statement.
|
1 2 |
SQL> ALTER SYSTEM SET ENABLE_DDL_LOGGING=TRUE; System altered. |
|
1 2 3 4 |
SQL> show parameter ENABLE_DDL_LOGGING NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ enable_ddl_logging boolean TRUE |
Where all log will go?
DDL log will be logged in two different places in two different formats
XML format :
1.$ORACLE_BASE/diag/rdbms/{DB_NAME}/{SID}/log/ddl/log.xml
Or
$ADR_HOME/log/ddl/log.xml
Text format :
2.$ORACLE_BASE/diag/rdbms/{DB_NAME}/{SID}/log/ddl_{instance}.log
Or
$ADR_HOME/log/ddl_{instance}.log
Note :
Incase if you are using Container database then all pluggable database DDL will goes into the same log file.
Let’s execute some DDL statements to check the log files.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SQL> create table ktexperts (c number); Table created. SQL> create table ktexperts1 (domain varchar2(15)); Table created. SQL> create user c##ktexperts identified by Oracle_4U container=all; User created. SQL> drop table ktexperts; Table dropped. SQL> drop table ktexperts1; Table dropped. SQL> drop user c##ktexperts cascade; User dropped. |
We have created two table(ktexperts,ktexperts1) and one common user(c##ktexperts) and dropped.
These DDL lines will be in logs .
Will check
|
1 2 3 4 5 6 7 8 9 |
[ora12c@CentOS7 ~]$ pwd /home/ora12c [ora12c@CentOS7 ~]$ cd $ADR_BASE [ora12c@CentOS7 ~]$ pwd /home/ora12c [ora12c@CentOS7 ~]$ cd /u01/app/ora12c/diag/rdbms/orcl/orcl/log/ddl [ora12c@CentOS7 ddl]$ ll total 4 -rw-r-----. 1 ora12c oinstall 1805 Jan 22 14:51 log.xml |
more log.xml
Or
Else you can check in ddl_orcl.log
|
1 2 3 4 5 6 7 8 9 10 |
[ora12c@CentOS7 log]$ cd /u01/app/ora12c/diag/rdbms/orcl/orcl/log [ora12c@CentOS7 log]$ pwd /u01/app/ora12c/diag/rdbms/orcl/orcl/log [ora12c@CentOS7 log]$ ll total 20 drwxr-x---. 2 ora12c oinstall 4096 Jan 22 15:01 ddl -rw-r-----. 1 ora12c oinstall 359 Jan 22 15:30 ddl_orcl.log drwxr-x---. 2 ora12c oinstall 4096 Jan 18 16:49 debug drwxr-x---. 2 ora12c oinstall 4096 Jan 18 16:49 imdb drwxr-x---. 2 ora12c oinstall 4096 Jan 18 16:49 test |
more ddl_orcl.log
|
1 2 3 4 5 6 7 |
[ora12c@CentOS7 log]$ more ddl_orcl.log Tue Jan 22 14:43:52 2019 diag_adl:create table ktexperts (c number) diag_adl:create table ktexperts1 (domain varchar2(15)) diag_adl:drop table ktexperts diag_adl:drop table ktexperts1 diag_adl:drop user c##ktexperts cascade |
How to disable?
check status
|
1 2 3 4 |
SQL> show parameter ENABLE_DDL_LOGGING NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ enable_ddl_logging boolean TRUE |
To Disable follow below command
|
1 2 |
SQL> ALTER SYSTEM SET ENABLE_DDL_LOGGING=FALSE; System altered. |
|
1 2 3 4 |
SQL> show parameter ENABLE_DDL_LOGGING NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ enable_ddl_logging boolean FALSE |
Licensing
The use of init.ora parameters ENABLE_DDL_LOGGING is licensed under Oracle Change Management Pack when set to TRUE. By default parameter value if FALSE.
Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates
KTEXPERTS is always active on below social media platforms.
Facebook : https://www.facebook.com/ktexperts/
LinkedIn : https://www.linkedin.com/company/ktexperts/
Twitter : https://twitter.com/ktexpertsadmin
YouTube : https://www.youtube.com/c/ktexperts
Instagram : https://www.instagram.com/knowledgesharingplatform
Loading...
