This post is also available in:
English
O Oracle Database 11g vêm com algumas opções de audit ligadas por default, o que pode ao longo do tempo esgotar a tablespace SYSTEM sem que o DBA perceba o porque. No entanto, esta mesma versão disponibiliza agora algumas packages DBMS que podem facilitar muito a vida do administrador que deseja migrar para outra tablespace ou alterar o período de retenção dos dados. Até a versão 10gR2, era preciso fazer essa alteração de maneira mais arcaica, que pode ser verificado no Doc ID 1019377.6 no suporte da Oracle.
Para começar, verifique se as suas opções de auditoria estão ligadas.
No meu caso, eu ativei para todas as operações do sistema e configurei para que fosse guardado também o SQL executado (DB_EXTENDED). Isso pode variar de caso a caso. Coloque os valores desejados e reinicie seu banco.
SQL> SHOW parameter audit NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ audit_file_dest string /u02/admin/rmanbkp/adump audit_syslog_level string audit_sys_operations boolean TRUE audit_trail string DB_EXTENDED
Vamos primeiro localizar em qual tablespace estão as tabelas de auditoria.
SQL> SELECT OWNER,
2 SEGMENT_NAME,
3 TABLESPACE_NAME
4 FROM DBA_SEGMENTS
5 WHERE SEGMENT_NAME IN ('AUD$', 'FGA_LOG$');
OWNER SEGMENT_NAME TABLESPACE_NAME
------------------------------ -------------------------------------------------------------------------------- ------------------------------
SYS FGA_LOG$ SYSTEM
SYS AUD$ SYSTEM
Agora vamos verificar se a infraestrutura do processo automático de expurgo do audit já foi inicializado.
SQL> SET SERVEROUTPUT ON
SQL> BEGIN
2 IF sys.DBMS_AUDIT_MGMT.is_cleanup_initialized(sys.DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD) THEN
3 DBMS_OUTPUT.put_line('YES');
4 ELSE
5 DBMS_OUTPUT.put_line('NO');
6 END IF;
7 END;
8 /
NO
SQL> SELECT PARAMETER_NAME, PARAMETER_VALUE, AUDIT_TRAIL FROM DBA_AUDIT_MGMT_CONFIG_PARAMS;
PARAMETER_NAME PARAMETER_VALUE AUDIT_TRAIL
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ----------------------------
DB AUDIT TABLESPACE SYSAUX STANDARD AUDIT TRAIL
DB AUDIT TABLESPACE SYSAUX 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 rows selected
Caso já tenha sido ativado, pule para a próxima etapa. Caso contrário, vamos então iniciar a infraestrutura do expurgo do audit com o intervalo de execução da limpeza (não retenção) para cada 1 dia (24 horas).
SQL> BEGIN 2 SYS.dbms_audit_mgmt.init_cleanup( 3 audit_trail_type => SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD, 4 default_cleanup_interval => 24 /* hours */); -- PARAMETRO NAO USADO NA 11GR2 (PARA USO FUTURO) 5 END; 6 / PL/SQL procedure successfully completed
Verificando novamente.
SQL> SET SERVEROUTPUT ON
SQL> BEGIN
2 IF sys.DBMS_AUDIT_MGMT.is_cleanup_initialized(sys.DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD) THEN
3 DBMS_OUTPUT.put_line('YES');
4 ELSE
5 DBMS_OUTPUT.put_line('NO');
6 END IF;
7 END;
8 /
YES
SQL> SELECT PARAMETER_NAME, PARAMETER_VALUE, AUDIT_TRAIL FROM DBA_AUDIT_MGMT_CONFIG_PARAMS;
PARAMETER_NAME PARAMETER_VALUE AUDIT_TRAIL
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ----------------------------
DB AUDIT TABLESPACE SYSAUX STANDARD AUDIT TRAIL
DB AUDIT TABLESPACE SYSAUX 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
DEFAULT CLEAN UP INTERVAL 24 STANDARD AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL 24 FGA AUDIT TRAIL
12 rows selected
Note o aparecimento das 2 últimas linhas com o valor "DEFAULT CLEAN UP INTERVAL" de 24 horas.
O próximo passo é a criação de uma tablespace exclusiva para guardar os dados de auditoria:
SQL> CREATE TABLESPACE AUDTBS DATAFILE '/u02/oradata/ORACL/audtbs01.dbf' SIZE 128M 2 AUTOEXTEND ON NEXT 64M MAXSIZE 2G 3 NOLOGGING default NOCOMPRESS ONLINE PERMANENT BLOCKSIZE 8K 4 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M SEGMENT SPACE MANAGEMENT AUTO; Tablespace created
Agora vamos mover as tabelas AUD$ e FGA_LOG$ para a nova tablespace criada. É recomendado que estas tabelas estejam vazias para otimizar o processo. Faça um truncate se possível ou então deixe os dados e aguarde.
SQL> BEGIN 2 SYS.DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION( 3 audit_trail_type => SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, 4 audit_trail_location_value => 'AUDTBS'); 5 END; 6 / PL/SQL procedure successfully completed SQL> SQL> BEGIN 2 SYS.DBMS_AUDIT_MGMT.set_audit_trail_location( 3 audit_trail_type => SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD, 4 audit_trail_location_value => 'AUDTBS'); 5 END; 6 / PL/SQL procedure successfully completed
Hora de verificar se as tabelas foram realmente movidas. Verifique se os valores do parâmetro "DB AUDIT TABLESPACE" na primeira query foram alterados.
SQL> SELECT PARAMETER_NAME, PARAMETER_VALUE, AUDIT_TRAIL FROM DBA_AUDIT_MGMT_CONFIG_PARAMS;
PARAMETER_NAME PARAMETER_VALUE AUDIT_TRAIL
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ----------------------------
DB AUDIT TABLESPACE AUDTBS STANDARD AUDIT TRAIL
DB AUDIT TABLESPACE AUDTBS 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
DEFAULT CLEAN UP INTERVAL 24 STANDARD AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL 24 FGA AUDIT TRAIL
12 rows selected
SQL>
SQL> SELECT OWNER,
2 SEGMENT_NAME,
3 TABLESPACE_NAME
4 FROM DBA_SEGMENTS
5 WHERE SEGMENT_NAME IN ('AUD$', 'FGA_LOG$');
OWNER SEGMENT_NAME TABLESPACE_NAME
------------------------------ -------------------------------------------------------------------------------- ------------------------------
SYS FGA_LOG$ AUDTBS
SYS AUD$ AUDTBS
Agora vamos criar um novo Scheduler Job que irá marcar diariamente todos os registros de auditoria anteriores a 90 dias como arquivados. Desta forma, o processo de limpeza poderá limpá-los.
SQL> BEGIN 2 DBMS_SCHEDULER.CREATE_JOB ( 3 job_name => 'SYS.DAILY_AUDIT_ARCHIVE_TIMESTAMP', 4 job_type => 'PLSQL_BLOCK', 5 job_action => 'BEGIN DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,LAST_ARCHIVE_TIME => SYSDATE-90); END;', 6 start_date => sysdate, 7 repeat_interval => 'FREQ=HOURLY;INTERVAL=24', 8 enabled => TRUE, 9 comments => 'Create an archive timestamp' 10 ); 11 END; 12 / PL/SQL procedure successfully completed
Fazendo a verificação se o job foi criado.
SQL> SELECT OWNER,JOB_NAME,JOB_STYLE,JOB_CREATOR,JOB_TYPE,JOB_ACTION,START_DATE,REPEAT_INTERVAL,ENABLED,STATE,SYSTEM,NLS_ENV,COMMENTS FROM dba_scheduler_jobs WHERE job_name LIKE '%AUDIT%'; OWNER JOB_NAME JOB_STYLE JOB_CREATOR JOB_TYPE JOB_ACTION START_DATE REPEAT_INTERVAL ENABLED STATE SYSTEM NLS_ENV COMMENTS ------------------------------ ------------------------------ ----------- ------------------------------ ---------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------- --------------- ------ -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- SYS DAILY_AUDIT_ARCHIVE_TIMESTAMP REGULAR SYS PLSQL_BLOCK BEGIN DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(AUDIT_TRAIL_TYPE => DBMS_AUDIT_ 22/05/13 13:29:13,000000 -03:00 FREQ=HOURLY;INTERVAL=24 TRUE SCHEDULED TRUE NLS_LANGUAGE='BRAZILIAN PORTUGUESE' NLS_TERRITORY='BRAZIL' NLS_CURRENCY='R$' NLS Create an archive timestamp
Por fim, vamos criar o processo para limpar diariamente os registros que foram marcados pelo job anterior.
SQL> BEGIN 2 SYS.DBMS_AUDIT_MGMT.CREATE_PURGE_JOB( 3 AUDIT_TRAIL_TYPE => SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, 4 AUDIT_TRAIL_PURGE_INTERVAL => 24 /* hours */, 5 AUDIT_TRAIL_PURGE_NAME => 'Daily_Audit_Purge_Job', 6 USE_LAST_ARCH_TIMESTAMP => TRUE 7 ); 8 END; 9 / PL/SQL procedure successfully completed
Fazendo a verificação se o job foi criado.
SQL> SELECT JOB_NAME,JOB_STATUS,AUDIT_TRAIL,JOB_FREQUENCY FROM DBA_AUDIT_MGMT_CLEANUP_JOBS; JOB_NAME JOB_STATUS AUDIT_TRAIL JOB_FREQUENCY -------------------------------------------------------------------------------- ---------- ---------------------------- -------------------------------------------------------------------------------- DAILY_AUDIT_PURGE_JOB ENABLED STANDARD AUDIT TRAIL FREQ=HOURLY;INTERVAL=24 SQL> SELECT OWNER,JOB_NAME,JOB_STYLE,JOB_CREATOR,JOB_TYPE,JOB_ACTION,START_DATE,REPEAT_INTERVAL,ENABLED,STATE,SYSTEM,NLS_ENV,COMMENTS FROM dba_scheduler_jobs WHERE job_name LIKE '%AUDIT%'; OWNER JOB_NAME JOB_STYLE JOB_CREATOR JOB_TYPE JOB_ACTION START_DATE REPEAT_INTERVAL ENABLED STATE SYSTEM NLS_ENV COMMENTS ------------------------------ ------------------------------ ----------- ------------------------------ ---------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------- --------------- ------ -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- SYS DAILY_AUDIT_ARCHIVE_TIMESTAMP REGULAR SYS PLSQL_BLOCK BEGIN DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(AUDIT_TRAIL_TYPE => DBMS_AUDIT_ 22/05/13 13:29:13,000000 -03:00 FREQ=HOURLY;INTERVAL=24 TRUE SCHEDULED TRUE NLS_LANGUAGE='BRAZILIAN PORTUGUESE' NLS_TERRITORY='BRAZIL' NLS_CURRENCY='R$' NLS Create an archive timestamp SYS DAILY_AUDIT_PURGE_JOB REGULAR SYS PLSQL_BLOCK BEGIN DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(1, TRUE); END; 22/05/13 13:30:13,003284 AMERICA/RECIFE FREQ=HOURLY;INTERVAL=24 TRUE SCHEDULED TRUE NLS_LANGUAGE='BRAZILIAN PORTUGUESE' NLS_TERRITORY='BRAZIL' NLS_CURRENCY='R$' NLS Audit clean job = 'Daily_Audit_Purge_Job'
Para finalizar, recomendo remover todas as opções default da auditoria do Oracle e religá-las apenas para execuções "Whenever Successful", não catalogando comandos executados que falharam por falta de privilégios.
SQL> noaudit all; SQL> noaudit all privileges; SQL> noaudit exempt access policy; Noaudit succeeded Noaudit succeeded Noaudit succeeded sql> audit all by access whenever successful; -- Adiciona 27 opções de AUDIT sql> audit alter any table by access whenever successful; sql> audit create any table by access whenever successful; sql> audit drop any table by access whenever successful; sql> audit create any procedure by access whenever successful; sql> audit drop any procedure by access whenever successful; sql> audit alter any procedure by access whenever successful; sql> audit grant any privilege by access whenever successful; sql> audit grant any object privilege by access whenever successful; sql> audit grant any role by access whenever successful; sql> audit audit system by access whenever successful; sql> audit create external job by access whenever successful; sql> audit create any job by access whenever successful; sql> audit create any library by access whenever successful; sql> audit create public database link by access whenever successful; sql> audit exempt access policy by access whenever successful; sql> audit alter user by access whenever successful; sql> audit create user by access whenever successful; sql> audit role by access whenever successful; sql> audit create session by access whenever successful; sql> audit drop user by access whenever successful; sql> audit alter database by access whenever successful; sql> audit alter system by access whenever successful; sql> audit alter profile by access whenever successful; sql> audit drop profile by access whenever successful; Audit succeeded Audit succeeded Audit succeeded Audit succeeded Audit succeeded Audit succeeded Audit succeeded Audit succeeded Audit succeeded Audit succeeded Audit succeeded Audit succeeded Audit succeeded Audit succeeded Audit succeeded Audit succeeded Audit succeeded Audit succeeded Audit succeeded Audit succeeded Audit succeeded Audit succeeded Audit succeeded Audit succeeded Audit succeeded
Verificando..
SQL> SELECT * FROM DBA_STMT_AUDIT_OPTS order by 3;
USER_NAME PROXY_NAME AUDIT_OPTION SUCCESS FAILURE
------------------------------ ------------------------------ ---------------------------------------- ---------- ----------
ALTER ANY PROCEDURE BY ACCESS NOT SET
ALTER ANY TABLE BY ACCESS NOT SET
ALTER DATABASE BY ACCESS NOT SET
ALTER PROFILE BY ACCESS NOT SET
ALTER SYSTEM BY ACCESS NOT SET
ALTER USER BY ACCESS NOT SET
CLUSTER BY ACCESS NOT SET
CONTEXT BY ACCESS NOT SET
CREATE ANY JOB BY ACCESS NOT SET
CREATE ANY LIBRARY BY ACCESS NOT SET
CREATE ANY PROCEDURE BY ACCESS NOT SET
CREATE ANY TABLE BY ACCESS NOT SET
CREATE EXTERNAL JOB BY ACCESS NOT SET
CREATE PUBLIC DATABASE LINK BY ACCESS NOT SET
CREATE SESSION BY ACCESS NOT SET
CREATE USER BY ACCESS NOT SET
DATABASE LINK BY ACCESS NOT SET
DIMENSION BY ACCESS NOT SET
DIRECTORY BY ACCESS NOT SET
DROP ANY PROCEDURE BY ACCESS NOT SET
DROP ANY TABLE BY ACCESS NOT SET
DROP PROFILE BY ACCESS NOT SET
DROP USER BY ACCESS NOT SET
EXEMPT ACCESS POLICY BY ACCESS NOT SET
GRANT ANY OBJECT PRIVILEGE BY ACCESS NOT SET
GRANT ANY PRIVILEGE BY ACCESS NOT SET
GRANT ANY ROLE BY ACCESS NOT SET
INDEX BY ACCESS NOT SET
MATERIALIZED VIEW BY ACCESS NOT SET
MINING MODEL BY ACCESS NOT SET
NOT EXISTS BY ACCESS NOT SET
PROCEDURE BY ACCESS NOT SET
PROFILE BY ACCESS NOT SET
PUBLIC DATABASE LINK BY ACCESS NOT SET
PUBLIC SYNONYM BY ACCESS NOT SET
ROLE BY ACCESS NOT SET
ROLLBACK SEGMENT BY ACCESS NOT SET
SEQUENCE BY ACCESS NOT SET
SYNONYM BY ACCESS NOT SET
SYSTEM AUDIT BY ACCESS NOT SET
SYSTEM GRANT BY ACCESS NOT SET
TABLE BY ACCESS NOT SET
TABLESPACE BY ACCESS NOT SET
TRIGGER BY ACCESS NOT SET
TYPE BY ACCESS NOT SET
USER BY ACCESS NOT SET
VIEW BY ACCESS NOT SET
47 rows selected
SQL>
Pronto, agora você não precisa mais se preocupar com o crescimento descontralado da sua tablespace SYSTEM. A auditoria está isolada em uma tablespace dedicada.
Gostou? Não deixe de comentar ou deixar um 👍!
14 comentários
Pular para o formulário de comentário
Rodrigo ,
Que excelente artigo sobre expurgo de auditoria , estou implementando auditoria aqui na empresa e é exatamente isso que vou precisar , não sabia dos novos recursos do R11G.
Muito bom !
Valeu !!!!
abç
Autor
Obrigado!
Grande abç!
Excelente artigo,
Estou implementando a auditoria na empresa onde trabalho e este artigo me ajudou bastante.
Muito Obrigada!
Autor
Que bom Deyse! Fico grato por ouvir isso! =]
Abcs,
Rodrigo
Rodrigo,
Recentemente, instalamos um banco na versão 12.1.0.1.0, sendo que "The Unified Auditing" não está habilitado e "The get_stats_history_retention is 7".
No tablespace AUDSYS, os objetos "table" CLI_SWP$21f1d8bc$1$1 and "lobsegment" SYS_LOB0000091751C00014$$ estão em constante crescimento.
Você tem alguma dica de como limitar o crescimentos destes objetos?
Grato,
Carlos.
Autor
Oi Carlos,
As melhores formas de limitar são alterando a quantidade de auditoria que você gera ou aumentando a frequência e diminuindo a retenção para maiores expurgos dos dados históricos.
Caso não seja possível, outra recomendação é exportar com alguma periodicidade os dados das tabelas de auditorias e limpá-las.
Abcs,
Rodrigo
Muito bom.
Rodrigo, excelente artigo sobre auditoria. Há tempos eu nao via algo tão completo.
Uma dúvida que tenho em relação á aud$ é se eu posso aplicar a compressão OLTP nela. É recomendado ou tem algum efeito colateral?
Autor
Oi Reginaldo,
Tudo bem?
Nunca é recomendado alterar uma tabela interna de BD ($ tables), quanto mais ao formato de compressão. Sem dúvidas a compressão OLTP, pela forma que ela é concebida, vai gerar um impacto grande nas operações de BD que geram auditoria.
O melhor a se fazer neste caso é fazer um expurgo (diário/semanal via job) da aud$ para uma outra tabela de outro schema usando "load compress" (não OLTP).
Abcs,
RJ
Olá Rodrigo, parabéns pelo artigo, realmente bastante completo.
Gostaria de saber a respeito dos arquivos .aud que são gravados na pasta adump, nesse caso eles também são excluídos conforme a retenção ou só com um rm agendado no linux?
Desde já agradeço.
Abs.
Autor
Oi Dione,
Para arquivos aud (que são gerados caso o seu audit trail location esteja como OS ou para logins feitos com SYSDBA), eles precisam ser agendados. No entanto, em vez de executar um "rm", utilize o utilitário adrci passando como parâmetro o tempo de retenção. É mais fácil e mais seguro.
Abraços,
RJ
Olá Rodrigo!
Parabéns pelo artigo! Muito esclarecedor!! Obrigada por compartilhar!
Tenho uma dúvida, sobre a rotina de limpeza, tenho que fazer uma separa para a FGA_LOG$ Porque percebi que a minha tabela FGA_LOG$ não está sendo "limpa".
Muito obrigada!
Herica Beccalli
Autor
Oi Herica, tudo bem?
Sim, você pode fazer uma política separada ou então alterar a constante passada nos parâmetros. Dê uma neste link para ver a que tabela cada constante aponta: https://docs.oracle.com/database/121/ARPLS/d_audit_mgmt.htm#BABIDGEA
Abcs,
Rodrigo
Boa tarde. Parabens e mjuito bem detalhado . Uma duvida a mais . Possível listar os horários que essas alterações estão sendo feitas? Exemplo de gerar um relatório para ser enviado ao respons. .. Obrigado