Introduction▲
Et Oracle inventa la machine à remonter le temps
Combien de fois avez-vous du arrêter la base, faire des restaurations UNTIL CANCEL, analyser les redo-logs avec log miner, ou mettre en place des exports de vos tables? Et tout cela pourquoi? Pour un simple UPDATE sans clause WHERE, un DELETE mal ciblé ou mal construit.
Et bien maintenant tout cela est fini. Oracle depuis la version 9i a inventé une nouvelle fonctionnalité aux applications diverses et variées : Le FLASHBACK QUERY.
I. Qu'est-ce que c'est ?▲
FLASHBACK QUERY est une nouvelle fonctionnalité qui vous permet de voir des données telles quelle étaient dans le temps (en vous basant sur une date ou sur un SCN). A quoi ça sert me direz vous? Et bien fini le temps où il fallait arrêter la base de données juste pour récupérer quelques lignes, maintenant tout cela se fait en ligne. Plus besoin de déranger les utilisateurs, tout se fait de manière transparente pour eux. Très simple d'utilisation (vous entrez une date ou un SCN et hop vous voyez les données telles qu'elles étaient à ce moment la) il peut même ne nécessiter aucune interventions du DBA si l'utilisateur dispose des droits suffisants.
II. Les Avantages▲
Un avantage majeur de FLASHBACK est qu'il peut être utilisé de manière complètement transparente par des applications qui peuvent ainsi générer des statistiques temporelles sans avoir à sauvegarder quoi que ce soit.
Aucune interruption de service, tous les ordres DML sont autorisés pendant une requête FLASHBACK, même si l'objet en question est utilisé par FLASHBACK. Plusieurs sessions concurrentes peuvent utiliser FLASHBACK en même temps. La durée d'exécution dépendra de la récupération des données nécessaires pour remonter dans le temps.
III. Les limitations▲
Il faut savoir que pour revenir dans le temps sur une table, il est évident qu'il ne faut pas que la structure de la table ait été modifiée. Si c'est le cas l'erreur ORA-1466 sera automatiquement renvoyée.
Le retour dans le temps basé sur une date n'est précis qu'à plus ou moins 5 minutes, il faudra donc utiliser le SCN qui lui sera beaucoup plus précis.
La durée maximum de sauvegarde est actuellement limitée à 5 jours maximum.
Vous ne pourrez pas imbriquer les appels à FLASHBACK et vous ne pourrez pas utiliser de FLASHBACK Query sur des tables à travers un lien de base de données.
IV. Comment ça marche▲
IV-A. L'installation▲
Avant de pouvoir utiliser cet outil, il va vous falloir configurer la base de données.
La première chose à faire va être d'enclencher la gestion automatique des UNDOs à la place de l'ancienne méthode (avec les Rollback Segments)
Pour savoir dans quel mode vous êtes, entrez la commande suivante :
SELECT name, value
from v$parameter t
where t.NAME in ('undo_management','undo_retention');
Name Value
--------------- ---------
undo_management AUTO
undo_retention 10800Vous devriez voir apparaître AUTO si cette fonctionnalité est enclenchée. Sinon exécutez les commandes suivantes :
ALTER SYSTEM SET undo_management=AUTO SCOPE=spfile;
ALTER SYSTEM SET undo_retention=10800 SCOPE=both;
CREATE UNDO
TABLESPACE "UNDOTBS1"
DATAFILE '<PATH>\UNDOTBS01.DBF' SIZE 200M
REUSE AUTOEXTEND
ON NEXT 5120K MAXSIZE 32767M;Redémarrez la base de données.
Le paramètre UNDO_MANAGEMENT sert à activer/désactiver le mode UNDO. Les 2 valeurs possibles sont AUTO ou MANUAL
Le paramètre UNDO_RETENTION (en secondes) sert à définir le temps que nous pourrons remonter durant une requête FLASHBACK. Les valeurs possibles vont de 0 à 2^31-1.
Plus vous voudrez revenir loin dans le temps plus le nombre d'informations augmentera et plus vous utiliserez de place.
Ensuite il ne vous restera plus qu'à donner le privilège EXECUTE sur le package DBMS_FLASHBACK à l'utilisateur souhaité.
Vous n'avez pas à choisir la ou les tables que vous souhaitez pouvoir revoir. FlashBack vous permettra de revoir l'ensemble de la base de données telle qu'elle était dans le temps. Donc vous serez en mesure de revoir toutes les tables pour autant que leur structure n'ait pas été changée. La seule chose que vous devez paramétrer est la durée maximale à sauvegarder.
IV-B. La méthode générale▲
La méthode pour utiliser le FLASHBACK Query se déroule en 3 étapes (que ce soit en SQL ou en PL/SQL) :
- Retour dans le temps avec (DBMS_FLASHBACK.ENABLE_AT_TIME ou DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER)
- Exécution d'une requête qui nous permettra de récupérer les informations souhaitées.
- Retour dans le présent avec DBMS_FLASHBACK.DISABLE
Attention à partir du moment ou vous êtes retourné dans le passé les seules commandes autorisées sont des commandes SELECT. Il vous faudra donc ramener les données qui vous intéressent dans un curseur.
IV-B-1. Exemples d'utilisation▲
Pour les exemples d'utilisations que je vous propose, nous utiliserons les bonnes vieilles tables de SCOTT ainsi que le compte SCOTT.
La première étape consiste à donner les droits d'exécution du package DBMS_FLASBACK à SCOTT.
CONNECT sys/<pass> AS SYSDBA
GRANT EXECUTE ON dbms_flashback TO scott;Voici les scripts SQL de création et d'alimentation de la table EMP :
CONNECT scott/tiger@<VOTRE BASE>
CREATE TABLE EMP
(EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
DEPTNO NUMBER(2));
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES (7369, 'SMITH', 'CLERK', 7902, to_date('17-12-1980', 'dd-mm-yyyy'), 800, NULL, 20);
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES (7499, 'ALLEN', 'SALESMAN', 7698, to_date('20-02-1981', 'dd-mm-yyyy'), 1600, 300, 30);
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES (7521, 'WARD', 'SALESMAN', 7698, to_date('22-02-1981', 'dd-mm-yyyy'), 1250, 500, 30);
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES (7566, 'JONES', 'MANAGER', 7839, to_date('02-04-1981', 'dd-mm-yyyy'), 2975, NULL, 20);
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES (7654, 'MARTIN', 'SALESMAN', 7698, to_date('28-09-1981', 'dd-mm-yyyy'), 1250, 1400, 30);
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES (7698, 'BLAKE', 'MANAGER', 7839, to_date('01-05-1981', 'dd-mm-yyyy'), 2850, NULL, 30);
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES (7782, 'CLARK', 'MANAGER', 7839, to_date('09-06-1981', 'dd-mm-yyyy'), 2450, NULL, 10);
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES (7788, 'SCOTT', 'ANALYST', 7566, to_date('19-04-1987', 'dd-mm-yyyy'), 3000, NULL, 20);
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES (7839, 'KING', 'PRESIDENT', NULL, to_date('17-11-1981', 'dd-mm-yyyy'), 5000, NULL, 10);
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES (7844, 'TURNER', 'SALESMAN', 7698, to_date('08-09-1981', 'dd-mm-yyyy'), 1500, 0, 30);
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES (7876, 'ADAMS', 'CLERK', 7788, to_date('23-05-1987', 'dd-mm-yyyy'), 1100, NULL, 20);
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES (7900, 'JAMES', 'CLERK', 7698, to_date('03-12-1981', 'dd-mm-yyyy'), 950, NULL, 30);
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES (7902, 'FORD', 'ANALYST', 7566, to_date('03-12-1981', 'dd-mm-yyyy'), 3000, NULL, 20);
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES (7934, 'MILLER', 'CLERK', 7782, to_date('23-01-1982', 'dd-mm-yyyy'), 1300, NULL, 10);
COMMIT;IV-C. Récupérer des données dans le temps en utilisant une date▲
Pour cet exemple nous allons modifier une ligne avec la commande suivante
UPDATE emp SET sal=6000 WHERE ename='KING';
COMMIT;Puis nous allons voir si nous arrivons à voir les données telles qu'elles étaient dans le temps (attention si vous ne disposiez pas de la table avant de lancer le flashback query ) il vous faudra attendre 5 minutes)
EXECUTE DBMS_FLASHBACK.ENABLE_AT_TIME(sysdate- (3/1440));
SELECT * FROM emp where ename='KING';
EXECUTE DBMS_FLASHBACK.DISABLE;Voici le résultat :
SQL> EXECUTE DBMS_FLASHBACK.ENABLE_AT_TIME(sysdate- (3/1440));
PL/SQL procedure successfully completed
SQL> select *
2 from emp
3 where ename ='KING';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7839 KING PRESIDENT 17/11/1981 5000,00 10
SQL> EXECUTE DBMS_FLASHBACK.DISABLE;
PL/SQL procedure successfully completedVoila se qui se passerait si vous tentiez de lancer une FLASHBACK QUERY sur une nouvelle table :
SQL> CREATE TABLE t_test (id number, sal number);
Table created
SQL> INSERT INTO t_test values (1,1000);
1 row inserted
SQL> COMMIT;
Commit complete
SQL> EXECUTE DBMS_FLASHBACK.ENABLE_AT_TIME(sysdate- (3/1440));
PL/SQL procedure successfully completed
SQL> SELECT *
2 FROM t_test;
SELECT *
FROM t_test
ORA-01466: lecture des données impossible - Définition de tables modifiée
SQL> EXECUTE DBMS_FLASHBACK.DISABLE;
PL/SQL procedure successfully completedCette erreur est du au fait que la table n'existait pas avant et qu'aucunes informations n'avaient été enregistrées dans le dictionnaire de données concernant cette table.
Le seul moyen de ramener ces informations dans le présent sera d'utiliser un bloc PL/SQL Anonyme.
IV-D. Ramener des données du passé au présent en utilisant une Date▲
Voici un exemple de bloc PL/SQL anonyme qui permet de ramener des données du passé vers le présent.
DECLARE
CURSOR emp_before IS
SELECT sal FROM emp WHERE ename = 'KING';
CURSOR emp_now IS
SELECT sal FROM emp WHERE ename = 'KING';
v_sal_before NUMBER;
v_sal_now NUMBER;
BEGIN
dbms_flashback.enable_at_time(SYSDATE - (30 / 1440));
OPEN emp_before;
dbms_flashback.disable;
FETCH emp_before
INTO v_sal_before;
CLOSE emp_before;
OPEN emp_now;
FETCH emp_now
INTO v_sal_now;
CLOSE emp_now;
dbms_output.put_line('Sal Before : ' || v_sal_before);
dbms_output.put_line('Sal Now : ' || v_sal_now);
END;Voici le résultat :
Sal Before : 5000
Sal Now : 6000Cette fonctionnalité nous permettrait de corriger tout DELETE ou mauvais UPDATE.
Dans cet exemple on supposera qu'un mauvais update a été effectué avec la commande suivante :
UPDATE emp SET sal=0;
COMMIT;
SQL> SELECT *
2 FROM emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 17/12/1980 0,00 20
7499 ALLEN SALESMAN 7698 20/02/1981 0,00 300,00 30
7521 WARD SALESMAN 7698 22/02/1981 0,00 500,00 30
7566 JONES MANAGER 7839 02/04/1981 0,00 20
7654 MARTIN SALESMAN 7698 28/09/1981 0,00 1400,00 30
7698 BLAKE MANAGER 7839 01/05/1981 0,00 30
7782 CLARK MANAGER 7839 09/06/1981 0,00 10
7788 SCOTT ANALYST 7566 19/04/1987 0,00 20
7839 KING PRESIDENT 17/11/1981 0,00 10
7844 TURNER SALESMAN 7698 08/09/1981 0,00 0,00 30
7876 ADAMS CLERK 7788 23/05/1987 0,00 20
7900 JAMES CLERK 7698 03/12/1981 0,00 30
7902 FORD ANALYST 7566 03/12/1981 0,00 20
7934 MILLER CLERK 7782 23/01/1982 0,00 10
14 rows selectedLançons maintenant la procédure de restauration des salaires (attention, étant donné que nous utilisons la méthode basée sur le temps il faudra respecter un délai de plus ou moins 5 minutes entre chaque actions, nous verrons ensuite comment se baser sur le numéro de SCN)
DECLARE
CURSOR emp_before IS
SELECT * FROM emp WHERE ename = 'KING';
rec emp%ROWTYPE;
BEGIN
dbms_flashback.enable_at_time(SYSDATE - (1 / 1440));
OPEN emp_before;
dbms_flashback.disable;
LOOP
FETCH emp_before
INTO rec;
EXIT WHEN emp_before%NOTFOUND;
UPDATE emp SET sal = rec.sal WHERE empno = rec.empno;
COMMIT;
END LOOP;
CLOSE emp_before;
EXCEPTION
WHEN OTHERS THEN
dbms_flashback.disable;
END;Regardons maintenant le résultat de notre action :
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 17/12/1980 800,00 20
7499 ALLEN SALESMAN 7698 20/02/1981 1600,00 300,00 30
7521 WARD SALESMAN 7698 22/02/1981 1250,00 500,00 30
7566 JONES MANAGER 7839 02/04/1981 2975,00 20
7654 MARTIN SALESMAN 7698 28/09/1981 1250,00 1400,00 30
7698 BLAKE MANAGER 7839 01/05/1981 2850,00 30
7782 CLARK MANAGER 7839 09/06/1981 2450,00 10
7788 SCOTT ANALYST 7566 19/04/1987 3000,00 20
7839 KING PRESIDENT 17/11/1981 5000,00 10
7844 TURNER SALESMAN 7698 08/09/1981 1500,00 0,00 30
7876 ADAMS CLERK 7788 23/05/1987 1100,00 20
7900 JAMES CLERK 7698 03/12/1981 950,00 30
7902 FORD ANALYST 7566 03/12/1981 3000,00 20
7934 MILLER CLERK 7782 23/01/1982 1300,00 10
14 rows selectedNous avons donc pu récupérer nos informations.
IV-E. Ramener des données du passé au présent en utilisant un SCN▲
Nous vu comment fonctionnait le FLASHBACK avec une date. Le problème de cette méthode est la grande incertitude de notre position dans le temps, car nous avons une marge de plus ou moins 5 minutes par rapport à la date que nous avons demandée.
Par exemple on suppose que le SCN 1000 correspond à l'heure 8:41 et que le SCN 1005 correspond à l'heure 8:46. Si l'on tentait de faire un retour dans le temps entre les 8:41:00 et 8:45:59 alors Oracle se baserait sur le SCN 1000, alors qu'un FLASHBACK pour 8:46 sera basé sur le SCN 1005. Ce qui signifie que nous avons une marge d'erreur de 5 SCN.
Bien que cette méthode soit plus intéressante, il vous faudra stocker de manière indépendante les N° de SCN qui nous intéresserons. De plus dans cet exemple nous utiliserons une FLASHBACK QUERY avec une boucle FOR (ce qui simplifiera le code).
Pour récupérer le n° de SCN courant il vous suffira d'utiliser la fonction dbms_flashback.get_system_change_number (renvoie le SCN dans un NUMBER).
On crée une table qui sert à sauvegarder les numéros de SCN :
create table save_scn (save_scn number);Ensuite on lance une simulation d'action :
DECLARE
v_scn NUMBER;
BEGIN
v_scn := dbms_flashback.get_system_change_number;
INSERT INTO save_scn VALUES (v_scn);
COMMIT;
END;
/
BEGIN
DELETE FROM emp WHERE ename = 'KING';
COMMIT;
DELETE emp WHERE ename = 'ADAMS';
COMMIT;
-- On fait un UPDATE qui lui devra être conservé.
UPDATE emp SET sal = 1500 WHERE ename = 'JAMES';
COMMIT;
END;
/Puis on lance notre récupération :
DECLARE
CURSOR emp_before IS
SELECT * FROM emp;
CURSOR emp_now(v_empno NUMBER) IS
SELECT empno FROM emp WHERE empno = v_empno;
v_is_present NUMBER;
v_scn NUMBER;
BEGIN
-- On récupère le n° de scn
SELECT save_scn INTO v_scn FROM save_scn;
dbms_output.put_line('Version Actuelle');
FOR rec IN (SELECT empno, ename, sal FROM emp) LOOP
dbms_output.put(rec.empno);
dbms_output.put(' ' || rec.ename);
dbms_output.put_line(' ' || rec.sal);
END LOOP;
-- Maintenant on veut revenir en arrière.
dbms_flashback.enable_at_system_change_number(query_scn => v_scn);
-- Voici aussi un exemple d'utilisation avec une BOUCLE FOR
FOR rec IN emp_before LOOP
-- On reviens dans le présent
dbms_flashback.disable;
-- On regarde si la ligne existe dans la table du présent
OPEN emp_now(rec.empno);
FETCH emp_now
INTO v_is_present;
CLOSE emp_now;
-- Si elle n'est pas présente on fait un insert
IF v_is_present IS NULL THEN
INSERT INTO emp
VALUES
(rec.empno, rec.ename, rec.job, rec.mgr, rec.hiredate, rec.sal, rec.comm, rec.deptno);
COMMIT;
END IF;
v_is_present := NULL;
END LOOP;
dbms_output.put_line('');
dbms_output.put_line('');
dbms_output.put_line('Nouvelle Version');
FOR rec IN (SELECT empno, ename, sal FROM emp) LOOP
dbms_output.put(rec.empno);
dbms_output.put(' ' || rec.ename);
dbms_output.put_line(' ' || rec.sal);
END LOOP;
END;
/Et voici le résultat :
Version Actuelle
7369 SMITH 800
7499 ALLEN 1600
7521 WARD 1250
7566 JONES 2975
7654 MARTIN 1250
7698 BLAKE 2850
7782 CLARK 2450
7788 SCOTT 3000
7844 TURNER 1500
7900 JAMES 1500
7902 FORD 3000
7934 MILLER 1300
Nouvelle Version
7369 SMITH 800
7499 ALLEN 1600
7521 WARD 1250
7566 JONES 2975
7654 MARTIN 1250
7698 BLAKE 2850
7782 CLARK 2450
7788 SCOTT 3000
7839 KING 5000
7844 TURNER 1500
7876 ADAMS 1100
7900 JAMES 1500
7902 FORD 3000
7934 MILLER 1300On constate que nos deux lignes sont bien revenues et que le salaire de JAMES n'a pas été modifié.
V. Annexes▲
Pour pouvoir consulter ces documents vous devez avoir un compte sur OTN (Oracle Technology Network) valide.
Vous pouvez en créer un gratuitement en suivant ce lien
Découvrez les autres nouveautés d'Oracle 9i avec l'article de SheikYerbouti
https://sheikyerbouti.developpez.com/evol_9i/
VI. Remerciements▲
Merci à toute l'équipe de Développez et à SheikYerbouti.