Il y a plusieurs mois, un client m’a sollicité, un peu trop tard malheureusement, afin d’annuler une mise à jour massive effectuée de façon malencontreuse dans une table critique d’une de ses bases de données de «production», à savoir la table relative à ses clients.
Pour qu’à l’avenir, et en cas de besoin similaire, nous n’ayons plus à employer des méthodes chronophages et risquées pour arriver à terme, j’ai évoqué à mon client déjà satisfait de sa base de données «12c», la possibilité qu’il le soit encore plus en implémentant «Flashback data archive».
La fonctionnalité «Flashback data archive» est arrivée en option avec Oracle 11g et est désormais intégrée en 12c, aux fonctionnalités d’une «Enterprise Edition». Elle permet de gérer des données historiques concernant des tables particulières à cibler, en conservant dans le temps toutes les «images avant» des blocs leurs étant relatifs.
La différence entre cette fonctionnalité et les autres technologies de flashback (Flashback table, Flashback query, Flashback transaction, Flashback version) est la persistance dans le temps des informations historisées, qui le sont dans des objets archives spécifiques dédiés et non pas dans le/les tablespace(s) d’annulation. De ce fait, grâce à cette option, l’annulation d’ordres « SQL » qui auraient été exécutés de façon malencontreuse dans les tables ciblées, peut l’être bien après la durée définie, entre autres, par le paramètre « UNDO_RETENTION ».
La fonctionnalité «Flashback Database» (s’appuyant également sur des archives spécifiques, les «Flashback log») ne permet quant à elle pas de répondre au besoin exprimé ici, dans le sens où sa granularité est la base de données.
«Flashback data archive» est beaucoup plus efficace que des solutions spécifiques qui seraient, par exemple, basées sur l’utilisation de triggers dédiés dont le coût en termes de maintenance ne serait pas négligeable, et dont les effets sur les performances globales de la base de données seraient plus conséquents.
Dans cet article, nous allons appréhender les différentes étapes de la mise en place de cette fonctionnalité pour une table ciblée, afin de pouvoir dans un second temps interagir avec ses données historiques.
La base de données sur laquelle nous nous appuierons est une PDB (Pluggable database), attachée à une CDB (Container database) en version 12.1.0.2.0 Enterprise Edition
A) Préparation de la base de données
- Création d’un tablespace qui stockera les archives de données
SQL> CREATE TABLESPACE "DATAARCHIVES" DATAFILE '/home/oracle/app/oracle/oradata/cdb1/orcl/dataarchives01.dbf' SIZE 1G AUTOEXTEND ON NEXT 500M MAXSIZE 5G; Tablespace created.
- Affectation à l’administrateur, des privilèges nécessaires à la gestion des archives
SQL> grant FLASHBACK ARCHIVE ADMINISTER to sys; Grant succeeded.
B) Créer un objet «Flashback Data Archive» et activer le «Flashback Archiving» sur notre table critique
La table évoquée par mon client et dans laquelle une mise à jour non souhaitée a été effectuée se nomme « customers » et appartient à un schéma nommé « sales ».
Mon client a souhaité être en mesure de consulter rapidement et facilement, à l’avenir, une année complète d’historique des données de cette table.
L’objet «Flashback Data Archive» amené à stocker les dites-données dans le tablespace dédié a été créé de cette façon:
SQL> create flashback archive customers tablespace DATAARCHIVES quota 5G retention 1 YEAR; Flashback archive created.
Une fois l’objet «Flashback Data Archive» créé, il a fallu activer le suivi d’historique des données de la table « sales.customers » :
SQL> alter table sales.customers flashback archive customers; Table altered.
Enfin, mon client souhaitait être en mesure d’interroger les données d’historique de la table « customers », en cas de besoin, en tant qu’utilisateur « sales ».
Il a donc fallu accorder les privilèges adéquats à ce dernier:
SQL> grant flashback archive on customers to sales; Grant succeeded. SQL> grant all on DBMS_FLASHBACK to sales; Grant succeeded.
C) Vérifions désormais l’existence de l’objet «FLASHBACK DATA ARCHIVE», dans notre base de données:
- Quels sont les autres objets «Flashback Data Archives» présents et quand ont-ils été créés?
SQL> select flashback_archive_name, create_time from DBA_FLASHBACK_ARCHIVE; FLASHBACK_ARCHIVE_NAME --------------------------------------------------------------------------- CREATE_TIME --------------------------------------------------------------------------- CUSTOMERS 20-SEP-15 09.11.52.000000000 AM
- Quels sont les tablespaces utilisés par les objets «Flashback Data Archives» existants?
SQL> select tablespace_name from DBA_FLASHBACK_ARCHIVE_TS where flashback_archive_name='CUSTOMERS'; TABLESPACE_NAME ------------------------------ DATAARCHIVES
- Quel est le nom de table d’archive associée à la table « sales.customers?
SQL> select ARCHIVE_TABLE_NAME from DBA_FLASHBACK_ARCHIVE_TABLES where TABLE_NAME='CUSTOMERS' and OWNER_NAME='sales'; ARCHIVE_TABLE_NAME ----------------------------------------------------- SYS_FBA_HIST_92114
D) Interroger et récupérer des données historiques de notre table critique
Quelques semaines après l’activation du «Flashback Archiving» dans la table «sales.CUSTOMERS» de sa base de données de production, mon client m’a rappelé après s’être aperçu que du fait d’une nouvelle erreur applicative ayant eu lieu une semaine auparavant: la limite du crédit autorisé à certains de ses clients avait été modifiée!
Mon client avait constaté ce problème, du fait de la limite du crédit autorisé à une de ses clientes particulières, qui ne correspondait plus du tout à la réalité.
L’histoire se répétait donc!
Cette fois néanmoins, nous allions être en mesure de rétablir aisément la situation et ce, même si la valeur associée au « UNDO_RETENTION » défini dans la base de données incriminée, est bien inférieure à une semaine.
Afin d’aboutir beaucoup plus rapidement que la dernière fois, j’ai donc invité mon client, à revérifier le montant du crédit nouvellement autorisé à Mademoiselle Adriana Campbell, lors de ses séances de shopping à Buenos Aires:
SQL> select CUST_CREDIT_LIMIT from CUSTOMERS where CUST_FIRST_NAME='Adriana' and CUST_LAST_NAME='Campbell' and CUST_STATE_PROVINCE='Buenos Aires'; CUST_CREDIT_LIMIT ----------------- 10000
Mon client m’affirmant de nouveau qu’une semaine auparavant, Mademoiselle Adriana Campbell n’était pas autorisée à un crédit de plus de 7000 dollars, et que rien n’explique officiellement que cela ne soit plus ainsi.
Mon client ne connaissait pas le nombre de lignes impactées par cette modification malencontreuse, mais a souhaité rapidement rétablir la situation pour ce cas particulier.
Nous avons donc, en amont d’une application plus globale, testé l’application du correctif en ciblant les données relatives à sa cliente préférée:
SQL> DELETE from customers WHERE CUST_FIRST_NAME='Adriana' and CUST_LAST_NAME='Campbell' and CUST_STATE_PROVINCE='Buenos Aires'; 1 row deleted. SQL> INSERT INTO customers SELECT * FROM customers AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '7' DAY) where CUST_FIRST_NAME='Adriana' and CUST_LAST_NAME='Campbell' and CUST_STATE_PROVINCE='Buenos Aires'; 1 row created. SQL> select CUST_CREDIT_LIMIT from CUSTOMERS where CUST_FIRST_NAME='Adriana' and CUST_LAST_NAME='Campbell' and CUST_STATE_PROVINCE='Buenos Aires'; CUST_CREDIT_LIMIT ----------------- 7000
Désormais, l’erreur était donc bien corrigeable en l’espace de quelques secondes, alors que cela aurait de nouveau pris énormément de temps avec la méthode précédente d’il y a quelques mois.
Vous jetterez une attention toute particulière à la requête d’insertion, s’appuyant sur une sélection des données présentes dans la table « sales.customers », une semaine auparavant (clause «AS OF TIMESTAMP»).
Mon client était donc heureux, Mademoiselle Campbell allait certainement l’être un peu moins…
Dans cet article, nous avons mis en œuvre les différentes étapes nécessaires pour implémenter l’historisation des données d’une table et ce, sur une année complète.
Nous avons également illustré l’utilisation de cette historisation spécifique, afin d’annuler aisément et très rapidement, une erreur utilisateur s’étant produite à une date bien antérieure à la durée de rétention (UNDO_RETENTION) définie dans notre base de données.
Le fait qu’un tel besoin de conservation permette également de répondre à d’éventuelles contraintes réglementaires est également à noter. Dans un tel cadre, «FDA» peut en effet aisément se substituer à des fonctionnalités applicatives potentiellement couteuses.
Plusieurs sujets concernant « Flashback Data Archive » n’ont pas été abordés ici (gestion/fonctionnement des «Flashback Data Archives», restrictions LDD(s) sur les tables historiées, optimisation de la gestion de l’annulation (UNDO) afin d’obtenir des résultats plus optimaux, prise en charge de l’évolution complète de schémas, etc…). Je vous invite à vous y intéresser également, dans l’optique d’utiliser cette fonctionnalité intéressante de façon optimale.
En complément de la gestion des «Flashback Data Archives», il peut être intéressant de regarder la possibilité de mettre en place la compression pour ces objets. Cette fonctionnalité, bien que payante, peut s’avérer très utilise en terme de gestion de l’espace consommé par les archives spécifiques évoquées.
