Blog

Entrainez-vous à LogMiner !

Vous serez peut-être amené un jour à retrouver l’ensemble des lignes supprimées par un ordre delete « après la bataille » et après que undo_retention ne vous permette de les retrouver avec SELECT ... AS OF TIMESTAMP .... Dans un tel cas, LogMiner est sans doute l’outil que vous utiliserez alors, que penseriez-vous d’un entraînement ?

Etape 1 : un scénario déjà vu !

Commencez par imaginer un scénario que vous avez peut-être déjà vu dans lequel une table existe depuis « toujours » dans le schéma de votre application :

create table scott.demo(id number)
    tablespace users;

begin
for i in 1..1000 loop
 insert into scott.demo values (i);
end loop;
end;
/

commit;

alter system archive log current;

select thread#, max(sequence#)
from v$archived_log
group by thread#
order by thread#;

THREAD# MAX(SEQUENCE#)
------- --------------
    1             86

SQL> select name from v$archived_log
where sequence#=86 and thread#=1;

NAME
---------------------------------------------------------
/fra/BLACK/archivelog/2008_03_30/o1_mf_1_86_3z0h44tt_.arc

Un jour un développeur qui a accès à la production ou si vous voulez rendre le scénario plus crédible, dites un DBA malhonnête, accède à la base de données et supprime quelques lignes…

delete from scott.demo where id < 112;

commit;

Le temps passe et vous ne vous apercevez pas ! En effet, un TRUNCATE ou un DROP sont assez faciles à détecter : l’application devient assez méconnaissable après une de ces opérations. Bien sur, ça ne veux pas dire que c’est facile à récupérer. Et LogMiner ne vous aidera sûrement pas ! mais un DELETE ou un UPDATE de quelques lignes sont beaucoup plus pernicieux :

alter system archive log current;

select thread#, max(sequence#)
from v$archived_log
group by thread#
order by thread#;

THREAD# MAX(SEQUENCE#)
------- --------------
    1             87

select name from v$archived_log
 where sequence#=87 and thread#=1;

NAME
---------------------------------------------------------
/fra/BLACK/archivelog/2008_03_30/o1_mf_1_87_3z0ht5jv_.arc

Finalement après quelques jours et la plainte de plusieurs utilisateurs, ça arrive sur votre bureau! Quelqu’un a détruit des données. On pense que l’application a été piratée ; c’est plus rassurant que d’imaginer que quelqu’un a fait une bêtise et ne s’est pas dénoncé ! Et c’est peut-être vrai en plus. Toujours est-il que l’audit est inactif sur votre système et que vous avez l’occasion de devenir une sorte de super administrateur. Ou de faire virer quelqu’un, y compris vous même !

Etape 2 : Ne travaillez pas en production !

Je sais ce que vous vous dites et vous avez raison : Je suis un lâche et un peureux. Je n’interviens jamais en production, à moins que le système soit déjà planté et en tout cas, jamais pour faire un DROP, un TRUNCATE ou un ordre LMD. Pas d’exception pour LogMiner ; Connectez-vous simplement à la production le temps de « DUMP »er le dictionnaire dans les redo logs et déconnectez vous. Commencez par vérifier que votre base de données a des informations complémentaires activées dans les redo logs pour utiliser LogMiner et sinon activez-les, même le temps de copier le dictionnaire :

select SUPPLEMENTAL_LOG_DATA_MIN
from v$database;

SUPPLEME
--------
NO

alter database add supplemental log data;

Database altered.

select SUPPLEMENTAL_LOG_DATA_MIN
from v$database;

SUPPLEME
--------
YES

EXECUTE DBMS_LOGMNR_D.BUILD( -
 OPTIONS=> DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);

SELECT NAME FROM V$ARCHIVED_LOG WHERE DICTIONARY_BEGIN='YES';

NAME
---------------------------------------------------------
/fra/BLACK/archivelog/2008_03_30/o1_mf_1_89_3z0j8hxc_.arc
/fra/BLACK/archivelog/2008_03_30/o1_mf_1_90_3z0j8mxh_.arc

SELECT NAME FROM V$ARCHIVED_LOG WHERE DICTIONARY_END='YES';

NAME
---------------------------------------------------------
/fra/BLACK/archivelog/2008_03_30/o1_mf_1_90_3z0j8mxh_.arc

alter database drop supplemental log data;

Nota Bene :

  • Toutes les informations utiles pour utiliser LogMiner sont dans la documentation… Utilisez-la : Oracle Database Utilities, 11g Release 1 : 18 Using LogMiner to Analyze Redo Log Files
  • En fait et à moins que votre système ne souffre explicitement de « Log Sync Waits », j’aurais tendance à penser que ça peut être une excellente idée de fonctionner avec les « Supplemental Log Data ». En effet vous pourrez alors simplement tracer des opérations impossible autrement (Sur des clusters de table par exemple) ou connaître le programme qui a exécuté une opération meme si l’audit est inactif. Autrement dit, c’est peut-être une manière simple de différencier une opération qui vient de l’application d’une opération qui vient d’une session SQL*Plus ou d’un autre outil de DBA ou de développeur.
  • Vous devrez également repérer les fichiers d’archivelogs qui vous intéressent en vous basant probablement sur une plage de date pendant laquelle vous suspecter que les opérations ont eu lieu. Cela suppose évidemment que les archivelogs sont toujours la. Il sera d’ailleurs peut-être utile d’allonger la période de rétention de ces fichiers le temps de finir votre travail.

Etape 3 : Session LogMiner

Vous voilà prêt ou presque à lancer la session LogMiner sur une autre base de données. Bien sur, il faut que depuis votre second serveur vous puissiez accéder les fichier d’archivelogs du premier ; ASM en RAC, NFS, CIFS ou scp sont vos amis dans une telle situation. Alors si vous pouvez accéder aux fichiers de archivelogs depuis votre second serveur, vous voila prêt ! Juste quelques précisions :

  • Vous allez créer une session LogMiner ; Celle-ci est liée à une session de base de données et vous ne pourrez donc pas vous déconnecter entre les différentes étapes de vos manipulations de LogMiner. Cette façon de procéder permet d’avoir plusieurs sessions logminer concurrentes sur la même base de données ; Elle a des conséquences sur la manière d’utiliser logMiner surtout si vous voulez scripter une telle session.
  • Une fois votre session logMiner prête, vous voudrez recherchez l’ensemble des ordres SQL correspondant à vos critères (e.g. tous les DELETE sur la table DEMO). Pour ce faire, vous allez interroger la fixed view V$LOGMNR_CONTENTS. Contrairement à la plupart des autres fixed view, le contenu de V$LOGMNR_CONTENTS sera construit dynamiquement en scannant les archivelogs de votre session LogMiner. Dans l’exemple qui suit vous ne scannerez que quelques fichiers de logs donc le cout associez est faible. Dans une situation réelle, il est très probable que la plage d’archive que vous observiez soit très large et donc que la requête de cette vue soit très longue (plusieurs heures). Pour éviter de scanner toute la plage d’archivelogs de multiple fois :
    • Enregistrer le résultat de votre SELECT dans une table avec, par exemple une requête du type CREATE TABLE X AS SELECT... FROM V$LOGMNR_CONTENTS WHERE ....
    • Prenez des critères assez larges pour ensuite examiner votre table résultante du premier SELECT avec plus de précision. Selectionnez un maximum de colonnes de V$LOGMNR_CO
      NTENTS      .
    • Ne travaillez pas en production

Enfin voila pour utiliser LogMiner, procédez dans les étapes qui suivent :

begin
DBMS_LOGMNR.ADD_LOGFILE(
LOGFILENAME => '/fra/BLACK/archivelog/2008_03_30/o1_mf_1_87_3z0ht5jv_.arc',
OPTIONS => DBMS_LOGMNR.NEW);

DBMS_LOGMNR.ADD_LOGFILE(
LOGFILENAME => '/fra/BLACK/archivelog/2008_03_30/o1_mf_1_88_3z0j7z3r_.arc',
OPTIONS => DBMS_LOGMNR.ADDFILE);

DBMS_LOGMNR.ADD_LOGFILE(
LOGFILENAME => '/fra/BLACK/archivelog/2008_03_30/o1_mf_1_89_3z0j8hxc_.arc',
OPTIONS => DBMS_LOGMNR.ADDFILE);

DBMS_LOGMNR.ADD_LOGFILE(
LOGFILENAME => '/fra/BLACK/archivelog/2008_03_30/o1_mf_1_90_3z0j8mxh_.arc',
OPTIONS => DBMS_LOGMNR.ADDFILE);

end;
/
  • Démarrez ensuite la session LogMiner avec DBMS_LOGMNR.ADD_LOGFILE. Vous pouvez spécifiez une plage de date, de SCN ou les options de visualisation des données.
EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => -
 DBMS_LOGMNR.DICT_FROM_REDO_LOGS + -
 DBMS_LOGMNR.COMMITTED_DATA_ONLY);
  • Effectuez vos requêtes LogMiner et descendez dans le détail de
create table scott.X
as select * from V$LOGMNR_CONTENTS
 where operation='DELETE' and table_name='DEMO';

select count(*) from scott.X;

 COUNT(*)
----------
      111

Voila, c’est terminé ; gardez à l’esprit que LogMiner ne vous donne pas l’ordre SQL initial mais bien un ordre par ligne modifiée. D’autre part, si l’objet n’existe plus lors de la capture du dictionnaire en production, vous ne verrez que l’object id dans votre requête et cela même si vous utilisez l’option DDL_DICT_TRACKING. En effet cette option permet de traquer les modifications du dictionnaire de données qu’après sa création dans les fichiers de redo logs.

Thomas Bordet
Thomas Bordet
Voir tous ses articles
Articles récents

Mentions légales & Politique de confidentialité