關於審計:
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 ACCESS
CREATE ANY JOB BY ACCESS BY ACCESS
GRANT ANY OBJECT PRIVILEGE BY ACCESS BY ACCESS
EXEMPT ACCESS POLICY BY ACCESS BY ACCESS
CREATE ANY LIBRARY BY ACCESS BY ACCESS
GRANT ANY PRIVILEGE BY ACCESS BY ACCESS
DROP PROFILE BY ACCESS BY ACCESS
ALTER PROFILE BY ACCESS BY ACCESS
DROP ANY PROCEDURE BY ACCESS BY ACCESS
ALTER ANY PROCEDURE BY ACCESS BY ACCESS
CREATE ANY PROCEDURE BY ACCESS BY ACCESS
ALTER DATABASE BY ACCESS BY ACCESS
GRANT ANY ROLE BY ACCESS BY ACCESS
CREATE PUBLIC DATABASE LINK BY ACCESS BY ACCESS
DROP ANY TABLE BY ACCESS BY ACCESS
ALTER ANY TABLE BY ACCESS BY ACCESS
CREATE ANY TABLE BY ACCESS BY ACCESS
DROP USER BY ACCESS BY ACCESS
ALTER USER BY ACCESS BY ACCESS
CREATE USER BY ACCESS BY ACCESS
CREATE SESSION BY ACCESS BY ACCESS
AUDIT SYSTEM BY ACCESS BY ACCESS
ALTER 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 40
LOGON 460
LOGOFF 377
ALTER USER 2
SYSTEM GRANT 12
ALTER SYSTEM 10
CREATE PUBLIC SYNONYM 5
ALTER DATABASE 2
CREATE DATABASE LINK 1
DROP 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;