My top 10 Oracle 11g New Features Part 5: DDL-logging in the alert log in 11g

This week I deliver for Oracle Belgium in Brussels.
It is an 11g New Features for Administrators course.
I have shown some nice features this afternoon
which are not really documented yet
and want to share some of it here.

As of Oracle database 11g we can have loggin information for DDL operations in the alert log files. I say files in plural because we have two different alert log files now in 11g:
one is the traditional alert_<sid>.log as we had it ever since,
the other one is an log.xml file which is located in the diagnostic directory in the alert folder. The xml file gives you a lot more information than the traditional alert log file.

The diagnostic directory is specified by the parameter DIAGNOSTIC_DEST  in 11g which depricates all the *_DUMP_DEST parameters from older releases. It defaults to $ORACLE_BASE/diag/

This feature can be very handy because it shows a logging entry for every DDL statement with a timestamp in the two alert log files. You will like it if you must find out when the wrong table was dropped or truncated ecactly in order to flash it back.

It is not enabled by default and you must change the parameter to TRUE first in 11gR1. 

Here is a little demo:

ALTER SYSTEM SET enable_ddl_logging=TRUE SCOPE=BOTH; SYS AS SYSDBA @ orcl SQL> show parameter ddlNAME                                 TYPE        VALUE
———————————— ———– ——————————
ddl_lock_timeout                     integer     0
enable_ddl_logging                   boolean     TRUE  

[oracle@edw2r8p0 ~]$ tail -f /u01/app/oracle/diag/rdbms/orcl/orcl/alert/log.xml

<txt>drop table hr.tt 
</txt></msg>
<msg time=’2007-10-16T15:29:58.343+02:00′ org_id=’oracle’ comp_id=’rdbms’ msg_id=’opiexe:3933:4222364190′ client_id=” type=’NOTIFICATION’ group=‘schema_ddl’ level=’16′ module=’SQL*Plus’ pid=’27158′> <txt>drop table t
 </txt>
</msg> Here is the same tail for the traditional alert_<sid>.log: Tue Oct 16 15:29:58 2007drop table tNice one, eh?
=;-) 

About these ads