启用oracle ddl日志跟踪功能

郑全

从11g开始,就有了抓取ddl日志功能，只是有一些bug，12c做了增强，控制ddl日志功能的参数是 enable_ddl_logging，默认未开启

1.11g的启用ddl日志抓取

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for 64-bit Windows: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
SQL>

SQL> show parameter ddl_
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
ddl_lock_timeout                     integer     0
enable_ddl_logging                   boolean     FALSE

修改为 TRUE
SQL> alter system set enable_ddl_logging=true;
System altered.
SQL> create table abc (testid number);


11g ddl 日志就放在 alert_sid.log中，

cat D:\oracle\diag\rdbms\pridb\orcl\trace\alert_orcl.log

...
Mon Jul 29 12:57:58 2019
create table abc (testid number)

2. 12c ddl日志

-- 版本
SQL> select * from v$version;
BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production              0
PL/SQL Release 12.1.0.2.0 - Production                                                    0
CORE    12.1.0.2.0      Production                                                                0
TNS for Linux: Version 12.1.0.2.0 - Production                                            0
NLSRTL Version 12.1.0.2.0 - Production                                                    0
SQL>

--参数
SQL> show parameter ddl
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
ddl_lock_timeout                     integer     0
enable_ddl_logging                   boolean     FALSE

--修改参数为TRUE
SQL> alter system set enable_ddl_logging=true;
System altered.
SQL> show parameter ddl
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
ddl_lock_timeout                     integer     0
enable_ddl_logging                   boolean     TRUE

该参数是动态修改。

--ddl日志位置
在12c,ddl日志和alert日志分开放置，放在两个地方：
文本：
$ORACLE_BASE/diag/rdbms/DB_NAME/SID/log/ddl_sid.log

xml:

$ORACLE_BASE/diag/rdbms/DB_NAME/SID/log/ddl/log.xml

--创建一个用户

SQL> create table emp (empid number);

Table created.

--验证日志：

--log

[oracle@dbserver log]$ cat /u01/app/oracle/diag/rdbms/sztech/sztech/log/ddl_sztech.log
diag_adl:drop table emp
diag_adl:create table emp(empid number)
diag_adl:create table emp (empid number)
Mon Jul 29 01:00:20 2019

--xml

[oracle@dbserver ddl]$ strings /u01/app/oracle/diag/rdbms/sztech/sztech/log/ddl/log.xml
<msg time='2019-07-29T00:39:32.173-04:00' org_id='oracle' comp_id='rdbms'
msg_id='kpdbLogDDL:18370:2946163730' type='UNKNOWN' group='diag_adl'
level='16' host_id='dbserver' host_addr='192.168.133.120'
version='1'>
<txt>drop table emp
</txt>
</msg>
<msg time='2019-07-29T00:39:43.423-04:00' org_id='oracle' comp_id='rdbms'
msg_id='kpdbLogDDL:18370:2946163730' type='UNKNOWN' group='diag_adl'
level='16' host_id='dbserver' host_addr='192.168.133.120'>
<txt>create table emp(empid number)
</txt>
</msg>
<msg time='2019-07-29T00:47:32.226-04:00' org_id='oracle' comp_id='rdbms'
msg_id='opiexe:4383:2946163730' type='UNKNOWN' group='diag_adl'
level='16' host_id='dbserver' host_addr='192.168.133.120'>
<txt>create table emp (empid number)
</txt>
</msg>


注意，不是所有的ddl都能看到，比如create user 就看不到，下面罗列了可以抓取的ddl 类型：

• 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