I suppose most readers will be aware that Oracle held a big launch for 11g yesterday, including the release of various technical docs at OTN. I’ve only managed a quick scan because I’m up to my eyeballs in some internal ASH/AWR training I’m giving later today, but what particularly interested me was this paragraph in the New Features Overview.
“Time Travel” with Oracle Flashback Data Archive
Oracle Database 11g
also features Total Recall with Oracle Flashback Data Archive, which enables
you to query data in selected tables “as of” earlier times in the past,
thereby providing an easy, practical way to add a time dimension to your data
for change tracking, ILM, auditing, and compliance. Oracle Flashback Data
Archive provides automatic and efficient storage of “change” data to enable
fast query access to old versions of the data.The DBA can set retention
policies to automatically purge data when your system reaches specified age
thresholds
I’m not sure how much attention that might get because it sounds like just another minor Flashback feature, but I suspect it’s really Rob Squire’s stuff that I talked about here. Definitely one for further investigation.
Doug here is a small example of Total Recall.
CREATE FLASHBACK ARCHIVE DEFAULT fla1 TABLESPACE testarea1
QUOTA 10G RETENTION 1 YEAR;
Flashback archive created.
Now set a table in the flashback arhive mode!
alter table hr.employees flashback archive fla1;
Table altered.
update hr.employees set salary = 5000 where salary = 4000;
1 row updated.
–> 192 5000
Prompt now see If I can get my data back!
SELECT employee_id, salary from hr.employees AS OF
TIMESTAMP TO_TIMESTAMP (‘2007-06-19 20:28:00’, ‘YYYY-MM-DD HH24:MI:SS’);
..
==>Cool 192 4000
..
So looks like flashback gets a new marketing name “Total Recal”
You data only ages out after your specified retention time.
Thanks, Yuri.
I’ll need to have a play around with it. I have a feeing that this feature might be improved further in coming versions 😉
“Total Recall” in the title – interesting, but I used the same in my post next day and reading yours only now…
My fault – I wasn’t paying much attention to my Google Reader last days and once clicked on “mark all as read” and now suffer consequences.
Ha, I read your blog and didn’t even notice that!
>”Total Recall” in the title – interesting, but I used the same in my post next day and reading yours only now…
You mean … that brilliant post that should be voted as ‘Blogentry nec plus ultra of the year’ ? 😉