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.
