Hoy vamos a ver algo tan básico como es el mantenimiento de las tablas de auditoria. Los registros de auditoria de la base de datos es una de esas cosas que tienden a crecer indiscriminadamente llenándonos los tablespaces del sistema sin que nos demos cuenta. Hoy vamos a ver como limitar estos registros a los de los últimos 100 días (algo mas de 3 meses).

En las versiones anteriores (9i,10g ) el mantenimiento de las tablas de auditoria era un poco «por tu cuenta y riesgo», había documentación de como hacerlo pero decía que no era soportada ( por ejemplo la nota Note: 1019377.6 Script to move SYS.AUD$ table out of SYSTEM tablespace ).
Afortunadamente en la versión 11g Oracle ha creado el paquete DBMS_AUDIT_MGMT facilitándonos la labor.

Lo primero que tenemos que ver es donde se encuentran las tabas de auditoria, para ello usaremos la consulta

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

O con la llamada al paquete

column parameter_name format a30
column parameter_value format a20
SELECT * FROM dba_audit_mgmt_config_params;

PARAMETER_NAME            PARAMETER_VALUE      AUDIT_TRAIL
------------------------ ------------------ ----------------------
DB AUDIT TABLESPACE            TS_AUD         STANDARD AUDIT TRAIL
DB AUDIT TABLESPACE            TS_AUD         FGA AUDIT TRAIL
AUDIT FILE MAX SIZE            10000          OS AUDIT TRAIL
AUDIT FILE MAX SIZE            10000          XML AUDIT TRAIL
AUDIT FILE MAX AGE             5              OS AUDIT TRAIL
AUDIT FILE MAX AGE             5              XML AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE      10000          STANDARD AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE      10000          FGA AUDIT TRAIL
OS FILE CLEAN BATCH SIZE       1000           OS AUDIT TRAIL
OS FILE CLEAN BATCH SIZE       1000           XML AUDIT TRAIL

10 filas seleccionadas.

Por defecto, estas tablas estarán en el tablespace SYSTEM, es muy importante el mover estas tablas fuera de este tablespace si vamos a trabajar sobre ellas,ya uqe, sino Oracle usará el tablespace SYSAUX,algo que no queremos que pase bajo ningún concepto.
Lo primero que vamos a hacer es crear un tablespace para la auditoria, así que, vamos a ver el tamaño que necesitamos y a crear un tablespace con suficiente tamaño:

SQL> select sum(bytes)/1024/1024 Mb 
        from dba_segments 
          where segment_name in ('AUD$', 'FGA_LOG$');
-------
200,0625
SQL> create tablespace TS_AUD
   datafile '\oracle\oradata\instancia\ts_audit01.dbf' size 250M;

Una vez tengamos el tablespace creado, podemos usar la llamada DBMS_AUDIT_MGMT.set_audit_trail_location para mover las tablas actuales al nuevo tablespace

BEGIN
 DBMS_AUDIT_MGMT.set_audit_trail_location(
 audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
 audit_trail_location_value => 'TS_AUD');
END;
 
BEGIN
 DBMS_AUDIT_MGMT.set_audit_trail_location(
 audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
 audit_trail_location_value => 'TS_AUD');
END;

Con estos pasos, ya tenemos las tablas de auditoria en un tablespace especifico llamado TS_AUD, es en este momento cuando podemos comenzar con las tareas de mantenimiento de los registros de auditoría.

Como decíamos al principio, vamos ha configurar la base de datos para que se borren los registros anteriores a 100 días.

Lo primero que tendremos que hacer es inicializar el paquete con DBMS_AUDIT_MGMT.init_cleanup
En nuestro caso lo lanzaremos sobre AUDIT_TRAIL_AUD_STD Ya que lo que queremos limpiar son los registros de las tablas de auditoria de la base de datos, si quisiéramos limpiar otro (por ejemplo los del S.O seguiríamos la tabla Audit Trail Types)

BEGIN
 DBMS_AUDIT_MGMT.init_cleanup(
 audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
 default_cleanup_interval => 24 /* horas*/);
END;

Tras esto, con la propiedad SET_LAST_ARCHIVE_TIMESTAMP indicamos cual es la fecha del ultimo registro que queremos guardar.(en nuestro caso borraremos todo lo anterior a 100 días)

BEGIN
  DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
    audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
    last_archive_time => SYSTIMESTAMP-100 /*100 días*/);
END;

Antes de ejecutar el trabajo, vamos a ver cual es el registro más antiguo:

SQL> select min(ntimestamp#) from sys.aud$;
-------------------------------------------
17/10/13 00:00:50,119000

Ejecutamos el purgado con la llamada

BEGIN
  DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
   audit_trail_type=> DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
   use_last_arch_timestamp => TRUE);
END;

Tras ejecutar esto, el último registro debería de corresponderse con la fecha SYSDATE-100.
Si lo comprobamos tendremos que

SQL> select to_date(SYSTIMESTAMP-100) from dual 
--------
11/11/13
SQL> select min(ntimestamp#) from sys.aud$;
------------------------------------------
11/11/13 16:46:08,889000

Con lo que, efectivamente, habremos borrado todos los registros anteriores a 100 días.

A partir de ahora, podemos, o bien llevar a cabo los borrados de manera puntual con la llamada anterior, o planificarlos con en un job de purgado con la llamada

BEGIN
  DBMS_AUDIT_MGMT.CREATE_PURGE_JOB(
    audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
    audit_trail_purge_interval => 24 /* horas */,  
    audit_trail_purge_name => 'PURGADO_DE_TABLAS_AUDITORIA',
    use_last_arch_timestamp=> TRUE);
END;

Como siempre, podemos encontrar mas información en soporte Oracle en las notas :
Documentación del paquete DBMS_AUDIT_MGMT
Note 72460.1 Moving AUD$ to Another Tablespace and Adding Triggers to AUD$
Note: 1019377.6 Script to move SYS.AUD$ table out of SYSTEM tablespace
Note: 166301.1 How to Reorganize SYS.AUD$ Table
Note: 731908.1 New Feature DBMS_AUDIT_MGMT To Manage And Purge Audit Information
Note: 73408.1 How to Truncate, Delete, or Purge Rows from the Audit Trail Table SYS.AUD$