Re: Building app around Flashback Versions Query

<mimmo.briganti_at_route1.com> wrote in message news:1133451730.363220.46850_at_g14g2000cwa.googlegroups.com...
> Hi everyone,
>
> Currently we have an app that maintains an audit trail manually via
> triggers, almost identical to the way Flashback Versions Query provides
> results with the pseudocolumns versions_operation, versions_starttime,
> versions_endtime, etc.
>
> What I'd like to do is remove the admin overhead of the manual history
> tracking and enable an undo retention period of 7 years. I've checked
> the Oracle docs and a value of (2 to the power of 32) - 1 in seconds is
> the max, which is more than enough. If I create a bigfile undo
> tablespace on ASM and turn on retention guarantee with autoextend on
> and maxsize unlimited, and assuming we have enough disk space in the
> ASM disk group, do you think this would work? We would also enable
> flashback database on our logical standby to the same level so that we
> can flashback across major DDL changes, open the database is read-only
> mode, and then recover and resume redo apply.
>
> The company I work for wants to know if anyone out there is building
> apps around Flashback Query and Flashback Database. I know many apps
> exist that were built off LogMiner, but they're very cautious and need
> some assurance. Does anyone know of companies on 10g r1 or r2 using
> flashback and building apps around this functionality? It's a very
> powerful technology and solves the problem of adding the dimension of
> time to standard relational databases.
>
> I realize the existing history will be lost, but the application is
> only one month old.
>
> Thanks in advance for all replies.
>

Have you checked how much undo your application generated in that month - what would that look like when multiplied up to seven years.

And when you wanted to query something as at one month ago, you would have to read the current version or a block and then take it backwards in time one undo operation at a time, which would probably be one undo block read at a time for one month. What's the performance going to be like.

And with an undo tablespace big enough for seven years of undo, the whole tablespace will have to be online and in read-write mode. How often were you planning on backing it up ?

Anyone planning on keeping a seven year audit trail is going to have a massive volume problem.

But I think your idea, whilst actually keeping the volume to a minimum, isn't going to work because of the side effects. You might be better off with (say) a one-month undo, and just backup the whole database once per month before the undo get re-used - with a little overlap for safety. Then if you need some out of date data, you restore the nearest database and roll it forward/flash it back.

At least with your in-house mechanism, if you keep all the aged data, you can make old audit trail material read-only, which may make the backup and recovery more robust.

Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html Cost Based Oracle: Fundamentals

http://www.jlcomp.demon.co.uk/appearances.html Public Appearances - schedule updated 4th Nov 2005 Received on Thu Dec 01 2005 - 11:46:18 CST