Blog

le 08/04/2011
Administrateur
Oracle

Tout ce que vous avez toujours voulu savoir sur … le FLASHBACK (partie 1)

Avec Oracle Database 9i, la notion de Flashback est apparue. Avec Oracle Database 10g et Oracle Database 11g, de nouvelles notions de Flashback ont été ajoutées.
Le problème, c’est que cette notion de Flashback englobe beaucoup de technologies qui souvent n’ont rien à voir les unes avec les autres.
Le but de cet article est de faire la lumière sur ces différents types de FlashBack.
D’abord qu’est ce que le l’architecture FlashBack ? C’est la possibilité de faire des « Flashback », donc de faire des « retours arrière » dans le temps, soit en faisant une récupération de données, soit en voulant juste visualiser les données de ce moment la.
Mais au fait savez-vous combien il y a de types de Flashback ?
Il y a 8 types de Flashback !!! Si vous ne les connaissez pas tous …

La famille flashback

Si vous voulez essayer de récupérer une table qui a été supprimée « accidentellement » : c’est le Flashback Drop qu’il faudra utiliser.
Si vous voulez connaitre la valeur d’une ligne d’une table à une date donnée antérieure : c’est le Flashback Query qu’il faudra utiliser.
Le Flashback Data Archive peut également répondre au besoin précédent.
Si vous voulez connaitre l’évolution de la valeur d’une ligne d’une table pour un intervalle de dates donné : c’est le Flashback Versions Query qu’il faudra utiliser.
Mieux, si grâce a Flashback Query, vous avez localisé la date exacte et que vous voulez revenir à la version de la table de cette date la, vous devrez utiliser Flashback Table.
Si vous voulez connaitre pour une transaction donnée, l’ordre ou les ordres qui permettraient d’annuler cette transaction, c’est le Flashback Transaction Query qu’il faudra utiliser.
Si de plus, vous voulez appliquer les ordres SQL de manière à annuler cette transaction, c’est le Flashback Transaction Backout qu’il faudra utiliser.
Enfin si vous voulez rétablir toute la base de données jusqu’à un point antérieur dans le temps en annulant toutes les modifications apportées depuis ce point, C’est le Flashback Database qu’il faudra utiliser.

On remarque que dans certains cas, le Flashback ne fait que lire des données, dans d’autres cas, il fait de la modification de données.

Ou Flashback va chercher les données ?

Les différents Flashback n’utilisent pas les mêmes zones pour récupérer les données.

Flashback Database utilise les journaux Flashback pour procéder à un flashback de la base de données.
Flashback Drop utilise la notion de « corbeille ».
Flashback Data Archive utilise la notion d’archive Flashback.
Flashback Table, Flashback Version Query, Flashback Query, Flashback Transaction Query utilisent les données d’annulation (undo).
Flashback Transaction Backout utilise les undo et les archivelogs.

Historique des Flashback :

Oracle Database 9i :

Flashback Query

Oracle Database 10g:

Flashback Table
Flashback Drop
Flashback Version Query
Flashback Transaction Query
Flashback Database

Oracle Database 11g:

Flashback Data Archive
Flashback Transaction Backout (en fait c’est l’évolution du Flashback Transaction Query de la 10g)

Allons-y, l’ordre de présentation qui a été choisi est l’ordre historique.
NB : Pour tous les exemples qui suivent on supposera que les paramètres d’initialisation on été définis :

UNDO_MANAGEMENT= AUTO
UNDO_TABLESPACE=UNDO
UNDO_RETENTION=1800

1 – FLASHBACK QUERY

Principe : Permet de voir la valeur de données telles quelle étaient dans le temps (en se basant sur une date ou sur un SCN)
Le paramètre UNDO_RETENTION (en secondes) sert à définir le temps ou nous pourrons espérer remonter durant une requête FLASHBACK.
Exemple :

SQL> 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));
SQL> 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);
SQL> COMMIT;
SQL> SELECT  sal   FROM    EMP WHERE   ename ='KING';
SAL
-----
5000

Le 01 Janvier 2011 à 11h10

SQL> UPDATE  emp  SET  sal=6000  WHERE  ename='KING';
SQL> COMMIT;
SQL> SELECT  sal  FROM  EMP WHERE   ename ='KING';
SAL
-----
6000

Dans cet exemple, on peut voir la valeur de l’employé KING une date antérieure précisée
Ici à 11h00 le 1er janvier 2010.

SQL> SELECT  sal    FROM EMP
AS OF   TIMESTAMP  TO_TIMESTAMP('2011-01-01 11:00:00','yyyy-mm-dd hh24:mi:ss')
WHERE     ename ='KING';
SAL
-----
5000

On peut aussi le faire de la manière suivante en utilisant le package DBMS_FLASHBACK. Cet ordre regarde la valeur des données de la base il y a 3 minutes , on peut voir que la valeur était bien à 5000 :

SQL> EXECUTE DBMS_FLASHBACK.ENABLE_AT_TIME(sysdate- (3/1440));
PL/SQL procedure successfully completed
SQL> select * from emp where ename ='KING';
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7839 KING       PRESIDENT       17/11/1981    5000,00               10

Si on désactive le flashback , on voit que la valeur actuelle est bien de 6000

SQL> EXECUTE DBMS_FLASHBACK.DISABLE;
PL/SQL procedure successfully completed
SQL> SELECT sal FROM EMP WHERE ename ='KING';
SAL
-----
6000

Et éventuellement on peut revenir à cette valeur dans le temps passé, cela nécessite une requête comme suit :

SQL> UPDATE EMP SET sal =
      (SELECT sal    FROM EMP
       AS of  timestamp TO_TIMESTAMP ('2010-01-01 11:00:00','yyyy-mm-dd hh24:mi:ss')
       WHERE ename ='KING')
WHERE ename ='KING';
SQL> COMMIT;
SQL> SELECT  sal  FROM EMP WHERE  ename ='KING';
SAL
-----
5000

PS : Cette fonctionnalité est valable depuis la version 9i

2 – FLASHBACK TABLE

Principe : Permet de récupérer une table jusqu’à un point dans le passé sans nécessiter de restauration à partir d’une sauvegarde.
Cette opération va pouvoir être réalisée « in-place », pendant que la base de données est ouverte, en annulant (rollback) uniquement les modifications apportées aux tables indiquées et à leurs objets dépendants. Les données nécessaires à une opération Flashback Table sont extraites du tablespace d’annulation.
Pour procéder au flashback d’une table, vous devez activer le déplacement de lignes dans cette table (enable row movement). Lorsque vous activez le déplacement de lignes, le serveur Oracle peut déplacer une ligne dans la table.
Note : Vous pouvez utiliser Flashback Versions Query et Flashback Transaction Query pour déterminer l’heure de flashback appropriée.
Pré-requis :
1 – Le déplacement de lignesdoit être activé pour cette table :

SQL> ALTER TABLE EMP ENABLE ROW MOVEMENT;

2 – L’utilisateur qui va faire cet action doit avoir le privilège système « FLASHBACK ANY TABLE » ou le privilège « FLASHBACK » sur cette table
Exemple :
Suppression de toutes les lignes de la table EMP :

SQL> DELETE FROM EMP ;
14 rows deleted.
SQL> COMMIT;

Procéder au flashback de la table EMP à il y a 5 minutes

SQL> FLASHBACK TABLE EMP TO TIMESTAMP SYSTIMESTAMP - INTERVAL '5' MINUTE;
Flashback complete.
SQL> SELECT COUNT(*) FROM EMP ;
COUNT(*)
----------
14

On aurait pu aussi récupérer la table à une date précise : Exemple le 01 Janv. à minuit

SQL> FLASHBACK TABLE EMP        
     TO TIMESTAMP
     TO TIMESTAMP TO_TIMESTAMP('2010-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS');

On aurait pu aussi récupérer la table à un SCN précis :

SQL> var scn number;
SQL> BEGIN
2     :scn := TIMESTAMP_TO_SCN(SYSTIMESTAMP);
3  END;
4  /
PL/SQL procedure successfully completed.

SQL> DELETE FROM EMP ;
14 rows deleted.
SQL> COMMIT;

SQL> SELECT COUNT(*) FROM EMP
COUNT(*)
----------
1 row selected.
SQL> FLASHBACK TABLE EMP TO SCN :scn;
Flashback complete.
SQL> SELECT COUNT(*) FROM EMP ;
COUNT(*)
----------
14

Avec EM Database Control :

Availability > Perform Recovery ( sous Manage )

Recovery Scopes = Tables
Operation Type = Flashback Existing Tables
Puis click sur le bouton “Recover”

On peut choisir :
1 – Flashback to a timestamp
2 – Flashback to a restore point
3 – Flashback to a known SCN
Puis dans l’écran suivant, la ( ou les tables) sur lesquelles on veux faire un flashback. Si on ne connait pas le numéro de SCN on peut cliquer sur la première option : « Evaluate row changes and transactions to decide on a point in time”.

Préciser la table que l’on veut analyser ( ex : SCOTT.EMP ) puis choisir la ligne que l’on veut analyser ( Step 2 : where ename=’KING’ )
Cela va nous donner le numéro de SCN

Attention : Comme ce flashback va récupérer les données dans le tablespace d’annulation, il faut que les données s’y trouvent encore pour les récupérer (ce qui n’est pas garanti).

3-FLASHBACK DROP

Principe : Le Flashback Drop permet de restaurer les tables accidentellement supprimées.
Avec cette fonctionnalité, on va pouvoir annuler les effets d’une instruction DROP TABLE sans avoir recours à la récupération jusqu’à un point dans le temps traditionnelle. Le paramètre d’initialisation RECYCLEBIN doit être a ON (c’est le défaut).
Exemple : Suppression de la table EMP

SQL> drop table EMP;
Table dropped.
SQL> select * from EMP;
ORA-00942: Table or view does not exist

SQL> select * from user_tables where table_name = ‘EMP’;
no rows selected

La données de la table ont été mises dans la corbeille.

SQL> SELECT object_name, original_name FROM user_recyclebin;
OBJECT_NAME                      ORIGINAL_NAME
------------------------------   -------------
BIN$HGnc55/7rRPgQPeM/qQoRw==$0   EMP

Récupération de la table par un flashback drop.

SQL> flashback table emp TO BEFORE DROP ;
Flashback complete
SQL> select count(*) from EMP;
COUNT(*)
--------
14

Récupération de la table par un flashback drop et renommage en EMP2

SQL> flashback table emp TO BEFORE DROP RENAME EMP2 ;
Flashback complete

Si on veut supprimer la table et ne pas la mettre dans la corbeille:

SQL> drop table EMP PURGE ;

Attention :
Si la table avait des indexes ou des contraintes , ils vont être renommés en BIN$…
Après le flashback drop , ils conserveront ce nom en BIN$.

Avec EM Database Control :
Schema > Tables ( sous Database Objects )
Saisir le schéma que l’on veut visualiser
On remarque en bas à droite le bouton « Recycle Bin »
On pourra visualiser les objets de la corbeille et les récupérer.

4-FLASHBACK VERSION QUERY

Principe : Permet de consulter les versions d’une ligne qui existent entre deux points dans le temps ou deux SCN (System Change Number).
On va utiliser cette option pour extraire l’historique des lignes.
Mise en œuvre :

SQL> ALTER SESSION SET nls_timestamp_format = 'DD-MON-YYYY HH24:MI:SS';
SQL> UPDATE EMP SET SAL = 8000 WHERE  ENAME=’KING’ ;
SQL> commit;
SQL> UPDATE EMP SET SAL = 9000 WHERE  ENAME=’KING’ ;
SQL> commit;
SQL> SELECT sal,
     VERSIONS_STARTSCN         SSTART_SCN,
     VERSIONS_STARTTIME        START_TIME,
     VERSIONS_ENDTIME          END_TIME,
     VERSIONS_XID,                              -- transaction_id of version
     VERSIONS_OPERATION        VO               -- DML operation of version
FROM  EMP VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE
WHERE ENAME=’KING’ ;
ORDER BY  VERSIONS_ENDTIME;
SAL     STARTSCN    STARTTIME                  ENDTIME                     VERSIONS_XID     VO
------  --------   -------------------------   ------------------------    ---------------  ------
5000    1380863    10-AUG-2005 18:00:53.000  10-AUG-2005 18:00:05.000  040026008A010000    I
8000    1380491    10-AUG-2005 18:01:05.000  10-AUG-2005 18:01:14.000  040029008A010000    U
9000    1380486    10-AUG-2005 18:01:14.000  10-AUG-2005 18:01:26.000  040027008A010000    U
PS : On peut mettre une date ( format timestamp à la place de MINVALUE et MAXVALUE )

Avec EM Database Control :

Availability > Perform Recovery ( sous Manage )
Recovery Scopes = Tables
Operation Type = Flashback Existing Tables
« Evaluate row changes and transactions to décide on a point in time”
Préciser la table que l’on veut analyser ( ex : SCOTT.EMP ) puis choisir la ligne que l’on veut analyser ( Step 2 : where ename=’KING’ )
Cela va nous donner : les informations de l’ordre select précisé plus haut

Suite au prochain numéro ….