Re: Manual flashback implementation

Well, you didn't state what version you were using, so I had to guess...

> 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.

Well, you can embed the dictionary within the redo logs (and hence in the archives) so you should always be able to interpret the archives with a dictionary that was current at the time you created them.

>Besides, my
> initial research into it left me thinking that it would be much more
> complicated to construct the historical database using LogMiner.

It's not easy, for sure.

>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).
>

Have you therefore considered Workspace Management? A very nice feature indeed, it allows you to 'version enable' a table, and then to create any number of workspaces, within which the data is immune from changes made in the 'live' space on the 'real' data. You can periodically refresh the workspace data from the live workspace. You can lock tables, or rows, from further modification in a workspace. You can merge workspace data back into the live workspace. It's very flexible. Not entirely sure it's what you're after, but it might be.

> I'm also not sure what you mean by "upgrade". This process will run on
> a 9.2 database so flashback is available.

See my first comment above. I can't just guess these things, you know!

>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?

Nope. You can only flashback to a time within the undo_retention period (you might be able to flashback further, but it's not guaranteed). And you can't flashback past DDL commands.

>
> 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.

Well, I think Workspace Manager might come to your rescue. Otherwise, you're on your own (unless someone else can post something more helpful).

Regards
HJR
> 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 - 15:29:26 CST