Because audit records may grows up to undesired size. Today I will show you one of the easy way to care aud$ table.
Scenario:
- Create new tablespace
- Create new archive table
- Create procedure
- Create Scheduler or Cron Job
- Execute and check result
Firstly I will create new tablespace for my achived aud table.
SQL> create tablespace arch_tbs
datafile '/u01/app/oracle/oradata/ulfet_db/arch_tbs01.dbf'
size 500M;
Tablespace created.-- you can set more space
SQL> alter tablespace arch_tbs add datafile '/u01/app/oracle/oradata/ulfet_db/arch_tbs02.dbf' size 500M;
Tablespace altered.
Now I will create new table with aud$ table structure but new table will be range partitioned.
--You can create daily/monthly/yearly partition also subpartition too.
To get table structure I use dbms_metadata.get_ddl package`s procedure.
SQL> set pagesize 1000
SQL> select
to_char(dbms_metadata.get_ddl('TABLE', 'AUD$'))
from dual;
TO_CHAR(DBMS_METADATA.GET_DDL('TABLE','AUD$'))
--------------------------------------------------------------------------------
CREATE TABLE "SYS"."AUD$"
( "SESSIONID" NUMBER NOT NULL ENABLE,
"ENTRYID" NUMBER NOT NULL ENABLE,
"STATEMENT" NUMBER NOT NULL ENABLE,
"TIMESTAMP#" DATE,
"USERID" VARCHAR2(30),
"USERHOST" VARCHAR2(128),
"TERMINAL" VARCHAR2(255),
"ACTION#" NUMBER NOT NULL ENABLE,
"RETURNCODE" NUMBER NOT NULL ENABLE,
"OBJ$CREATOR" VARCHAR2(30),
"OBJ$NAME" VARCHAR2(128),
"AUTH$PRIVILEGES" VARCHAR2(16),
"AUTH$GRANTEE" VARCHAR2(30),
"NEW$OWNER" VARCHAR2(30),
"NEW$NAME" VARCHAR2(128),
"SES$ACTIONS" VARCHAR2(19),
"SES$TID" NUMBER,
"LOGOFF$LREAD" NUMBER,
"LOGOFF$PREAD" NUMBER,
"LOGOFF$LWRITE" NUMBER,
"LOGOFF$DEAD" NUMBER,
"LOGOFF$TIME" DATE,
"COMMENT$TEXT" VARCHAR2(4000),
"CLIENTID" VARCHAR2(64),
"SPARE1" VARCHAR2(255),
"SPARE2" NUMBER,
"OBJ$LABEL" RAW(255),
"SES$LABEL" RAW(255),
"PRIV$USED" NUMBER,
"SESSIONCPU" NUMBER,
"NTIMESTAMP#" TIMESTAMP (6),
"PROXY$SID" NUMBER,
"USER$GUID" VARCHAR2(32),
"INSTANCE#" NUMBER,
"PROCESS#" VARCHAR2(16),
"XID" RAW(8),
"AUDITID" VARCHAR2(64),
"SCN" NUMBER,
"DBID" NUMBER,
"SQLBIND" CLOB,
"SQLTEXT" CLOB,
"OBJ$EDITION" VARCHAR2(30)
) PCTFREE 10 PCTUSED 40 INITRANS 1
MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576
MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST
GROUPS 1 BUFFER_POOL DEFAULT
FLASH_CACHE DE FAULT
CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM"
LOB ("SQLBIND") STORE AS BASICFILE (
TABLESPACE "SYSTEM"
ENABLE STORAGE IN ROW CHUNK
8192 RETENTION NOCACHE LOGGING
STORAGE(INITIAL 65536 NEXT 1048576
MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT))
LOB ("SQLTEXT") STORE AS BASICFILE (
TABLESPACE "SYSTEM" ENABLE
STORAGE IN ROW CHUNK 8192 RETENTION
NOCACHE LOGGING
STORAGE(INITIAL 65536 NEXT 1048576
MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1
FREELIST GROUPS 1 BUFFER_POOL DEFAULT
FLASH_CACHE DE FAULT
CELL_FLASH_CACHE DEFAULT))
SQL> --a little bit change and creation script is ready
SQL> CREATE TABLE SYS.AUD$_ARCH
(
SESSIONID NUMBER NOT NULL ENABLE,
ENTRYID NUMBER NOT NULL ENABLE,
STATEMENT NUMBER NOT NULL ENABLE,
TIMESTAMP# DATE,
USERID VARCHAR2(30 BYTE),
USERHOST VARCHAR2(128 BYTE),
TERMINAL VARCHAR2(255 BYTE),
ACTION# NUMBER,
RETURNCODE NUMBER,
OBJ$CREATOR VARCHAR2(30 BYTE),
OBJ$NAME VARCHAR2(128 BYTE),
AUTH$PRIVILEGES VARCHAR2(16 BYTE),
AUTH$GRANTEE VARCHAR2(30 BYTE),
NEW$OWNER VARCHAR2(30 BYTE),
NEW$NAME VARCHAR2(128 BYTE),
SES$ACTIONS VARCHAR2(19 BYTE),
SES$TID NUMBER,
LOGOFF$LREAD NUMBER,
LOGOFF$PREAD NUMBER,
LOGOFF$LWRITE NUMBER,
LOGOFF$DEAD NUMBER,
LOGOFF$TIME DATE,
COMMENT$TEXT VARCHAR2(4000 BYTE),
CLIENTID VARCHAR2(64 BYTE),
SPARE1 VARCHAR2(255 BYTE),
SPARE2 NUMBER,
OBJ$LABEL RAW(255),
SES$LABEL RAW(255),
PRIV$USED NUMBER,
SESSIONCPU NUMBER,
NTIMESTAMP# TIMESTAMP(6),
PROXY$SID NUMBER,
USER$GUID VARCHAR2(32 BYTE),
INSTANCE# NUMBER,
PROCESS# VARCHAR2(16 BYTE),
XID RAW(8),
AUDITID VARCHAR2(64 BYTE),
SCN NUMBER,
DBID NUMBER,
SQLBIND CLOB,
SQLTEXT CLOB, OBJ$EDITION VARCHAR2(30) )
TABLESPACE ARCH_TBS
NOLOGGING
PARTITION BY RANGE (NTIMESTAMP#)
(
PARTITION P201211 VALUES
LESS THAN (TO_DATE('2012-12-01', 'YYYY-MM-DD')),
PARTITION P201212 VALUES
LESS THAN (TO_DATE('2013-01-01', 'YYYY-MM-DD')),
PARTITION P201301 VALUES
LESS THAN (TO_DATE('2013-02-01', 'YYYY-MM-DD')),
PARTITION P201302 VALUES
LESS THAN (TO_DATE('2013-03-01', 'YYYY-MM-DD')),
PARTITION P201303 VALUES
LESS THAN (TO_DATE('2013-04-01', 'YYYY-MM-DD')),
PARTITION P201304 VALUES
LESS THAN (TO_DATE('2013-05-01', 'YYYY-MM-DD'))
)
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
Table created.
SQL>
Now time to create procedure. Procedure will 1 input parameter (date).
CREATE OR REPLACE PROCEDURE SYS.purge_audit_trail2 (input_days IN NUMBER) AS
purge_date DATE;
i NUMBER := 0;
rcount NUMBER := 0;
BEGIN
purge_date := TRUNC (SYSDATE - input_days);
DBMS_SYSTEM.
ksdwrt (2,'AUDIT: Purging Audit Trail until '|| purge_date|| ' Start datetime : '|| TO_CHAR (SYSDATE, 'dd/mm/yyyy, HH24:MI:SS')); -- writing message to alert log
FOR c IN (SELECT ROWID AS rwid,
sessionid,
entryid,
STATEMENT,
timestamp#,
userid,
userhost,
terminal,
action#,
returncode,
obj$creator,
obj$name,
auth$privileges,
auth$grantee,
new$owner,
new$name,
ses$actions,
ses$tid,
logoff$lread,
logoff$pread,
logoff$lwrite,
logoff$dead,
logoff$time,
comment$text,
clientid,
spare1,
spare2,
obj$label,
ses$label,
priv$used,
sessioncpu,
ntimestamp#,
proxy$sid,
user$guid,
instance#,
process#,
xid,
auditid,
SCN,
dbid,
sqlbind,
sqltext, obj$edition
FROM SYS.aud$
WHERE ntimestamp# < purge_date)
LOOP
INSERT INTO SYS.AUD$_ARCH
VALUES (c.sessionid,
c.entryid,
c.STATEMENT,
c.timestamp#,
c.userid,
c.userhost,
c.terminal,
c.action#,
c.returncode,
c.obj$creator,
c.obj$name,
c.auth$privileges,
c.auth$grantee,
c.new$owner,
c.new$name,
c.ses$actions,
c.ses$tid,
c.logoff$lread,
c.logoff$pread,
c.logoff$lwrite,
c.logoff$dead,
c.logoff$time,
c.comment$text,
c.clientid,
c.spare1,
c.spare2,
c.obj$label,
c.ses$label,
c.priv$used,
c.sessioncpu,
c.ntimestamp#,
c.proxy$sid,
c.user$guid,
c.instance#,
c.process#,
c.xid,
c.auditid,
c.SCN,
c.dbid,
c.sqlbind,
c.sqltext, c.obj$edition); DELETE FROM SYS.aud$
WHERE ROWID = c.rwid;
i := i + 1;
IF MOD (i, 1000) = 0
THEN
rcount := rcount + i;
i := 0;
COMMIT;
END IF;
END LOOP;
rcount := rcount + i;
COMMIT;
DBMS_SYSTEM.
ksdwrt (3,'AUDIT: Purging Audit Trail until '|| purge_date|| ' has completed. Processed : '|| rcount|| ' rows. End datetime: '|| TO_CHAR (SYSDATE, 'dd/mm/yyyy, HH24:MI:SS')); -- writing message to alert log and trace file
END;
/
You can create scheduler and set:
SQL> BEGIN
sys.dbms_scheduler.create_job(
job_name => 'PURGE_AUDIT_TABLE',
job_type => 'PLSQL_BLOCK',
job_action => 'begin purge_audit_trail(10); end;',
schedule_name => 'MAINTENANCE_WINDOW_GROUP',
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'Audit Trail Purge',
auto_drop => FALSE,
enabled => TRUE
);
END;
/
PL/SQL procedure successfully completed.
SQL>
Check
SQL> select owner, job_name, job_name, program_owner,
program_name, enabled, last_start_date, next_run_date
from dba_scheduler_jobs where job_name like '%AUD%'
But I will create cron job and this script will work every day of months :
5,15,25 (3 times per months) at 01:00 a.m.
[oracle@localhost ~]$ crontab -l
0 01 5,15,25 * * /home/oracle/audit_purge_table.sh
Script is audit_purge_table.sh
[oracle@localhost ~]$ vi audit_purge_table.sh
#! /bin/sh
. /home/oracle/.bashrc
export ORACLE_SID=ulfetdb
sqlplus -S '/as sysdba' <<NEOF>>/home/audit_ptable.log
SET SERVEROUTPUT ON;
begin
SYS.purge_audit_trail2(3); --purge older 3 days
end;
/
exit;
NEOF
Now checking script`s output log:
[oracle@localhost ~]$ tail -10 audit_purge_table.log
PL/SQL procedure successfully completed.
Checking alert log:
[oracle@localhost ~]$ tail -5 /u01/app/oracle/diag/rdbms/ulfet_db/ulfetdb/trace/alert_ulfetdb.log
Fri Feb 01 01:11:30 2013
SMCO started with pid=29, OS id=17010
Fri Feb 01 01:20:01 2013
AUDIT: Purging Audit Trail until 31-01-2013 Start datetime : 01/02/2013, 01:20:01
AUDIT: Purging Audit Trail until 31-01-2013 Processed : 391 rows. End datetime: 01/02/2013, 01:20:02
[oracle@localhost ~]$
SQL> select count(1) from AUD$_ARCH partition(p201301);
COUNT(1)
----------
391
SQL>
Source: http://blog.abigold.fr/joomla/index.php?option=com_content&view=article&id=122:purge-audit-table-aud&catid=66:oracle-securite&Itemid=53
That`s all.
