Re: LogMiner Restrictions

From: Howard J. Rogers <howardjr2000_at_yahoo.com.au>
Date: Wed, 22 Jan 2003 11:04:32 +1100
Message-ID: <w4lX9.29843$jM5.77093@newsfeeds.bigpond.com>

"Joe Yong" <flyingbuick_at_yahoo.com> wrote in message news:6ad26002.0301211407.74d92f68_at_posting.google.com...
> Hi all,
> I was looking into LogMiner for Oracle9iR2 and found the following
> restrictions. Would appreciate any assistance on this.
>
> 1. Restrictions: Simple and nested abstract datatype, Collections,
> Object Refs, Index Organized Tables and Create Table As Select of a
> table with a clustered key.
>
> The Oracle manual indicates that the following are not supported. What
> does that mean for each of these features? For example does it mean I
> won't get useful information from V$logmnr_contents for transactions
> that access IOTs? I could not find further explanations for any of
> these.

Bear in mind that IOTs don't have rowids. They are index segments, fundamentally. Which means that their entries get their 'locational significance' from the value of their primary key, not by some arbitrary assignment of a rowid. If we have a block split and need to move the row, so be it.

Now what does logminer essentially record... the rowid for the transaction. Yes, you can now add in extra 'supplemental log groups' to record additional bits of identifying data, but the point is anything other than a regular heap table is subject to all sorts of provisos in the logminer world.

>
> 2. LogMiner cannot be used in a MTS environment. Got this from an
> Oracle World presentation.
> Is this referring to Microsoft Transaction Server or Multi-Threaded
> Server? Eitherway, why?

Multi threaded server. Frankly, I'd be gobsmacked if this was the truth. So there's a bit of investigation to do this end. But logminer analyses the redo logs. Transactions in MTS generate redo just as they would do in dedicated server configuration. Therefore, I would presume that there's just as much redo to analyse as there would be in dedicated server. And therefore, I'd be exceptionally surprised if log miner refused to work in MTS. There is of course the fact that if you "desc v$logmnr_contents" you'll see columns such as USERNAME and so on (which might be hard to work out if the server process concerned with the transaction is shared between session). Perhaps such columns are problematical when you are running MTS, and hence the comments you heard?

Just a quick test therefore:

SQL> connect scott/tiger_at_oemrep;
Connected.
SQL> select count(*) from v$circuit;

COUNT(*)

[and if v$circuit has any rows whatsoever, then we have an MTS connection].

SQL> update emp set sal=1300;
20 rows updated.
SQL> commit;
Commit complete.

SQL> connect / as sysdba
Connected.
SQL> exec dbms_logmnr_d.build('hjrdict.ora','c:\') PL/SQL procedure successfully completed. SQL> exec dbms_logmnr.add_logfile('D:\ORACLE\ORADATA\OEMREP\REDO01.LOG', dbms_logmnr.new)
PL/SQL procedure successfully completed. SQL> exec dbms_logmnr.add_logfile('D:\ORACLE\ORADATA\OEMREP\REDO02.LOG', dbms_logmnr.addfile)
PL/SQL procedure successfully completed. SQL> exec dbms_logmnr.add_logfile('D:\ORACLE\ORADATA\OEMREP\REDO03.LOG', dbms_logmnr.addfile)
PL/SQL procedure successfully completed. SQL> exec dbms_logmnr.add_logfile('D:\ORACLE\ORADATA\OEMREP\REDO5A.LOG', dbms_logmnr.addfile)
PL/SQL procedure successfully completed.

[so that's all my redo logs added to the list of things to analyse]

SQL> exec dbms_logmnr.start_logmnr(dictfilename=>'c:\hjrdict.ora') PL/SQL procedure successfully completed.

SQL> select sql_undo from v$logmnr_contents 2 where seg_name='EMP';

SQL_UNDO

update "SCOTT"."EMP" set "SAL" = '900' where "SAL" = '1300' and ROWID = 'AAAHXDAAIAAAQIUAAA'; update "SCOTT"."EMP" set "SAL" = '900' where "SAL" = '1300' and ROWID = 'AAAHXDAAIAAAQIUAAB'; etc etc etc

So, a transaction generated in a session using a shared server nevertheless is analyzable (is that a word??!) using log miner.

So quite what your Oracle World presentation was saying, I don't know. Maybe they were suggesting you can't *analyze* the logs in MTS???

OK, another quick test:

SQL> connect sys/blah_at_oemrep as sysdba
Connected.
SQL> select count(*) from v$circuit;

COUNT(*)

[so SYS has connected, but via a shared server process].

SQL> exec dbms_logmnr.add_logfile('D:\ORACLE\ORADATA\OEMREP\REDO01.LOG', dbms_logmnr.new)
PL/SQL procedure successfully completed.

SQL> exec dbms_logmnr.add_logfile('D:\ORACLE\ORADATA\OEMREP\REDO02.LOG', dbms_logmnr.addfile)
PL/SQL procedure successfully completed.

SQL> exec dbms_logmnr.add_logfile('D:\ORACLE\ORADATA\OEMREP\REDO03.LOG', dbms_logmnr.addfile)
PL/SQL procedure successfully completed.

SQL> exec dbms_logmnr.add_logfile('D:\ORACLE\ORADATA\OEMREP\REDO5A.LOG', dbms_logmnr.addfile)
PL/SQL procedure successfully completed.

SQL> exec dbms_logmnr.start_logmnr(dictfilename=>'c:\hjrdict.ora') PL/SQL procedure successfully completed.

[No problems calling the packages therefore].

SQL> select sql_undo from v$logmnr_contents 2 where seg_name='EMP'
3 and rownum=1;

SQL_UNDO

update "SCOTT"."EMP" set "SAL" = '900' where "SAL" = '1300' and ROWID = 'AAAHXDA
AIAAAQIUAAA'; [And no trouble seeing the transactions, either]

I honestly don't know what you were told at Oracle World, or what they were getting at. But Log Miner works just as well in dedicated as it does in shared server mode.

Regards
HJR Received on Tue Jan 21 2003 - 18:04:32 CST