In version 11.1.0.6, Oracle introduced flashback data archives under the marketing term Total Recall. Previously you could only flashback your table to an earlier point in time, as long as the needed undo information was available. With flashback data archives you can extend this period with as long as you specify. You create a flashback data archive, specify a retention period and associate the archive with a table. In version 11.1.0.6 there was a nasty bug, as I described in this post. In version 11.1.0.7, Oracle fixed this bug, and you could safely use flashback data archives for journaling, as described here. However, you could not modify the schema, for example add a constraint to a table with a flashback data archive associated, in 11.1.0.7. In the mentioned post I described how to overcome that, but it was cumbersome.
And then came 11.2.0.1. The new features guide mentions:
1.5.1.1 Flashback Data Archive Support for DDLs
Oracle Database 11g Release 2 (11.2) users can now use most DDL commands on tables that are being tracked with Flashback Data Archive. This includes:
* Add, Drop, Rename, Modify Column
* Drop, Truncate Partition
* Rename, Truncate Table
* Add, Drop, Rename, Modify Constraint
For more complex DDL (for example, upgrades and split table), the Disassociate and Associate PL/SQL procedures can be used to temporarily disable Total Recall on specified tables. The Associate procedure enforces schema integrity after association; the base table and history table schemas must be the same.
This feature makes it much easier to use the Total Recall option with complex applications that require the ability to modify the schema.
So now it seems the last restriction is removed and we can use this technology for journaling. Let's test how it works in 11.2.0.1 by using a similar setup as in my first two blog posts about this subject.
The tablespace already exists:
rwijk@ORA11GR2> create tablespace my_tablespace datafile 'extra_file.dat' size 10M
2 /
create tablespace my_tablespace datafile 'extra_file.dat' size 10M
*
ERROR at line 1:
ORA-01543: tablespace 'MY_TABLESPACE' already exists
Create the flashback archive, a foreign key table and a table T with the flashback data archive associated:
rwijk@ORA11GR2> create flashback archive flashback_archive_10_years
2 tablespace my_tablespace
3 retention 10 year
4 /
Flashback archive created.
rwijk@ORA11GR2> create table fktable (col number(10) primary key)
2 /
Table created.
rwijk@ORA11GR2> create table t
2 ( pkcol number(10) primary key
3 , fkcol number(10) not null references fktable(col)
4 , description varchar2(11)
5 )
6 flashback archive flashback_archive_10_years
7 /
Table created.
The flashback data archive table is created:
rwijk@ORA11GR2> select archive_table_name
2 from dba_flashback_archive_tables
3 where table_name = 'T'
4 /
ARCHIVE_TABLE_NAME
-----------------------------------------------------
SYS_FBA_HIST_75925
1 row selected.
rwijk@ORA11GR2> exec dbms_lock.sleep(15)
PL/SQL procedure successfully completed.
Add a row to the foreign key table and switch on auditing to get access to the user. This is done because all journaling information is available when using Flashback Version Query, except for the user who changed the data. Switching on auditing records much more than just the username (just do a describe of sys.aud$), but at least it gives the username without additional coding.
rwijk@ORA11GR2> insert into fktable (col) values (1)
2 /
1 row created.
rwijk@ORA11GR2> audit insert,update,delete on t by access
2 /
Audit succeeded.
And create the view that represents the journaling table:
rwijk@ORA11GR2> create view v
2 as
3 select tv.versions_operation jn_operation
4 , ao.username jn_user
5 , tv.versions_starttime jn_datetime
6 , tv.versions_startscn jn_scn
7 , tv.pkcol
8 , tv.fkcol
9 , tv.description
10 from t versions between scn minvalue and maxvalue tv
11 , user_audit_object ao
12 where tv.versions_xid = ao.transactionid (+)
13 /
View created.
Now add some data to see if it works:
rwijk@ORA11GR2> insert into t
2 select level
3 , 1
4 , lpad('*',11,'*')
5 from dual
6 connect by level <= 10
7 /
10 rows created.
rwijk@ORA11GR2> commit
2 /
Commit complete.
rwijk@ORA11GR2> pause
rwijk@ORA11GR2> update t
2 set description = 'a'
3 where pkcol = 1
4 /
1 row updated.
rwijk@ORA11GR2> commit
2 /
Commit complete.
rwijk@ORA11GR2> pause
rwijk@ORA11GR2> delete t
2 where pkcol = 7
3 /
1 row deleted.
rwijk@ORA11GR2> commit
2 /
Commit complete.
rwijk@ORA11GR2> pause
rwijk@ORA11GR2> select * from v order by jn_scn
2 /
J JN_USER JN_DATETIME JN_SCN PKCOL FKCOL DESCRIPTION
- ---------- -------------------------------- ---------- ----- ----- -----------
I RWIJK 16-DEC-09 03.02.16 PM 5032930 7 1 ***********
I RWIJK 16-DEC-09 03.02.16 PM 5032930 2 1 ***********
I RWIJK 16-DEC-09 03.02.16 PM 5032930 10 1 ***********
I RWIJK 16-DEC-09 03.02.16 PM 5032930 9 1 ***********
I RWIJK 16-DEC-09 03.02.16 PM 5032930 8 1 ***********
I RWIJK 16-DEC-09 03.02.16 PM 5032930 1 1 ***********
I RWIJK 16-DEC-09 03.02.16 PM 5032930 6 1 ***********
I RWIJK 16-DEC-09 03.02.16 PM 5032930 5 1 ***********
I RWIJK 16-DEC-09 03.02.16 PM 5032930 4 1 ***********
I RWIJK 16-DEC-09 03.02.16 PM 5032930 3 1 ***********
U RWIJK 16-DEC-09 03.02.34 PM 5032938 1 1 a
D RWIJK 16-DEC-09 03.02.34 PM 5032941 7 1 ***********
12 rows selected.
Everything is there: 10 inserted rows, 1 updated row and 1 deleted row.
Now, add an extra column with a check constraint on it. Adding a column was possible in 11.1.0.x as well, but adding the constraint was not possible. Now it is.
rwijk@ORA11GR2> alter table t add (status varchar2(3))
2 /
Table altered.
rwijk@ORA11GR2> alter table t add constraint ck_status check (status in ('NEW','OLD'))
2 /
Table altered.
Note though that it takes considerably more time when adding a constraint on a history-tracked table than on a regular table. On my laptop it consistently takes approximately 10 seconds.
Now adjust the journaling view to have it contain the extra column:
rwijk@ORA11GR2> create or replace view v
2 as
3 select tv.versions_operation jn_operation
4 , ao.username jn_user
5 , tv.versions_starttime jn_datetime
6 , tv.versions_startscn jn_scn
7 , tv.pkcol
8 , tv.fkcol
9 , tv.description
10 , tv.status
11 from t versions between scn minvalue and maxvalue tv
12 , user_audit_object ao
13 where tv.versions_xid = ao.transactionid (+)
14 /
View created.
And select from the new view:
rwijk@ORA11GR2> select * from v order by jn_scn
2 /
J JN_USER JN_DATETIME JN_SCN PKCOL FKCOL DESCRIPTION STATUS
- ---------- -------------------------------- ---------- ----- ----- ----------- ------
I RWIJK 16-DEC-09 03.19.15.000000000 PM 5034213 1 1 ***********
I RWIJK 16-DEC-09 03.19.15.000000000 PM 5034213 7 1 ***********
I RWIJK 16-DEC-09 03.19.15.000000000 PM 5034213 4 1 ***********
I RWIJK 16-DEC-09 03.19.15.000000000 PM 5034213 2 1 ***********
I RWIJK 16-DEC-09 03.19.15.000000000 PM 5034213 3 1 ***********
I RWIJK 16-DEC-09 03.19.15.000000000 PM 5034213 10 1 ***********
I RWIJK 16-DEC-09 03.19.15.000000000 PM 5034213 5 1 ***********
I RWIJK 16-DEC-09 03.19.15.000000000 PM 5034213 6 1 ***********
I RWIJK 16-DEC-09 03.19.15.000000000 PM 5034213 8 1 ***********
I RWIJK 16-DEC-09 03.19.15.000000000 PM 5034213 9 1 ***********
U RWIJK 16-DEC-09 03.19.18.000000000 PM 5034217 1 1 a
11 rows selected.
11 rows? Before adding the column and constraint, there were 12! The last one - the delete - has disappeared. To find out if it is the last one or the delete, I add another column with constraint, and do a delete followed by an insert. Will it remove the last (the insert) or the delete?
rwijk@ORA11GR2> delete t where pkcol in (3,5)
2 /
2 rows deleted.
rwijk@ORA11GR2> commit
2 /
Commit complete.
rwijk@ORA11GR2> insert into t values ( 11, 1, 'bla', 'OLD' )
2 /
1 row created.
rwijk@ORA11GR2> commit
2 /
Commit complete.
rwijk@ORA11GR2> alter table t add (status2 varchar2(3))
2 /
Table altered.
rwijk@ORA11GR2> alter table t add constraint ck_status2 check (status2 in ('NEW','OLD'))
2 /
Table altered.
rwijk@ORA11GR2> pause
rwijk@ORA11GR2> create or replace view v
2 as
3 select tv.versions_operation jn_operation
4 , ao.username jn_user
5 , tv.versions_starttime jn_datetime
6 , tv.versions_startscn jn_scn
7 , tv.pkcol
8 , tv.fkcol
9 , tv.description
10 , tv.status
11 , tv.status2
12 from t versions between scn minvalue and maxvalue tv
13 , user_audit_object ao
14 where tv.versions_xid = ao.transactionid (+)
15 /
View created.
rwijk@ORA11GR2> select * from v order by jn_scn
2 /
J JN_USER JN_DATETIME JN_SCN PKCOL FKCOL DESCRIPTION STATUS STA
- ---------- -------------------------------- ---------- ----- ----- ----------- ------ ---
I RWIJK 16-DEC-09 03.19.15.000000000 PM 5034213 1 1 ***********
I RWIJK 16-DEC-09 03.19.15.000000000 PM 5034213 7 1 ***********
I RWIJK 16-DEC-09 03.19.15.000000000 PM 5034213 3 1 ***********
I RWIJK 16-DEC-09 03.19.15.000000000 PM 5034213 5 1 ***********
I RWIJK 16-DEC-09 03.19.15.000000000 PM 5034213 2 1 ***********
I RWIJK 16-DEC-09 03.19.15.000000000 PM 5034213 9 1 ***********
I RWIJK 16-DEC-09 03.19.15.000000000 PM 5034213 10 1 ***********
I RWIJK 16-DEC-09 03.19.15.000000000 PM 5034213 4 1 ***********
I RWIJK 16-DEC-09 03.19.15.000000000 PM 5034213 6 1 ***********
I RWIJK 16-DEC-09 03.19.15.000000000 PM 5034213 8 1 ***********
U RWIJK 16-DEC-09 03.19.18.000000000 PM 5034217 1 1 a
I RWIJK 16-DEC-09 03.19.53.000000000 PM 5034390 11 1 bla OLD
12 rows selected.
So it is the delete that is automatically removed.
Finally I want to know whether it was the addition of the column or the addition of the constraint that made the delete statement disappear. Since this behaviour was not there in 11.1.0.7 when you could add a column, I was tempted to believe it is the addition of the constraint. To know for sure, I repeated the above piece of code without adding the constraint:
rwijk@ORA11GR2> select * from v order by jn_scn
2 /
J JN_USER JN_DATETIME JN_SCN PKCOL FKCOL DESCRIPTION STATUS
- ---------- -------------------------------- ---------- ----- ----- ----------- ------
I RWIJK 16-DEC-09 03.43.29.000000000 PM 5035521 6 1 ***********
I RWIJK 16-DEC-09 03.43.29.000000000 PM 5035521 1 1 ***********
I RWIJK 16-DEC-09 03.43.29.000000000 PM 5035521 7 1 ***********
I RWIJK 16-DEC-09 03.43.29.000000000 PM 5035521 2 1 ***********
I RWIJK 16-DEC-09 03.43.29.000000000 PM 5035521 10 1 ***********
I RWIJK 16-DEC-09 03.43.29.000000000 PM 5035521 9 1 ***********
I RWIJK 16-DEC-09 03.43.29.000000000 PM 5035521 8 1 ***********
I RWIJK 16-DEC-09 03.43.29.000000000 PM 5035521 5 1 ***********
I RWIJK 16-DEC-09 03.43.29.000000000 PM 5035521 4 1 ***********
I RWIJK 16-DEC-09 03.43.29.000000000 PM 5035521 3 1 ***********
U RWIJK 16-DEC-09 03.44.04.000000000 PM 5035535 1 1 a
D RWIJK 16-DEC-09 03.44.41.000000000 PM 5035710 3 1 ***********
D RWIJK 16-DEC-09 03.44.41.000000000 PM 5035710 5 1 ***********
I RWIJK 16-DEC-09 03.44.41.000000000 PM 5035713 11 1 bla OLD
14 rows selected.
rwijk@ORA11GR2> pause
rwijk@ORA11GR2> alter table t add (status2 varchar2(3))
2 /
Table altered.
rwijk@ORA11GR2> --alter table t add constraint ck_status2 check (status2 in ('NEW','OLD'))
rwijk@ORA11GR2> --/
rwijk@ORA11GR2> pause
rwijk@ORA11GR2> create or replace view v
2 as
3 select tv.versions_operation jn_operation
4 , ao.username jn_user
5 , tv.versions_starttime jn_datetime
6 , tv.versions_startscn jn_scn
7 , tv.pkcol
8 , tv.fkcol
9 , tv.description
10 , tv.status
11 , tv.status2
12 from t versions between scn minvalue and maxvalue tv
13 , user_audit_object ao
14 where tv.versions_xid = ao.transactionid (+)
15 /
View created.
rwijk@ORA11GR2> select * from v order by jn_scn
2 /
J JN_USER JN_DATETIME JN_SCN PKCOL FKCOL DESCRIPTION STATUS STA
- ---------- -------------------------------- ---------- ----- ----- ----------- ------ ---
I RWIJK 16-DEC-09 03.43.29.000000000 PM 5035521 1 1 ***********
I RWIJK 16-DEC-09 03.43.29.000000000 PM 5035521 7 1 ***********
I RWIJK 16-DEC-09 03.43.29.000000000 PM 5035521 3 1 ***********
I RWIJK 16-DEC-09 03.43.29.000000000 PM 5035521 5 1 ***********
I RWIJK 16-DEC-09 03.43.29.000000000 PM 5035521 2 1 ***********
I RWIJK 16-DEC-09 03.43.29.000000000 PM 5035521 9 1 ***********
I RWIJK 16-DEC-09 03.43.29.000000000 PM 5035521 10 1 ***********
I RWIJK 16-DEC-09 03.43.29.000000000 PM 5035521 4 1 ***********
I RWIJK 16-DEC-09 03.43.29.000000000 PM 5035521 6 1 ***********
I RWIJK 16-DEC-09 03.43.29.000000000 PM 5035521 8 1 ***********
U RWIJK 16-DEC-09 03.44.04.000000000 PM 5035535 1 1 a
I RWIJK 16-DEC-09 03.44.41.000000000 PM 5035713 11 1 bla OLD
12 rows selected.
So it is the addition of a column that made the delete disappear, and not the addition of the constraint.
The conclusion is that flashback data archives still cannot be used for journaling, but since the above seems like a quite obvious bug, a future patch set will probably solve it in the near future. And when that happens, it finally looks promising to use for journaling.
Nice post
ReplyDeleteHttp://dbataj.blogspot.com