Re: Building app around Flashback Versions Query

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@xxxxxxxxxx> wrote in message
> news:1133451730.363220.46850@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> > 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

.

Relevant Pages

  • Re: Building app around Flashback Versions Query
    ... almost identical to the way Flashback Versions Query provides ... > tracking and enable an undo retention period of 7 years. ... > flashback database on our logical standby to the same level so that we ... > apps around Flashback Query and Flashback Database. ...
    (comp.databases.oracle.server)
  • Re: Is there just one UNDO with Project?
    ... "the edited record is written to permanent disk storage" - Do you think ... One of the "cures" for broken .mpp files is to send them to the database ... So, actually, I believe the reason that the number of undo's was limited to ... One can undo changes ...
    (microsoft.public.project)
  • Re: Oracle 9i - database cannot open due to UNDOTBS01
    ... I am new to using Oracle and I am using 9.2.0.7. ... and spfile of my database. ... not backup was the UNDO. ... alter database datafile 'UNDOTBS01.DBF' OFFLINE DROP; ...
    (comp.databases.oracle.server)
  • Re: Is there just one UNDO with Project?
    ... I don't know if MSP uses parts of the Jet dbms engine "under the hood" or not but almost all databases I'm familiar with have similar behavior. ... One can undo changes made to a given record while it's in memory for editing but once the record pointer moves to a new record, the edited record is written to permanent disk storage with all changes overwriting the original record in the permanent file and no longer able to be undone. ... Most proggies that allow multiple undo levels are editing to an image in memory and don't commit changes to the actual permanent storage file until it is closed or otherwise explicitly saved. ... But that strategy is just not practical with a database table that potentially could have billions of records - they all read and load either a single record at a time or at most a "page" consisting of a relatively small handful of records. ...
    (microsoft.public.project)
  • Re: My main form wont open
    ... How the heck do you "undo the changes" after you saved? ... Even when you close the database out for the day & go home from work it ... > if you don't have a backup copy of your db, try to "undo" all the changes ... >> OK - I've redid the query. ...
    (microsoft.public.access.forms)