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
<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
</msg> Here is the same tail for the traditional alert_<sid>.log: Tue Oct 16 15:29:58 2007drop table tNice one, eh?