Friday, February 1, 2013

Archive and purge aud$ table

If in your production database configured audit, DBA should maintain audit tables.
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.

Cannot access dba_directories inside procedure

 Recently I faced one of familiar Oracle error ORA -00942 : table or view does not exist   I got it in while compiling procedure, becaus...