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.
Création du fichier dictionnaire
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 :
dictionary_filename: nom du fichier dictionnairedictionary_location: localisation du fichier dictionnaire
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 :
Étape 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 :
select value 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 est 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.
Étape 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');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 :
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 :
- éditer le fichier
dbmslmd.sqllocalisé dans le répertoire$ORACLE_HOME/rdbms/adminet remplacer la ligneTYPE col_desc_array IS VARRAY(513) OF col_descriptionpar la ligne
TYPE col_desc_array IS VARRAY(700) OF col_description - Exécuter le script
dbmslmd.sqlmodifié plus haut pour recompiler le packageDBMS_LOGMNR_D:sqlplus "/ as sysdba"@?/rdbms/admin/dbmslmd.sql; - Recompiler ensuite le corps du package
DBMS_LOGMNR_D:sqlplus "/ as sysdba"alter package DBMS_LOGMNR_D compile body ;
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 |
Création d’une nouvelle liste de fichiers de redo log
À 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 :
exec DBMS_LOGMNR.ADD_LOGFILE (-
Logfilename => '/cgcdb/oracle/OEMD1ORA/archivelog/T1_S199.arc', -
Options => dbms_logmnr.NEW);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 :
exec DBMS_LOGMNR.ADD_LOGFILE (-
Logfilename => '/cgcdb/oracle/OEMD1ORA/archivelog/T1_S200.arc', -
Options => dbms_logmnr.ADDFILE);
exec DBMS_LOGMNR.ADD_LOGFILE (-
Logfilename => '/cgcdb/oracle/OEMD1ORA/archivelog/T1_S201.arc', -
Options => dbms_logmnr.ADDFILE);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 :
exec DBMS_LOGMNR.ADD_LOGFILE (-
Logfilename => '/cgcdb/oracle/OEMD1ORA/archivelog/T1_S201.arc', -
Options => dbms_logmnr.REMOVEFILE);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.
Utilisation du LogMiner
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.
exec dbms_logmnr.start_logmnr( -
dictfilename => '/Software/oracle/Instances/OEMD1ORA/udump/dictionary.ora');Paramètres optionnels
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.
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') );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).
exec dbms_logmnr.start_logmnr( -
dictfilename => '/Software/oracle/Instances/OEMD1ORA/udump/dictionary.ora', -
startscn => 48899), -
endscn => 49000 );Vues V$LOGMNR_DICTIONARY et V$LOGMNR_PARAMETERS
La vue V$LOGMNR_PARAMETERS donne les options utilisées lors du lancement du 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 indiquele 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
Consultation des informations avec V$LOGMNR_CONTENTS
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 :
- de déterminer quand la corruption logique s’est produite
- de traquer les changements spécifiques sur une table
- de traquer les changements réalisés par un user en particulier
- de visualiser l’accès aux données
- avec un archivage de données de faire du tuning et du capacity planning.
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 :
- les tables IOT
- les index/tables clustered
- les types de données non scalaires
- les lignes chaînées
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.
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';Arrêt du LogMiner
Pour arrêter le LogMiner pour la session courante :
execute DBMS_LOGMNR.END_LOGMNR();