3 juin 2021
LogMiner 1 : initialisation et démarrage; pas si simple que ça - LogMiner 1 : initialization and startup; not that simple
Introduction
LogMiner est un outil Oracle extrêmement puissant. Il permet d'auditer les opérations DML et DDL survenues sur une base, identifier qui les a exécutées, quand mais aussi comment les annuler voir les réexécuter. Bref, la richesse de cet outil fait que vous devez le connaître en tant que DBA mais son utilisation n'est pas si simple que cela. J'en veux pour preuve que la documentation que j'avais rédigée sur Oracle 11g n'est plus utilisable sur Oracle 12c, peut-être à cause de la nouvelle architecture multi-tenants qui a entraîné de grands bouleversements dans les outils Oracle.
Ayant récupéré une VM Oracle 19, j'ai voulu retravailler sur LogMiner car cet outil est vraiment très utile. Nous verrons que son initialisation est toujours aussi complexe, d'autant plus que la doc Oracle n'est pas très friendly pour les débutants.
Le coeur de LogMiner est composé des packages DBMS_LOGMNR, DBMS_LOGMNR_D et de la vue V$LOGMNR_CONTENTS.
Attention aux infos suivantes
- Oracle 19c : pas de nom de table ou de colonne de plus de 30 caractères
- vue V$LOGMNR_CONTENTS :
- vous devez avoir le privilège SYSDBA ou LOGMINING pour la requêter
- ses enregistrements sont triés par le SCN des opérations
- elle est différente des autre vues Oracle car les données ne sont pas présentes par défaut dans cette vue mais sont extraites à chaque requête depuis les redo logs du disque dur
Attention aux infos suivantes
- Oracle 19c : pas de nom de table ou de colonne de plus de 30 caractères
- vue V$LOGMNR_CONTENTS :
- vous devez avoir le privilège SYSDBA ou LOGMINING pour la requêter
- ses enregistrements sont triés par le SCN des opérations
- elle est différente des autre vues Oracle car les données ne sont pas présentes par défaut dans cette vue mais sont extraites à chaque requête depuis les redo logs du disque dur
Autres articles sur LogMiner :
- LogMiner 2 : les archived redologs, le mode archive log, la FRA
- LogMiner 3 : infos utiles pour les ordres DML et DDL
- LogMiner 4 : impossible de récupérer le contenu d'une table droppée par erreur
Points d'attention
N/A.
Base de tests
Une base Oracle 19c multi-tenants.
Exemples
============================================================================================
Activation des supplemental logging
============================================================================================
Ma base est une 19c, architecture multi-tenants.
SQL> select banner, cdb from v$version, v$database;
BANNER CDB
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production YES
Je suis dans le CDB$ROOT.
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
Première chose à faire, modifier le paramètre d'affichage des dates pour avoir l'heure.
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
Session altered.
L'activation des supplemental logging est un pré-requis au lancement de LogMiner. De ce que j'ai compris, "SUPPLEMENTAL LOG DATA" signifie qu'on veut ajouter dans les redo log (LOG) des informations (DATA) supplémentaires (SUPPLEMENTAL) sur les enregistrements modifiés par les opérations DML et sur les opérations DDL. Par défaut, ils ne sont pas activés.
SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;
SUPPLEME
--------
NO
J'ai essayé de lancer la requête ci-dessous mais ça coince... Pourtant ici https://docs.oracle.com/en/database/oracle/oracle-database/19/sutil/oracle-logminer-utility.html#GUID-D2DDD67C-E1CC-45A6-A2A7-198E4C142FA3 Oracle nous conseille cet ordre SQL.
En 11g j'avais utilisé cette commande et elle passait sans problème mais en 19, avec l'architecture CDB, beaucoup de choses ont changé.
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA
*
ERROR at line 1:
ORA-31541: Supplemental logging is not enabled in CDB$ROOT.
Si je me mets dans une PDB, c'est pas mieux, l'opération doit se faire dans le CDB$ROOT.
SQL> alter session set container = ORCL;
Session altered.
SQL> alter pluggable database add supplemental log data;
alter pluggable database add supplemental log data
*
ERROR at line 1:
ORA-31541: Supplemental logging is not enabled in CDB$ROOT.
Je repasse dans le CDB$ROOT et, en cherchant sur le net, je tombe sur cette commande qui marche mieux: visiblement il faut dire à Oracle quoi logger en plus quand des opérations DML ont lieu.
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, FOREIGN KEY, UNIQUE INDEX) COLUMNS;
Database altered.
On vérifie que c'est OK. Extrait de la doc : "IMPLICIT - Minimal supplemental logging is enabled because all or a combination of primary key, unique key, and foreign key supplemental logging is enabled".
SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;
SUPPLEME
--------
IMPLICIT
Pour supprimer les supplemental logging, il existe une commande d'après la doc Oracle qui, pas de chance, ne marche pas... décidemment, cette doc n'est pas facile à utiliser.
SQL> ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;
ALTER DATABASE DROP SUPPLEMENTAL LOG DATA
*
ERROR at line 1:
ORA-32589: unable to drop minimal supplemental logging
============================================================================================
Spécifier un dictionnaire LogMiner
============================================================================================
La prochaine étape est de dire à LogMiner quel dictionnaire de données il doit utiliser. C'est indispensable pour que les id des objets affichés dans LogMiner soient remplacés par leur nom, pour plus de lisibilité. Oracle préconise d'utiliser le catalogue en ligne, c'est à dire le dictionnaire de données : "To direct LogMiner to use the dictionary currently in use for the database, specify the online catalog as your dictionary source when you start LogMiner." Nous avons le choix : soit on utilise DBMS_LOGMNR_D.BUILD pour créer un dictionnaire pour LogMiner puis on démarre l'outil soit on spécifie le dictionnaire à utiliser quand on démarre LogMiner.
Démarrons LogMiner en lui disant d'utiliser le catalogue en ligne. Et zut, erreur : pb de logfile introuvable.
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
BEGIN DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG); END;
*
ERROR at line 1:
ORA-01291: missing log file
ORA-06512: at "SYS.DBMS_LOGMNR", line 72
ORA-06512: at line 1
J'essaye une autre syntaxe, avec des dates de début et de fin mais encore un échec. Nous sommes le 1er juin 2021.
SQL> select first_change#, first_time, sequence# from V$log where status = 'CURRENT';
FIRST_CHANGE# FIRST_TIME SEQUENCE#
------------- -------------------- ----------
3675521 30-MAY-2021 17:08:39 29
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR( STARTTIME => '30-May-2021 08:30:00', ENDTIME => '01-Jun-2021 08:45:00', OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG );
BEGIN DBMS_LOGMNR.START_LOGMNR( STARTTIME => '30-May-2021 08:30:00', ENDTIME => '01-Jun-2021 08:45:00', OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG ); END;
*
ERROR at line 1:
ORA-01291: missing log file
ORA-06512: at "SYS.DBMS_LOGMNR", line 72
ORA-06512: at line 1
OK, je regarde alors quelles sont les dates min et max dans les redo logs utilisés.
SQL> select GROUP#, STATUS, SEQUENCE#, ARCHIVED, FIRST_CHANGE#, FIRST_TIME, NEXT_CHANGE# , NEXT_TIME from v$log order by 1;
GROUP# STATUS SEQUENCE# ARC FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------------- ---------- --- ------------- -------------------- ------------
1 INACTIVE 28 NO 3599131 08-MAY-2021 08:08:58 3675521 30-MAY-2021 17:08:39
2 CURRENT 29 NO 3675521 30-MAY-2021 17:08:39 1.8447E+19
3 INACTIVE 27 NO 3437908 01-MAY-2021 10:54:20 3599131 08-MAY-2021 08:08:58
Ca coince encore!
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR( STARTTIME => '01-MAY-2021 10:54:20', ENDTIME => '30-MAY-2021 17:08:39', OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG );
BEGIN DBMS_LOGMNR.START_LOGMNR( STARTTIME => '01-MAY-2021 10:54:20', ENDTIME => '30-MAY-2021 17:08:39', OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG ); END;
*
ERROR at line 1:
ORA-01291: missing log file
ORA-06512: at "SYS.DBMS_LOGMNR", line 72
ORA-06512: at line 1
Bon, visiblement il faut que je dise d'abord à Oracle quels sont les fichiers redo logs à utiliser pour son dictionnaire de données. La liste des redologs non archivés est la suivante.
SQL> select GROUP#, STATUS, MEMBER from v$logfile order by 1;
GROUP# STATUS MEMBER
-------------------------------------------------------------------
1 /u01/app/oracle/oradata/ORCLCDB/redo01.log
2 /u01/app/oracle/oradata/ORCLCDB/redo02.log
3 /u01/app/oracle/oradata/ORCLCDB/redo03.log
Attention au piège : pour le premier fichier à ajouter, utilisez la procédure DBMS_LOGMNR.NEW alors que pour les autres ce sera DBMS_LOGMNR.ADDFILE. Je n'ajoute que les redologs non archivés; pour les archivés nous verrons un autre jour.
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '/u01/app/oracle/oradata/ORCLCDB/redo01.log', OPTIONS => DBMS_LOGMNR.NEW);
PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '/u01/app/oracle/oradata/ORCLCDB/redo02.log', OPTIONS => DBMS_LOGMNR.ADDFILE);
PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '/u01/app/oracle/oradata/ORCLCDB/redo03.log', OPTIONS => DBMS_LOGMNR.ADDFILE);
PL/SQL procedure successfully completed.
Une fois les fichiers redologs paramétrés, LogMiner accepte enfin de démarrer.
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
PL/SQL procedure successfully completed.
============================================================================================
La vue V$LOGMNR_CONTENTS
============================================================================================
Il existe plusieurs vues dynamiques de performances pour LogMiner. La plus importante est V$LOGMNR_CONTENTS.
SQL> select distinct object_name from dba_objects where object_name like 'V$%LOGMNR%' or object_name like 'CDB%LOGMNR%' order by 1;
OBJECT_NAME
-----------------------------------
CDB_LOGMNR_LOG
CDB_LOGMNR_PROFILE_PLSQL_STATS
CDB_LOGMNR_PROFILE_TABLE_STATS
CDB_LOGMNR_PURGED_LOG
CDB_LOGMNR_SESSION
V$LOGMNR_CONTENTS
V$LOGMNR_DICTIONARY
V$LOGMNR_DICTIONARY_LOAD
V$LOGMNR_LATCH
V$LOGMNR_LOGFILE
V$LOGMNR_LOGS
V$LOGMNR_PARAMETERS
V$LOGMNR_PROCESS
V$LOGMNR_SESSION
V$LOGMNR_STATS
V$LOGMNR_TRANSACTION
16 rows selected.
La vue V$LOGMNR_CONTENTS est-elle accessible depuis le CDB$ROOT? Oui!, c'est bon, l'initialisation de LogMiner est terminée, on peut enfin interroger l'historique des opérations de notre base :-)
SQL> select count(*) from V$LOGMNR_CONTENTS;
COUNT(*)
----------
726169
Comme vous le voyez, cette vue est très riche.
SQL> desc v$LOGMNR_CONTENTS
Name Null? Type
-----------------------------------
SCN NUMBER
START_SCN NUMBER
COMMIT_SCN NUMBER
TIMESTAMP DATE
START_TIMESTAMP DATE
COMMIT_TIMESTAMP DATE
XIDUSN NUMBER
XIDSLT NUMBER
XIDSQN NUMBER
XID RAW(8)
PXIDUSN NUMBER
PXIDSLT NUMBER
PXIDSQN NUMBER
PXID RAW(8)
TX_NAME VARCHAR2(256)
OPERATION VARCHAR2(32)
OPERATION_CODE NUMBER
ROLLBACK NUMBER
SEG_OWNER VARCHAR2(386)
SEG_NAME VARCHAR2(256)
TABLE_NAME VARCHAR2(386)
SEG_TYPE NUMBER
SEG_TYPE_NAME VARCHAR2(32)
TABLE_SPACE VARCHAR2(92)
ROW_ID VARCHAR2(18)
USERNAME VARCHAR2(384)
OS_USERNAME VARCHAR2(4000)
MACHINE_NAME VARCHAR2(4000)
AUDIT_SESSIONID NUMBER
SESSION# NUMBER
SERIAL# NUMBER
SESSION_INFO VARCHAR2(4000)
THREAD# NUMBER
SEQUENCE# NUMBER
RBASQN NUMBER
RBABLK NUMBER
RBABYTE NUMBER
UBAFIL NUMBER
UBABLK NUMBER
UBAREC NUMBER
UBASQN NUMBER
ABS_FILE# NUMBER
REL_FILE# NUMBER
DATA_BLK# NUMBER
DATA_OBJ# NUMBER
DATA_OBJV# NUMBER
DATA_OBJD# NUMBER
SQL_REDO VARCHAR2(4000)
SQL_UNDO VARCHAR2(4000)
RS_ID VARCHAR2(32)
SSN NUMBER
CSF NUMBER
INFO VARCHAR2(64)
STATUS NUMBER
REDO_VALUE NUMBER
UNDO_VALUE NUMBER
SAFE_RESUME_SCN NUMBER
CSCN NUMBER
OBJECT_ID RAW(16)
EDITION_NAME VARCHAR2(384)
CLIENT_ID VARCHAR2(64)
SRC_CON_NAME VARCHAR2(384)
SRC_CON_ID NUMBER
SRC_CON_UID NUMBER
SRC_CON_DBID NUMBER
SRC_CON_GUID RAW(16)
CON_ID NUMBER
Oh, j'ai perdu plein d'opérations avec ma tentative de création d'une table... Comment cela s'explique-t-il? Vous avez-une idée?
SQL> desc ZZLOGMNR_CONTENTS
ERROR:
ORA-04043: object ZZLOGMNR_CONTENTS does not exist
SQL> select count(*) FROM V$LOGMNR_CONTENTS;
COUNT(*)
----------
86574
A noter que la vue V$LOGMNR_CONTENTS n'est pas accessible depuis une PDB mais on peut quand même faire un DESC dessus.
SQL> alter session set container = ORCL;
Session altered.
SQL> select count(*) FROM V$LOGMNR_CONTENTS;
select count(*) FROM V$LOGMNR_CONTENTS
*
ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database
SQL> desc V$LOGMNR_CONTENTS
Name Null? Type
----------------------------------------- -------- ----------------------------
SCN NUMBER
START_SCN NUMBER
...
Oracle recommande, si possible, de recopier le contenu de cette vue dans une table pour éviter les multiples accès aux disques durs à chaque requête. En clair, on prend un snapshot ou un instantané de la vue puis toutes les requêtes se feront sur la nouvelle table. Je la crée dans le schéma SYS du CDB$ROOT. Attention : toute nouvelle opération SQL ne sera pas dans cette nouvelle vue.
Ah, c'est pas si simple que ça... Bon, je ne vais pas aller plus loin, c'est une optimisation de LogMiner et le but de l'article est de présenter les étapes indispensables pour démarrer cet outil.
SQL> CREATE TABLE ZZLOGMNR_CONTENTS AS SELECT * FROM V$LOGMNR_CONTENTS;
CREATE TABLE ZZLOGMNR_CONTENTS AS SELECT * FROM V$LOGMNR_CONTENTS
*
ERROR at line 1:
ORA-00310: archived log contains sequence 30; sequence 27 required
ORA-00334: archived log: '/u01/app/oracle/oradata/ORCLCDB/redo03.log'
============================================================================================
Interroger l'historique de la base avec V$LOGMNR_CONTENTS
============================================================================================
Retournons dans le CDB$ROOT et voyons ce que contiennent nos redologs pour chaque user.
SQL> select SEG_OWNER, OPERATION, COUNT(*)
from V$LOGMNR_CONTENTS
group by SEG_OWNER, OPERATION
order by SEG_OWNER, OPERATION;
SEG_OWNER OPERATION COUNT(*)
------------------------------ ------
SYS DELETE 2
SYS INSERT 298
SYS INTERNAL 1
SYS LOB_WRITE 3
SYS SEL_LOB_LOCATOR 1
SYS UPDATE 96
UNKNOWN DIRECT INSERT 86372
COMMIT 90
INTERNAL 108
ROLLBACK 13
START 102
11 rows selected.
OK, rien avec le user HR... eh bien on va créer des opérations DML avec ce user et voir si elles apparaissent bien dans la vue. A noter qu'avant l'Insert je fais un Truncate de la table zz1 qui contenait 100 000 rows.
SQL> connect HR
Enter password:
Connected.
SQL> show con_name
CON_NAME
------------------------------
ORCL
SQL> insert into zz1 values (1000, 'DUPONT', 'MICHEL', 1000);
1 row created.
SQL> commit;
Commit complete.
Je me reconnecte comme SYS et là, je découvre que ma session LogMiner s'est terminée quand j'ai changé de user.
SQL> connect SYS as sysdba
Enter password:
Connected.
SQL> select SEG_OWNER, OPERATION, COUNT(*)
from V$LOGMNR_CONTENTS
group by SEG_OWNER, OPERATION
order by SEG_OWNER, OPERATION;
from V$LOGMNR_CONTENTS
*
ERROR at line 2:
ORA-01306: dbms_logmnr.start_logmnr() must be invoked before selecting from v$logmnr_contents
Bon, on redémarre LogMiner.
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
BEGIN DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG); END;
*
ERROR at line 1:
ORA-01291: missing log file
ORA-06512: at "SYS.DBMS_LOGMNR", line 72
ORA-06512: at line 1
Ah oui, il faut TOUT refaire!
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '/u01/app/oracle/oradata/ORCLCDB/redo01.log', OPTIONS => DBMS_LOGMNR.NEW);
PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '/u01/app/oracle/oradata/ORCLCDB/redo02.log', OPTIONS => DBMS_LOGMNR.ADDFILE);
PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '/u01/app/oracle/oradata/ORCLCDB/redo03.log', OPTIONS => DBMS_LOGMNR.ADDFILE);
PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
PL/SQL procedure successfully completed.
Ne me demandez pas pourquoi en quelques minutes on est passé de 86574 à 688960 enregistrements. J'ai fais un Truncate sur une table de 100 000 rows en plus de l'Insert, c'est tout...
SQL> select count(*) from V$LOGMNR_CONTENTS;
COUNT(*)
----------
688960
Ah, le résultat est nettement plus excitant, j'ai des opérations du user HR :-) L'opération DDL doit être le Truncate. Bizarrement j'ai aussi une opération du user HR02 et de users qui n'étaient pas présents dans le Select précédent...
SQL> select SEG_OWNER, OPERATION, COUNT(*)
from V$LOGMNR_CONTENTS
group by SEG_OWNER, OPERATION
order by SEG_OWNER, OPERATION;
SEG_OWNER OPERATION COUNT(*)
------------------------------ -----------
AUDSYS INSERT 11
AUDSYS UPDATE 6
CTXSYS INSERT 1
DBSNMP DELETE 43
DBSNMP INSERT 48
HR DDL 1
HR DELETE 2
HR INSERT 3
HR02 DDL 3
SYS DDL 161
SYS DELETE 51768
SYS INSERT 180921
SYS INTERNAL 12
SYS LOB_WRITE 82
SYS SEL_LOB_LOCATOR 12
SYS UNSUPPORTED 121982
SYS UPDATE 30091
UNKNOWN DIRECT INSERT 232342
COMMIT 31686
DDL 3
DPI SAVEPOINT 1
INTERNAL 9276
ROLLBACK 325
START 31835
6
25 rows selected.
25 rows selected.
============================================================================================
Fin de la session LogMiner
============================================================================================
Pour fermer la session LogMiner, soit vous clôturez votre session Oracle soit vous exécutez la procédure DBMS_LOGMNR.END_LOGMNR soit vous exécutez la procédure DBMS_LOGMNR.ADD_LOGFILE avec l'option NEW.
Une fois celle-ci appelée, la vue V$LOGMNR_CONTENTS n'est plus accessible.
SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR;
PL/SQL procedure successfully completed.
SQL> select count(*) from V$LOGMNR_CONTENTS;
select count(*) from V$LOGMNR_CONTENTS
*
ERROR at line 1:
ORA-01306: dbms_logmnr.start_logmnr() must be invoked before selecting from v$logmnr_contents
Conclusion
On s'arrête là pour aujourd'hui, l'objectif était "juste" de voir comment démarrer LogMiner. Comme dit, ce n'est pas si simple, surtout que la doc, si elle est exhaustive, ne vas pas directement à l'essentiel.
/https%3A%2F%2Fassets.over-blog.com%2Ft%2Fcedistic%2Fcamera.png)
Publicité
Publicité
Commentaires
/https%3A%2F%2Fprofilepics.canalblog.com%2Fprofilepics%2F1%2F5%2F1555182.jpg)