關於oracle11g的審計功能

dcswinner發表於2011-12-22
Oracle
現象：
今天上午發現外網資料庫有幾個sql特別的慢，經過分析這幾個sql是往aud$基表中插資料的sql，這個表現在有記錄數5千多萬，佔據了系統表空間約10g的空間，這樣會導致資料庫的效能下降（雖然oracle11g文件說效能影響可以忽略，但是通過我們的觀察，確實帶來效能下降明細），這樣下去，隨著審計記錄的增長，會有將系統表空間撐爆的可能性。
另外檢視了其他幾個11g的資料庫，都有這個現象，只是其他庫的這張基表的資料目前還沒有那麼大（幾十萬行記錄）。
 
原因分析：
在oracle11g中，資料庫的審計功能是預設開啟的（這和oracle10g的不一樣，10g預設是關閉的），oracle11gR2的官方文件上寫的是錯的，當上說default是none，而且是審計到DB級別的，這樣就會往aud$表裡記錄統計資訊。
 
問題處理方法：
1.如果審計不是必須的，可以關掉審計功能；
SQL> alter system set audit_trail=none scope=spfile;
SQL>showdown immediate;
SQL>startup
2.刪除已有的審計資訊
可以直接truncate表aud$,
或者採取dbms_audit_mgmt來清除。
3.或者將aud$表移到另外一個表空間下，以減少system表空間的壓力和被撐爆的風險。
 
附：11g中有關audit_trail引數的設定說明：

AUDIT_TRAIL

Property Description
Parameter type String
Syntax AUDIT_TRAIL = { none | os | db [, extended] | xml [, extended] }
Default value none
Modifiable No
Basic No

AUDIT_TRAIL enables or disables database auditing.

Values:

  • none

    Disables standard auditing. This value is the default if the AUDIT_TRAIL parameter was not set in the initialization parameter file or if you created the database using a method other than Database Configuration Assistant. If you created the database using Database Configuration Assistant, then the default is db.

  • os

    Directs all audit records to an operating system file. Oracle recommends that you use the os setting, particularly if you are using an ultra-secure database configuration.

  • db

    Directs audit records to the database audit trail (the SYS.AUD$ table), except for records that are always written to the operating system audit trail. Use this setting for a general database for manageability.

    If the database was started in read-only mode with AUDIT_TRAIL set to db, then Oracle Database internally sets AUDIT_TRAIL to os. Check the alert log for details.

  • db, extended

    Performs all actions of AUDIT_TRAIL=db, and also populates the SQL bind and SQL text CLOB-type columns of the SYS.AUD$ table, when available. These two columns are populated only when this parameter is specified.

    If the database was started in read-only mode with AUDIT_TRAIL set to db, extended, then Oracle Database internally sets AUDIT_TRAIL to os. Check the alert log for details.

  • xml

    Writes to the operating system audit record file in XML format. Records all elements of the AuditRecord node except Sql_Text and Sql_Bind to the operating system XML audit file.

  • xml, extended

    Performs all actions of AUDIT_TRAIL=xml, and populates the SQL bind and SQL text CLOB-type columns of the SYS.AUD$ table, wherever possible. These columns are populated only when this parameter is specified.

You can use the SQL AUDIT statement to set auditing options regardless of the setting of this parameter.

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12129601/viewspace-713788/，如需轉載，請註明出處，否則將追究法律責任。

相關文章