Olá galera! Trabalhando hoje em um cliente encontrei um bug em um dos novos recursos do Oracle Database 12c, a Unified Auditing, o qual escrevi recentemente um artigo na OTN e você pode ler na íntegra com este link.
Vamos ao bug…
Cheguei hoje cedo no escritório e logo apareceu um alerta no monitoramento referente ao espaço disponível na tablespace SYSAUX. A SYSAUX não é tão comum assim ficar grande dependendo do que você tem instalado e o que você grava nela. A SYSAUX neste cliente estava com 9GB, isso porque eu limitei o tamanho máximo dela a 10GB e o monitoramento alertou quando chegou a 90% de uso. Tá aí um motivo para não deixar suas tablespaces com tamanho ilimitado. Como este cliente não tem DBA interno e não tem uma pessoa sempre olhando para o ambiente, então somente nosso monitoramento pegou este problema.
Logo pensei que fosse um outro bug conhecido referente à retenção de estatísticas históricas que o Oracle não consegue remover, então consultei buscando por isso usando a seguinte query:
SQL> col Mb form 9,999,999 SQL> col SEGMENT_NAME form a40 SQL> col SEGMENT_TYPE form a6 SQL> set lines 120 SQL> select sum(bytes/1024/1024) Mb, segment_name,segment_type from dba_segments 2 where tablespace_name = 'SYSAUX' 3 and segment_name like 'WRI$_OPTSTAT%' 4 and segment_type='TABLE' 5 group by segment_name,segment_type order by 1 asc 6 / MB SEGMENT_NAME SEGMEN ---------- ---------------------------------------- ------ 0 WRI$_OPTSTAT_SYNOPSIS_HEAD$ TABLE 0 WRI$_OPTSTAT_AUX_HISTORY TABLE 0 WRI$_OPTSTAT_SYNOPSIS_PARTGRP TABLE 1 WRI$_OPTSTAT_TAB_HISTORY TABLE 1 WRI$_OPTSTAT_IND_HISTORY TABLE 3 WRI$_OPTSTAT_OPR_TASKS TABLE 3 WRI$_OPTSTAT_OPR TABLE 7 rows selected.
Percebi que não era este o problema, até porque este bug, retratado na nota 14373728.8, estava relacionado às versões 11.2.0.2 e 11.2.0.3 do Oracle Database, mas meu cliente estava já na versão 12.1.0.1.
Executei a seguinte query para verificar quais eram os maiores segmentos da tablespace SYSAUX:
SQL> select * from (select owner,segment_name,segment_type,tablespace_name, 2 round(bytes/1024/1024,1) "size_in_MB" 3 from dba_segments 4 where tablespace_name='SYSAUX' 5 order by bytes/1024/1024 desc) where rownum < 10 order by 5 desc; OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME size_in_MB ------------------------------ ---------------------------------------- -------------------- -------------------- ---------- AUDSYS SYS_LOB0000089459C00014$$ LOBSEGMENT SYSAUX 4647.1 AUDSYS CLI_SWP$ee19fa3c$1$1 TABLE SYSAUX 3654 XDB SYS_LOB0000018450C00025$$ LOBSEGMENT SYSAUX 96.2 SYS I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST INDEX SYSAUX 47 SYS SYS_LOB0000005824C00005$$ LOBSEGMENT SYSAUX 36.2 AUDSYS CLI_TIME$ee19fa3c$1$1 INDEX SYSAUX 30 AUDSYS CLI_SCN$ee19fa3c$1$1 INDEX SYSAUX 28 AUDSYS CLI_LOB$ee19fa3c$1$1 INDEX SYSAUX 27 SYS I_WRI$_OPTSTAT_H_ST INDEX SYSAUX 25 9 rows selected.
Identifiquei um LOB e uma tabela do schema AUDSYS, logo, se tratava de auditoria. Normalmente quando temos necessidade de realizar a auditoria pelo banco de dados nós criamos uma tablespace padrão para essa finalidade, mas neste banco de dados não havia a necessidade de habilitar auditoria. Por padrão a Unified Auditing não vem habilitada com todos os seus recursos, porém a política ORA_SECURECONFIG é por padrão habilitada e estava gravando registros de maneira inesperada. Tá aí o primeiro ponto. Até aí sem problemas, pois foi só remover os registros e desabilitar a política. Inclusive depois que atuei, encontrei uma nota que fala desse assunto: 1624051.1.
Executei os seguintes comandos para limpar os registros de auditoria já gravados e então desabilitei a política ORA_SECURECONFIG após alinhar com o cliente:
SQL> exec DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP ( - AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, - LAST_ARCHIVE_TIME => sysdate) PL/SQL procedure successfully completed. SQL> exec DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL( - AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, - USE_LAST_ARCH_TIMESTAMP => TRUE) PL/SQL procedure successfully completed. SQL> exec DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL( - AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, - USE_LAST_ARCH_TIMESTAMP => FALSE) PL/SQL procedure successfully completed. SQL> noaudit policy ORA_SECURECONFIG; Noaudit succeeded. SQL> select count(*) from unified_audit_trail; COUNT(*) ---------- 0
Dependendo do tamanho das trilhas de auditoria estes comandos podem demorar para finalizar.
Daí que veio o problema… Os registros foram todos removidos conforme podemos ver, porém os segmentos continuaram com o mesmo tamanho. :0
Podemos ver novamente através da seguinte query já utilizada:
SQL> select * from (select owner,segment_name,segment_type,tablespace_name, 2 round(bytes/1024/1024,1) "size_in_MB" 3 from dba_segments 4 where tablespace_name='SYSAUX' 5 order by bytes/1024/1024 desc) where rownum < 10 order by 5 desc; OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME size_in_MB ------------------------------ ---------------------------------------- -------------------- -------------------- ---------- AUDSYS SYS_LOB0000089459C00014$$ LOBSEGMENT SYSAUX 4647.1 AUDSYS CLI_SWP$ee19fa3c$1$1 TABLE SYSAUX 3654 XDB SYS_LOB0000018450C00025$$ LOBSEGMENT SYSAUX 96.2 SYS I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST INDEX SYSAUX 47 SYS SYS_LOB0000005824C00005$$ LOBSEGMENT SYSAUX 36.2 AUDSYS CLI_TIME$ee19fa3c$1$1 INDEX SYSAUX 30 AUDSYS CLI_SCN$ee19fa3c$1$1 INDEX SYSAUX 28 AUDSYS CLI_LOB$ee19fa3c$1$1 INDEX SYSAUX 27 SYS I_WRI$_OPTSTAT_H_ST INDEX SYSAUX 25 9 rows selected.
Consultando mais uma vez o MOS (My Oracle Support, ou Metalink para os mais antigos) encontrei outro bug de número 18109788, tratado na nota 1935169.1, que diz que a package DBMS_AUDIT_MGMT não libera o espaço utilizado pelo segmento de LOB de auditoria. Esta nota ainda diz que este problema não pode ser corrigido nesta versão do Oracle Database e fazer upgrade para 12.1.0.2 também não irá resolver. Para voltar os objetos ao seu tamanho original a Oracle recomenda recriar o banco de dados. Mas para amenizar o problema você pode simplesmente desabilitar a política ORA_SECURECONFIG como fiz ou se deixar habilitada você deve frequentemente efetuar uma manutenção de limpeza nos registros similar também ao que eu fiz.
Espero que este blog te ajude em algum momento.
Até a próxima…
Franky