星期三, 2月 29, 2012

Oracle 11g 預設開啟auditing 所以記得定期清空此sys.aud$ 表格 或 disable db auditing...

相關的參數:
AUDIT_TRAIL=DB
會把DML資料記錄到db 內部的一個表格 sys.aud$.

AUDIT_TRAIL=OS
把auditing 落地寫到DB 的本機空間, 設定參數AUDIT_TRAIL=OS, 須重啟DB。
此方法會將資料庫軌跡寫到$ORACLE_BASE/admin/adump 裡面,時間久了需注意OS空間使用度



在AUDIT_TRAIL=DB的情形下, 如果不定期truncate 掉, system tablespace 會一直長大, 之後要resize 就會有問題了...

以8kb db block size 為例 , 單一的datafile最大可以auto extend長到32GB...此時無法再extend 的話 db 就會hang 住...

http://arjudba.blogspot.com/2008/10/how-to-truncate-or-delete-rows-from.html

How to truncate or delete rows from audit trail table sys.aud$

1)Only appropriate privileged user can do delete operation on SYS.AUD$ table. The user must have either of the following privileges.
-SYS user.
-DELETE ANY TABLE system privilege. (If O7_DICTIONARY_ACCESSIBILITY=TRUE)
-A user to whom SYS has granted the object privilege DELETE on SYS.AUD$ table.

2)Before deleting any rows you may want to archive the table. You can achive this by creating a table from SYS.AUD$ and export that. Don't export SYS.AUD$ directly.
SQL>CREATE TABLE AUDIT_RECORD TABLESPACE users as select * from SYS.AUD$;
Now export the table as,
SQL> host exp tables=AUDIT_RECORD file=audit_record.dmp

3)To delete all records from audit trail table SYS.AUD$ issue,
SQL>DELETE FROM SYS.AUD$;

To delete all records of particular audited table from the audit trail issue,
SQL>DELETE FROM sys.aud$ WHERE obj$name='&table_nmae';

But deleting in this way will not reduce size on the system tablespace or aud$ table. In order to reduce size follow section 4.

4)Truncate audit table to reduce size.
SQL>CONN / as sysdba
SQL>TRUNCATE TABLE sys.aud$;


Truncate uses the DROP STORAGE clause but keeps only minextents extents, thus only 1 extent.
Related Documents
http://arjudba.blogspot.com/2008/10/issuing-of-moving-sysaud-to-another.html
http://arjudba.blogspot.com/2008/10/how-to-move-audit-table-out-of-system.html
http://arjudba.blogspot.com/2008/10/how-to-reorganize-audit-trail-sysaud.html
http://arjudba.blogspot.com/2008/05/about-audittrail-parameter.html
http://arjudba.blogspot.com/2008/04/configure-and-administer-database.html
http://arjudba.blogspot.com/2008/04/basics-of-database-auditing.html

update 2017/3/17
發現有別的方法可以搬移aud$ 到其他的tablespace , 此方法也適用於12c DB
ref1:
Moving the Database Audit Trail to a Different Tablespace
 https://oracle-base.com/articles/11g/auditing-enhancements-11gr2

ref2:
http://oracle-help.com/oracle-12c/oracle-12cr2/move-aud-table-another-tablespace-using-dbms_audit_mgmt/?lipi=urn%3Ali%3Apage%3Ad_flagship3_feed%3BSMvvrWj8Rxa5bKThzjrryg%3D%3D


CONN / AS SYSDBA

SELECT table_name, tablespace_name
FROM   dba_tables
WHERE  table_name IN ('AUD$', 'FGA_LOG$')
ORDER BY table_name;

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
AUD$                           SYSTEM
FGA_LOG$                       SYSTEM

SQL>

Next, create a new tablespace to hold the audit trail.

CREATE TABLESPACE audit_aux
  DATAFILE '/u01/app/oracle/oradata/DB11G/audit_aux01.dbf'
  SIZE 1M AUTOEXTEND ON NEXT 1M;

Then we move the standard audit trail to the new tablespace.

BEGIN
  DBMS_AUDIT_MGMT.set_audit_trail_location(
    audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
    audit_trail_location_value => 'AUDIT_AUX');
END;
/

PL/SQL procedure successfully completed.

SQL>

-- Check locations.
SELECT table_name, tablespace_name
FROM   dba_tables
WHERE  table_name IN ('AUD$', 'FGA_LOG$')
ORDER BY table_name;

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
AUD$                           AUDIT_AUX
FGA_LOG$                       SYSTEM

SQL>
Next we move the fine-grained audit trail.

BEGIN
  DBMS_AUDIT_MGMT.set_audit_trail_location(
    audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
    audit_trail_location_value => 'AUDIT_AUX');
END;
/

PL/SQL procedure successfully completed.

SQL>

-- Check locations.
SELECT table_name, tablespace_name
FROM   dba_tables
WHERE  table_name IN ('AUD$', 'FGA_LOG$')
ORDER BY table_name;

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
AUD$                           AUDIT_AUX
FGA_LOG$                       AUDIT_AUX

SQL>


沒有留言:

LinkWithin-相關文件

Related Posts Plugin for WordPress, Blogger...