关于Oracle 11g 审计文件

关于审计：Oracle 11g默认启用的审计选项，AUDIT_TRAIL参数的缺省值为DB，表示审计数据将记录在数据库中的SYS.AUD$审计字典基表上。而在Oracle 10g中该参数默认值为none，即不启用审计。Oracle官方宣称默认启用的审计日志不会对绝大多数产品数据库的性能带来过大的负面影响，同时Oracle公司还推荐使用基于OS文件的审计日志记录方式（OS audit trail files）。因为在11g中CREATE SESSION将被作为受审计的权限来被记录，因此当SYSTEM表空间因磁盘空间而无法扩展时将导致这部分审计记录无法生成，最终导致普通用户的新会话将无法正常创建，普通用户将无法登陆数据库。在这种场景中仍可以使用SYSDBA身份的用户创建会话，在将审计数据合适备份后删除一部分记录，或者TRUNCATEAUD$都可以解决上述问题。当AUDIT_TRAIL设置为OS时，审计记录文件将在AUDIT_FILE_DEST参数所指定的目录中生成。全部这些文件均可以随时被删除或复制。以下权限将对所有用户审计:SQL> select privilege,success,failure fromdba_priv_audit_opts; PRIVILEGE SUCCESS FAILURE---------------------------------------- --------------------CREATE EXTERNAL JOB BY ACCESS BY ACCESSCREATE ANY JOB BY ACCESS BY ACCESSGRANT ANY OBJECT PRIVILEGE BY ACCESS BY ACCESSEXEMPT ACCESS POLICY BY ACCESS BY ACCESSCREATE ANY LIBRARY BY ACCESS BY ACCESSGRANT ANY PRIVILEGE BY ACCESS BY ACCESSDROP PROFILE BY ACCESS BY ACCESSALTER PROFILE BY ACCESS BY ACCESSDROP ANY PROCEDURE BY ACCESS BY ACCESSALTER ANY PROCEDURE BY ACCESS BY ACCESSCREATE ANY PROCEDURE BY ACCESS BY ACCESSALTER DATABASE BY ACCESS BY ACCESSGRANT ANY ROLE BY ACCESS BY ACCESSCREATE PUBLIC DATABASE LINK BY ACCESS BY ACCESSDROP ANY TABLE BY ACCESS BY ACCESSALTER ANY TABLE BY ACCESS BY ACCESSCREATE ANY TABLE BY ACCESS BY ACCESSDROP USER BY ACCESS BY ACCESSALTER USER BY ACCESS BY ACCESSCREATE USER BY ACCESS BY ACCESSCREATE SESSION BY ACCESS BY ACCESSAUDIT SYSTEM BY ACCESS BY ACCESSALTER SYSTEM BY ACCESS BY ACCESS 23 rows selected.当前数据库中的现有的审计记录，LOGNO/LOGOFF为审计create session产生的：SQL> select action_name,count（*） from dba_audit_trailgroup by action_name;ACTION_NAME COUNT（*）---------------------------- ----------LOGOFF BY CLEANUP 40LOGON 460LOGOFF 377ALTER USER 2SYSTEM GRANT 12ALTER SYSTEM 10CREATE PUBLIC SYNONYM 5ALTER DATABASE 2CREATE DATABASE LINK 1DROP PUBLIC SYNONYM 5 96.216 SYSTEM表空间过大：96.216中 LOGOFF/LOGOFF分别为2亿多条记录，使用得aud$表大小为80G。解决方法：1.当aud$表过大时，可以清除表中的审讯数据：SQL> truncate table sys.aud$;2.可以关闭对create session的审讯：SQL> noaudit create session;3.关闭数据库的审讯，需要重启数据库：SQL> alter system set audit_trail=none scope=spfile;更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址