Re: Maybe very silly question abou Log Miner

What you want are "supplemental log groups" -which, despite their name, have nothing to do with adding extra log groups as in 'files', but is merely a request to include primary key, unique key or any other combination of columns that take your fancy, within the redo stream, even if the DML statements haven't actually modified them.

The command is something along the lines of 'alter table emp add supplemental log group (empno, ename, sal);' -but I don't have my Big Boy's Book of Syntax handy to check for sure. You can also do things like 'alter table emp add supplemental data (primary key)'. And, if utter madness strikes, you can always do 'alter database add supplemental data (primary key)'.

Be warned that, particularly if you do this last one, then every table throughout the database gets additional redo data logged. And, which is worse, if a table doesn't actually have a primary key (PeopleSoft, anyone?!!) then every column in the table is logged.

As I say, you can also add unique key supplementals, though there then arises an issue: a table can have multiple unique keys, so which one gets chosen for the logging? When I tested this in 9iR1, it appeared to be the oldest unique constraint declared (ie, the first one). Which is fair enough: but disabling and then re-enabling the constraint means that same constraint is now no longer the oldest constraint, and therefore what gets logged changes over time.

Unfortunately, this is only a feature of 9i. Which is, frankly, a bit of an oversight on Oracle's part. The inclusion of the rowid alone in the logs would have been totally workable if we were still back in the days of 8.0, because in that version -and in total agreement with relational theory- once a row has acquired a row id, it can never, ever change it (short of an export-truncate-import). But 8i broke this rule in at least two crucial places: one, 'alter table emp move' means every row in the EMP table acquires a new rowid; and two, 'create table blah...partition by range...enable row movement', allowing a January order to move to the February partition when a sales date is modified. Given that 8i was also the version that introduced log miner, it was always a bit of an oddity to have a new tool that failed to take account of other, simultaneously released, new features.

But they've caught up in 9i. Primary keys, for examle, never change (or oughtn't to) so they will always identify the appropriate row, no matter where it's wandered to since its dollop of redo was generated.

Be afraid, however... very afraid. Used indiscriminately, the additional amount of redo this new feature causes to be created can swamp a finely-balanced redo subsystem. The performance implications can be very dramatic indeed.

Regards
HJR "Dusan Bolek" <pagesflames_at_usa.net> wrote in message news:1e8276d6.0303280047.45cb0dbf_at_posting.google.com...
> I'm trying to use Log Miner as some kind of audit technology. Original
> idea was to take archived redologs and read them on other dedicated
> database. However, I cannot overcome one single problem, which I know
> from beginning of my logmining in 8.1.6. Problem is that keys used for
> identifiing row to be updated (in where conditions) are ommited and
> instead of this in sql_redo from v$logmnr_contents are used rowids.
> Example of original insert:
>
> update log_test set text3 = 'some new value'
> where id = 777;
>
> and INSERT from v$logmnr_contents:
>
> update "BOLEK"."LOG_TEST" set "TEXT3" = 'some new value' where "TEXT3"
> = 'some old value' and ROWID = 'AAAFrVAABAAAHrKAAC';
>
> I knew that this is the format of logminer output, but it still
> puzzles me if there is no other way to find a key used for original
> update. With this is logmining in any other database then the original
> one unusable from audit point of view. For auditing, I need to know
> exactly which row was updated.
> Is there any way to accomplish this, or is this just a limitation that
> logminer has and there is nothing to do with?
>
> Thanks for your answers
>
> --
> _________________________________________
>
> Dusan Bolek, Ing.
> Oracle team leader
>
> Note: pagesflames_at_usa.net has been cancelled due to changes (maybe we
> can call it an overture to bankruptcy) on that server. I'm still using
> this email to prevent SPAM. Maybe one day I will change it and have a
> proper mail even for news, but right now I can be reached by this
> email.
Received on Fri Mar 28 2003 - 04:31:31 CST