LogMiner 4 : impossible de récupérer le contenu d'une table droppée par erreur - unable to recover a table dropped by mistake

25 juillet 2021

LogMiner 4 : impossible de récupérer le contenu d'une table droppée par erreur - unable to recover a table dropped by mistake

Introduction
Dans cet article nous allons voir pourquoi LogMiner ne permet pas de récupérer les données d'une table droppée par erreur. Cet utilitaire est excellent pour auditer les opérations survenues sur une base mais, bizarrement, il ne permet pas de gérer la suppression accidentelle d'une table.

Autres articles sur LogMiner :
- LogMiner 1 : initialisation et démarrage; pas si simple que ça
- LogMiner 2 : les archived redologs, le mode archive log, la FRA
- LogMiner 3 : infos utiles pour les ordres DML et DDL

Article au format texte, pour plus de lisibilité : Blog_DBA_Oracle_LogMiner_4

Points d'attention
N/A.

Base de tests
Une base Oracle 19c multi-tenants.

Exemples
============================================================================================
Base de tests
============================================================================================
Il arrive qu'un DBA ou un développeur supprime par erreur une table. Nous avons vu ici comment faire un PITR sur table avec RMAN : http://dbaoraclesql.canalblog.com/archives/2020/06/21/38384934.html

Néanmoins, si les sauvegardes ne remontent pas jusqu'à la date de suppression, cette méthode n'est pas envisageable. Idem pour la commande FLASHBACK TABLE... : il faut que la corbeille (recyclebin) soit activée, qu'elle n'ait pas été purgée, que la taille de la table tienne dans la corbeille et qu'il n'y ait pas eu d'autres objets droppés après qui ont éjecté de la corbeille la table à récupérer.

Bref, je pensais alors utiliser LogMiner pour recréer la table droppée, régénérer ses données avec les ordres DML (INSERT, UPDATE, DELETE) des redo logs mais ce n'est pas si simple que ça.

Nous sommes le 19/07/2021, à 03:52:08 (décalage horaire dû à ma VM). Je suis connecté à ma PDB ORCL.
SQL> select sysdate from dual;
SYSDATE
_______________________
19-JUL-2021 03:52:08

SQL> show user con_name
USER is "HR"
CON_NAME
------------------------------
ORCL

Contenu de ma table.
SQL> select * from zz1 order by ID;
ID NAME FIRST_NAME
---------- ------------
1 ARINAGE DAVID
6 DUPONT DAVID
7 DUPONT JEAN
8 DUPONT JEAN
9 DUPONT JEAN
10 DUPONT JEAN
6 rows selected.

Contenu des redologs archivés pour la table HR.ZZ1
Je vous renvoie vers mes posts précédents pour voir comment activer LogMiner et ce que j'ai mis dedans. Les dernières opérations sur ma table datent du 24 juin.
SQL> select XIDUSN || '-' || XIDSLT || '-' || XIDSQN AS "Trans ID", TIMESTAMP, SQL_REDO, SQL_UNDO from V$LOGMNR_CONTENTS where SEG_OWNER = 'HR' and SEG_NAME = 'ZZ1' order by TIMESTAMP, "Trans ID";
Trans ID TIMESTAMP SQL_REDO SQL_UNDO
____________________________________________________________________________________________________________________________
11-11-386 22-JUN-2021 09:17:54 ALTER TABLE "HR"."ZZ1" RENAME CONSTRAINT "SYS_C0012440" TO "BIN$xVueHmhsfybgUwEAAH/baQ==$0" ;
11-11-386 22-JUN-2021 09:17:54 drop table zz1 AS "BIN$xVueHmhxfybgUwEAAH/baQ==$0" ;
11-11-386 22-JUN-2021 09:17:54 ALTER TABLE "HR"."ZZ1" RENAME TO "BIN$xVueHmhxfybgUwEAAH/baQ==$0" ;
11-11-386 22-JUN-2021 09:17:54 ALTER TABLE "HR"."ZZ1" RENAME CONSTRAINT "ZZ1_CK_UPPER_NAME" TO "BIN$xVueHmhvfybgUwEAAH/baQ==$0" ;
11-11-386 22-JUN-2021 09:17:54 ALTER TABLE "HR"."ZZ1" RENAME CONSTRAINT "SYS_C0012442" TO "BIN$xVueHmhufybgUwEAAH/baQ==$0" ;
11-11-386 22-JUN-2021 09:17:54 ALTER TABLE "HR"."ZZ1" RENAME CONSTRAINT "SYS_C0012441" TO "BIN$xVueHmhtfybgUwEAAH/baQ==$0" ;
15-25-504 22-JUN-2021 09:21:37 create table ZZ1 (ID NUMBER(8) PRIMARY KEY, NAME VARCHAR2(50 CHAR) NOT NULL, FIRST_NAME VARCHAR2(50 CHAR) NOT NULL);
16-5-515 24-JUN-2021 09:56:11 insert into "HR"."ZZ1"("ID","NAME","FIRST_NAME") values ('8','DUPONT','DAVID'); delete from "HR"."ZZ1" where "ID" = '8' and "NAME" = 'DUPONT' and "FIRST_NAME" = 'DAVID' and ROWID = 'AAATmwAAMAAAMc3AAH';
16-5-515 24-JUN-2021 09:56:11 insert into "HR"."ZZ1"("ID","NAME","FIRST_NAME") values ('7','DUPONT','DAVID'); delete from "HR"."ZZ1" where "ID" = '7' and "NAME" = 'DUPONT' and "FIRST_NAME" = 'DAVID' and ROWID = 'AAATmwAAMAAAMc3AAG';
16-5-515 24-JUN-2021 09:56:11 insert into "HR"."ZZ1"("ID","NAME","FIRST_NAME") values ('6','DUPONT','DAVID'); delete from "HR"."ZZ1" where "ID" = '6' and "NAME" = 'DUPONT' and "FIRST_NAME" = 'DAVID' and ROWID = 'AAATmwAAMAAAMc3AAF';
16-5-515 24-JUN-2021 09:56:11 insert into "HR"."ZZ1"("ID","NAME","FIRST_NAME") values ('5','DUPONT','DAVID'); delete from "HR"."ZZ1" where "ID" = '5' and "NAME" = 'DUPONT' and "FIRST_NAME" = 'DAVID' and ROWID = 'AAATmwAAMAAAMc3AAE';
16-5-515 24-JUN-2021 09:56:11 insert into "HR"."ZZ1"("ID","NAME","FIRST_NAME") values ('4','DUPONT','DAVID'); delete from "HR"."ZZ1" where "ID" = '4' and "NAME" = 'DUPONT' and "FIRST_NAME" = 'DAVID' and ROWID = 'AAATmwAAMAAAMc3AAD';
16-5-515 24-JUN-2021 09:56:11 insert into "HR"."ZZ1"("ID","NAME","FIRST_NAME") values ('3','DUPONT','DAVID'); delete from "HR"."ZZ1" where "ID" = '3' and "NAME" = 'DUPONT' and "FIRST_NAME" = 'DAVID' and ROWID = 'AAATmwAAMAAAMc3AAC';
16-5-515 24-JUN-2021 09:56:11 insert into "HR"."ZZ1"("ID","NAME","FIRST_NAME") values ('2','DUPONT','DAVID'); delete from "HR"."ZZ1" where "ID" = '2' and "NAME" = 'DUPONT' and "FIRST_NAME" = 'DAVID' and ROWID = 'AAATmwAAMAAAMc3AAB';
16-5-515 24-JUN-2021 09:56:11 insert into "HR"."ZZ1"("ID","NAME","FIRST_NAME") values ('1','DUPONT','DAVID'); delete from "HR"."ZZ1" where "ID" = '1' and "NAME" = 'DUPONT' and "FIRST_NAME" = 'DAVID' and ROWID = 'AAATmwAAMAAAMc3AAA';
16-5-515 24-JUN-2021 09:56:11 insert into "HR"."ZZ1"("ID","NAME","FIRST_NAME") values ('9','DUPONT','DAVID'); delete from "HR"."ZZ1" where "ID" = '9' and "NAME" = 'DUPONT' and "FIRST_NAME" = 'DAVID' and ROWID = 'AAATmwAAMAAAMc3AAI';
16-5-515 24-JUN-2021 09:56:11 insert into "HR"."ZZ1"("ID","NAME","FIRST_NAME") values ('10','DUPONT','DAVID'); delete from "HR"."ZZ1" where "ID" = '10' and "NAME" = 'DUPONT' and "FIRST_NAME" = 'DAVID' and ROWID = 'AAATmwAAMAAAMc3AAJ';
16-5-515 24-JUN-2021 09:56:33 insert into "HR"."ZZ1"("ID","NAME","FIRST_NAME") values ('11','DURAND','PIERRE'); delete from "HR"."ZZ1" where "ID" = '11' and "NAME" = 'DURAND' and "FIRST_NAME" = 'PIERRE' and ROWID = 'AAATmwAAMAAAMc3AAK';
16-5-515 24-JUN-2021 09:56:41 update "HR"."ZZ1" set "FIRST_NAME" = 'JEAN' where "ID" = '7' and "FIRST_NAME" = 'DAVID' and ROWID = 'AAATmwAAMAAAMc3AAG'; update "HR"."ZZ1" set "FIRST_NAME" = 'DAVID' where "ID" = '7' and "FIRST_NAME" = 'JEAN' and ROWID = 'AAATmwAAMAAAMc3AAG';
16-5-515 24-JUN-2021 09:56:41 update "HR"."ZZ1" set "FIRST_NAME" = 'JEAN' where "ID" = '8' and "FIRST_NAME" = 'DAVID' and ROWID = 'AAATmwAAMAAAMc3AAH'; update "HR"."ZZ1" set "FIRST_NAME" = 'DAVID' where "ID" = '8' and "FIRST_NAME" = 'JEAN' and ROWID = 'AAATmwAAMAAAMc3AAH';
16-5-515 24-JUN-2021 09:56:41 update "HR"."ZZ1" set "FIRST_NAME" = 'JEAN' where "ID" = '9' and "FIRST_NAME" = 'DAVID' and ROWID = 'AAATmwAAMAAAMc3AAI'; update "HR"."ZZ1" set "FIRST_NAME" = 'DAVID' where "ID" = '9' and "FIRST_NAME" = 'JEAN' and ROWID = 'AAATmwAAMAAAMc3AAI';
16-5-515 24-JUN-2021 09:56:41 update "HR"."ZZ1" set "FIRST_NAME" = 'JEAN' where "ID" = '10' and "FIRST_NAME" = 'DAVID' and ROWID = 'AAATmwAAMAAAMc3AAJ'; update "HR"."ZZ1" set "FIRST_NAME" = 'DAVID' where "ID" = '10' and "FIRST_NAME" = 'JEAN' and ROWID = 'AAATmwAAMAAAMc3AAJ';
16-5-515 24-JUN-2021 09:56:41 update "HR"."ZZ1" set "FIRST_NAME" = 'JEAN' where "ID" = '11' and "FIRST_NAME" = 'PIERRE' and ROWID = 'AAATmwAAMAAAMc3AAK'; update "HR"."ZZ1" set "FIRST_NAME" = 'PIERRE' where "ID" = '11' and "FIRST_NAME" = 'JEAN' and ROWID = 'AAATmwAAMAAAMc3AAK';
16-5-515 24-JUN-2021 09:57:15 update "HR"."ZZ1" set "NAME" = 'ARINAGE' where "ID" = '1' and "NAME" = 'DUPONT' and ROWID = 'AAATmwAAMAAAMc3AAA'; update "HR"."ZZ1" set "NAME" = 'DUPONT' where "ID" = '1' and "NAME" = 'ARINAGE' and ROWID = 'AAATmwAAMAAAMc3AAA';
16-5-515 24-JUN-2021 09:57:28 delete from "HR"."ZZ1" where "ID" = '3' and "NAME" = 'DUPONT' and "FIRST_NAME" = 'DAVID' and ROWID = 'AAATmwAAMAAAMc3AAC'; insert into "HR"."ZZ1"("ID","NAME","FIRST_NAME") values ('3','DUPONT','DAVID');
16-5-515 24-JUN-2021 09:57:28 delete from "HR"."ZZ1" where "ID" = '4' and "NAME" = 'DUPONT' and "FIRST_NAME" = 'DAVID' and ROWID = 'AAATmwAAMAAAMc3AAD'; insert into "HR"."ZZ1"("ID","NAME","FIRST_NAME") values ('4','DUPONT','DAVID');
16-5-515 24-JUN-2021 09:57:28 delete from "HR"."ZZ1" where "ID" = '5' and "NAME" = 'DUPONT' and "FIRST_NAME" = 'DAVID' and ROWID = 'AAATmwAAMAAAMc3AAE'; insert into "HR"."ZZ1"("ID","NAME","FIRST_NAME") values ('5','DUPONT','DAVID');
16-5-515 24-JUN-2021 09:57:41 delete from "HR"."ZZ1" where "ID" = '11' and "NAME" = 'DURAND' and "FIRST_NAME" = 'JEAN' and ROWID = 'AAATmwAAMAAAMc3AAK'; insert into "HR"."ZZ1"("ID","NAME","FIRST_NAME") values ('11','DURAND','JEAN');
18-15-503 24-JUN-2021 09:58:48 delete from "HR"."ZZ1" where "ID" = '2' and "NAME" = 'DUPONT' and "FIRST_NAME" = 'DAVID' and ROWID = 'AAATmwAAMAAAMc3AAB'; insert into "HR"."ZZ1"("ID","NAME","FIRST_NAME") values ('2','DUPONT','DAVID');
14-2-381 24-JUN-2021 10:01:06 update "HR"."ZZ1" set "NAME" = 'DUBOIS' where "ID" = '8' and "NAME" = 'DUPONT' and ROWID = 'AAATmwAAMAAAMc3AAH'; update "HR"."ZZ1" set "NAME" = 'DUPONT' where "ID" = '8' and "NAME" = 'DUBOIS' and ROWID = 'AAATmwAAMAAAMc3AAH';
14-2-381 24-JUN-2021 10:01:34 update "HR"."ZZ1" set "NAME" = 'DUPONT' where ROWID = 'AAATmwAAMAAAMc3AAH';
31 rows selected.

============================================================================================
Dropper la table : perte des opérations DML dans les redo logs
============================================================================================
Suppression de la table HR.ZZ1
SQL> drop table HR.ZZ1;
Table dropped.

Argh... dans V$LOGMNR_CONTENTS les opérations DML de ma table ont été effacées... on est passé de 31 enregistrements à 7... bizarre, très bizarre! Peut-être parce que la table HR.ZZ1 n'existe plus dans le dictionnaire de données et donc les opérations dans les redologs archivés ne sont plus affichables par Oracle.
SQL> select XIDUSN || '-' || XIDSLT || '-' || XIDSQN AS "Trans ID", TIMESTAMP, SQL_REDO, SQL_UNDO from V$LOGMNR_CONTENTS where SEG_OWNER = 'HR' and SEG_NAME = 'ZZ1' order by TIMESTAMP, "Trans ID";
Trans ID TIMESTAMP SQL_REDO SQL_UNDO
__________________________________________________________________________________________________
11-11-386 22-JUN-2021 09:17:54 ALTER TABLE "HR"."ZZ1" RENAME CONSTRAINT "SYS_C0012440" TO "BIN$xVueHmhsfybgUwEAAH/baQ==$0" ;
11-11-386 22-JUN-2021 09:17:54 ALTER TABLE "HR"."ZZ1" RENAME CONSTRAINT "SYS_C0012441" TO "BIN$xVueHmhtfybgUwEAAH/baQ==$0" ;
11-11-386 22-JUN-2021 09:17:54 ALTER TABLE "HR"."ZZ1" RENAME CONSTRAINT "SYS_C0012442" TO "BIN$xVueHmhufybgUwEAAH/baQ==$0" ;
11-11-386 22-JUN-2021 09:17:54 ALTER TABLE "HR"."ZZ1" RENAME CONSTRAINT "ZZ1_CK_UPPER_NAME" TO "BIN$xVueHmhvfybgUwEAAH/baQ==$0" ;
11-11-386 22-JUN-2021 09:17:54 ALTER TABLE "HR"."ZZ1" RENAME TO "BIN$xVueHmhxfybgUwEAAH/baQ==$0" ;
11-11-386 22-JUN-2021 09:17:54 drop table zz1 AS "BIN$xVueHmhxfybgUwEAAH/baQ==$0" ;
15-25-504 22-JUN-2021 09:21:37 create table ZZ1 (ID NUMBER(8) PRIMARY KEY, NAME VARCHAR2(50 CHAR) NOT NULL, FIRST_NAME VARCHAR2(50 CHAR) NOT NULL);
7 rows selected.

Je vois que dans la FRA les fichiers archived redologs sont en lecture/écriture, je pensais qu'ils seraient en lecture seule pour tout le monde... est-ce que suite à mon DROP TABLE Oracle aurait supprimé des entrées dans les archived redologs? Non, c'est improbable.
[oracle@localhost archivelog]$ pwd
/u01/app/oracle/fast_recovery_area/ORCLCDB/archivelog

[oracle@localhost archivelog]$ ls -lR
...
./2021_06_24:
total 53000
-rw-r-----. 1 oracle oinstall 54262784 Jun 24 10:22 o1_mf_1_44_jf9593s1_.arc
-rw-r-----. 1 oracle oinstall 1536 Jun 24 10:22 o1_mf_1_45_jf9595nm_.arc
-rw-r-----. 1 oracle oinstall 3072 Jun 24 10:22 o1_mf_1_46_jf95979n_.arc

./2021_07_19:
total 56600
-rw-r-----. 1 oracle oinstall 57938944 Jul 19 05:12 o1_mf_1_47_jhbjjpkx_.arc
-rw-r-----. 1 oracle oinstall 1536 Jul 19 05:12 o1_mf_1_48_jhbjjrb8_.arc
-rw-r-----. 1 oracle oinstall 8704 Jul 19 05:12 o1_mf_1_49_jhbjjvw2_.arc

Restauration de la table par FLASHBACK : opérations DML visibles mais modifiées
C'est intrigant, et que se passe-t-il si j'undrop la table?
SQL> SHOW RECYCLEBIN;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ -----------
ZZ1 BIN$xVueHmh2fybgUwEAAH/baQ==$0 TABLE 2021-07-19:04:38:51

SQL> FLASHBACK TABLE HR.ZZ1 TO BEFORE DROP;
Flashback complete.

La table est revenue, avec ses données; cool!
SQL> select * from zz1 order by ID;
ID NAME FIRST_NAME
---------- -------------------
1 ARINAGE DAVID
6 DUPONT DAVID
7 DUPONT JEAN
8 DUPONT JEAN
9 DUPONT JEAN
10 DUPONT JEAN
6 rows selected.

En revanche je ne vois pas le nouveau CREATE TABLE ou l'opération de flashback... normal car je n'ai pas mis les redologs online dans ma session logminer. On voit que les opérations du 24 juin existent dans les archived redologs, elles sont réapparues comme par magie MAIS les colonnes SQL_REDO et SQL_UNDO ont été réécrites suite au drop et undrop de ma table; HEXTORAW('c107') n'est quand même pas hyper lisible. De ce que je comprends, c'est le SELECT sur V$LOGMNR_CONTENTS qui modifie l'affichage du contenu des redologs et filtre les objets inconnus du dictionnaire de données.
SQL> select XIDUSN || '-' || XIDSLT || '-' || XIDSQN AS "Trans ID", TIMESTAMP, SQL_REDO, SQL_UNDO from V$LOGMNR_CONTENTS where SEG_OWNER = 'HR' and SEG_NAME = 'ZZ1' order by TIMESTAMP, "Trans ID"
Trans ID TIMESTAMP SQL_REDO SQL_UNDO
____________ _______________________ ______________________________ ________________________________
11-11-386 22-JUN-2021 09:17:54 ALTER TABLE "HR"."ZZ1" RENAME CONSTRAINT "SYS_C0012440" TO "BIN$xVueHmhsfybgUwEAAH/baQ==$0" ;
11-11-386 22-JUN-2021 09:17:54 drop table zz1 AS "BIN$xVueHmhxfybgUwEAAH/baQ==$0" ;
11-11-386 22-JUN-2021 09:17:54 ALTER TABLE "HR"."ZZ1" RENAME TO "BIN$xVueHmhxfybgUwEAAH/baQ==$0" ;
11-11-386 22-JUN-2021 09:17:54 ALTER TABLE "HR"."ZZ1" RENAME CONSTRAINT "ZZ1_CK_UPPER_NAME" TO "BIN$xVueHmhvfybgUwEAAH/baQ==$0" ;
11-11-386 22-JUN-2021 09:17:54 ALTER TABLE "HR"."ZZ1" RENAME CONSTRAINT "SYS_C0012442" TO "BIN$xVueHmhufybgUwEAAH/baQ==$0" ;
11-11-386 22-JUN-2021 09:17:54 ALTER TABLE "HR"."ZZ1" RENAME CONSTRAINT "SYS_C0012441" TO "BIN$xVueHmhtfybgUwEAAH/baQ==$0" ;
15-25-504 22-JUN-2021 09:21:37 create table ZZ1 (ID NUMBER(8) PRIMARY KEY, NAME VARCHAR2(50 CHAR) NOT NULL, FIRST_NAME VARCHAR2(50 CHAR) NOT NULL);
16-5-515 24-JUN-2021 09:56:11 insert into "HR"."ZZ1"("COL 1","COL 2","COL 3") values (HEXTORAW('c109'),HEXTORAW('4455504f4e54'),HEXTORAW('4441564944')); delete from "HR"."ZZ1" where "COL 1" = HEXTORAW('c109') and "COL 2" = HEXTORAW('4455504f4e54') and "COL 3" = HEXTORAW('4441564944') and ROWID = 'AAATmwAAMAAAMc3AAH';
16-5-515 24-JUN-2021 09:56:11 insert into "HR"."ZZ1"("COL 1","COL 2","COL 3") values (HEXTORAW('c108'),HEXTORAW('4455504f4e54'),HEXTORAW('4441564944')); delete from "HR"."ZZ1" where "COL 1" = HEXTORAW('c108') and "COL 2" = HEXTORAW('4455504f4e54') and "COL 3" = HEXTORAW('4441564944') and ROWID = 'AAATmwAAMAAAMc3AAG';
16-5-515 24-JUN-2021 09:56:11 insert into "HR"."ZZ1"("COL 1","COL 2","COL 3") values (HEXTORAW('c107'),HEXTORAW('4455504f4e54'),HEXTORAW('4441564944')); delete from "HR"."ZZ1" where "COL 1" = HEXTORAW('c107') and "COL 2" = HEXTORAW('4455504f4e54') and "COL 3" = HEXTORAW('4441564944') and ROWID = 'AAATmwAAMAAAMc3AAF';
16-5-515 24-JUN-2021 09:56:11 insert into "HR"."ZZ1"("COL 1","COL 2","COL 3") values (HEXTORAW('c106'),HEXTORAW('4455504f4e54'),HEXTORAW('4441564944')); delete from "HR"."ZZ1" where "COL 1" = HEXTORAW('c106') and "COL 2" = HEXTORAW('4455504f4e54') and "COL 3" = HEXTORAW('4441564944') and ROWID = 'AAATmwAAMAAAMc3AAE';
16-5-515 24-JUN-2021 09:56:11 insert into "HR"."ZZ1"("COL 1","COL 2","COL 3") values (HEXTORAW('c105'),HEXTORAW('4455504f4e54'),HEXTORAW('4441564944')); delete from "HR"."ZZ1" where "COL 1" = HEXTORAW('c105') and "COL 2" = HEXTORAW('4455504f4e54') and "COL 3" = HEXTORAW('4441564944') and ROWID = 'AAATmwAAMAAAMc3AAD';
16-5-515 24-JUN-2021 09:56:11 insert into "HR"."ZZ1"("COL 1","COL 2","COL 3") values (HEXTORAW('c104'),HEXTORAW('4455504f4e54'),HEXTORAW('4441564944')); delete from "HR"."ZZ1" where "COL 1" = HEXTORAW('c104') and "COL 2" = HEXTORAW('4455504f4e54') and "COL 3" = HEXTORAW('4441564944') and ROWID = 'AAATmwAAMAAAMc3AAC';
16-5-515 24-JUN-2021 09:56:11 insert into "HR"."ZZ1"("COL 1","COL 2","COL 3") values (HEXTORAW('c103'),HEXTORAW('4455504f4e54'),HEXTORAW('4441564944')); delete from "HR"."ZZ1" where "COL 1" = HEXTORAW('c103') and "COL 2" = HEXTORAW('4455504f4e54') and "COL 3" = HEXTORAW('4441564944') and ROWID = 'AAATmwAAMAAAMc3AAB';
16-5-515 24-JUN-2021 09:56:11 insert into "HR"."ZZ1"("COL 1","COL 2","COL 3") values (HEXTORAW('c102'),HEXTORAW('4455504f4e54'),HEXTORAW('4441564944')); delete from "HR"."ZZ1" where "COL 1" = HEXTORAW('c102') and "COL 2" = HEXTORAW('4455504f4e54') and "COL 3" = HEXTORAW('4441564944') and ROWID = 'AAATmwAAMAAAMc3AAA';
16-5-515 24-JUN-2021 09:56:11 insert into "HR"."ZZ1"("COL 1","COL 2","COL 3") values (HEXTORAW('c10a'),HEXTORAW('4455504f4e54'),HEXTORAW('4441564944')); delete from "HR"."ZZ1" where "COL 1" = HEXTORAW('c10a') and "COL 2" = HEXTORAW('4455504f4e54') and "COL 3" = HEXTORAW('4441564944') and ROWID = 'AAATmwAAMAAAMc3AAI';
16-5-515 24-JUN-2021 09:56:11 insert into "HR"."ZZ1"("COL 1","COL 2","COL 3") values (HEXTORAW('c10b'),HEXTORAW('4455504f4e54'),HEXTORAW('4441564944')); delete from "HR"."ZZ1" where "COL 1" = HEXTORAW('c10b') and "COL 2" = HEXTORAW('4455504f4e54') and "COL 3" = HEXTORAW('4441564944') and ROWID = 'AAATmwAAMAAAMc3AAJ';
16-5-515 24-JUN-2021 09:56:33 insert into "HR"."ZZ1"("COL 1","COL 2","COL 3") values (HEXTORAW('c10c'),HEXTORAW('445552414e44'),HEXTORAW('504945525245')); delete from "HR"."ZZ1" where "COL 1" = HEXTORAW('c10c') and "COL 2" = HEXTORAW('445552414e44') and "COL 3" = HEXTORAW('504945525245') and ROWID = 'AAATmwAAMAAAMc3AAK';
16-5-515 24-JUN-2021 09:56:41 update "HR"."ZZ1" set "COL 3" = HEXTORAW('4a45414e') where "COL 1" = HEXTORAW('c108') and "COL 3" = HEXTORAW('4441564944') and ROWID = 'AAATmwAAMAAAMc3AAG'; update "HR"."ZZ1" set "COL 3" = HEXTORAW('4441564944') where "COL 1" = HEXTORAW('c108') and "COL 3" = HEXTORAW('4a45414e') and ROWID = 'AAATmwAAMAAAMc3AAG';
16-5-515 24-JUN-2021 09:56:41 update "HR"."ZZ1" set "COL 3" = HEXTORAW('4a45414e') where "COL 1" = HEXTORAW('c109') and "COL 3" = HEXTORAW('4441564944') and ROWID = 'AAATmwAAMAAAMc3AAH'; update "HR"."ZZ1" set "COL 3" = HEXTORAW('4441564944') where "COL 1" = HEXTORAW('c109') and "COL 3" = HEXTORAW('4a45414e') and ROWID = 'AAATmwAAMAAAMc3AAH';
16-5-515 24-JUN-2021 09:56:41 update "HR"."ZZ1" set "COL 3" = HEXTORAW('4a45414e') where "COL 1" = HEXTORAW('c10a') and "COL 3" = HEXTORAW('4441564944') and ROWID = 'AAATmwAAMAAAMc3AAI'; update "HR"."ZZ1" set "COL 3" = HEXTORAW('4441564944') where "COL 1" = HEXTORAW('c10a') and "COL 3" = HEXTORAW('4a45414e') and ROWID = 'AAATmwAAMAAAMc3AAI';
16-5-515 24-JUN-2021 09:56:41 update "HR"."ZZ1" set "COL 3" = HEXTORAW('4a45414e') where "COL 1" = HEXTORAW('c10b') and "COL 3" = HEXTORAW('4441564944') and ROWID = 'AAATmwAAMAAAMc3AAJ'; update "HR"."ZZ1" set "COL 3" = HEXTORAW('4441564944') where "COL 1" = HEXTORAW('c10b') and "COL 3" = HEXTORAW('4a45414e') and ROWID = 'AAATmwAAMAAAMc3AAJ';
16-5-515 24-JUN-2021 09:56:41 update "HR"."ZZ1" set "COL 3" = HEXTORAW('4a45414e') where "COL 1" = HEXTORAW('c10c') and "COL 3" = HEXTORAW('504945525245') and ROWID = 'AAATmwAAMAAAMc3AAK'; update "HR"."ZZ1" set "COL 3" = HEXTORAW('504945525245') where "COL 1" = HEXTORAW('c10c') and "COL 3" = HEXTORAW('4a45414e') and ROWID = 'AAATmwAAMAAAMc3AAK';
16-5-515 24-JUN-2021 09:57:15 update "HR"."ZZ1" set "COL 2" = HEXTORAW('4152494e414745') where "COL 1" = HEXTORAW('c102') and "COL 2" = HEXTORAW('4455504f4e54') and ROWID = 'AAATmwAAMAAAMc3AAA'; update "HR"."ZZ1" set "COL 2" = HEXTORAW('4455504f4e54') where "COL 1" = HEXTORAW('c102') and "COL 2" = HEXTORAW('4152494e414745') and ROWID = 'AAATmwAAMAAAMc3AAA';
16-5-515 24-JUN-2021 09:57:28 delete from "HR"."ZZ1" where "COL 1" = HEXTORAW('c104') and "COL 2" = HEXTORAW('4455504f4e54') and "COL 3" = HEXTORAW('4441564944') and ROWID = 'AAATmwAAMAAAMc3AAC'; insert into "HR"."ZZ1"("COL 1","COL 2","COL 3") values (HEXTORAW('c104'),HEXTORAW('4455504f4e54'),HEXTORAW('4441564944'));
16-5-515 24-JUN-2021 09:57:28 delete from "HR"."ZZ1" where "COL 1" = HEXTORAW('c105') and "COL 2" = HEXTORAW('4455504f4e54') and "COL 3" = HEXTORAW('4441564944') and ROWID = 'AAATmwAAMAAAMc3AAD'; insert into "HR"."ZZ1"("COL 1","COL 2","COL 3") values (HEXTORAW('c105'),HEXTORAW('4455504f4e54'),HEXTORAW('4441564944'));
16-5-515 24-JUN-2021 09:57:28 delete from "HR"."ZZ1" where "COL 1" = HEXTORAW('c106') and "COL 2" = HEXTORAW('4455504f4e54') and "COL 3" = HEXTORAW('4441564944') and ROWID = 'AAATmwAAMAAAMc3AAE'; insert into "HR"."ZZ1"("COL 1","COL 2","COL 3") values (HEXTORAW('c106'),HEXTORAW('4455504f4e54'),HEXTORAW('4441564944'));
16-5-515 24-JUN-2021 09:57:41 delete from "HR"."ZZ1" where "COL 1" = HEXTORAW('c10c') and "COL 2" = HEXTORAW('445552414e44') and "COL 3" = HEXTORAW('4a45414e') and ROWID = 'AAATmwAAMAAAMc3AAK'; insert into "HR"."ZZ1"("COL 1","COL 2","COL 3") values (HEXTORAW('c10c'),HEXTORAW('445552414e44'),HEXTORAW('4a45414e'));
18-15-503 24-JUN-2021 09:58:48 delete from "HR"."ZZ1" where "COL 1" = HEXTORAW('c103') and "COL 2" = HEXTORAW('4455504f4e54') and "COL 3" = HEXTORAW('4441564944') and ROWID = 'AAATmwAAMAAAMc3AAB'; insert into "HR"."ZZ1"("COL 1","COL 2","COL 3") values (HEXTORAW('c103'),HEXTORAW('4455504f4e54'),HEXTORAW('4441564944'));
14-2-381 24-JUN-2021 10:01:06 update "HR"."ZZ1" set "COL 2" = HEXTORAW('4455424f4953') where "COL 1" = HEXTORAW('c109') and "COL 2" = HEXTORAW('4455504f4e54') and ROWID = 'AAATmwAAMAAAMc3AAH'; update "HR"."ZZ1" set "COL 2" = HEXTORAW('4455504f4e54') where "COL 1" = HEXTORAW('c109') and "COL 2" = HEXTORAW('4455424f4953') and ROWID = 'AAATmwAAMAAAMc3AAH';
14-2-381 24-JUN-2021 10:01:34 update "HR"."ZZ1" set "COL 2" = HEXTORAW('4455504f4e54') where ROWID = 'AAATmwAAMAAAMc3AAH';
31 rows selected.

============================================================================================
Nouveau test de suppression de table
============================================================================================
OK, je recommence le test pour m'assurer quil n'y a pas de nouvelles mauvaises surprises :-)
Les opérations du 24/06 ne sont plus visibles, normal, c'est comme le test ci-dessus.
SQL> drop table HR.ZZ1;
Table dropped.

SQL> select XIDUSN || '-' || XIDSLT || '-' || XIDSQN AS "Trans ID", TIMESTAMP, SQL_REDO, SQL_UNDO from V$LOGMNR_CONTENTS where SEG_OWNER = 'HR' and SEG_NAME = 'ZZ1' order by TIMESTAMP, "Trans ID";
Trans ID TIMESTAMP SQL_REDO SQL_UNDO
_________________________________________________________________________________________________________
11-11-386 22-JUN-2021 09:17:54 ALTER TABLE "HR"."ZZ1" RENAME CONSTRAINT "SYS_C0012440" TO "BIN$xVueHmhsfybgUwEAAH/baQ==$0" ;
11-11-386 22-JUN-2021 09:17:54 ALTER TABLE "HR"."ZZ1" RENAME CONSTRAINT "SYS_C0012441" TO "BIN$xVueHmhtfybgUwEAAH/baQ==$0" ;
11-11-386 22-JUN-2021 09:17:54 ALTER TABLE "HR"."ZZ1" RENAME CONSTRAINT "SYS_C0012442" TO "BIN$xVueHmhufybgUwEAAH/baQ==$0" ;
11-11-386 22-JUN-2021 09:17:54 ALTER TABLE "HR"."ZZ1" RENAME CONSTRAINT "ZZ1_CK_UPPER_NAME" TO "BIN$xVueHmhvfybgUwEAAH/baQ==$0" ;
11-11-386 22-JUN-2021 09:17:54 ALTER TABLE "HR"."ZZ1" RENAME TO "BIN$xVueHmhxfybgUwEAAH/baQ==$0" ;
11-11-386 22-JUN-2021 09:17:54 drop table zz1 AS "BIN$xVueHmhxfybgUwEAAH/baQ==$0" ;
15-25-504 22-JUN-2021 09:21:37 create table ZZ1 (ID NUMBER(8) PRIMARY KEY, NAME VARCHAR2(50 CHAR) NOT NULL, FIRST_NAME VARCHAR2(50 CHAR) NOT NULL);
7 rows selected.

Ajout de redo logs dans la session LogMiner
Quels sont actuellement les fichiers redo logs et archived redo logs de ma session logminer? Ils ne dépassent pas le 24 juin.
SQL> select min(timestamp), max(timestamp) from v$logmnr_contents;
MIN(TIMESTAMP) MAX(TIMESTAMP)
_______________________ _______________________
11-JUN-2021 09:49:16 24-JUN-2021 10:22:08

SQL> select filename, high_time from V$LOGMNR_LOGS order by high_time;
FILENAME HIGH_TIME
___________________________________________________________________________________
/u01/app/oracle/fast_recovery_area/ORCLCDB/archivelog/2021_06_24/o1_mf_1_44_jf9593s1_.arc 24-JUN-2021 10:22:27
/u01/app/oracle/fast_recovery_area/ORCLCDB/archivelog/2021_06_24/o1_mf_1_45_jf9595nm_.arc 24-JUN-2021 10:22:29
/u01/app/oracle/fast_recovery_area/ORCLCDB/archivelog/2021_06_24/o1_mf_1_46_jf95979n_.arc 24-JUN-2021 10:22:31

Quels sont les archived redo logs actuels? Est-ce qu'il en manque dans ma session logminer depuis le 24 juin? OK, pas de nouveaux archived redo logs depuis le 24. Je vais archiver les redo logs online et les inclure dans ma session logminer.
SQL> select name, first_time, next_time from v$archived_log order by 2;
NAME FIRST_TIME NEXT_TIME
______________________________________________________________________ ____________
/u01/app/oracle/fast_recovery_area/ORCLCDB/archivelog/2021_06_10/o1_mf_1_38_jd3znbf5_.arc 08-JUN-2021 13:42:17 10-JUN-2021 07:59:06
/u01/app/oracle/fast_recovery_area/ORCLCDB/archivelog/2021_06_10/o1_mf_1_39_jd3zs9fc_.arc 10-JUN-2021 07:59:06 10-JUN-2021 08:01:45
/u01/app/oracle/fast_recovery_area/ORCLCDB/archivelog/2021_06_10/o1_mf_1_40_jd3zskow_.arc 10-JUN-2021 08:01:45 10-JUN-2021 08:01:53
/u01/app/oracle/fast_recovery_area/ORCLCDB/archivelog/2021_06_11/o1_mf_1_41_jd6tfgx9_.arc 10-JUN-2021 08:01:53 11-JUN-2021 09:48:30
/u01/app/oracle/fast_recovery_area/ORCLCDB/archivelog/2021_06_11/o1_mf_1_42_jd6tfjlt_.arc 11-JUN-2021 09:48:30 11-JUN-2021 09:48:32
/u01/app/oracle/fast_recovery_area/ORCLCDB/archivelog/2021_06_11/o1_mf_1_43_jd6tfmps_.arc 11-JUN-2021 09:48:32 11-JUN-2021 09:48:35
/u01/app/oracle/fast_recovery_area/ORCLCDB/archivelog/2021_06_24/o1_mf_1_44_jf9593s1_.arc 11-JUN-2021 09:48:35 24-JUN-2021 10:22:27
/u01/app/oracle/fast_recovery_area/ORCLCDB/archivelog/2021_06_24/o1_mf_1_45_jf9595nm_.arc 24-JUN-2021 10:22:27 24-JUN-2021 10:22:29
/u01/app/oracle/fast_recovery_area/ORCLCDB/archivelog/2021_06_24/o1_mf_1_46_jf95979n_.arc 24-JUN-2021 10:22:29 24-JUN-2021 10:22:31
9 rows selected.

J'archive les redologs online. Je n'utilise la commande switch logfile que trois fois car j'ai seulement trois redo logs online.
SQL> select group#, sequence#, status, first_time, next_time from v$log order by group#;
GROUP# SEQUENCE# STATUS FIRST_TIME NEXT_TIME
_________ ____________ ___________ _______________________ _______________________
1 49 INACTIVE 19-JUL-2021 05:12:56 19-JUL-2021 05:12:59
2 50 CURRENT 19-JUL-2021 05:12:59
3 48 INACTIVE 19-JUL-2021 05:12:54 19-JUL-2021 05:12:56

SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.

Nous avons maintenant trois nouveaux archived redo logs, ceux dont la colonne NEXT_TIME comprends la date du 19 juillet; ils étaient bien absent du SELECT précédent.
SQL> select name, first_time, next_time from v$archived_log order by 2;
NAME FIRST_TIME NEXT_TIME
___________________________________________________________________________________
/u01/app/oracle/fast_recovery_area/ORCLCDB/archivelog/2021_06_10/o1_mf_1_38_jd3znbf5_.arc 08-JUN-2021 13:42:17 10-JUN-2021 07:59:06
/u01/app/oracle/fast_recovery_area/ORCLCDB/archivelog/2021_06_10/o1_mf_1_39_jd3zs9fc_.arc 10-JUN-2021 07:59:06 10-JUN-2021 08:01:45
/u01/app/oracle/fast_recovery_area/ORCLCDB/archivelog/2021_06_10/o1_mf_1_40_jd3zskow_.arc 10-JUN-2021 08:01:45 10-JUN-2021 08:01:53
/u01/app/oracle/fast_recovery_area/ORCLCDB/archivelog/2021_06_11/o1_mf_1_41_jd6tfgx9_.arc 10-JUN-2021 08:01:53 11-JUN-2021 09:48:30
/u01/app/oracle/fast_recovery_area/ORCLCDB/archivelog/2021_06_11/o1_mf_1_42_jd6tfjlt_.arc 11-JUN-2021 09:48:30 11-JUN-2021 09:48:32
/u01/app/oracle/fast_recovery_area/ORCLCDB/archivelog/2021_06_11/o1_mf_1_43_jd6tfmps_.arc 11-JUN-2021 09:48:32 11-JUN-2021 09:48:35
/u01/app/oracle/fast_recovery_area/ORCLCDB/archivelog/2021_06_24/o1_mf_1_44_jf9593s1_.arc 11-JUN-2021 09:48:35 24-JUN-2021 10:22:27
/u01/app/oracle/fast_recovery_area/ORCLCDB/archivelog/2021_06_24/o1_mf_1_45_jf9595nm_.arc 24-JUN-2021 10:22:27 24-JUN-2021 10:22:29
/u01/app/oracle/fast_recovery_area/ORCLCDB/archivelog/2021_06_24/o1_mf_1_46_jf95979n_.arc 24-JUN-2021 10:22:29 24-JUN-2021 10:22:31
/u01/app/oracle/fast_recovery_area/ORCLCDB/archivelog/2021_07_19/o1_mf_1_47_jhbjjpkx_.arc 24-JUN-2021 10:22:31 19-JUL-2021 05:12:54
/u01/app/oracle/fast_recovery_area/ORCLCDB/archivelog/2021_07_19/o1_mf_1_48_jhbjjrb8_.arc 19-JUL-2021 05:12:54 19-JUL-2021 05:12:56
/u01/app/oracle/fast_recovery_area/ORCLCDB/archivelog/2021_07_19/o1_mf_1_49_jhbjjvw2_.arc 19-JUL-2021 05:12:56 19-JUL-2021 05:12:59

Maintenant j'inclus ces trois archived redo logs dans ma session logminer.
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '/u01/app/oracle/fast_recovery_area/ORCLCDB/archivelog/2021_07_19/o1_mf_1_47_jhbjjpkx_.arc', OPTIONS => DBMS_LOGMNR.ADDFILE);
PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '/u01/app/oracle/fast_recovery_area/ORCLCDB/archivelog/2021_07_19/o1_mf_1_48_jhbjjrb8_.arc', OPTIONS => DBMS_LOGMNR.ADDFILE);
PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '/u01/app/oracle/fast_recovery_area/ORCLCDB/archivelog/2021_07_19/o1_mf_1_49_jhbjjvw2_.arc', OPTIONS => DBMS_LOGMNR.ADDFILE);
PL/SQL procedure successfully completed.

Et voilà la nouvelle liste des fichiers de ma session LogMiner.
SQL> select filename, high_time from V$LOGMNR_LOGS order by high_time;
FILENAME HIGH_TIME
_______________________________________________________________________________
/u01/app/oracle/fast_recovery_area/ORCLCDB/archivelog/2021_06_24/o1_mf_1_44_jf9593s1_.arc 24-JUN-2021 10:22:27
/u01/app/oracle/fast_recovery_area/ORCLCDB/archivelog/2021_06_24/o1_mf_1_45_jf9595nm_.arc 24-JUN-2021 10:22:29
/u01/app/oracle/fast_recovery_area/ORCLCDB/archivelog/2021_06_24/o1_mf_1_46_jf95979n_.arc 24-JUN-2021 10:22:31
/u01/app/oracle/fast_recovery_area/ORCLCDB/archivelog/2021_07_19/o1_mf_1_47_jhbjjpkx_.arc 19-JUL-2021 05:12:54
/u01/app/oracle/fast_recovery_area/ORCLCDB/archivelog/2021_07_19/o1_mf_1_48_jhbjjrb8_.arc 19-JUL-2021 05:12:56
/u01/app/oracle/fast_recovery_area/ORCLCDB/archivelog/2021_07_19/o1_mf_1_49_jhbjjvw2_.arc 19-JUL-2021 05:12:59
6 rows selected.

Contenu des arhived redo logs : opérations DML visibles
Nous voyons les opérations DROP TABLE et de restauration de la table avec la commande flashback.
SQL> select XIDUSN || '-' || XIDSLT || '-' || XIDSQN AS "Trans ID", TIMESTAMP, SQL_REDO, SQL_UNDO from V$LOGMNR_CONTENTS where SEG_OWNER = 'HR' and SEG_NAME = 'ZZ1' order by TIMESTAMP, "Trans ID";
Trans ID TIMESTAMP SQL_REDO SQL_UNDO
___________________________________________________________________________________
11-11-386 22-JUN-2021 09:17:54 ALTER TABLE "HR"."ZZ1" RENAME CONSTRAINT "SYS_C0012440" TO "BIN$xVueHmhsfybgUwEAAH/baQ==$0" ;
11-11-386 22-JUN-2021 09:17:54 ALTER TABLE "HR"."ZZ1" RENAME CONSTRAINT "SYS_C0012441" TO "BIN$xVueHmhtfybgUwEAAH/baQ==$0" ;
11-11-386 22-JUN-2021 09:17:54 ALTER TABLE "HR"."ZZ1" RENAME CONSTRAINT "SYS_C0012442" TO "BIN$xVueHmhufybgUwEAAH/baQ==$0" ;
11-11-386 22-JUN-2021 09:17:54 ALTER TABLE "HR"."ZZ1" RENAME CONSTRAINT "ZZ1_CK_UPPER_NAME" TO "BIN$xVueHmhvfybgUwEAAH/baQ==$0" ;
11-11-386 22-JUN-2021 09:17:54 ALTER TABLE "HR"."ZZ1" RENAME TO "BIN$xVueHmhxfybgUwEAAH/baQ==$0" ;
11-11-386 22-JUN-2021 09:17:54 drop table zz1 AS "BIN$xVueHmhxfybgUwEAAH/baQ==$0" ;
15-25-504 22-JUN-2021 09:21:37 create table ZZ1 (ID NUMBER(8) PRIMARY KEY, NAME VARCHAR2(50 CHAR) NOT NULL, FIRST_NAME VARCHAR2(50 CHAR) NOT NULL);
19-5-531 19-JUL-2021 04:38:51 ALTER TABLE "HR"."ZZ1" RENAME CONSTRAINT "SYS_C0012556" TO "BIN$xVueHmhyfybgUwEAAH/baQ==$0" ;
19-5-531 19-JUL-2021 04:38:51 ALTER TABLE "HR"."ZZ1" RENAME CONSTRAINT "SYS_C0012557" TO "BIN$xVueHmhzfybgUwEAAH/baQ==$0" ;
19-5-531 19-JUL-2021 04:38:51 drop table HR.ZZ1 AS "BIN$xVueHmh2fybgUwEAAH/baQ==$0" ;
19-5-531 19-JUL-2021 04:38:51 ALTER TABLE "HR"."ZZ1" RENAME TO "BIN$xVueHmh2fybgUwEAAH/baQ==$0" ;
19-5-531 19-JUL-2021 04:38:51 ALTER TABLE "HR"."ZZ1" RENAME CONSTRAINT "SYS_C0012558" TO "BIN$xVueHmh0fybgUwEAAH/baQ==$0" ;
15-0-531 19-JUL-2021 04:43:34 FLASHBACK TABLE HR."BIN$xVueHmh2fybgUwEAAH/baQ==$0" TO BEFORE DROP;
16-0-539 19-JUL-2021 04:49:11 ALTER TABLE "HR"."ZZ1" RENAME CONSTRAINT "BIN$xVueHmhyfybgUwEAAH/baQ==$0" TO "BIN$xVueHmh3fybgUwEAAH/baQ==$1" ;
16-0-539 19-JUL-2021 04:49:11 ALTER TABLE "HR"."ZZ1" RENAME TO "BIN$xVueHmh7fybgUwEAAH/baQ==$0" ;
16-0-539 19-JUL-2021 04:49:11 ALTER TABLE "HR"."ZZ1" RENAME CONSTRAINT "BIN$xVueHmh0fybgUwEAAH/baQ==$0" TO "BIN$xVueHmh5fybgUwEAAH/baQ==$1" ;
16-0-539 19-JUL-2021 04:49:11 ALTER TABLE "HR"."ZZ1" RENAME CONSTRAINT "BIN$xVueHmhzfybgUwEAAH/baQ==$0" TO "BIN$xVueHmh4fybgUwEAAH/baQ==$1" ;
16-0-539 19-JUL-2021 04:49:11 drop table HR.ZZ1 AS "BIN$xVueHmh7fybgUwEAAH/baQ==$0" ;
18 rows selected.

Que voit-on pour ma table?
19-JUL-2021 04:38:51 : 5 opérations liées au drop de la table et son déplacement dans la corbeille (recyclebin) avec renommage des contraintes et de l'objet.
19-5-531 19-JUL-2021 04:38:51 ALTER TABLE "HR"."ZZ1" RENAME CONSTRAINT "SYS_C0012556" TO "BIN$xVueHmhyfybgUwEAAH/baQ==$0" ;
19-5-531 19-JUL-2021 04:38:51 ALTER TABLE "HR"."ZZ1" RENAME CONSTRAINT "SYS_C0012557" TO "BIN$xVueHmhzfybgUwEAAH/baQ==$0" ;
19-5-531 19-JUL-2021 04:38:51 drop table HR.ZZ1 AS "BIN$xVueHmh2fybgUwEAAH/baQ==$0" ;
19-5-531 19-JUL-2021 04:38:51 ALTER TABLE "HR"."ZZ1" RENAME TO "BIN$xVueHmh2fybgUwEAAH/baQ==$0" ;
19-5-531 19-JUL-2021 04:38:51 ALTER TABLE "HR"."ZZ1" RENAME CONSTRAINT "SYS_C0012558" TO "BIN$xVueHmh0fybgUwEAAH/baQ==$0" ;

19-JUL-2021 04:43:34 : récupération de la table via la commande flashback.
15-0-531 19-JUL-2021 04:43:34 FLASHBACK TABLE HR."BIN$xVueHmh2fybgUwEAAH/baQ==$0" TO BEFORE DROP;

19-JUL-2021 04:49:11 : nouveau drop de la table. Vous notez que le drop table est en position 5 alors que ci-dessus, lors du drop précédent, il était en position 3; peut-être est-ce à cause de la précision de la colonne TIMESTAMP...
16-0-539 19-JUL-2021 04:49:11 ALTER TABLE "HR"."ZZ1" RENAME CONSTRAINT "BIN$xVueHmhyfybgUwEAAH/baQ==$0" TO "BIN$xVueHmh3fybgUwEAAH/baQ==$1" ;
16-0-539 19-JUL-2021 04:49:11 ALTER TABLE "HR"."ZZ1" RENAME TO "BIN$xVueHmh7fybgUwEAAH/baQ==$0" ;
16-0-539 19-JUL-2021 04:49:11 ALTER TABLE "HR"."ZZ1" RENAME CONSTRAINT "BIN$xVueHmh0fybgUwEAAH/baQ==$0" TO "BIN$xVueHmh5fybgUwEAAH/baQ==$1" ;
16-0-539 19-JUL-2021 04:49:11 ALTER TABLE "HR"."ZZ1" RENAME CONSTRAINT "BIN$xVueHmhzfybgUwEAAH/baQ==$0" TO "BIN$xVueHmh4fybgUwEAAH/baQ==$1" ;
16-0-539 19-JUL-2021 04:49:11 drop table HR.ZZ1 AS "BIN$xVueHmh7fybgUwEAAH/baQ==$0" ;

============================================================================================
Recréation de la table supprimée avec l'opération DDL de LogMiner
============================================================================================
Maintenant revenons à notre besoin initial : récupérer la table droppée en utilisant LogMiner. Nous allons dans un premier temps rejouer la commande CREATE TABLE des archived redologs puis les opérations DML du 24 juin. V$LOGMNR_CONTENTS ne contient pas de colonne LONG, pas besoin de faire un SET LONG 1000000 sous SQL*Plus. Attention, Le CREATE TABLE est stocké en minuscules, comme il a été saisi, donc pensez à utiliser UPPER sur la colonne SQL_REDO. Notez le point-virgule à la fin du CREATE TABLE, prouvant que l'ordre SQL est affiché en entier. Je stocke l'ordre à rejouer dans un fichier .sql.
SQL> spool zzcreate.sql

SQL> select SQL_REDO from V$LOGMNR_CONTENTS where SEG_OWNER = 'HR' and SEG_NAME = 'ZZ1' and UPPER(SQL_REDO) like 'CREATE TABLE%';
SQL_REDO
_______________________________________________________________________________________________________________
create table ZZ1 (ID NUMBER(8) PRIMARY KEY, NAME VARCHAR2(50 CHAR) NOT NULL, FIRST_NAME VARCHAR2(50 CHAR) NOT NULL);

SQL> spool off

Dans le .sql je supprime la ligne du nom des colonnes, la ligne des underscores et je lance la commande CREATE TABLE. ATTENTION : il faut être dans le bon schéma, donc HR, et dans la bonne PDB avant de lancer le .sql!
SQL> show user con_name
USER is "HR"
CON_NAME
------------------------------
ORCL

SQL> @zzcreate.sql
Table created.

SQL> desc ZZ1
Name Null? Type
---------------------------------------
ID NOT NULL NUMBER(8)
NAME NOT NULL VARCHAR2(50 CHAR)
FIRST_NAME NOT NULL VARCHAR2(50 CHAR)

SQL> select count(*) from ZZ1;
COUNT(*)
----------
0

Contenu des arhived redo logs : opérations DML absentes
La table a été créée mais elle est vide. Que contiennent nos archived redologs maintenant? Aïe, les opérations DML ne sont toujours pas là... Je pense que le CREATE TABLE a donné à ma nouvelle table HR.ZZ1 un nouvel object_id et donc il est impossible à Oracle de faire le lien avec les données des redologs archivés de l'ancien objet car son object_id est différent. Avec le FLASHBACK BEFORE DROP c'était OK car la commande avait recréé l'ancienne table avec son ancien ID mais là Oracle a créé un nouvel objet, même si le schéma et le nom est le même.
SQL> select XIDUSN || '-' || XIDSLT || '-' || XIDSQN AS "Trans ID", TIMESTAMP, SQL_REDO, SQL_UNDO from V$LOGMNR_CONTENTS where SEG_OWNER = 'HR' and SEG_NAME = 'ZZ1' order by TIMESTAMP, "Trans ID";
Trans ID TIMESTAMP SQL_REDO SQL_UNDO
___________________________________________________________________________________________________________ ___________
11-11-386 22-JUN-2021 09:17:54 ALTER TABLE "HR"."ZZ1" RENAME CONSTRAINT "SYS_C0012440" TO "BIN$xVueHmhsfybgUwEAAH/baQ==$0" ;
11-11-386 22-JUN-2021 09:17:54 ALTER TABLE "HR"."ZZ1" RENAME CONSTRAINT "SYS_C0012441" TO "BIN$xVueHmhtfybgUwEAAH/baQ==$0" ;
11-11-386 22-JUN-2021 09:17:54 ALTER TABLE "HR"."ZZ1" RENAME CONSTRAINT "SYS_C0012442" TO "BIN$xVueHmhufybgUwEAAH/baQ==$0" ;
11-11-386 22-JUN-2021 09:17:54 ALTER TABLE "HR"."ZZ1" RENAME CONSTRAINT "ZZ1_CK_UPPER_NAME" TO "BIN$xVueHmhvfybgUwEAAH/baQ==$0" ;
11-11-386 22-JUN-2021 09:17:54 ALTER TABLE "HR"."ZZ1" RENAME TO "BIN$xVueHmhxfybgUwEAAH/baQ==$0" ;
11-11-386 22-JUN-2021 09:17:54 drop table zz1 AS "BIN$xVueHmhxfybgUwEAAH/baQ==$0" ;
15-25-504 22-JUN-2021 09:21:37 create table ZZ1 (ID NUMBER(8) PRIMARY KEY, NAME VARCHAR2(50 CHAR) NOT NULL, FIRST_NAME VARCHAR2(50 CHAR) NOT NULL);
19-5-531 19-JUL-2021 04:38:51 ALTER TABLE "HR"."ZZ1" RENAME CONSTRAINT "SYS_C0012556" TO "BIN$xVueHmhyfybgUwEAAH/baQ==$0" ;
19-5-531 19-JUL-2021 04:38:51 ALTER TABLE "HR"."ZZ1" RENAME CONSTRAINT "SYS_C0012557" TO "BIN$xVueHmhzfybgUwEAAH/baQ==$0" ;
19-5-531 19-JUL-2021 04:38:51 drop table HR.ZZ1 AS "BIN$xVueHmh2fybgUwEAAH/baQ==$0" ;
19-5-531 19-JUL-2021 04:38:51 ALTER TABLE "HR"."ZZ1" RENAME TO "BIN$xVueHmh2fybgUwEAAH/baQ==$0" ;
19-5-531 19-JUL-2021 04:38:51 ALTER TABLE "HR"."ZZ1" RENAME CONSTRAINT "SYS_C0012558" TO "BIN$xVueHmh0fybgUwEAAH/baQ==$0" ;
15-0-531 19-JUL-2021 04:43:34 FLASHBACK TABLE HR."BIN$xVueHmh2fybgUwEAAH/baQ==$0" TO BEFORE DROP;
16-0-539 19-JUL-2021 04:49:11 ALTER TABLE "HR"."ZZ1" RENAME CONSTRAINT "BIN$xVueHmhyfybgUwEAAH/baQ==$0" TO "BIN$xVueHmh3fybgUwEAAH/baQ==$1" ;
16-0-539 19-JUL-2021 04:49:11 ALTER TABLE "HR"."ZZ1" RENAME TO "BIN$xVueHmh7fybgUwEAAH/baQ==$0" ;
16-0-539 19-JUL-2021 04:49:11 ALTER TABLE "HR"."ZZ1" RENAME CONSTRAINT "BIN$xVueHmh0fybgUwEAAH/baQ==$0" TO "BIN$xVueHmh5fybgUwEAAH/baQ==$1" ;
16-0-539 19-JUL-2021 04:49:11 ALTER TABLE "HR"."ZZ1" RENAME CONSTRAINT "BIN$xVueHmhzfybgUwEAAH/baQ==$0" TO "BIN$xVueHmh4fybgUwEAAH/baQ==$1" ;
16-0-539 19-JUL-2021 04:49:11 drop table HR.ZZ1 AS "BIN$xVueHmh7fybgUwEAAH/baQ==$0" ;
18 rows selected.

Conclusion
De mes tests, je conclus qu'il n'est pas possible d'utiliser LogMiner pour recréer une table droppée et d'y rejouer ses ordres DML; il est possible de recréer un objet de structure équivalente à l'ancien objet et c'est tout.

Néanmoins ne nous laissons pas abattre, c'est dans l'adversité qu'on innove ou qu'on trouve des solutions!

Dans sa doc, Oracle parlait d'une optimisation consistant à copier régulièrement le contenu de V$LOGMNR_CONTENTS dans une table. Cela évite les accès systématiques au disque dur à chaque SELECT sur cette vue mais aussi de garder exploitable le contenu des redo logs puisque le contenu de cette table ne sera pas, lui, filtré par LogMiner.

Dernière solution, récupérer l'object_id de l'ancienne table (si possible) puis, après le drop et le nouveau CREATE TABLE, faire un update dans OBJ$ pour associer à la nouvelle table l'object_id de la table droppée et voir si dans V$LOGMNR_CONTENTS les ordres DML sont visibles. Il va de soi que ceci est un hack du dictionnaire de données et que j'en parle juste comme un cas d'école ou théorique et que cela ne doit jamais être fait en production.