Re: Manual flashback implementation

LogMiner seems to have many restrictions and caveats itself that concern me. I'm afraid that some series of events will occur that will cause Oracle to refuse to give me the data. From briefly reading the documentation, making sure you can always construct the proper data dictionary files seems like a potentially big problem. Besides, my initial research into it left me thinking that it would be much more complicated to construct the historical database using LogMiner. I should stress that what I need is a populated database with the complete history of the data so that it can be used by custom applications (mainly for producing historically accurate reports).

I'm also not sure what you mean by "upgrade". This process will run on a 9.2 database so flashback is available. My problem is that there are numerous constraints on flashback that could cause it to not work. Many of my tests with it resulted in Oracle refusing to perform the flashback query I requested. That is not acceptable. I must have a 100% guarantee that nothing that is done to the database will prevent me from querying the data as it looked in the past. Is there some configuration that will fix this will absolute certainty?

Another issue would be with DDL changes. This system will need the structure to always appear as it currently is. Any manual structural or data changes will be considered effective retroactively (as if they had been there all along).

I'm merely trying to fulfill the requirements of the system. I certainly have no desire to build something that is already there. If you can help me understand how Oracle can do that then great. Otherwise, I have to build something to do it, 'cludgy' or not.

Thanks,
Richard Kuhler

"Howard J. Rogers" wrote:
>
> Log Miner?
>
> Not being funny, but if this sort of functionality is *that* crucial to your
> application, why don't you upgrade to a version that supports it 'out of the
> box'. Anything else is going to be cludgy implementation with triggers
> firing off left, right and centre; slowing things down as a result; and
> possibly not doing to job particularly well at the end of it.
>
> Regards
> HJR
>
> "Richard Kuhler" <noone_at_nowhere.com> wrote in message
> news:_5wC9.18360$%k2.5216758_at_twister.socal.rr.com...
> > I'm looking for design ideas to manually implement the equivalent of
> > Oracle's flashback query. In other words, I need to be able to retrieve
> > the data as it looked at any point in the past. Since I need a 100%
> > guarantee that I can do this forever, I don't see how I can safely rely
> > on Oracle's flashback mechanism.
> > Here's what I've come up with...
> >
> > 1. Triggers on all base tables record all changes to a history table
> > 2. Any time a commit occurs, the SCN and time of the commit must be
> > recorded in my own table
> > 3. All rows that were modified during that commit must have the SCN
> > recorded in the history tables rows
> >
> > It's step 3 that I hate the most. This will result in lots of
> > additional updates in the system. Actually, it's not even clear to me
> > yet how I can implement steps 2 and 3 (is there some way to get this
> > information from Oracle?). I suspect I'm going to have to create my own
> > SCN mechanism and implement a special commit procedure for all processes
> > that manipulate data. There's also a delay between steps 2 and 3 when
> > queries would not be able to retrieve the data as of the current time
> > (although a delay of as much as 24 hours would be acceptable). I have a
> > feeling that it's probably possible to process the redo logs to build
> > the history table information with the necessary SCN in one step but I'm
> > not sure how best to proceed with that.
> >
> > Anybody have any ideas?
> >
> >
> > Thanks,
> > Richard Kuhler
> >
Received on Tue Nov 19 2002 - 14:40:38 CST