One of 11g new features is Flashback Data Archive, or in marketing terms: Oracle Total Recall. With earlier flashback technologies it was possible to access Oracle's undo information for as long as it was available. With flashback archives you can extend this period for as long as you specify. This makes it possible to implement journaling using this new technique. This blog entry describes how to do this and a number of things you need to be aware of.
Before 11g, journaling was typically implemented using a separate table with the same columns as the base table, plus three additional columns: jn_operation (Insert/Update/Delete), jn_user and jn_datetime. Three after row triggers on the base table made sure every DML was recorded in the journaling table. So let's see how the same can be achieved using flashback archives.
First of all I create a tablespace especially for flashback archives:
rwijk@ORA11G> create tablespace my_tablespace datafile 'extra_file.dat' size 10M
2 /
Tabelruimte is aangemaakt.
In the tablespace I can define several flashback archives each with its own retention period. For example:
rwijk@ORA11G> create flashback archive flashback_archive_10_years
2 tablespace my_tablespace
3 retention 10 year
4 /
Flashbackarchief is aangemaakt.
rwijk@ORA11G> create flashback archive flashback_archive_5_years
2 tablespace my_tablespace
3 retention 5 year
4 /
Flashbackarchief is aangemaakt.
And I create a table my_emp and attach it to the flashback archive with the 10 year retention period:
rwijk@ORA11G> create table my_emp
2 ( empno number(4) not null
3 , ename varchar2(10)
4 , sal number(7,2)
5 , deptno number(2)
6 ) partition by range (deptno)
7 ( partition deptno10 values less than (11)
8 , partition deptno20 values less than (21)
9 , partition deptno30 values less than (31)
10 , partition deptnorest values less than (maxvalue)
11 ) flashback archive flashback_archive_10_years
12 /
Tabel is aangemaakt.
The range partitioning is not required; I only do this to show one point later on in this blog entry.
The information about the flashback archives is available in the views DBA_FLASHBACK_ARCHIVE, DBA_FLASHBACK_ARCHIVE_TS and DBA_FLASHBACK_ARCHIVE_TABLES:
rwijk@ORA11G> select * from dba_flashback_archive
2 /
FLASHBACK_ARCHIVE_NAME FLASHBACK_ARCHIVE# RETENTION_IN_DAYS
-------------------------- ------------------ -----------------
CREATE_TIME LAST_PURGE_TIME STATUS
------------------------------ ------------------------------ -------
FLASHBACK_ARCHIVE_5_YEARS 2 1825
27-11-07 23:53:51,000000000 27-11-07 23:53:51,000000000
FLASHBACK_ARCHIVE_10_YEARS 1 3650
27-11-07 23:53:51,000000000 27-11-07 23:53:51,000000000
2 rijen zijn geselecteerd.
rwijk@ORA11G> select * from dba_flashback_archive_ts
2 /
FLASHBACK_ARCHIVE_NAME FLASHBACK_ARCHIVE# TABLESPACE_NAME
-------------------------- ------------------ ------------------------------
QUOTA_IN_MB
----------------------------------------
FLASHBACK_ARCHIVE_5_YEARS 2 MY_TABLESPACE
FLASHBACK_ARCHIVE_10_YEARS 1 MY_TABLESPACE
2 rijen zijn geselecteerd.
rwijk@ORA11G> select * from dba_flashback_archive_tables
2 /
TABLE_NAME OWNER_NAME
------------------------------ ------------------------------
FLASHBACK_ARCHIVE_NAME ARCHIVE_TABLE_NAME
-------------------------- ------------------------------
MY_EMP RWIJK
FLASHBACK_ARCHIVE_10_YEARS SYS_FBA_HIST_71097
1 rij is geselecteerd.
Now let's do some DML against this new table:
rwijk@ORA11G> insert into my_emp
2 select empno, ename, sal, deptno from emp
3 /
14 rijen zijn aangemaakt.
rwijk@ORA11G> commit
2 /
Commit is voltooid.
rwijk@ORA11G> update my_emp
2 set sal = sal * 1.1
3 where deptno = 20
4 /
5 rijen zijn bijgewerkt.
rwijk@ORA11G> commit
2 /
Commit is voltooid.
rwijk@ORA11G> update my_emp
2 set ename = initcap(ename)
3 where deptno != 30
4 /
8 rijen zijn bijgewerkt.
rwijk@ORA11G> commit
2 /
Commit is voltooid.
rwijk@ORA11G> insert into my_emp (empno,ename,sal,deptno)
2 values (7777,'VAN WIJK', 1600, 40)
3 /
1 rij is aangemaakt.
rwijk@ORA11G> delete my_emp
2 where sal = 3300
3 /
2 rijen zijn verwijderd.
rwijk@ORA11G> commit
2 /
Commit is voltooid.
And use a Flashback Version Query to retrieve all information:
rwijk@ORA11G> select versions_xid
2 , versions_startscn
3 , versions_starttime
4 , versions_endscn
5 , versions_endtime
6 , versions_operation
7 , empno
8 , ename
9 , sal
10 , deptno
11 from my_emp versions between scn minvalue and maxvalue
12 order by empno
13 , versions_startscn
14 /
VERSIONS_XID VERSIONS_STARTSCN VERSIONS_STARTTIME VERSIONS_ENDSCN
---------------- ----------------- -------------------- ---------------
VERSIONS_ENDTIME V EMPNO ENAME SAL DEPTNO
-------------------- - ----- ---------- ----- ------
0A00030079030000 1589358 27-11-07 23:58:39 1589361
27-11-07 23:58:39 I 7369 SMITH 800 20
0400070075030000 1589361 27-11-07 23:58:39 1589365
27-11-07 23:58:39 U 7369 SMITH 880 20
0700110078030000 1589365 27-11-07 23:58:39
U 7369 Smith 880 20
0A00030079030000 1589358 27-11-07 23:58:39
I 7499 ALLEN 1600 30
0A00030079030000 1589358 27-11-07 23:58:39
I 7521 WARD 1250 30
0A00030079030000 1589358 27-11-07 23:58:39 1589361
27-11-07 23:58:39 I 7566 JONES 2975 20
0400070075030000 1589361 27-11-07 23:58:39 1589365
27-11-07 23:58:39 U 7566 JONES 3273 20
0700110078030000 1589365 27-11-07 23:58:39
U 7566 Jones 3273 20
0A00030079030000 1589358 27-11-07 23:58:39
I 7654 MARTIN 1250 30
0A00030079030000 1589358 27-11-07 23:58:39
I 7698 BLAKE 2850 30
09001C00B5040000 1589368 27-11-07 23:58:39
I 7777 VAN WIJK 1600 40
0A00030079030000 1589358 27-11-07 23:58:39 1589365
27-11-07 23:58:39 I 7782 CLARK 2450 10
0700110078030000 1589365 27-11-07 23:58:39
U 7782 Clark 2450 10
0A00030079030000 1589358 27-11-07 23:58:39 1589361
27-11-07 23:58:39 I 7788 SCOTT 3000 20
0400070075030000 1589361 27-11-07 23:58:39 1589365
27-11-07 23:58:39 U 7788 SCOTT 3300 20
0700110078030000 1589365 27-11-07 23:58:39 1589368
27-11-07 23:58:39 U 7788 Scott 3300 20
09001C00B5040000 1589368 27-11-07 23:58:39
D 7788 Scott 3300 20
0A00030079030000 1589358 27-11-07 23:58:39 1589365
27-11-07 23:58:39 I 7839 KING 5000 10
0700110078030000 1589365 27-11-07 23:58:39
U 7839 King 5000 10
0A00030079030000 1589358 27-11-07 23:58:39
I 7844 TURNER 1500 30
0A00030079030000 1589358 27-11-07 23:58:39 1589361
27-11-07 23:58:39 I 7876 ADAMS 1100 20
0400070075030000 1589361 27-11-07 23:58:39 1589365
27-11-07 23:58:39 U 7876 ADAMS 1210 20
0700110078030000 1589365 27-11-07 23:58:39
U 7876 Adams 1210 20
0A00030079030000 1589358 27-11-07 23:58:39
I 7900 JAMES 950 30
0A00030079030000 1589358 27-11-07 23:58:39 1589361
27-11-07 23:58:39 I 7902 FORD 3000 20
0400070075030000 1589361 27-11-07 23:58:39 1589365
27-11-07 23:58:39 U 7902 FORD 3300 20
0700110078030000 1589365 27-11-07 23:58:39 1589368
27-11-07 23:58:39 U 7902 Ford 3300 20
09001C00B5040000 1589368 27-11-07 23:58:39
D 7902 Ford 3300 20
0A00030079030000 1589358 27-11-07 23:58:39 1589365
27-11-07 23:58:39 I 7934 MILLER 1300 10
0700110078030000 1589365 27-11-07 23:58:39
U 7934 Miller 1300 10
30 rijen zijn geselecteerd.
So indeed all journaling information is available here. All, but one: the user who modified the data is not there. So let's audit the table to get this piece of information as well and create a journaling view to join the audit information with the journaling info.
rwijk@ORA11G> audit insert,update,delete on my_emp by access
2 /
Audit is geslaagd.
rwijk@ORA11G> create view my_emp_jn_v
2 as
3 select me.versions_operation jn_operation
4 , ao.username jn_user
5 , me.versions_starttime jn_datetime
6 , me.versions_startscn jn_scn
7 , me.empno
8 , me.ename
9 , me.sal
10 , me.deptno
11 from my_emp versions between scn minvalue and maxvalue me
12 , user_audit_object ao
13 where me.versions_xid = ao.transactionid (+)
14 /
View is aangemaakt.
And do some more transactions, including one transaction in another session by a user called SOMEBODY_ELSE:
rwijk@ORA11G> insert into my_emp (empno,ename,sal,deptno)
2 values (7778, 'ROB', 1700, 40)
3 /
1 rij is aangemaakt.
rwijk@ORA11G> commit
2 /
Commit is voltooid.
rwijk@ORA11G> create user somebody_else identified by somebody_else
2 /
Gebruiker is aangemaakt.
rwijk@ORA11G> grant create session to somebody_else
2 /
Toekennen is geslaagd.
rwijk@ORA11G> grant delete on my_emp to somebody_else
2 /
Toekennen is geslaagd.
rwijk@ORA11G> remark Start another session and connect as somebody_else
rwijk@ORA11G> remark and issue the following commands:
rwijk@ORA11G> remark
rwijk@ORA11G> remark delete rwijk.my_emp where empno = 7777
rwijk@ORA11G> remark /
rwijk@ORA11G> remark commit
rwijk@ORA11G> remark /
rwijk@ORA11G> remark exit
rwijk@ORA11G> pause
rwijk@ORA11G> drop user somebody_else
2 /
Gebruiker is verwijderd.
rwijk@ORA11G> update my_emp
2 set sal = 1800
3 where empno = 7778
4 /
1 rij is bijgewerkt.
rwijk@ORA11G> commit
2 /
Commit is voltooid.
And look, the missing piece of information is also here:
rwijk@ORA11G> select *
2 from my_emp_jn_v
3 order by empno
4 , jn_scn
5 /
J JN_USER JN_DATETIME JN_SCN EMPNO ENAME SAL DEPTNO
- ------------- ------------------- -------- ----- ---------- ----- ------
I 28-11-07 00:05:03 1590771 7369 SMITH 800 20
U 28-11-07 00:05:03 1590774 7369 SMITH 880 20
U 28-11-07 00:05:03 1590778 7369 Smith 880 20
I 28-11-07 00:05:03 1590771 7499 ALLEN 1600 30
I 28-11-07 00:05:03 1590771 7521 WARD 1250 30
I 28-11-07 00:05:03 1590771 7566 JONES 2975 20
U 28-11-07 00:05:03 1590774 7566 JONES 3273 20
U 28-11-07 00:05:03 1590778 7566 Jones 3273 20
I 28-11-07 00:05:03 1590771 7654 MARTIN 1250 30
I 28-11-07 00:05:03 1590771 7698 BLAKE 2850 30
I 28-11-07 00:05:03 1590781 7777 VAN WIJK 1600 40
D SOMEBODY_ELSE 28-11-07 00:05:21 1590838 7777 VAN WIJK 1600 40
I RWIJK 28-11-07 00:05:06 1590799 7778 ROB 1700 40
U RWIJK 28-11-07 00:05:33 1590859 7778 ROB 1800 40
I 28-11-07 00:05:03 1590771 7782 CLARK 2450 10
U 28-11-07 00:05:03 1590778 7782 Clark 2450 10
I 28-11-07 00:05:03 1590771 7788 SCOTT 3000 20
U 28-11-07 00:05:03 1590774 7788 SCOTT 3300 20
U 28-11-07 00:05:03 1590778 7788 Scott 3300 20
D 28-11-07 00:05:03 1590781 7788 Scott 3300 20
I 28-11-07 00:05:03 1590771 7839 KING 5000 10
U 28-11-07 00:05:03 1590778 7839 King 5000 10
I 28-11-07 00:05:03 1590771 7844 TURNER 1500 30
I 28-11-07 00:05:03 1590771 7876 ADAMS 1100 20
U 28-11-07 00:05:03 1590774 7876 ADAMS 1210 20
U 28-11-07 00:05:03 1590778 7876 Adams 1210 20
I 28-11-07 00:05:03 1590771 7900 JAMES 950 30
I 28-11-07 00:05:03 1590771 7902 FORD 3000 20
U 28-11-07 00:05:03 1590774 7902 FORD 3300 20
U 28-11-07 00:05:03 1590778 7902 Ford 3300 20
D 28-11-07 00:05:03 1590781 7902 Ford 3300 20
I 28-11-07 00:05:03 1590771 7934 MILLER 1300 10
U 28-11-07 00:05:03 1590778 7934 Miller 1300 10
33 rijen zijn geselecteerd.
Note that I also used the SCN in the view, to be able to sort even when the transactions occur within one second.
So it looks very promising... well, until you read this warning in the documentation
Remember that DDLs that alter the structure of a table (such as drop/modify column, move table, drop partition, truncate table/partition, and add constraint) invalidate any existing undo data for the table. If you try to retrieve data from a time before such a DDL executed, you will get error ORA-1466.
Now this is a serious showstopper: in my experience the tables that need journaling are also the tables that require regular maintenance. And if that maintenance would invalidate existing undo ...
Let's test it (and here is why I range partitioned the table):
rwijk@ORA11G> alter table my_emp drop column comm
2 /
alter table my_emp drop column comm
*
FOUT in regel 1:
.ORA-55610: Ongeldig DDL-statement op tabel met historietracking
rwijk@ORA11G> alter table my_emp modify (ename varchar2(50))
2 /
alter table my_emp modify (ename varchar2(50))
*
FOUT in regel 1:
.ORA-55610: Ongeldig DDL-statement op tabel met historietracking
rwijk@ORA11G> alter table my_emp drop partition deptno10
2 /
alter table my_emp drop partition deptno10
*
FOUT in regel 1:
.ORA-55610: Ongeldig DDL-statement op tabel met historietracking
rwijk@ORA11G> truncate table my_emp
2 /
truncate table my_emp
*
FOUT in regel 1:
.ORA-55610: Ongeldig DDL-statement op tabel met historietracking
So Oracle does not even allow us to do this type of actions, and the existing undo is not harmed:
rwijk@ORA11G> select *
2 from my_emp_jn_v
3 order by empno
4 , jn_scn
5 /
J JN_USER JN_DATETIME JN_SCN EMPNO ENAME SAL DEPTNO
- ------------- ------------------- -------- ----- ---------- ----- ------
I 28-11-07 00:05:03 1590771 7369 SMITH 800 20
U 28-11-07 00:05:03 1590774 7369 SMITH 880 20
U 28-11-07 00:05:03 1590778 7369 Smith 880 20
I 28-11-07 00:05:03 1590771 7499 ALLEN 1600 30
I 28-11-07 00:05:03 1590771 7521 WARD 1250 30
I 28-11-07 00:05:03 1590771 7566 JONES 2975 20
U 28-11-07 00:05:03 1590774 7566 JONES 3273 20
U 28-11-07 00:05:03 1590778 7566 Jones 3273 20
I 28-11-07 00:05:03 1590771 7654 MARTIN 1250 30
I 28-11-07 00:05:03 1590771 7698 BLAKE 2850 30
I 28-11-07 00:05:03 1590781 7777 VAN WIJK 1600 40
D SOMEBODY_ELSE 28-11-07 00:05:21 1590838 7777 VAN WIJK 1600 40
I RWIJK 28-11-07 00:05:06 1590799 7778 ROB 1700 40
U RWIJK 28-11-07 00:05:33 1590859 7778 ROB 1800 40
I 28-11-07 00:05:03 1590771 7782 CLARK 2450 10
U 28-11-07 00:05:03 1590778 7782 Clark 2450 10
I 28-11-07 00:05:03 1590771 7788 SCOTT 3000 20
U 28-11-07 00:05:03 1590774 7788 SCOTT 3300 20
U 28-11-07 00:05:03 1590778 7788 Scott 3300 20
D 28-11-07 00:05:03 1590781 7788 Scott 3300 20
I 28-11-07 00:05:03 1590771 7839 KING 5000 10
U 28-11-07 00:05:03 1590778 7839 King 5000 10
I 28-11-07 00:05:03 1590771 7844 TURNER 1500 30
I 28-11-07 00:05:03 1590771 7876 ADAMS 1100 20
U 28-11-07 00:05:03 1590774 7876 ADAMS 1210 20
U 28-11-07 00:05:03 1590778 7876 Adams 1210 20
I 28-11-07 00:05:03 1590771 7900 JAMES 950 30
I 28-11-07 00:05:03 1590771 7902 FORD 3000 20
U 28-11-07 00:05:03 1590774 7902 FORD 3300 20
U 28-11-07 00:05:03 1590778 7902 Ford 3300 20
D 28-11-07 00:05:03 1590781 7902 Ford 3300 20
I 28-11-07 00:05:03 1590771 7934 MILLER 1300 10
U 28-11-07 00:05:03 1590778 7934 Miller 1300 10
33 rijen zijn geselecteerd.
Most of the time, maintenance is done by adding one or two columns, with some additional constraints. The adding of a column is not a problem for flashback archives, but adding constraints was also listed as being impossible. Let's verify that as well, by adding a primary key, unique key, foreign key and a check constraint:
rwijk@ORA11G> alter table my_emp add constraint my_pk primary key (empno)
2 /
Tabel is gewijzigd.
rwijk@ORA11G> alter table my_emp add constraint my_uk1 unique (ename)
2 /
Tabel is gewijzigd.
rwijk@ORA11G> alter table my_emp add constraint my_fk1 foreign key (deptno) references dept(deptno)
2 /
Tabel is gewijzigd.
rwijk@ORA11G> alter table my_emp add constraint ck1 check (sal>500)
2 /
Tabel is gewijzigd.
So, the documentation was wrong? Is it possible after all?
No.
Because the undo has been sneakily invalidated:
rwijk@ORA11G> select *
2 from my_emp_jn_v
3 order by empno
4 , jn_scn
5 /
J JN_USER JN_DATETIME JN_SCN EMPNO ENAME SAL DEPTNO
- ------------- ------------------- -------- ----- ---------- ----- ------
7369 Smith 880 20
7499 ALLEN 1600 30
7521 WARD 1250 30
7566 Jones 3273 20
7654 MARTIN 1250 30
7698 BLAKE 2850 30
7778 ROB 1800 40
7782 Clark 2450 10
7839 King 5000 10
7844 TURNER 1500 30
7876 Adams 1210 20
7900 JAMES 950 30
7934 Miller 1300 10
13 rijen zijn geselecteerd.
So if you make the mistake of adding a constraint, you lose all journaling information... This sure is something to be aware of. I think the adding of constraints to a table with a flashback archive should also raise an ORA-55610. Hopefully Oracle will fix this in a future release or patch set.
The only way I can think of to keep journaling information while still being able to do the necessary maintenance, is to create a journaling table in which you dump the information from the journaling view before invalidating the existing undo. Far from ideal, but doable as a workaround.
But what do we gain by implementing journaling this way?
The answer is simple: performance. No longer is it necessary to have database triggers. And because database triggers are evil, you win a lot, as can be seen in a little test in which I create 3 tables, one regular table, one table using a flashback archive and auditing, and one regular table with an after row trigger to populate a second journaling table:
rwijk@ORA11G> create flashback archive flashback_archive_10_years
2 tablespace my_tablespace
3 retention 10 year
4 /
Flashbackarchief is aangemaakt.
rwijk@ORA11G> create table my_emp_regular
2 ( empno number(6) not null
3 , ename varchar2(10)
4 , sal number(8,2)
5 , deptno number(2)
6 )
7 /
Tabel is aangemaakt.
rwijk@ORA11G> create table my_emp_flashback_archive
2 ( empno number(6) not null
3 , ename varchar2(10)
4 , sal number(8,2)
5 , deptno number(2)
6 ) flashback archive flashback_archive_10_years
7 /
Tabel is aangemaakt.
rwijk@ORA11G> audit insert,update,delete on my_emp_flashback_archive by access
2 /
Audit is geslaagd.
rwijk@ORA11G> create table my_emp_bad_old_journaling
2 ( empno number(6) not null
3 , ename varchar2(10)
4 , sal number(8,2)
5 , deptno number(2)
6 )
7 /
Tabel is aangemaakt.
rwijk@ORA11G> create table my_emp_bad_old_journaling_jn
2 ( jn_operation varchar2(1)
3 , jn_user varchar2(30)
4 , jn_datetime date
5 , jn_scn number
6 , empno number(6) not null
7 , ename varchar2(10)
8 , sal number(8,2)
9 , deptno number(2)
10 )
11 /
Tabel is aangemaakt.
rwijk@ORA11G> create trigger jn_ari
2 after insert on my_emp_bad_old_journaling
3 for each row
4 begin
5 insert into my_emp_bad_old_journaling_jn
6 ( jn_operation
7 , jn_user
8 , jn_datetime
9 , jn_scn
10 , empno
11 , ename
12 , sal
13 , deptno
14 )
15 values
16 ( 'I'
17 , user
18 , sysdate
19 , to_number(dbms_flashback.get_system_change_number)
20 , :new.empno
21 , :new.ename
22 , :new.sal
23 , :new.deptno
24 );
25 end;
26 /
Trigger is aangemaakt.
rwijk@ORA11G> set timing on
rwijk@ORA11G> insert into my_emp_regular
2 select level
3 , 'EMP' || to_char(level)
4 , 1000 + level
5 , 10 * (1 + mod(level,4))
6 from dual
7 connect by level <= 100000
8 /
100000 rijen zijn aangemaakt.
Verstreken: 00:00:00.48
rwijk@ORA11G> insert into my_emp_flashback_archive
2 select level
3 , 'EMP' || to_char(level)
4 , 1000 + level
5 , 10 * (1 + mod(level,4))
6 from dual
7 connect by level <= 100000
8 /
100000 rijen zijn aangemaakt.
Verstreken: 00:00:00.50
rwijk@ORA11G> insert into my_emp_bad_old_journaling
2 select level
3 , 'EMP' || to_char(level)
4 , 1000 + level
5 , 10 * (1 + mod(level,4))
6 from dual
7 connect by level <= 100000
8 /
100000 rijen zijn aangemaakt.
Verstreken: 00:00:13.39
As expected, almost no overhead using flashback data archives and therefore a lot faster than using the old fashioned way of journaling. But the maintenance thing described earlier is something to worry about in advance.
Further reading:
Yuri van Buren about Flashback Data Archive
Oracle 11g Total Recall - Flashback in the hands of Database Designers and Application Developers, at last. And: the end of Journalling Tables by Lucas Jellema
What seemed like a random comment in OTN, turned out to be interesting research into the new feature.
ReplyDeleteThanks for catching the issue with the constraint. Its a scary situation - losing the undo and not knowing about it.