Reference documentation from Oracle.
https://docs.oracle.com/database/121/REFRN/refrn10302.htm#REFRN10302
I am going to fire up 2 terminals with 2 sessions. One will be setting the parameter and testing them and another terminal will be monitoring the Oracle alert.log.
I am running Oracle 12.1.0.1.0.
Terminal#1
The default is FALSE.
SQL> show parameter enable_ddl_logging
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
enable_ddl_logging boolean FALSE
SQL> ALTER SYSTEM SET enable_ddl_logging=TRUE SCOPE=BOTH;
System altered.
SQL> show parameter enable_ddl_logging;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
enable_ddl_logging boolean TRUE
SQL> alter index IX_RES_POOL_INV_IS_DELETED rebuild online;
Index altered.
SQL> CREATE TABLE TEST (
2 NAME VARCHAR2(3)
3 );
Table created.
SQL> alter index IX_RES_POOL_INV_IS_DELETED rebuild online;
Index altered.
Terminal#2
On another terminal tail -f the alert.log. The alter index did not get logged into the Oracle alert.log but enabling the ddl_logging got logged instead.Tue Dec 30 12:47:12 2014
ALTER SYSTEM SET enable_ddl_logging=TRUE SCOPE=BOTH;
Note: strange that it does not logged the alter index statement. However, perform a shutdown .. now it logs the message in Oracle alert.log. Again, the "alter index" did not get logged.
diag_adl:ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE
After reading the Oracle documentation, it states that ddl may get logs into a separate logs under ../log directory.
Under the log directory
/u01/app/oracle/diag/rdbms/orcl/orcl/log
There you go .. the DDLs did get logged into this location.
[oracle@oracle12c log]$ pwd;ls -lrt;cat ddl_orcl.log
/u01/app/oracle/diag/rdbms/orcl/orcl/log
total 244
drwxr-x--- 2 oracle oracle 4096 Dec 30 12:48 ddl
-rw-r----- 1 oracle oracle 291 Dec 30 13:12 ddl_orcl.log
diag_adl:alter index IX_RES_POOL_INV_IS_DELETED rebuild online
diag_adl:alter database open
Tue Dec 30 12:59:53 2014
diag_adl:alter index IX_RES_POOL_INV_IS_DELETED rebuild online
diag_adl:CREATE TABLE TEST (
NAME VARCHAR2(3)
)
diag_adl:alter index IX_RES_POOL_INV_IS_DELETED rebuild online
Conclusion for the ENABLE_DDL_LOGGING are, system level DDLs will be logged in the Oracle alert.log such as "shutdown immediate" and "startup". Database DDLs will be logged on a separate directory under ../log in ddl_orcl.log. User will need to look at different places depending on what type of DDLs.
Majority of it look acceptable to me so far. This left another puzzle I am not able to figure out yet. I am not finding where the DDLs of "alter session set nls_date_format" goes. This alter session did not get logged anywhere. May be this is not part of DDL statement to be logged but why did Shutdown and Startup get logged ?
No comments:
Post a Comment