If you enable auditing then the auditing
information, (stored in table SYS.AUD$) will start growing and because is
relies on SYSTEM tablespace you might have performance problems in the future.
Auditing housekeeping must be setup.
Here is a practical guide, from a 5TByte E-Business Suite, with 50MB auditing produced every day.
Here is a practical guide, from a 5TByte E-Business Suite, with 50MB auditing produced every day.
1. Create a SYSTEM.AUD$_BU table stored in a different tablespace(AUDIT_DATA) where you will move all you auditing produced
CREATE TABLESPACE
AUDIT_DATA DATAFILE
'/filesystem034/oradata/SID/audit_data_001.dbf'
SIZE 10000M AUTOEXTEND OFF
LOGGING
PERMANENT
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 1M
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT
MANUAL;
CREATE TABLE
SYSTEM.AUD$_BU TABLESPACE AUDIT_DATA
AS SELECT * FROM
SYS.AUD$
WHERE 1=2;
2. Create a procedure(Keep_Size_Aud_Log) that
moves the rows from SYS.AUD$ to SYSTEM.AUD$_BU
CREATE OR REPLACE
PROCEDURE Keep_Size_Aud_Log
IS
rowCount NUMBER;
BEGIN
SELECT COUNT(*) INTO rowCount FROM sys.aud$ ;
IF rowCount > 0
THEN
COMMIT;
INSERT /*+ APPEND */ INTO SYSTEM.aud$_bu
(SELECT * FROM sys.aud$);
COMMIT;
EXECUTE IMMEDIATE 'truncate table
sys.aud$';
sys.Dbms_System.ksdwrt (3,'ORA-AUDIT TRAIL:
rows moved from SYS.AUD$ to SYSTEM.AUD$_BU');
END IF;
END Keep_Size_Aud_Log;
/
3. Execute the procedure every day at midnight
with a job
DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
(
job
=> X
,what
=> 'SYS.KEEP_SIZE_AUD_LOG;'
,next_date
=> TO_DATE('27/02/2008 12:35:21','dd/mm/yyyy hh24:mi:ss')
,INTERVAL
=> 'TRUNC(SYSDATE+1)'
,no_parse
=> FALSE
);
END;
Tip: To speed
up searching on SYSTEM.AUD$_BU you can create 2 indexes (one on timestamp# and
the other to userid)
CREATE INDEX
SYSTEM.AUD$_BU_TIME_IDX ON SYSTEM.AUD$_BU (TIMESTAMP#)
NOLOGGING TABLESPACE
AUDIT_DATA;
CREATE INDEX
SYSTEM.AUD$_BU_USERID_IDX ON SYSTEM.AUD$_BU (USERID)
NOLOGGING TABLESPACE
AUDIT_DATA;
It will be good audit archive`s table partition
ReplyDelete