CNDBA社区
1 enable_ddl_logging 参数说明
正常情况下，Oracle 的 log 日志中是不会记录 DDL 操作的，所以对于 DDL 操作的分析，只能通过审计平台来进行。
如果要跟踪 Oracle 的 DDL 操作记录，可以启动 DDL Logging 功能，配置ENABLE_DDL_LOGGING参数即可。 官网对该参数的说明如下：
https://docs.oracle.com/en/database/oracle/oracle-database/23/refrn/ENABLE_DDL_LOGGING.html
该参数默认为 False，支持在 PDB中修改。 启动改功能后，如下 DDL 会记录到 log 日志中：
ALTER/CREATE/DROP/TRUNCATE CLUSTER
ALTER/CREATE/DROP FUNCTION
GRANT
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
REVOKE
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
这里注意日志位置的不通：
- 在 Oracle 11g 中，DDL 也会记录到 alert log 中。
- 在 Oracle 12c 中，会有个单独的 ddl 文件，注意有 xml 和文本两种格式的。
2 操作示例
2.1 11g
[dave@www.cndba.cn ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Jan 15 21:03:07 2024
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
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 Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
SQL> alter system set enable_ddl_logging=true scope=both;
System altered.
SQL> create table cndba as select * from dba_users;
Table created.
SQL>
查看日志：
[dave@www.cndba.cn trace]$ pwd
/data/app/oracle/diag/rdbms/orcl/orcl/trace
[dave@www.cndba.cn trace]$ tail -20 alert_orcl.log
Private strand flush not complete
Current log# 3 seq# 51 mem# 0: /data/app/oracle/oradata/orcl/redo03.log
Thread 1 advanced to log sequence 52 (LGWR switch)
Current log# 1 seq# 52 mem# 0: /data/app/oracle/oradata/orcl/redo01.log
Mon Jan 15 09:06:39 2024
Thread 1 cannot allocate new log, sequence 53
Private strand flush not complete
Current log# 1 seq# 52 mem# 0: /data/app/oracle/oradata/orcl/redo01.log
Thread 1 advanced to log sequence 53 (LGWR switch)
Current log# 2 seq# 53 mem# 0: /data/app/oracle/oradata/orcl/redo02.log
Mon Jan 15 15:16:51 2024
Thread 1 cannot allocate new log, sequence 54
Private strand flush not complete
Current log# 2 seq# 53 mem# 0: /data/app/oracle/oradata/orcl/redo02.log
Thread 1 advanced to log sequence 54 (LGWR switch)
Current log# 3 seq# 54 mem# 0: /data/app/oracle/oradata/orcl/redo03.log
Mon Jan 15 21:03:44 2024
ALTER SYSTEM SET enable_ddl_logging=TRUE SCOPE=BOTH;
Mon Jan 15 21:04:00 2024
create table cndba as select * from dba_users
[dave@www.cndba.cn trace]$
2.2 19c
[dave@www.cndba.cn ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jan 15 21:05:21 2024
Version 19.21.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.21.0.0.0
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
BANNER_FULL
--------------------------------------------------------------------------------
BANNER_LEGACY
--------------------------------------------------------------------------------
CON_ID
----------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.21.0.0.0
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
0
BANNER
--------------------------------------------------------------------------------
BANNER_FULL
--------------------------------------------------------------------------------
BANNER_LEGACY
--------------------------------------------------------------------------------
CON_ID
----------
SQL> alter system set enable_ddl_logging=true scope=both;
System altered.
SQL> create table cndba as select * from dba_users;
Table created.
SQL>
查看日志：有两种格式：
[dave@www.cndba.cn cndba]$ pwd
/data/app19c/oracle/diag/rdbms/cndba/cndba
[dave@www.cndba.cn cndba]$ grep -nr "create table cndba as" ./
./log/ddl/log.xml:6: <txt> create table cndba as select * from dba_users
./log/ddl_cndba.log:2:diag_adl: create table cndba as select * from dba_users
[dave@www.cndba.cn cndba]$
[dave@www.cndba.cn log]$ cat ddl_cndba.log
2024-01-15T21:05:49.453536+08:00
diag_adl: create table cndba as select * from dba_users
[dave@www.cndba.cn log]$
[dave@www.cndba.cn ddl]$ pwd
/data/app19c/oracle/diag/rdbms/cndba/cndba/log/ddl
[dave@www.cndba.cn ddl]$ ll
total 4
-rw-r-----. 1 oracle19c oinstall 360 Jan 15 21:05 log.xml
[dave@www.cndba.cn ddl]$ cat log.xml
<msg time='2024-01-15T21:05:49.453+08:00' org_id='oracle' comp_id='rdbms'
msg_id='opiexe:5137:2946163730' type='UNKNOWN' group='diag_adl'
level='16' host_id='www.cndba.cn' host_addr='fe80::3ad5:47ff:fec8:d139%bond0'
pid='33016' version='2' con_uid='1'
con_id='1' con_name='CDB$ROOT'>
<txt> create table cndba as select * from dba_users
</txt>
</msg>
[dave@www.cndba.cn ddl]$
版权声明：本文为博主原创文章，未经博主允许不得转载。
