De nombreux projets exigent désormais de tracer les modifications de vos bases de données. Pour cela, il existe plusieurs technologies qui vont des triggers « for each row » à Log Miner, Streams, GoldenGate ou Change Data Capture, de-supporté dans les futures versions.
Si votre besoin est d’historiser les modifications, Total Recall (a.k.a Flashback Archive) est une solution extrêmement efficace. Elle adresse la complexité de gestion des méthodes manuelles. Elle est simple, performante, robuste, souple et sécurisée ; cet article illustre ces différents aspects…
Simplicité
Pour mettre en place une flashback archive et ainsi tracer l’ensemble des modifications d’une table, il suffit de créer un tablespace et une flashback archive à l’aide de la commande create flashback archive comme ci-dessous :
create tablespace flasharchive_tbs
datafile size 256M;
create flashback archive flasharchive
tablespace flasharchive_tbs
retention 2 year;
set tab off
col owner_name format a10
col FLASHBACK_ARCHIVE_NAME format a15 head ARCHIVE
col RETENTION_IN_DAYS format 9999 head DAYS
select OWNER_NAME, FLASHBACK_ARCHIVE_NAME, RETENTION_IN_DAYS
from dba_flashback_archive;
OWNER_NAME ARCHIVE DAYS
---------- --------------- -----
SYS FLASHARCHIVE 730
col tablespace_name format a18 head TABLESPACE
col QUOTA_IN_MB format a10 head QUOTAS
select FLASHBACK_ARCHIVE_NAME, TABLESPACE_NAME, QUOTA_IN_MB
from DBA_FLASHBACK_ARCHIVE_TS;
ARCHIVE TABLESPACE QUOTAS
--------------- ------------------ ----------
FLASHARCHIVE FLASHARCHIVE_TBS
Pour qu’un utilisateur puisse l’utiliser, il suffit alors de lui donner le privilège objet flashback archive sur la-dite archive comme ci-dessous :
grant flashback archive on flasharchive to demo;
Pous simplifier encore l’utilisation de Total Recall, vous pouvez définir une flashback archive par défaut :
alter flashback archive flasharchive set default
Et voilà, la clause FLASHBACK ARCHIVE des commandes CREATE TABLE et ALTER TABLE permet d’utiliser les archives ainsi créées aussi simplement que s’il s’agissait d’une table et son tablespace d’UNDO :
connect demo/demo
create table T1 (
id number,
text varchar2(10))
flashback archive flasharchive;
create table T2 (
id number,
text varchar2(10));
alter session set nls_date_format='DD/MM/YYYY HH24:MI:SS';
col sysdate new_value tsp
select sysdate from dual;
insert into T1 values (1,'X');
insert into T2 values (1,'X');
commit;
delete from T1;
delete from T2;
commit;
set tab off
col id format 99
col text format a5
select t1.*, versions_operation, versions_startscn, versions_endscn
from T1 versions
between timestamp cast(to_date('&&tsp','DD/MM/YYYY HH24:MI:SS') as timestamp)
and systimestamp
order by versions_startscn;
ID TEXT V VERSIONS_STARTSCN VERSIONS_ENDSCN
--- ----- - ----------------- ---------------
1 X I 1124454 1124458
1 X D 1124458
select t2.*, versions_operation, versions_startscn, versions_endscn
from T2
versions between timestamp cast(to_date('&&tsp','DD/MM/YYYY HH24:MI:SS') as timestamp)
and systimestamp
order by versions_startscn;
ID TEXT V VERSIONS_STARTSCN VERSIONS_ENDSCN
--- ----- - ----------------- ---------------
1 X I 1124454 1124458
1 X D 1124458
Vous pouvez visualiser les tables ayant des flashback archives à l’aide de la commande ci-dessous :
col "TABLE" format a8
col "ARCHIVE_TABLE_NAME" format a18
select OWNER_NAME||'.'||TABLE_NAME "TABLE",
FLASHBACK_ARCHIVE_NAME,
ARCHIVE_TABLE_NAME,
status
from DBA_FLASHBACK_ARCHIVE_TABLES
TABLE ARCHIVE ARCHIVE_TABLE_NAME STATUS
-------- --------------- ------------------ -------
DEMO.T1 FLASHARCHIVE SYS_FBA_HIST_75843 ENABLED
Performance
Comme vous en vous rendrez compte en lançant des ordres DML, l’impact de cette fonctionnalité est relativement marginale du fait de son architecture basée sur les tablespaces UNDO et le process FBDA dédié à la capture des données UNDO et des métadonnées de la table :
insert into T1
(select rownum,'X'
from dual
connect by level <= 100000);
100000 rows created.
Elapsed: 00:00:00.11
insert into T2
(select rownum,'X'
from dual
connect by level <= 100000);
100000 rows created.
Elapsed: 00:00:00.10
delete from T1;
100000 rows deleted.
Elapsed: 00:00:01.18
delete from T2;
100000 rows deleted.
Elapsed: 00:00:01.22
commit;
Robustesse
La solution est robuste ; changez le tablespace d’undo à l’aide de la commande ci-dessous :
connect / as sysdba
create undo tablespace undo2 datafile size 256M;
alter system set undo_tablespace=undo2 scope=both;
alter system switch logfile;
alter system checkpoint;
drop tablespace undotbs1 including contents and datafiles;
Vous retrouvez vos données dans la table avec une flashback archive alors que la table qui n’en a pas renvoie l’erreur ORA-01555 :
select t2.*, versions_operation, versions_startscn, versions_endscn
from T2
versions between scn 1124454
and 1124459
order by versions_startscn;
ERROR at line 2:
ORA-01555: snapshot too old: rollback segment number with name "" too small
set tab off
col id format 99
col text format a5
select t1.*, versions_operation, versions_startscn, versions_endscn
from T1
versions between scn 1124454
and 1124459
order by versions_startscn;
ID TEXT V VERSIONS_STARTSCN VERSIONS_ENDSCN
--- ----- - ----------------- ---------------
1 X I 1124454 1124458
1 X D 1124458 1124458
Souplesse
La plupart des fonctionnalités de flashback sont disponibles; y compris la capacité de revenir en arrière avec la commande flashback table et ceci, même s’il n’en est pas fait mention dans la documentation :
alter table t1 enable row movement;
flashback table t1 to scn 1124455;
select *
from T1;
ID TEXT
--- -----
1 X
En outre vous pourrez continuer à utiliser certaines commandes DDL comme pour ajouter/supprimer une colonne, renommer la table ou lancer un TRUNCATE.
Sécurité
Enfin la solution est sécurisée ; ainsi même si vous avez le droit de créer et supprimer une table, cela devient impossible même pour son propriétaire lorsque l’archive est active :
drop table T1;
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table
alter table t1 no flashback archive;
*
ERROR at line 1:
ORA-55620: No privilege to use Flashback Archive
Pour permettre ce type d’opérations, il faut explicitement ou via le role DBA donner le privilège système
connect / as sysdba
grant flashback archive administer to demo;
connect demo/demo
alter table t1 no flashback archive;
drop table T1 purge;
drop table T2 purge;
