Developpez.com - SGBD & SQL
X

Choisissez d'abord la catégorieensuite la rubrique :


Le FLASHBACK QUERY sous Oracle 9i

23/04/2004

Par Helyos (helyos.developpez.com)
 


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




Contacter le responsable de la rubrique SGBD & SQL