Voici la suite et fin de notre long article sur les nombreuses techniques de Flashback Oracle ( Lien vers le 1er article sur le Flashback ). Dans cette seconde partie je vais vous présenter
- Flashback Transaction Query
- Flashback Database
- Flashback Data Archive
- Flashback Transaction Blackout
Allez, c’est parti et c’est là que ça se passe …
5 – FLASHBACK TRANSACTION QUERY
Principe : Permet d’afficher toutes les modifications apportées par une transaction. Vous pouvez utiliser l’identificateur de transaction renvoyé par le select du Flashback Version Query pour exécuter une opération Flashback Transaction Query.
Utilisation :
SQL> INSERT into EMP values ( 125 , 'THIERRY', 'PRESIDENT', NULL,
to_date('17-11-1981', 'dd-mm-yyyy'),8000, NULL, 10);
SQL> COMMIT ;
SQL> UPDATE EMP
SET sal = 9000
WHERE ename = 'THIERRY';
COMMIT;
SQL> UPDATE EMP
SET sal = 10000
WHERE ename = 'THIERRY';
COMMIT;
SQL> DELETE from EMP Where ename = ‘THIERRY’ ;
SQL> SELECT xid
2 , operation
3 , undo_sql
4 FROM flashback_transaction_query
5 WHERE table_owner = USER
6 AND table_name = 'EMP'
7 ORDER BY start_timestamp;
On va pouvoir utiliser la vue FLASHBACK_TRANSACTION_QUERY (il faut avoir le privilège SELECT ANY TRANSACTION ) pour déterminer quelles sont les instructions SQL à utiliser pour annuler les modifications apportées par une transaction spécifique ou sur une période donnée.
XID OPERATION UNDO_SQL
---------------- -------- ---------------------------------------------------------
06001B0078030000 INSERT delete from "SCOTT"."EMP" where ROWID = 'AAAR3dAAEAAAACVAAA';
0200050083030000 UPDATE update "SCOTT"."EMP" set "SAL" = '8000' where ROWID = 'AAAR3dAAEAAAACVAAA';
04000E00C5020000 UPDATE update "SCOTT"."EMP" set "SAL" = '9000' where ROWID = 'AAAR3dAAEAAAACVAAA';
0900170092030000 DELETE insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM",
"DEPTNO") values ('125','THIERRY','PRESIDENT',NULL,TO_DATE('17-NOV-81', 'DD-MON-RR'),
'10000',NULL,'10');
La colonne UNDO_SQL montre l’ordre qu’il faudrait exécuter pour annuler cette transaction.
6 – FLASHBACK DATABASE
Principe : Rétablir la base de données jusqu’à un point antérieur dans le temps en annulant toutes les modifications apportées a partir de ce point
On pourrait voir ca comme un bouton de rembobinage pour la base de données, un genre de machine à remonter dans le temps ! Cette opération est rapide car vous n’avez pas besoin de restaurer de sauvegarde.
Vous pouvez utiliser cette fonctionnalité pour annuler des modifications ayant entraîné des corruptions logiques des données (erreur humaine ou logicielle). Dans le cas d’une corruption physique de la base, cette méthode ne sera pas utilisable et on devra passer par les méthodes traditionnelles de récupération avec des sauvegardes
L’opération Flashback Database est plus rapide que la récupération jusqu’à un point dans le temps traditionnelle (PITR), laquelle utilise des fichiers restaurés et des fichiers de journalisation (fichiers redo log).
Flashback Database est implémentée à l’aide d’un type de fichier journal appelé journal Flashback Database. La base de données Oracle consigne régulièrement des images « avant » de blocs de données dans les journaux Flashback Database. Les images des blocs peuvent être réutilisées pour rétablir rapidement les fichiers de données jusqu’au point dans le temps correspondant au moment où les journaux Flashback Database ont été capturés, juste avant le point de rétablissement visé.
Les modifications issues des fichiers de journalisation (fichiers redo log) sont ensuite appliquées pour combler les manques.
Les journaux Flashback Database sont automatiquement créés et gérés dans la zone de récupération rapide.
Utilisation :
1 – Activer Flashback Database avec Enterprise Manager
- Recovery Settings ( onglet Maintenance )
- Cochez la case de la région Flash Recovery « Enable Flashback Database »
- Indiquez le délai de conservation des données Flashback « Flashback Retention Time », il indique jusqu’à quel point dans le passé on souhaite pouvoir procéder à un flashback de la base de données.
- Une fois l’option activée, on va vous demander de redémarrer la base de données.
ATTENTION :
On devra de plus se placer en mode ARCHIVELOG .
ATTENTION en 11g R2 :
Cette opération peut se faire online , pas besoin d’arrêt/redémarrage base.
ou : 1 – Activer Flashback Database avec des commandes SQL :
SQL> shutdown immediate; SQL> startup mount; SQL> alter database archivelog; SQL> alter system set DB_FLASHBACK_RETENTION_TARGET=4320; SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE=536870912; SQL> alter system set DB_RECOVERY_FILE_DEST='/u02/fra'; SQL> alter database flashback on; SQL> alter database open;
Pour déterminer le plus petit SCN et l’heure exacte jusqu’ou on peut faire un Flashback de la base de données on peut faire :
SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME FROM V$FLASHBACK_DATABASE_LOG;
Puis …. activité au niveau de la base ….
2 – FlashBack de la Database à une date ou un SCN donné :
SQL> SHUTDOWN IMMEDIATE SQL> STARTUP MOUNT SQL> exit
si on connait le SCN sinon on mettra une heure (timestamp)
$ rman target / nocatalog RMAN> FLASHBACK DATABASE TO SCN=1137367;
sinon
SQL> FLASHBACK DATABASE to timestamp to_timestamp('01-01-2007 10:00', 'DD-MM-YYYY HH24:MI:SS');
puis
SQL> alter database open read only;
3 – Vérification des données puis arrêt/reouverture de la base
SQL> shutdown immediate SQL> startup mount SQL> alter database open resetlogs
7 – FLASHBACK DATA ARCHIVE
Principe : Le problème avec le FlashBack Query est qu’il va chercher les données dans le Tablespace UNDO, et on n’est jamais sur et certain que ca va marcher (durée de vie limitée des données dans le TBS UNDO).
En 11g est apparu un nouvel objet, Le FlashBack Data Archive. C’est un conteneur physique permettant de stocker des informations historiques.
Ce FlashBack Data Archive va être stocké dans un ou plusieurs tablespaces dédiés et va assurer le suivi de l’historique d’une ou plusieurs tables.
On va donc lui définir une durée de conservation spacifique. Les donnés qui vont dépasser cette durée de conservation vont automatiquement être purgées. On pourra, par ailleurs, faire des purges à la demande.
De plus cela a une incidence minimale sur les performances et le stockage de ces données car elles seront stockées dans un format compressé.
Utilisation :
1. Créer un tablespace pour le Flashback Data Archive. (Par exemple FDA_TBS1. )
La taille de ce tablespace dépendra de l’activité sur la (les) tables qui sont archivées dans ce Flashback Data Archive.
2. Créer un user qui sera chargé d’administrer des FlashBack Data Archive :
SQL> CREATE USER archive_admin identified by welcome1 DEFAULT TABLESPACE fda_tbs1 TEMPORARY TABLESPACE TEMP ACCOUNT UNLOCK ; SQL> GRANT FLASHBACK_ARCHIVE_ADMINISTER to archive_admin ;
3. Créer un Flashback Data Archive et donner a l’utilisateur HR le privilège d’utiliser l’archive FLA1 :
SQL> Connect archive_admin / welcome1 SQL> CREATE FLASHBACK ARCHIVE fda1 TABLESPACE fda_tbs1 QUOTA 10M RETENTION 5 YEAR; SQL> GRANT FLASHBACK ARCHIVE on FLA1 to HR ;
4. Permettre l’archivage de la table EMPLOYEES dans le Flashback Data Archive fla1 :
SQL> Connect hr / hr SQL> ALTER TABLE EMPLOYEES FLASHBACK ARCHIVE FLA1 ;
Toutes les 5 minutes un nouveau processus en arrière-plan Flashback Data Archive ( nommé FBDA ) va aller chercher les données en mémoire ou dans le segment UNDO pour les mettre dans l’archive
. . .
2 ans plus tard , on pourra voir des valeurs de données 1 an avant
SQL> SELECT *
FROM HR.EMPLOYEES
AS OF TIMESTAMP TO_TIMESTAMP ('2009-01-01 00:00:00', 'YYYY-MMDD HH24:MI:SS');
Note : Attention ce flashback fait partie de l’option “Oracle Total Recall”, payante.
8 – FLASHBACK TRANSACTION BACKOUT ( FTB )
Principe : La Version 10g offrait la possibilité de voir l’ordre des transactions qui s’étaient déroulées. ( Voir 5 – Flashback Transaction Query ) et de visualiser l’ordre permettant de l’annuler.
En version 11g , grâce a l’option Flashback Transaction Backout on va pouvoir directement annuler une transaction spécifique et toutes les transactions dépendantes tout en gardant la base de données en ligne.
A noter qu’il faut être en mode ARCHIVELOG !!!
Définition : Une transaction dépendante est liée soit par une relation WAW (write-after-write) dans laquelle une transaction modifie les mêmes données que celles qui ont été modifiées par la transaction cible, soit par une contrainte de clé primaire dans laquelle une transaction réinsère la même valeur de clé primaire que celle qui a été supprimée par la transaction cible.
L’option Flashback Transaction Backout utilise les informations d’annulation et de journalisation associées aux blocs d’annulation pour créer et exécuter une transaction de compensation afin de rétablir les données affectées dans leur état d’origine.
Utilisation :
Il y a d’abord des options à positionner au niveau base :
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS; SQL> GRANT EXECUTE ON dbms_flashback TO hr; SQL> GRANT select any transaction TO hr; sqlplus HR/HR SQL> alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS'; /*=============== Test transaction ==============================*/ SQL> create table region2 as select * from regions ; /*=============== Test transaction 1 =============================*/ INSERT INTO hr.regions2 VALUES (10,'Pole'); INSERT INTO hr.regions2 VALUES (20,'Moon'); INSERT INTO hr.regions2 VALUES (30,'Venus'); INSERT INTO hr.regions2 VALUES (40,'Mars'); INSERT INTO hr.regions2 VALUES (50,'Saturn'); COMMIT; /*========================= Test transaction 2================================*/ /*== Region 10 et 20 ont une dependance WAW avec la tansaction 1 ==*/ /*=============================================================================*/ UPDATE hr.regions2 SET region_name='Two Poles' WHERE region_id = 10; UPDATE hr.regions2 SET region_name='Many Moons' WHERE region_id = 20; COMMIT; /*======================== Test transaction 3 ===================================*/ /*== Region 10 a une dependance WAW avec les transactions 1 and 2 ==*/ /*==============================================================================*/ /*== Region 40 and 50 has a WAW dependency on transaction 1 ==*/ UPDATE hr.regions2 SET region_name='No star' WHERE region_id = 10; UPDATE hr.regions2 SET region_name='Red star' WHERE region_id = 40; UPDATE hr.regions2 SET region_name='Big star' WHERE region_id = 50; COMMIT; /*======================== Test transaction 4 =======================*/ /*== Region 30 a une dependance WAW avec transaction 1 ==*/ /*==================================================================*/ UPDATE hr.regions2 SET region_name='Still called Venus' WHERE region_id = 30; COMMIT;
/* Il faut au moins un archivelog pour demarrer l’analyse de backout */
connect / as sysdba SQL > ALTER SYSTEM ARCHIVE LOG CURRENT;
On a les 4 transactions, on peut le voir avec l’ordre select suivant :
SQL> select table_owner , xid , operation , undo_sql 2 from FLASHBACK_TRANSACTION_QUERY 3 where logon_user='HR' 4* and table_name='REGIONS2' TABL XID OPERATION UNDO_SQL ---- ---------------- ---------- -------------------------------------------------- HR 03000D00A6030000 INSERT delete from "HR"."REGIONS3" where ROWID = 'AAASOmAAEAAAAJHAAB'; HR 03000D00A6030000 INSERT delete from "HR"."REGIONS3" where ROWID = 'AAASOmAAEAAAAJHAAA'; HR 03000D00A6030000 INSERT delete from "HR"."REGIONS3" where ROWID = 'AAASOmAAEAAAAJHAAC'; HR 03000D00A6030000 INSERT delete from "HR"."REGIONS3" where ROWID = 'AAASOmAAEAAAAJHAAD'; HR 03000D00A6030000 INSERT delete from "HR"."REGIONS3" where ROWID = 'AAASOmAAEAAAAJHAAE'; HR 020016007A030000 UPDATE update "HR"."REGIONS3" set "REGION_NAME" = 'Pole' where ROWID = 'AAASOmAAEAAAAJHAAA'; HR 020016007A030000 UPDATE update "HR"."REGIONS3" set "REGION_NAME" = 'Moon' where ROWID = 'AAASOmAAEAAAAJHAAB'; HR 06001E0096030000 UPDATE update "HR"."REGIONS3" set "REGION_NAME" = 'Mars' where ROWID = 'AAASOmAAEAAAAJHAAD'; HR 06001E0096030000 UPDATE update "HR"."REGIONS3" set "REGION_NAME" = 'Saturn' where ROWID = 'AAASOmAAEAAAAJHAAE'; HR 06001E0096030000 UPDATE update "HR"."REGIONS3" set "REGION_NAME" = 'Two Poles' where ROWID = 'AAASOmAAEAAAAJHAAA'; HR 09000A008C030000 UPDATE update "HR"."REGIONS3" set "REGION_NAME" = 'Venus' where ROWID = 'AAASOmAAEAAAAJHAAC';
On peut aussi le voir (et c’est plus facile) avec EM Database Control ( Onglet Availability > Manage > View and Manage Transactions ) Query Filter : Table_Name = HR.REGION2
On va se positionner sur la troisieme transaction ( celle correspondant aux 2 updates , la premiere etant T0 , la seconde T1 )
Si on clique sur transaction ID , on peut visualiser les 2 ordres updates de la transaction ( XID de la transaction : 020016007A030000 )
Décidons que c’est cette transaction que l’on veut annuler , on va cliquer sur le bouton « FlashBack Transaction » .
Allons y !!! Lançons nous !!!! ( On remarque avant que l’option par defaut est NONCONFLICT_ONLY )
Si on clique sur le premier lien de transaction ID on voit :
[exec=yes] update "HR"."REGIONS3" set "REGION_NAME" = 'Moon' where "REGION_ID" = '20' and "REGION_NAME" = 'Many Moons' and ROWID = 'AAASOmAAEAAAAJHAAB' [exec=no] update "HR"."REGIONS3" set "REGION_NAME" = 'Pole' where "REGION_ID" = '10' and "REGION_NAME" = 'Two Poles' and ROWID = 'AAASOmAAEAAAAJHAAA'
Cela signifie qu’il va executer la transaction correspondant a la region 20. Mais pas la transaction correspondant a la region 10 ( En effet il ne peut pas l’annuler car il y a une WAW avec la transaction 3 , et on est en mode NONCONFLICT_ONLY)
On va changer l’option Change Recovery Option = Cascade ( En cliquant sur le bouton a cet effet )
Si on clique sur le premier lien de transaction ID on voit :
[exec=yes] update "HR"."REGIONS3" set "REGION_NAME" = 'Moon' where "REGION_ID" = '20' and "REGION_NAME" = 'Many Moons' and ROWID = 'AAASOmAAEAAAAJHAAB' [exec=yes] update "HR"."REGIONS3" set "REGION_NAME" = 'Pole' where "REGION_ID" = '10' and "REGION_NAME" = 'Two Poles' and ROWID = 'AAASOmAAEAAAAJHAAA'
Cela signifie qu’il va executer la transaction correspondant aux régions 10 et 20. Et donc annuler TOUTE la transaction 2 et revenir aux valeurs de T1 pour ces 2 lignes (mode CASCADE).
Si on clique sur le bouton Next , et avant de FINALISER le backout , on peut faire un select de la table REGIONS2.
Dans la case « Execute SQL » , on tape : « SELECT * FROM REGIONS2 » et on visualise l’état qu’aurait la table APRES exécution du Backout.
On poura aussi faire du FTB (Flashback Transactional Backout ) en utilisant la procédure TRANSACTION_BACKOUT du package DBMS_FLASHBACK.
Exemple :
begin
dbms_flashback.TRANSACTION_BACKOUT
( numtxns => 1 ,
xids => xid_array('03001200ED020000'),
options => DBMS_FLASHBACK.CASCADE );
end;
/
En resumé : En voulant annuler une transaction et suivant l’option ( NOCONFLICT_ONLY , CASCADE , … ) que l’on a choisit on peut annuler des ordres non désirés. Donc cette fonctionnalité est a utiliser avec vraiement beaucoup de precaution !!!!!!
Résumé du FLASHBACK :
Quand on utilise la notion de FLASHBACK , il faut bien savoir de quoi l’on parle car les Flashback sont tous différents les uns des autres !
