LOGMINER avec Oracle 9i

Les REDO LOGs … Fichiers contenant toutes les informations sur toutes les transactions survenues dans notre base de données préférée. Le problème de ces fichiers c'est que l'on ne peut pas éditer le contenu aussi facilement. C'est pour cela que Oracle nous à fournit un outil très pratique permettant d'analyser et d'utiliser le contenu de ces fichiers REDO LOG.

Vous pourrez utiliser cet utilitaire pour faire un audit des actions effectuées sur la base, sur un objet ou même effectuées par un utilisateur précis.

Dans cet article je vais vous expliquer les fonctions de base et comment utiliser cet utilitaire.

Depuis sa création, LogMiner a beaucoup évolué. Voici la liste des dernières fonctionnalités qui lui ont été ajoutées :

• Support des ordres DDL (Maintenant LogMiner est en mesure d'extraire les ordres DDL qui ont été exécuté sur la base de données. Ils sont affichés dans la colonne SQL_REDO).
• Capacités à traduire les DML associés avec des Index en Cluster.
• Support des lignes chaînées et des lignes migrées.
• Support des INSERT en mode Direct Path.
• Création du dictionnaire dans les fichiers REDO LOG.
• Possibilité d'utiliser le dictionnaire de la base de données.
• Détection de l'état et de la validité du dictionnaire de données de LogMiner.
• Capacité à détecter automatiquement les objets crées après la création du dictionnaire de LogMiner.
• Possibilité d'appliquer directement les ordres DML appliqués sur le dictionnaire de la base de données sur le dictionnaire de LogMiner (seulement pour le dictionnaire LogMiner contenu sur un fichier ou pour le dictionnaire LogMiner contenu dans les fichiers REDO LOG).
• Possibilité de ne pas prendre en compte les blocs corrompus.
• Affichage des ordres REDO et UNDO basés sur les informations de la clé primaire de la table.
• Nouvelle interface graphique (intégrée à OEM).
• Extraction des transactions validées et non plus de toutes les transactions.
• Accès aux données dans les fichiers REDO LOG courants.

Bien que LogMiner soit un outil très puissant, il existe quelques restrictions relatives à son utilisation. Par exemple :

• LogMiner ne supporte pas les objets de type LONG et LOBS, les objects types, les collections (nested table et varrays), les objects ref, les index organized tables.
• Pour le Direct Path la clause LOGGING doit être utilisée et la base doit être en mode ARCHIVELOG.

Dans ces exemples j'ai utilisé une base Oracle 9.0.2 en mode NOARCHIVE. J'ai utilisé la nouvelle fonctionnalité de LogMiner pour accéder aux REDO LOG courant. Attention toutefois à ne pas utiliser celui en cours d'utilisation.

La première étape consiste à créer un Snapshot du dictionnaire de données afin de pouvoir avoir les définitions des objets présents dans la base de données.

Avant de générer notre dictionnaire nous allons initialiser le paramètre UTL_FILE_DIR grâce à la commande suivante sous Oracle9i :

SQL> ALTER system SET utl_file_dir='c:\temp\' SCOPE=spfile;

Ou en ajoutant cette ligne au fichier init.ora :

utl_file_dir=c:\temp\

Puis redémarrez la base. Ensuite lançons la création du dictionnaire de données de LogMiner.

SQL> EXECUTE Dbms_Logmnr_D.Build(dictionary_filename =>'dico.ora', 
                    dictionary_location => 'c:\temp\', 
                    options =>dbms_logmnr_d.store_in_flat_file);

Dans cet exemple nous créerons notre dictionnaire dans un fichier plat (il est aussi possible de créer ce dictionnaire dans les fichiers REDO LOG avec l'option store_in_redo_logs ou bien d'utiliser le dictionnaire de données courant, cela sera spécifié lors du démarrage de l'instance LogMiner).

Exemple de création du dictionnaire de données dans les fichiers REDO LOG :

SQL> EXECUTE DBMS_LOGMNR_D.BUILD (options => DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);

Voici un exemple de contenu du dictionnaire de LogMiner (qui est en fait un fichier texte).

CREATE_TABLE DICTIONARY_TABLE ( DB_NAME VARCHAR2(9), DB_ID NUMBER(20), DB_CREATED VARCHAR2(20), DB_DICT_CREATED VARCHAR2(20),
 DB_RESETLOGS_CHANGE# NUMBER(22), DB_RESETLOGS_TIME VARCHAR2(20), DB_VERSION_TIME VARCHAR2(20), DB_REDO_TYPE_ID VARCHAR2(8), 
 DB_REDO_RELEASE VARCHAR2(60), DB_CHARACTER_SET VARCHAR2(30), DB_VERSION VARCHAR2(64), DB_STATUS VARCHAR2(64), 
 DB_DICT_MAXOBJECTS NUMBER(22), DB_DICT_OBJECTCOUNT NUMBER(22), DB_DICT_SCN NUMBER(22), DB_THREAD_MAP RAW(8), 
 DB_TXN_SCNBAS NUMBER(22), DB_TXN_SCNWRP NUMBER(22));

INSERT_INTO DICTIONARY_TABLE VALUES ('GALAXY3',3604002028,'01/27/2004 17:00:28','04/28/2004 13:32:42',
190578,'01/27/2004 17:00:32','01/27/2004 17:00:28','REDODATA','9.2.0.0.0','WE8MSWIN1252','9.2.0.1.0','Production',
31981,29966,1209043,,1360684,0);

CREATE_TABLE OBJ$_TABLE (OBJ# NUMBER(22), DATAOBJ# NUMBER(22), OWNER# NUMBER(22), NAME VARCHAR2(30), NAMESPACE NUMBER(22), 
SUBNAME VARCHAR2(30), TYPE# NUMBER(22), CTIME DATE, MTIME DATE, STIME DATE, STATUS NUMBER(22), REMOTEOWNER VARCHAR2(30), 
LINKNAME VARCHAR2(128), FLAGS NUMBER(22), OID$ RAW(16), SPARE1 NUMBER(22), SPARE2 NUMBER(22), SPARE3 NUMBER(22), 
SPARE4 VARCHAR2(1000), SPARE5 VARCHAR2(1000), SPARE6 DATE ); 

INSERT_INTO OBJ$_TABLE VALUES (30,30,0,'I_COBJ#',4,'',1,to_date('05/12/2002 16:18:00', 'MM/DD/YYYY HH24:MI:SS'),
to_date('05/12/2002 16:18:00', 'MM/DD/YYYY HH24:MI:SS'),to_date('05/12/2002 16:18:00', 'MM/DD/YYYY HH24:MI:SS'),1,'','',
0,,0,65535,,'','', );
INSERT_INTO OBJ$_TABLE VALUES (8,8,0,'C_FILE#_BLOCK#',5,'',3,to_date('05/12/2002 16:17:59', 'MM/DD/YYYY HH24:MI:SS'),
to_date('05/12/2002 16:17:59', 'MM/DD/YYYY HH24:MI:SS'),to_date('05/12/2002 16:17:59', 'MM/DD/YYYY HH24:MI:SS'),1,'','',
0,,0,65535,,'','', );
INSERT_INTO OBJ$_TABLE VALUES (14,8,0,'SEG$',1,'',2,to_date('05/12/2002 16:17:59', 'MM/DD/YYYY HH24:MI:SS'),
to_date('05/12/2002 16:17:59', 'MM/DD/YYYY HH24:MI:SS'),to_date('05/12/2002 16:17:59', 'MM/DD/YYYY HH24:MI:SS'),1,'','',
0,,0,1,,'','', );
INSERT_INTO OBJ$_TABLE VALUES (34,34,0,'I_UNDO1',4,'',1,to_date('05/12/2002 16:18:00', 'MM/DD/YYYY HH24:MI:SS'),
to_date('05/12/2002 16:18:00', 'MM/DD/YYYY HH24:MI:SS'),to_date('05/12/2002 16:18:00', 'MM/DD/YYYY HH24:MI:SS'),1,'','',
0,,0,65535,,'','', );
...

Une fois le dictionnaire de LogMiner crée, il va falloir lui spécifier manuellement les fichiers REDO LOG (et les fichiers REDO LOG archivés) que l'on souhaite analyser.

Le premier fichier REDO LOG à analyser devra être ajouté avec la commande suivante :

SQL> EXECUTE Dbms_Logmnr.Add_Logfile(options => Dbms_Logmnr.New, logfilename =>'<chemin complet>');

Ensuite on rajoutera les fichiers REDO LOG suivants avec la commande

SQL> EXECUTE Dbms_Logmnr.Add_Logfile( options => Dbms_Logmnr.AddFile, logfilename => '<chemin complet>');

Si vous avez ajouté un mauvais fichier REDO LOG voici la commande pour le supprimer

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '<chemin complet>', OPTIONS => DBMS_LOGMNR.REMOVEFILE);

Une fois les fichiers REDO LOG ajoutés, nous allons ouvrir une session de travail LogMiner.

Si l'on souhaite travailler avec tout les REDO LOG ajoutés :

SQL> EXECUTE Dbms_Logmnr.Start_Logmnr(dictfilename =>'C:\temp\dico.ora');

Sur une période donnée :

SQL> EXECUTE Dbms_Logmnr.Start_Logmnr(dictfilename =>'C:\temp\dico.ora', 
                        starttime => to_date('01-Jan-2001 00:00:00', 'DD-MON-YYYY HH:MI:SS'), 
                        endtime => to_date('01-Jan-2001 10:00:00', 'DD-MON-YYYY HH:MI:SS'));

En spécifiant un numéro de SCN :

SQL> EXECUTE Dbms_Logmnr.Start_Logmnr(dictfilename =>'C:\temp\dico.ora', 
                        startscn => 100, 
                        endscn => 150);

Il existe de nombreuses options (à spécifier avec le paramètre options) pour démarrer la session LogMiner, voici les plus intéressantes :

• COMMITTED_DATA_ONLY : N'affichera dans les vues que les données qui auront été validées. Ce qui vous permettra de ne pas polluer vos résultats avec les données non annulées ou encore en cours de transactions.
• DICT _FROM_ONLINE_CATALOG : Permet de démarrer une session LogMiner sans avoir généré un dictionnaire dans un fichier plat ou dans les REDO LOGs.
• DICT_FROM_REDO_LOGS : Permet de spécifier à LogMiner qu'il va trouver son propre dictionnaire dans un des REDO LOGs que vous aurez spécifié avec la commande DBMS_LOGMNR.ADD_LOGFILE.
• CONTINUOUS_MINE : Permet d'ajouter automatiquement tout les fichiers REDO LOG archivés et en cours, vous n'avez besoin de spécifier qu'un seul fichier REDO LOG.

Il est bien sur possible de combiner plusieurs options à la fois comme dans l'exemple suivant :

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS =>     DBMS_LOGMNR.DDL_DICT_TRACKING + 
        DBMS_LOGMNR.NO_DICT_RESET_ONSELECT + 
        DBMS_LOGMNR.DICT_FROM_REDO_LOGS);

Il existe 4 vues dynamiques liées à notre session LogMiner (celles-ci n'existent que pendant notre session LogMiner)

• V$LOGMNR_DICTIONARY : Cette vue contient les informations sur le dictionnaire de données de LogMiner.
• V$LOGMNR_PARAMETERS : Cette vue contient les informations concernant la session LogMiner en cours.
• V$LOGMNR_LOGS : Cette vue contient les informations sur tous les fichiers REDO LOG qui ont été ajoutés pour cette session.
• V$LOGMNR_CONTENTS : Cette vue contient les informations issues des fichiers REDO LOG.

Maintenant que nous avons lancé notre session LogMiner nous allons rechercher toutes les informations qui ont été effectuées sur la table EMP du user SCOTT.

SELECT scn, operation, sql_redo, sql_undo
FROM v$logmnr_contents
WHERE seg_owner='SCOTT' AND seg_name='EMP';

Et ensuite il nous restera juste à clôturer notre session LogMiner avec la commande

SQL> EXECUTE Dbms_Logmnr.end_logmnr;

Recherche d'action sur une table en particulier.

Dans cet exemple on va considérer que des manipulations incorrectes ont été effectuées sur la table EMP de l'utilisateur SCOTT.

Avant toute chose (et pour gagner du temps) nous allons choisir notre REDO LOG. Il est évident que les actions suivantes ne sont pas nécessaires dans un environnement de production.

SQL> CONNECT / AS sysdba
Connecté.

SQL> ALTER SYSTEM SWITCH LOGFILE;
Système modifié.

SQL> SELECT * FROM v$log WHERE STATUS ='CURRENT';

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TI
------------- --------
         2          1         15  104857600          1 NO  CURRENT
      1468349 29/04/04

Voici les manipulations incorrectes à effectuer en tant qu'utilisateur SCOTT:

CONNECT scott/tiger@<votre base>;
UPDATE emp SET SAL=1000;
UPDATE emp SET SAL=10000 WHERE ename='KING';
COMMIT;

SQL> connect / as sysdba
Connecté.

SQL> ALTER SYSTEM SWITCH LOGFILE;
Système modifié.

SQL> SELECT * FROM v$log WHERE STATUS ='CURRENT';

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TI
------------- --------
         3          1         16  104857600          1 NO  CURRENT
      1468800 29/04/04

Avec cet état nous savons donc que le REDO LOG à fournir à LogMiner sera un des REDO LOG du groupe 2.

On crée maintenant le dictionnaire de données pour LogMiner

SQL> EXECUTE Dbms_Logmnr_D.Build(dictionary_filename =>'dico.ora', 
            dictionary_location => 'C:\temp\', 
            options =>dbms_logmnr_d.store_in_flat_file);

Procédure PL/SQL terminée avec succès.

Maintenant que notre dictionnaire est prêt on lui rajoute notre REDO LOG .

SQL> EXECUTE Dbms_Logmnr.Add_Logfile(options => Dbms_Logmnr.New, 
            logfilename =>'C:\oracle\oradata\GALAXY3\REDO02.LOG');

Procédure PL/SQL terminée avec succès.

Ensuite on démarre notre session LogMiner en lui spécifiant que l'on ne souhaite que les transactions validées.

SQL> EXECUTE Dbms_Logmnr.Start_Logmnr(dictfilename =>'C:\temp\dico.ora', 
            options=>DBMS_LOGMNR.COMMITTED_DATA_ONLY);

Procédure PL/SQL terminée avec succès.

Ensuite on fait une recherche sur le segment EMP

SQL> select operation, sql_undo, sql_redo
  2  from v$logmnr_contents
  3  where seg_name='EMP';

OPERATION
--------------------------------
SQL_UNDO
---------------------------------------------------------------------------
SQL_REDO
---------------------------------------------------------------------------
UPDATE
update "SCOTT"."EMP" set "SAL" = '800' where "SAL" = '1000' and ROWID = 'AAAHwMAABAAAMUiAAA';
update "SCOTT"."EMP" set "SAL" = '1000' where "SAL" = '800' and ROWID = 'AAAHwMAABAAAMUiAAA';

UPDATE
update "SCOTT"."EMP" set "SAL" = '1600' where "SAL" = '1000' and ROWID = 'AAAHwMAABAAAMUiAAB';
update "SCOTT"."EMP" set "SAL" = '1000' where "SAL" = '1600' and ROWID = 'AAAHwMAABAAAMUiAAB';

...

15 ligne(s) sélectionnée(s).

J'ai volontairement coupé le résultat. On remarquera donc que les actions Redo et Undo sont basées sur le ROWID.

On ferme notre session LogMiner

SQL> EXECUTE Dbms_Logmnr.end_logmnr;

Ici on va régénérer le même état, mais cette fois-ci on affichera en plus les informations sur la clé primaire de notre table.

On configure la colonne EMPNO de la table EMP pour être une clé primaire.

SQL> ALTER table scott.emp add constraint empno_pk primary key (empno);
Table modifiée.

Pour afficher les ordres Undo et Redo avec des informations basées sur la clé primaire, il nous faudra demander à Oracle de stocker de manière explicite un surcroît d'informations dans les fichiers REDO LOGs.

Au niveau de la base de données

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

Au niveau de la table

ALTER TABLE scott.emp add supplemental log group emp_group_1 (empno);

Ensuite on relance toutes les actions du LogMiner et on obtient le résultat suivant :

OPERATION
--------------------------------
SQL_UNDO
---------------------------------------------------------------------------
SQL_REDO
---------------------------------------------------------------------------

UPDATE
update "SCOTT"."EMP" set "SAL" = '1000' where "EMPNO" = '7839' and "SAL" = '10000' and ROWID = 'AAAHwMAABAAAMUiAAI';
update "SCOTT"."EMP" set "SAL" = '10000' where "EMPNO" = '7839' and "SAL" = '1000' and ROWID = 'AAAHwMAABAAAMUiAAI';

...

Depuis la version Oracle 9i, l'interface graphique OEM c'est vu ajouter une nouvelle section LogMiner viewer. Cette nouvelle interface graphique n'est disponible que sur un serveur OEM (et donc non disponible en mode autonome).

Première étape : se connecter au serveur OEM (vous devrez l'avoir installer préalablement)

Ensuite on lance le LogMiner Viewer

Nous arrivons alors sur l'interface principale du LogMiner Viewer.

L'exemple suivant utilisera les options par défaut. La première tâche a effectuer ici sera de créer notre interrogation. Je vous laisse le soin de regarder les différentes options qui sont à votre disposition dans cet outil graphique. Voici donc la création de l'interrogation ainsi que son résultat

Ensuite il vous suffira de cliquer sur Exécuter et LogMiner Viewer exécutera de manière automatique les actions nécessaires.

V$LOGMNR_DICTIONARY
http://download-west.oracle.com/docs/cd/A91202_01/901_doc/server.901/a90190/ch3105.htm#988491

V$LOGMNR_PARAMETERS
http://download-west.oracle.com/docs/cd/A91202_01/901_doc/server.901/a90190/ch3107.htm

V$LOGMNR_LOGS
http://download-west.oracle.com/docs/cd/A91202_01/901_doc/server.901/a90190/ch3106.htm

V$LOGMNR_CONTENTS
http://download-west.oracle.com/docs/cd/A91202_01/901_doc/server.901/a90190/ch3104.htm

Tahiti Pas si paradisiaque que cela.
http://tahiti.oracle.com

LogMiner
http://download-west.oracle.com/docs/cd/A91202_01/901_doc/server.901/a90117/logminer.htm

Découvrez les autres nouveautés d'Oracle 9i avec l'article de SheikYerbouti
https://sheikyerbouti.developpez.com/evol-9i/