oracle 审计drop单张表,如何在无审计的环境中追踪Truncate/Drop等危险的DDL操作

在有充分审计Audit   SQL的情况下，定位某条DROp/Truncate DDl还是比较容易的。

问题是 没有任何SQL审计时呢？

首先需要 明确的是 对于 关键的产品数据库系统而言 有效的审计非常重要，不能将以下的方法当做审计选项来用。

其次对于重要的环境和重要的对象， 一般推荐创建一个DDL Trigger 让直接运行的DDL报错，而需要先将对应的DDL Disable之后才能成功 执行DDL， 这样可以降低表/索引因为人为失误导致的误操作。

最简单的仍是通过 dba_objects 定位其最后的DDL时间，虽然这个LAST_DDL_TIME 未必是真实的案发时间了， 但如何与应用程序报错结合起来 还是能大致了解问题发生的时间段的， 而这个时间段 对于问题追查至关重要。 此外对于DROP命令而言显然弄不到这个LAST_DDL_TIME。

接着可以通过ASH 视图 DBA_HIST_ACTIVE_SESS_HISTORY和 V$ACTIVE_SESSION_HISTORY来定位一些DDL语句， 由于ASH默认是1秒采样一次，所以如果遇到了一些例如RAC 中truncate/drop 常见的 DFS Lock Handle、Enqueue Lock等等待，那么一般ASH都能捕捉到这个DDL，当然这也看运气，毕竟ASH不是审计功能。

SQL_OPCODE    12 为DROP TABLE  10为 DROP INDEX、85为TRUNCATE TABLE、86为TRUNCATE CLUSTER

select USER_ID, SQL_OPCODE, XID, SAMPLE_TIME,MODULE,MACHINE

from v$active_session_history

where SQL_OPCODE in (12, 10, 85, 86)

and SAMPLE_TIME between xx and xx;

select USER_ID, SQL_OPCODE, XID, SAMPLE_TIME,MODULE,MACHINE

from dba_hist_active_sess_history

where SQL_OPCODE in (12, 10, 85, 86)

and SAMPLE_TIME between xx and xx;

如果上述查询给出少量精准结果(例如MACHINE和MODULE很特殊)，那么一般就很容易定位了。  如果查出大量结果，一般优先排除应用程序模块例如 JDBC Thin，如果应用程序本身有BUG导致莫名的DDL，那么理论上应当经常发生。 如果看到一些例如SQLPLUS、PL/SQL Developer认为登陆执行上述命令的记录，则需要特别关注。

由于DDL语句不作为 共享SQL保存在V$SQL、V$SQLAREA中所以 就算你获得了SQL_ID还是看不到这些SQL语句的，所以无法通过SQL_TEXT来定位这些SQL到底是什么样子。

这个时候往往需要做Logminer了， 但好在我们有大致的Sample  Time和XID 这样定位SQL就很简单。

EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => ‘/oracle/logs/log1.f’, OPTIONS => DBMS_LOGMNR.NEW);

EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => ‘/oracle/logs/log2.f’, OPTIONS => DBMS_LOGMNR.ADDFILE);

execute DBMS_LOGMNR.START_LOGMNR(DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG+DBMS_LOGMNR.COMMITTED_DATA_ONLY);

SELECT (XIDUSN || ‘.’ || XIDSLT || ‘.’ || XIDSQN) AS XID,

USERNAME, SQL_REDO FROM V$LOGMNR_CONTENTS WHERE XID=’XXX’;

EXECUTE DBMS_LOGMNR.END_LOGMNR;

此外在没有审计的情况下 值得参考的数据还有 Listener监听器的日志、OS登陆的Shell日志等。