Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Building app around Flashback Versions Query

Re: Building app around Flashback Versions Query

From: <mimmo.briganti_at_route1.com>
Date: 1 Dec 2005 10:40:11 -0800
Message-ID: <1133462411.054541.98940@f14g2000cwb.googlegroups.com>


Volumes are low and the same record does not get changed very often -- it's a small database, about 1 gb right now. I don't see it ever getting really big. If I enable block change tracking, incremental backups should be pretty fast if the undo tablespace gets huge. Thanks for your comments.

Mimmo

Jonathan Lewis wrote:
> <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 - 12:40:11 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US