Gerenciando Trilha de Auditoria Padrão e FGA no Oracle Database 11g

Caros amigos, hoje vou compartilhar algo com vocês que eu criei e me ajudou muito em gerenciar o espaço ocupado pelo Audit Trail (trilha de auditoria) do banco de dados Oracle 11g quando usamos Auditoria Padrão ou FGA (Fine Grained Auditing). Por padrão o banco de dados Oracle 11g configura auditoria de algumas ações como por exemplo: logon, logoff, uso de privilégios "ANY", etc. gerando dados em tabelas como a SYS.AUD$ (para a auditoria padrão) e a SYS.FGA_LOG$ (para a FGA).
Com o tempo, a quantidade gerada de dados nestas tabelas pode fazer com que o tablespace SYSTEM cresça bastante e por isso é interessante separar os dados de auditoria em um tablespace diferente.
Se você tiver adquirido o recurso Oracle Audit Vault esta trilha de auditoria é gerenciada automaticamente, ou seja, é copiada para o banco de dados do Audit Vault e removida do banco de dados de origem. Além disso, o Audit Vault tem outros recursos de relatórios, envio de alertas e uma interface mais amigável para consulta de auditoria.

Tablespaces são unidades lógicas de armazenamento que o banco de dados Oracle utiliza para gravar seus dados. Nos tablespaces existem segmentos que são tabelas ou índices por exemplo. O que seria interessante criar é um tablespace novo para gravação destes dados de auditoria.

Criei um script que faz tudo isso de forma automática. Além de configurar a auditoria recomendada ele também move as tabelas de auditoria para fora do tablespace SYSTEM (de sistema, onde ficam outras tabelas internas do Oracle) e, mensalmente, copia dados de auditoria para uma outra tabela para consultas históricas deixando sempre a tabela de auditoria com poucos dados. No exemplo, uma tabela é criada no mesmo banco de dados, porém você pode adaptá-lo para armazenar a tabela em outro banco de dados.

Abaixo o script com a explicação sobre cada parte faz. Executar com usuário "SYS":

-- 1. Configurando auditoria (precisa de restart). Gerencie arquivos de auditoria gerados para o usuário SYS em audit_file_dest.

 AUDIT ALL ON SYS.AUD$ BY ACCESS;
AUDIT ALL ON SYS.FGA_LOG$ BY ACCESS;
ALTER SYSTEM SET AUDIT_SYS_OPERATIONS=TRUE SCOPE=SPFILE SID='*';
ALTER SYSTEM SET AUDIT_TRAIL='DB_EXTENDED' SCOPE=SPFILE SID='*';

 -- 2. Checando espaço necessário para criar tablespace TS_AUDIT_TRAIL de auditoria e conta AUDIT_TRAIL (precisa usar OMF, do contrário especifique o caminho manualmente). A conta AUDIT_TRAIL foi bloqueada propositalmente, pois não precisaremos ativá-la.

 DECLARE V_BYTES NUMBER;
BEGIN
SELECT SUM(BYTES)*2 INTO V_BYTES 
  FROM DBA_SEGMENTS
 WHERE    SEGMENT_NAME = 'AUD$'
       OR SEGMENT_NAME = 'FGA_LOG$'
       OR SEGMENT_NAME IN
             (SELECT SEGMENT_NAME
                FROM DBA_LOBS
               WHERE TABLE_NAME = 'AUD$' OR TABLE_NAME = 'FGA_LOG$');
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('* Um tablespace TS_AUDIT_TRAIL com '||ROUND(V_BYTES/1024/1024)||' MBs vai ser criado. *');
DBMS_OUTPUT.PUT_LINE(CHR(10));
END;
/

 DECLARE V_BYTES NUMBER;
BEGIN
SELECT SUM(BYTES)*2 INTO V_BYTES 
  FROM DBA_SEGMENTS
 WHERE    SEGMENT_NAME = 'AUD$'
       OR SEGMENT_NAME = 'FGA_LOG$'
       OR SEGMENT_NAME IN
             (SELECT SEGMENT_NAME
                FROM DBA_LOBS
               WHERE TABLE_NAME = 'AUD$' OR TABLE_NAME = 'FGA_LOG$');
EXECUTE IMMEDIATE 'CREATE TABLESPACE TS_AUDIT_TRAIL DATAFILE SIZE '||V_BYTES||' AUTOEXTEND ON NEXT 100M SEGMENT SPACE MANAGEMENT AUTO';
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('* Tablespace com '||ROUND(V_BYTES/1024/1024)||' MBs criado com sucesso.');
END;
/

 CREATE USER AUDIT_TRAIL IDENTIFIED BY aud$user$oracle
DEFAULT TABLESPACE TS_AUDIT_TRAIL
QUOTA UNLIMITED ON TS_AUDIT_TRAIL
ACCOUNT LOCK;

 -- 3. Criando tabelas de histórico para arquivamento de dados de auditoria (crie índices posteriormente se desejar). A coluna "PLHOL" está sendo alterada de LONG para CLOB para permitir a cópia. Não haverá impacto durante a auditoria, pois esta coluna não é utilizada e provavelmente será removida no futuro.

 ALTER TABLE SYS.FGA_LOG$ MODIFY(PLHOL CLOB);
CREATE TABLE AUDIT_TRAIL.AUDIT_ARCHIVE COMPRESS BASIC TABLESPACE TS_AUDIT_TRAIL AS SELECT * FROM SYS.AUD$;
CREATE TABLE AUDIT_TRAIL.AUDIT_ARCHIVE_FGA COMPRESS BASIC TABLESPACE TS_AUDIT_TRAIL AS SELECT * FROM SYS.FGA_LOG$;
DELETE FROM SYS.AUD$ WHERE NTIMESTAMP#<=(SELECT MAX(NTIMESTAMP#) FROM AUDIT_TRAIL.AUDIT_ARCHIVE);
DELETE FROM SYS.FGA_LOG$ WHERE NTIMESTAMP#<=(SELECT MAX(NTIMESTAMP#) FROM AUDIT_TRAIL.AUDIT_ARCHIVE_FGA);
COMMIT;

 -- 4. Movendo auditoria para novo tablespace TS_AUDIT_TRAIL.

 BEGIN
 SYS.DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
  AUDIT_TRAIL_TYPE            => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,
  AUDIT_TRAIL_LOCATION_VALUE  => 'TS_AUDIT_TRAIL');
END;
/

 -- 5. Realizando shrink da tabela AUD$ e FGA_LOG$ com coleta de estatisticas.

 ALTER TABLE SYS.AUD$ ENABLE ROW MOVEMENT;
ALTER TABLE SYS.AUD$ SHRINK SPACE CASCADE;
ALTER TABLE SYS.FGA_LOG$ ENABLE ROW MOVEMENT;
ALTER TABLE SYS.FGA_LOG$ SHRINK SPACE CASCADE;
EXEC DBMS_STATS.GATHER_TABLE_STATS('SYS','AUD$');
EXEC DBMS_STATS.GATHER_TABLE_STATS('SYS','FGA_LOG$');

 -------------------------------------------------------------------------------------
--Mantendo o audit trail. Configurar agendamento.
-------------------------------------------------------------------------------------

 -- 6. Configurando Scheduler Job para executar manutenção de auditoria dia 01 de cada mês. Você pode alterar a frequência conforme a sua necessidade.

 BEGIN
  SYS.DBMS_SCHEDULER.CREATE_JOB
    (
       job_name        => 'SYS.AUDIT_TRAIL_MAINTENANCE'
      ,start_date      => NULL
      ,repeat_interval => 'FREQ=MONTHLY;BYMONTHDAY=1;BYHOUR=00;BYMINUTE=00;BYSECOND=00'
      ,end_date        => NULL
      ,job_class       => 'DEFAULT_JOB_CLASS'
      ,job_type        => 'PLSQL_BLOCK'
      ,job_action      => 'BEGIN
    INSERT INTO AUDIT_TRAIL.AUDIT_ARCHIVE SELECT * FROM SYS.AUD$;
    INSERT INTO AUDIT_TRAIL.AUDIT_ARCHIVE_FGA SELECT * FROM SYS.FGA_LOG$;
    DELETE FROM SYS.AUD$ WHERE NTIMESTAMP#<=(SELECT MAX(NTIMESTAMP#) FROM AUDIT_TRAIL.AUDIT_ARCHIVE);
    DELETE FROM SYS.FGA_LOG$ WHERE NTIMESTAMP#<=(SELECT MAX(NTIMESTAMP#) FROM AUDIT_TRAIL.AUDIT_ARCHIVE_FGA);
    COMMIT;
    EXECUTE IMMEDIATE ''ALTER TABLE AUDIT_TRAIL.AUDIT_ARCHIVE MOVE COMPRESS BASIC'';
    EXECUTE IMMEDIATE ''ALTER TABLE AUDIT_TRAIL.AUDIT_ARCHIVE_FGA MOVE COMPRESS BASIC'';
    DBMS_STATS.GATHER_TABLE_STATS(''SYS'',''AUD$'');
    DBMS_STATS.GATHER_TABLE_STATS(''SYS'',''FGA_LOG$'');
END;'
      ,comments        => NULL
    );
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'SYS.AUDIT_TRAIL_MAINTENANCE'
     ,attribute => 'RESTARTABLE'
     ,value     => FALSE);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'SYS.AUDIT_TRAIL_MAINTENANCE'
     ,attribute => 'LOGGING_LEVEL'
     ,value     => SYS.DBMS_SCHEDULER.LOGGING_RUNS);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
    ( name      => 'SYS.AUDIT_TRAIL_MAINTENANCE'
     ,attribute => 'MAX_FAILURES');
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
    ( name      => 'SYS.AUDIT_TRAIL_MAINTENANCE'
     ,attribute => 'MAX_RUNS');
  BEGIN
    SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
      ( name      => 'SYS.AUDIT_TRAIL_MAINTENANCE'
       ,attribute => 'STOP_ON_WINDOW_CLOSE'
       ,value     => FALSE);
  EXCEPTION
    WHEN OTHERS THEN
      NULL;
  END;
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'SYS.AUDIT_TRAIL_MAINTENANCE'
     ,attribute => 'JOB_PRIORITY'
     ,value     => 3);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
    ( name      => 'SYS.AUDIT_TRAIL_MAINTENANCE'
     ,attribute => 'SCHEDULE_LIMIT');
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'SYS.AUDIT_TRAIL_MAINTENANCE'
     ,attribute => 'AUTO_DROP'
     ,value     => FALSE);

   SYS.DBMS_SCHEDULER.ENABLE
    (name                  => 'SYS.AUDIT_TRAIL_MAINTENANCE');
END;
/

 -- 7. Testando a execucao do Job. Verifique a saida LAST_RUN_STATUS abaixo. Em caso de "FAILED", verificar os erros consultando as tabelas DBA_SCHEDULER_JOB_RUN_DETAILS.

 BEGIN
  DBMS_SCHEDULER.run_job (job_name => 'SYS.AUDIT_TRAIL_MAINTENANCE',use_current_session => FALSE);
END;
/
COLUMN LAST_RUN_STATUS format a11
SELECT STATUS LAST_RUN_STATUS
  FROM DBA_SCHEDULER_JOB_RUN_DETAILS
 WHERE JOB_NAME = 'AUDIT_TRAIL_MAINTENANCE'
 AND ACTUAL_START_DATE=(SELECT MAX(ACTUAL_START_DATE) FROM DBA_SCHEDULER_JOB_RUN_DETAILS);

 ----------------- Processo concluído -----------------

 Caso necessite exportar a tabela de auditoria para expurgar dados antigos, basta executar o script abaixo no sistema operacional UNIX. Depois poderá excluir os dados da tabela AUDIT_ARCHIVE:

 data=`date +%d-%m-%Y-%H:%M`
expdp <user/pass> dumpfile=EXPORT_AUD_$data.DMP logfile=EXPORT_AUD_$data.LOG version=COMPATIBLE directory=<directory> schemas=AUDIT_TRAIL include=table:\"IN\(\'AUDIT_ARCHIVE\'\)\"


Até mais!

Comentários

Postar um comentário

Postagens mais visitadas deste blog

Evitando FULL TABLE SCANS quando usar IS NULL em cláusulas WHERE

Imagem
Olá pessoal, Neste post vou falar sobre um tuning simples, mas eficaz quando trabalhamos com valores nulos em filtros. Antes de tudo, contextualizando: O que é uma tabela? R. Uma tabela é um objeto do tipo SEGMENTO no banco de dados que armazena dados em forma de linhas e colunas, certo? Tipo uma planilha do excel: O que é um índice? R. Índice é outro objeto do tipo SEGMENTO que, uma vez criado em uma tabela por exemplo poderá ser utilizado para agilizar a pesquisa quando alguns critérios forem utilizados. Imagine um índice de um livro. Ele é feito para agilizar a pesquisa e você encontrar o assunto que está procurando de forma mais rápida. No banco de dados é similar, o índice é constituído basicamente do valor da coluna (ou colunas) indexada da tabela com um ponteiro que aponta para a linha (ou linhas) que possui aquele valor. Basicamente assim: - Funções é um objeto de código de programação que retorna algum valor. Por exemplo: a função SUM retorna o somatório dos
Continue lendo...

ALTER USER IDENTIFIED BY VALUES

Imagem
O comando CREATE/ALTER USER <usuario> IDENTIFIED BY VALUES '<password hash>' não é documentado. É utilizado internamente para armazenar as senhas que foram anteriormente salvas com o utilitário export na tabela do dicionário de dados SYS.USER$. Em algumas situações onde o DBA precisa se conectar com um certo usuário, mas não sabe a senha dele ou a possibilidade de salvar estas senhas ganha-se tempo ao invés de sair perguntando por elas. Às vezes você como DBA precisa realizar alguma manutenção que é preciso ser com aquele schema específico, mas a empresa não quer fornecer a senha do usuário/schema então este recurso pode ser utilizado da seguinte maneira: Você se conecta com um usuário administrativo (DBA ou SYSDBA) e executa a consulta e anota o resultado: SQL> spool senha.log SQL> SELECT password FROM dba_users     WHERE username='SCOTT'; PASSWORD                      ------------------------------ F894844C34402B67         
Continue lendo...

Restore no RMAN falha com "ORA-01180: can not create datafile 1"

Depois de um tempo ausente, volto com este probleminha que já aconteceu comigo duas vezes. Pediram-me para que eu restaurasse um banco de dados para criação de uma base de testes (ainda bem) e no momento da restauração algo assim aconteceu: creating datafile No=1 name=/....../o1_mf_system_5n2w3nky_.dbf released channel: t1 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of restore command at 09/05/2010 09:34:12 ORA-01180: can not create datafile 1 ORA-01110: data file 1: '/....../o1_mf_system_5n2w3nky_.dbf' Segundo o METALINK, isso acontece porque "O Rman acha que não tem backups pra restaurar, então ele recria os arquivos para aplicar os archives. No entanto isso não funciona para a tablespace SYSTEM". De fato é verdade. Quando tentamos restaurar os demais arquivos dá c
Continue lendo...