Effet caché de la database 11G – Augmentation du volume des traces

Pour continuer dans la série des effets de bords que l’on constate une fois que l’on passe sa base de données dans la version 11G (aujourd’hui en version 11.2.0.2 pour tous les OS supportés), après les impacts de “Direct Path read” et ceux de “Deferred Segment Creation”, voici quelques informations concernant le volume de trace automatiquement généré qui peut devenir à court terme assez gênant si on ne prend pas de précautions suffisantes.
Les exemples de shell donnés ici sont applicables sur les environnements linux mais seront facilement repris sur les plateformes Windows.
Le problème se situe à deux niveaux :

  • Les enregistrements d’audit à l’intérieur de la base de données
  • Le nouveau référentiel de diagnostic (Automatic Diagnostic Repository)

Audit par défaut

En  ce qui concerne l’audit, les nouvelles options de sécurité, mise en œuvre par défaut  lorsque l’on créé une base 11G avec l’assistant dbca  ou avec l’utilisation de l’assistant dbua pour la migration, sont :

  • Paramétrage AUDIT_TRAIL sur DB
  • Audit par défaut de certain privilèges et ordres SQL relatifs à la sécurité (voir le contenu des vues DBA_PRIV_AUDIT_OPTS et  DBA_PRIV_AUDIT_OPTS):
ALTER ANY PROCEDURE
CREATE ANY LIBRARY
DROP ANY TABLE
ALTER ANY TABLE
CREATE ANY PROCEDURE
DROP PROFILE
ALTER DATABASE
CREATE ANY TABLE
DROP USER
ALTER PROFILE
CREATE EXTERNAL JOB
EXEMPT ACCESS POLICY
ALTER SYSTEM
CREATE PUBLIC DB LINK
GRANT ANY OBJECT PRIVILEGE
ALTER USER
CREATE SESSION
GRANT ANY PRIVILEGE
AUDIT SYSTEM
CREATE USER
GRANT ANY ROLE
CREATE ANY JOB
DROP ANY PROCEDURE
Statements with BY ACCESS clause

Ceci va enrichir rapidement la table SYS.AUD$  qui  est située dans  le tablespace SYSTEM,  avec au moins une ligne à chaque connexion; c’est très bien pour la sécurité, mais nos amis les développeurs ont juste oublié de fournir une procédure automatique de purge (à quoi servent  les taches de maintenance?). On peut donc au bout de quelques mois se retrouver avec le tablespace SYSTEM qui prend plus de 10Go.
Si  cela vous arrive, vérifier en rapidement la cause par un simple select :

SELECT SUM(BYTES/1024/1024) “Mo” FROM DBA_SEGMENTS WHERE OWNER=’SYS’ AND SEGMENT_NAME=’AUD$’ ;

Pour conserver ces informations  n’hésitez pas à les archiver en passant par une commande de type :

INSERT INTO ARCH_AUDIT SELECT * FROM SYS.AUD$ WHERE NTIMESTAMP# < &PURGE_DATE”

Ensuite prévoyez un mécanisme de purge périodique:

  • Si vous êtes en version 11.1 ,  créer votre propre tache et inspirez vous pour cela du très bon blog de nos amis de Pythian ici
  • Si vous êtes en version 11.2 voire 11.1.0.7 ,  Oracle fournit le package DBMS_AUDIT_MGMT pour vous facilitez la tache, voir la documentation “Oracle Database Security Guide 11G”  et la note MOS (My Oracle Support feu Metalink ) 731908.1
    L’utilisation de ce package était précédemment  subordonnée à l’option “Database Vault” , mais ce n’est plus aussi clair maintenant, il apparait dans la documentation sans faire référence à “Database Vault” , le doute est donc permis, ainsi que l’usage …

Si vous souhaitez récupérer de l’espace en retaillant le fichier associé, tenter une commande “TRUNCATE” , suivi d’un “ALTER DATABASE DATAFILE ‘<path du fichier>’ RESIZE 1000M”  , mais vous risquez fort de vous retrouvez avec l’erreur suivante :

Erreur SQL : ORA-03297: le fichier contient des données utilisées au-delà de la valeur RESIZE requise.

Pour vous aider à déterminer la bonne valeur possible (si vous avez un peu de chance et que d’autres extents ne sont pas venus s’intercaler après ceux de la table AUD$) , je vous renvoie à la note MOS 130866.1 , n’hésitez pas à modifier le premier curseur pour filtrer les fichiers à traiter.
Il reste la possibilité de déplacer la table SYS.AUD$ dans un autre tablespace (on se demande déja pourquoi elle n’est pas présente dans SYSAUX au lieu de SYSTEM) en utilisant l’explication de la note MOS  72460.1 ou encore une fois grâce au package DBMS_AUDIT_MGMT (chaudement recommandé par la dite note), vous avez ici, un exemple d’utilisation ainsi que quelques autres fonctionnalités intéressantes si vous gérer aussi un audit basé sur les fichiers OS.
Pour stopper complètement la génération de ces enregistrements :

  • Paramétrage AUDIT_TRAIL=NONE
  • Ou exécution de la commande NOAUDIT pour tous les privilèges listés, n’hésitez pas à générer les commandes dynamiquement :select ‘NOAUDIT ‘ || PRIVILEGE||’;’ from dba_priv_audit_opts ;select ‘NOAUDIT ‘ || AUDIT_OPTION ||’;’ from dba_stmt_audit_opts ;

Référentiel automatique des diagnostics

Pour  les nouvelles traces induites par la fonctionnalité “Automatic Diagnostic Repository” , on s’aperçoit assez vite que le répertoire associé au diagnostic (arborescence qui commence à l’entrée définie par le paramètre DIAGNOSTIC_DEST) prend quelques Giga octets. En regardant de plus près, on voit que  le répertoire « alert »  associé à l’instance de votre base de données contient une liste de fichier de type log_n.xml de 10Mo,  qui correspondent à la rotation du fichier d’alert au format xml nommé log.xml.
Pour purger ces fichiers deux méthodes sont possibles :

1 – Utiliser les périodes de rétention définies et les raccourcir

Il existe deux règles de rétention associées aux fichiers dans le référentiel de trace : SHORTP_POLICY qui définie en heure la durée de vie des fichiers de type traces, core dump, et packaging d’incident,  la valeur par défaut est de 720 heures soit 30 jours. LONGP_POLICY durée de vie en heures pour les fichiers relatifs aux informations et au dump associés aux incidents et au fichier d’alert , la valeur par défaut est de 8760 heures soit 365 jours .
Ces valeurs sont visible par la commande “SHOW CONTROL” de l’utilitaire adrci:

adrci> show control
ADR Home = /home/oracle/app/oracle/product/11.2.0/dbhome_1/log/diag/rdbms/dbua0/DBUA0:
*************************************************************************
ADRID                SHORTP_POLICY        LONGP_POLICY         LAST_MOD_TIME                            LAST_AUTOPRG_TIME                        LAST_MANUPRG_TIME                        ADRDIR_VERSION       ADRSCHM_VERSION      ADRSCHMV_SUMMARY     ADRALERT_VERSION     CREATE_TIME
-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ----------------------------------------
4294180986           720                  8760                 2009-10-29 16:41:16.033955 -07:00                                                                                          1                    2                    76                   1                    2009-10-29 16:41:16.033955 -07:00

La commande “SET CONTROL” sera utilisée pour définir des périodes plus courte, ainsi, les commandes suivantes vont définir une rétention de 8 jours pour les traces et les fichiers coredump et une rétention d’un mois pour le fichier d’alert et les dumps associés aux incidents :

adrci> set control (shortp_policy=192) ;
adrci> set control (longp_policy=720) ;
adrci> show control
ADR Home = /home/oracle/app/oracle/product/11.2.0/dbhome_1/log/diag/rdbms/dbua0/DBUA0:
*************************************************************************
ADRID                SHORTP_POLICY        LONGP_POLICY         LAST_MOD_TIME                            LAST_AUTOPRG_TIME                        LAST_MANUPRG_TIME                        ADRDIR_VERSION       ADRSCHM_VERSION      ADRSCHMV_SUMMARY     ADRALERT_VERSION     CREATE_TIME
-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ----------------------------------------
4294180986           192                  720                  2011-05-17 01:37:39.122743 -07:00                                                                                          1                    2                    76                   1                    2009-10-29 16:41:16.033955 -07:00

Il est important de noter les points suivants :

  • Pour les instances de base de données ce sont les process MMON qui ont la responsabilité de faire le ménage, c’est donc automatique.
  • Pour les autres produits Oracle s’appuyant sur le référentiel de diagnostic, particulièrement les listeners, aucun process n’est dédié pour cela, il faut donc prévoir une tache de maintenance qui réalisera l’opération suivante :
adrci> set home diag/tnslsnr/localhost/listener
adrci> purge

2 – Définir sa propre rétention et utiliser les options de la commande “purge”.

Cette commande possède une option  » –age » qui permet de spécifier en minute la rétention des fichiers à purger, elle s’applique toujours avec la spécification d’un type, option « –type », pour les catégories ALERT, INCIDENT, TRACE, CDUMP, HM et (depuis la version 11.2) UTSCDMP.  Exemple :  “adrci>purge –age 60 –type incident”,  supprime tous les fichiers associés aux incidents de plus d’une heure.
Pour automatiser le nettoyage, un shell de ce type peut faire l’affaire (en n’oubliant pas que la commande “purge” s’applique toujours sur un répertoire home spécifique):

#!/bin/bash
# purge_adrci.sh
#
export PURGE=/tmp/purge.scr
export AGE=21600
cat /dev/null>$PURGE
for i in `adrci exec="show home"|grep -v :`
do
echo "set home $i;" >>$PURGE
echo "purge -age $AGE -type alert;" >>$PURGE
echo "purge -age $AGE -type incident;" >>$PURGE
echo "purge -age $AGE -type trace;" >>$PURGE
echo "purge -age $AGE -type cdump;" >>$PURGE
echo "purge -age $AGE -type hm;" >>$PURGE
echo "purge -age $AGE -type UTSCDMP;" >>$PURGE
done
adrci script=$PURGE

Il faut bien comprendre les limitations des commandes “purge” de l’utilitaire adrci:

  • La rétention ne s’applique qu’à la date des fichiers et la suppression ne travaille que sur les fichiers complets (pas de suppression de ligne à l’intérieur des fichiers).
  • Les commandes de purge ne s’applique pas au fichier alert_<SID>.log ou listener.log des répertoires trace (pour Oracle ces fichiers ne sont là que pour la compatibilité avec les versions antérieures et seront amenés à disparaitre).  Il faut une tache dédiée de purge pour eux. Pour  cela vous en avez certainement déjà, rappelez vous que les process maintiennent un handle constant sur les fichiers tant qu’ils sont en cours (listener ou base) donc l’espace n’est pas libéré sur une commande mv ou rm. la fonctionnalité logrotate est disponible pour la plupart des linux afin de vous aider pour cela.

Sans ces quelques précautions, une saturation rapide de vos systèmes de fichier est à prévoir avec du travail superflu pour vos équipes de supervision, n’hésiter pas à investir un peu de temps en amont pour réguler tout cela.