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$