Utilisation du LogMiner Oracle 8i : analyse des fichiers de redo log


1- Introduction

L'utilitaire Oracle LogMiner permet de lire les informations contenues dans les fichiers de redo log on line et archivés. Celui-ci est particulièrement pratique pour identifier et annuler des corruptions logiques (delete sur une table etc..). L'utilitaire LogMiner analyse les fichiers de redo log, convertissant leur contenu en des commandes SQL qui représentent les opérations logiques réalisées sur la base de données.

Cet article se propose de présenter la mise en œuvre de l'utilitaire LogMiner et les vues associées pour extraire les informations dans les fichiers de redo log.

2- Création du fichier dictionnaire

2-1- Généralités sur le fichier dictionnaire

L'utilitaire LogMiner fonctionne pour une instance avec une base de données montée ou non montée, il utilise un fichier de dictionnaire qui est un fichier spécial qui précise la base de données pour laquelle le fichier a été créé ainsi que la date et l'heure de la création. Le fichier de dictionnaire n'est pas indispensable mais fortement recommandé.

Sans le fichier dictionnaire, les commandes SQL ne seront pas traduites et l'utilitaire LogMiner affichera les identifiants des objets et les valeurs hexadécimales. Par exemple, sans le fichier dictionnaire la commande SQL :

INSERT INTO emp(name, salary) VALUES ('John Doe', 50000);

sera affichée par le LogMiner sour la forme :

insert into Object#2581(col#1, col#2) values (hextoraw('4a6f686e20446f65'),
hextoraw('c306'));"

La création du fichier dictionnaire consiste à monter la base de données et à extraire les informations du dictionnaire dans un fichier externe. Il est impératif de créer le fichier dictionnaire pour la même base de données qui a généré les fichiers de redo log en ligne et archivés. Une fois le fichier de dictionnaire créé, les fichiers de redo log en ligne ou archivés peuvent être analysés.

Lors de la création du fichier dictionnaire, les paramètres ci-dessous sont spécifiés :

2-2- Prérequis et création du fichier dictionnaire avec DBMS_LOGMNR_D.BUILD

Pour créer le fichier dictionnaire pour les versions Oracle 8i et supérieures :

1. le paramètre d'initialisation utl_file_dir doit être valide, ce paramètre doit pointer vers un répertoire valide, répertoire dans lequel le fichier dictionnaire sera généré. Pour retrouver la valeur du paramètre utl_file_dir :

selectvalue from v$parameter where name='utl_file_dir'
value
-----------------------------------------
/Software/oracle/Instances/OEMD1ORA/udump

Si ce paramètre n'est pas valide, le fichier d'initialisation de l'instance init<INSTANCE_NAME>.ora doit être modifié pour faire pointer ce paramètre vers un répertoire valide et l'instance doit être redémarré car il s'agit d'un paramètre statique.

2. lancer la procédure stockée PL/SQL BUILD du package DBMS_LOGMNR_D en spécifiant le nom du fichier dictionnaire et le répertoire où sera généré le fichier qui doit être le même que celui indiqué par le paramètre utl_file_dir :

execute DBMS_LOGMNR_D.BUILD (-
             DICTIONARY_FILENAME => 'dictionary.ora', -
             DICTIONARY_LOCATION => '/Software/oracle/Instances/OEMD1ORA/udump');

2-3- Bug de la procédure DBMS_LOGMNR_D.BUILD avec Oracle 8.1.7.0 / 8.1.7.1

Avec Oracle 8.1.7.0 / 8.1.7.1, il existe un bug lors de la génération du fichier dictionary.ora :

SQL> execute dbms_logmnr_d.build(-
>         dictionary_filename => 'dictionary.ora', -
>         dictionary_location => '/Software/oracle/Instances/OEMD1ORA/udump');
BEGIN dbms_logmnr_d.build( dictionary_filename => 'dictionary.ora', dictionary_
location => '/Software/oracle/Instances/OEMD1ORA/udump'); END;
*
ERROR at line 1:
ORA-06532: Subscript outside of limit
ORA-06512: at "SYS.DBMS_LOGMNR_D", line 793
ORA-06512: at line 1

Si il n'est pas possible d'upgrader la version Oracle vers une version 8.1.7.2 qui corrige ce problème, le contournement est le suivant :

3- Spécification des fichiers de redo log pour analyse : DBMS_LOGMNR.ADD_LOGFILE

Une fois le fichier de dictionnaire créé, l'analyse des fichiers de redo log peut démarrer. La première étape consiste à donner les fichiers de redo log à analyser avec la procédure ADD_LOGFILE du package DBMS_LOGMNR. Les constantes ci-dessous sont utilisées avec la procédure ADD_LOGFILE :

Constante Action
NEW créé une nouvelle liste de fichiers de redo log à analyser
ADDFILE ajoute des redo logs à la liste
REMOVEFILE supprime des redo logs à la liste

3-1- Création d'une nouvelle liste de fichiers de redo log

A l'issue de la création du fichier dictionnaire, une liste de fichiers de redo log doit être initialisée pour le LogMiner avec l'option NEW de la procédure DBMS_LOGMNR.ADD_LOGFILE et en spécifiant un premier fichier de redo log à analyser :

sqlplus "/ as sysdba"
SQL > exec DBMS_LOGMNR.ADD_LOGFILE (-
        Logfilename => '/cgcdb/oracle/OEMD1ORA/archivelog/T1_S199.arc', -
        Options => dbms_logmnr.NEW);

3-2- Ajout de fichiers de redo log à la liste du LogMiner

Des fichiers de redo log peuvent être ajoutés à l'analyse du LogMiner avec l'option ADDFILE de la procédure DBMS_LOGMNR.ADD_LOGFILE :

sqlplus "/ as sysdba"
SQL > exec DBMS_LOGMNR.ADD_LOGFILE (-
        Logfilename => '/cgcdb/oracle/OEMD1ORA/archivelog/T1_S200.arc', -
        Options => dbms_logmnr.ADDFILE);
        
SQL > exec DBMS_LOGMNR.ADD_LOGFILE (-
        Logfilename => '/cgcdb/oracle/OEMD1ORA/archivelog/T1_S201.arc', -
        Options => dbms_logmnr.ADDFILE);

3-3- Suppression de fichiers de redo log à la liste du LogMiner

Des fichiers de redo log peuvent être supprimés de l'analyse du LogMiner avec l'option REMOVEFILE de la procédure DBMS_LOGMNR.ADD_LOGFILE :

sqlplus "/ as sysdba"
SQL > exec DBMS_LOGMNR.ADD_LOGFILE (-
        Logfilename => '/cgcdb/oracle/OEMD1ORA/archivelog/T1_S201.arc', -
        Options => dbms_logmnr.REMOVEFILE);

3-4- Vue V$LOGMNR_LOGS

La vue V$LOGMNR_LOGS donne les informations sur les fichiers de redo logs paramétrés avec DBMS_LOGMNR.ADD_LOGFILE pour une analyse avec l'utilitaire LogMiner :

select log_id,
       db_name,
       filename,
       to_char(low_time, 'dd/mm/yyyy hh24:mi:ss'),
       to_char(high_time, 'dd/mm/yyyy hh24:mi:ss'),
       low_scn,
       next_scn
from v$logmnr_logs;
log_id db_name filename                                      low_time         high_time        low_scn next_scn
------ ------- --------------------------------------------- ---------------- ---------------- ------- --------
199    OEM     /cgcdb/oracle/OEMD1ORA/archivelog/T1_S199.arc 04/03/2005 02:22 10/03/2005 23:19 28443   48899
200    OEM     /cgcdb/oracle/OEMD1ORA/archivelog/T1_S200.arc 10/03/2005 23:19 11/03/2005 00:25 48899   49046

La vue V$LOGMNR_LOGS indique bien que deux fichiers de redo log sont paramétrés pour une analyse avec l'utilitaire LogMiner.

Par ailleurs, des informations complémentaires sont donnés sur les fichiers de redo logs : à titre d'exemple, avec les colonnes low_time, high_time, le fichier de redo log T1_S199.arc contient les transactions et changements survenus entre le 04/03/2005 02 :22 :11 et le 10/03/2005 23 :19 :18.

4- Utilisation du LogMiner

4-1- Démarrage du LogMiner : DBMS_LOGMNR.START_LOGMNR

La procédure DBMS_LOGMNR.START_LOGMNR doit être lancée pour démarrer l'analyse des fichiers de redo logs par l'utilitaire LogMiner. Cette procédure accepte des paramètres, mais le paramètre DICTFILENAME qui spécifie le fichier de dictionnaire à utiliser est obligatoire.

SQL > exec dbms_logmnr.start_logmnr( -
        dictfilename => '/Software/oracle/Instances/OEMD1ORA/udump/dictionary.ora');

4-1-1- Paramètres optionnels

4-1-1-1- Recherche par intervalles de temps : STARTTIME et ENDTIME

Optionnellement, les paramètres STARTTIME et ENDTIME permettent de filtrer l'analyse par intervalle de temps. Ces paramètres STARTTIME et ENDTIME sont des dates.

SQL > exec dbms_logmnr.start_logmnr( -
        dictfilename => '/Software/oracle/Instances/OEMD1ORA/udump/dictionary.ora', -
        starttime => to_date('09/03/2005 23:59:59', 'DD/MM/YYYY HH24:MI:SS'), -
        endtime => to_date('11/03/2005 00:00:00', 'DD/MM/YYYY HH24:MI:SS') );
4-1-1-2- Recherche par intervalles de SCN (System Change Number) : STARTSCN et ENDSCN

Optionnellement, les paramètres STARTSCN et ENDSCN permettent de filtrer l'analyse par intervalle de SCN (System Change Number).

SQL > exec dbms_logmnr.start_logmnr( -
        dictfilename => '/Software/oracle/Instances/OEMD1ORA/udump/dictionary.ora', -
        startscn => 48899), -
        endscn => 49000 );

4-1-2- Vues V$LOGMNR_DICTIONARY et V$LOGMNR_PARAMETERS

La vue V$LOGMNR_PARAMETERS permet de retrouver rapidement avec quelles options ont été lancées l'utilitaire LogMiner :

select to_char(start_date,'dd/mm/yyyy hh24:mi:ss') as starttime,
       to_char(end_date, 'dd/mm/yyyy hh24:mi:ss') as endtime,
       start_scn as startscn,
       end_scn as endscn
from v$logmnr_parameters;
STARTTIME        ENDTIME          STARTSCN ENDSCN
---------------- ---------------- -------- ------
09/03/2005 23:59 11/03/2005 00:00 0        0

La vue V$LOGMNR_DICTIONARY permet de retrouver le fichier dictionnaire utilisé par le LogMiner, sa date de création et la base de données correspondante :

select to_char(timestamp,'dd/mm/yyyy hh24:mi:ss') as created_file,
       db_name,
       filename
from v$logmnr_dictionary;
CREATED_FILE     DB_NAME FILENAME
---------------- ------- --------------------------------------------------------
11/03/2005 00:25 OEM     /Software/oracle/Instances/OEMD1ORA/udump/dictionary.ora

4-2- Consultation des informations avec V$LOGMNR_CONTENTS

4-2-1- Généralités sur la vue V$LOGMNR_CONTENTS

La vue V$LOGMNR_CONTENTS recense toutes les informations analysées par l'utilitaire LogMiner dans les fichiers de redo log spécifiés. Parmi les colonnes très utiles dans la vue V$LOGMNR_CONTENTS :

desc V$LOGMNR_CONTENTS ;
Colonne        Type             Description
------------   --------------   ----------------------------------------------------
SCN NUMBER     System           change number de l'enregistrement de redo
TIMESTAMP      DATE             Date et heure de l'enregistrement de redo
LOG_ID         NUMBER           ID du fichier de redo log (donné par V$LOGMNR_LOGS)
...            ...              ...
SEG_OWNER      VARCHAR2(32)     Propriétaire du segment impacté
SEG_NAME       VARCHAR2(32)     Nom du segment
SEG_TYPE       NUMBER           Type de segment
TABLE_SPACE    VARCHAR2(32)     Tablespace de localisation du segment
USERNAME       VARCHAR2(32)     Utilisateur de l'enregistrement de redo
SESSION_INFO   VARCHAR2(4000)   Info sur la session qui a généré le redo
ROLLBACK       NUMBER           Rollback sur le changement ou pas ?
OPERATION      VARCHAR2(32)     Opération réalisée
SQL_REDO       VARCHAR2(4000)   Commande SQL 
SQL_UNDO       VARCHAR2(4000)   Commande SQL d'annulation 

La vue V$LOGMNR_CONTENTS liste les commandes SQL reconstruites qui représentent les opérations originales (colonne SQL_REDO) et la commande SQL correspondante pour annuler les opérations (colonne SQL_UNDO). Il suffit d'appliquer les commandes indiquées dans la colonne SQL_UNDO pour annuler les changements originaux réalisés dans une base de données.

Bien plus, la vue V$LOGMNR_CONTENTS permet :

4-2-2- Restrictions pour Oracle 8i

Le LogMiner n'obtient des informations que sur les opérations DML pour les tables conventionnelles, la vue V$LOGMNR_CONTENTS ne supporte pas les opérations sur :

L'utilitaire LogMiner ne supporte pas également les opérations d'insertion en mode direct path, même si les opérations sont loguées.

4-2-3- Exemple pratique

Recherche des opérations réalisées sur la table DUMMY par l'utilisateur SCOTT

select log_id,
       to_char(timestamp,'dd/mm/yyyy hh24:mi:ss'),
       seg_owner, seg_name, seg_type,
       table_space,
       sql_redo, sql_undo,
       username, session_info,
       operation, rollback
from v$logmnr_contents
where username='SCOTT'
      and seg_name='DUMMY'
LOG_ID    TIMESTAMP    SEG_OWNER    SEG_NAME    SEG_TYPE    TABLE_SPACE
------    ---------    ---------    --------    --------    -----------
200       10/03/2005   SCOTT        DUMMY       TABLE       USERS
          23:29:57


SQL_REDO                                            SQL_UNDO
--------                                            --------
insert into "SCOTT"."DUMMY"("DUMMY") values (0);    delete from "SCOTT"."DUMMY" where "DUMMY" = 0
                                                    and ROWID = 'AAAAugAADAAAAAXAAA';


USERNAME     SESSION_INFO                               OPERATION    ROLLBACK
--------     ------------                               ---------    --------
SCOTT        LoginUserName = SCOTT, ClientInfo = ,      INSERT       0
             OsUserName = CGC\Admin,
             MachineName = MSHOME\CGC

La consultation de V$LOGMNR_CONTENTS montre qu'une insertion a été réalisée à 23 :29 :57 le 10/03/2005 dans la table DUMMY (Owner : SCOTT) et toutes les informations sur la session qui a réalisé l'opération sont données.

Pour annuler cette opération, la colonne SQL_UNDO donne la commande SQL qui permet d'annuler cette insertion :

delete from "SCOTT"."DUMMY" where "DUMMY" = 0 and ROWID = 'AAAAugAADAAAAAXAAA';

4-3- Arrêt du LogMiner

Pour arrêter le LogMiner pour la session courante :

SQL > execute DBMS_LOGMNR.END_LOGMNR();

Annexe

Historique

Version Date Commentaires
1.0 03/2005 Version initiale

Liens

Oracle9i Database Administrator's Guide Release 2 (9.2), Using LogMiner to Analyze Redo Logs
Oracle9i Supplied PL/SQL Packages and Types Reference, DBMS_LOGMNR
Oracle9i Supplied PL/SQL Packages and Types Reference, DBMS_LOGMNR_D
Books OnLine Oracle 9i R2
Oracle