Le FLASHBACK QUERY sous Oracle 9i

23/04/2004

Introduction
1. Qu'est ce que c'est ?
2. Les Avantages
3. Les limitations
4. Comment ça marche
4.1. L'installation
4.2. La méthode générale
4.2.1. Exemples d'utilisation
4.3. Récupérer des données dans le temps en utilisant une date
4.4. Ramener des données du passé au présent en utilisant une Date
4.5. Ramener des données du passé au présent en utilisant un SCN
5. Annexes
6. Remerciements

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.

1. 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 ce 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.

2. 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.

Aucunes interruptions de services, 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.

3. 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 maximums.

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.

4. Comment ça marche

4.1. 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	10800

Vous 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.

Attention 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é.

4.2. 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.

4.2.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;

4.3. 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 vérifions que nous voyons 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 completed

Voila 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  FRMOM 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 completed

Cette erreur est du au fait que la table n'existait pas avant et qu'aucune information n'avait été enregistrée 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.

4.4. 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 : 6000

Cette 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 selected

Lanç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 selected

Nous avons donc pu récupérer nos informations.

4.5. 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 vous intéresseront. 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 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 1300

On constate que nos 2 lignes sont bien revenues et que le salaire de JAMES n'a pas été modifié.

5. Annexes

Attention : 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

Flashback
http://download-west.oracle.com/docs/cd/A91202_01/901_doc/appdev.901/a88876/adg08sql.htm#10851

6. Remerciements