On the DDL support in 9i's LogMIner:

DDL_DICT_TRACKING

If the dictionary in use is a flat file or in the redo log files, LogMiner ensures that its internal dictionary is updated if a DDL event occurs. This ensures that correct SQL_REDO and SQL_UNDO information is maintained for objects that are modified after the LogMiner dictionary is built.

This option cannot be used in conjunction with the DICT_FROM_ONLINE_CATALOG option.

NO_DICT_RESET_ONSELECT
This option is only valid if the DDL_DICT_TRACKING option is also specified. It prevents LogMiner from reloading its internal dictionary at the beginning of each select operation on V$LOGMNR_CONTENTS. This can be an advantage because it can be time consuming to refresh the dictionary if a DDL operation has updated the dictionary.

If you use this option, be aware that because the dictionary has not been refreshed for subsequent select operations, you may get incompletely reconstructed SQL_REDO and SQL_UNDO information for objects that are modified in the redo log files. Such incomplete reconstructions produce SQL that cannot be executed.

Q:

If I got it right, DDL_DICT_TRACKING "refreshes" either the offline dictionary or the online dictionary in the redo logs, in the case of a new DDL. What I'm supposing NO_DICT_RESET_ON SELECT is stating is that with with option not set, this refersh ocurs at each select. And when I set this option, the refresh doesn't happens at every new select on the dictionary.

After all, when is the dictionary refershed: at every new DDL or every new SELECT ?

If I want to have PK columns appearing in my logminer contents, instead of only rowid, I can use "supplemental" log groups with, for example:

ALTER TABLE ... ADD SUPPLEMENTAL LOG GROUP ... ( ..., ...);

or

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

I find this terminology to be strange. It's not in a fact a "supplemental" log group in physical terms (like a redo log file group), but rather additional log data, mapping the additional columns.

Am I right ?

Can Logminer be used on a NOARCHIVELOG database to analyze the current redolg files? thanks.

Dear Tom,

I have Oracle 9i EE on windows with noarchivemode.

below is my logfile list.
sys@NIKUNJ> select * from v$logfile;

GROUP# STATUS TYPE
---------- ------- -------
MEMBER
------------------------------------
3 STALE ONLINE
C:\ORACLE\ORADATA\NIKUNJ\REDO03.LOG
2 STALE ONLINE
C:\ORACLE\ORADATA\NIKUNJ\REDO02.LOG
1 ONLINE
C:\ORACLE\ORADATA\NIKUNJ\REDO01.LOG

sys@NIKUNJ>

I had try to do as below

sys@NIKUNJ> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => 'C:\ORACLE\ORADATA\NIKUNJ\REDO01.LOG',OPTIONS => DBMS_LOGMNR.NEW);

PL/SQL procedure successfully completed.

sys@NIKUNJ>
sys@NIKUNJ> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

PL/SQL procedure successfully completed.

sys@NIKUNJ>
sys@NIKUNJ> EXECUTE DBMS_LOGMNR.START_LOGMNR(STARTTIME => TO_DATE('30-Sep-2005 10:30:00', 'DD-MON-YYYY HH:MI:SS'),-
> ENDTIME => TO_DATE('30-Sep-2005 11:30:00', 'DD-MON-YYYY HH:MI:SS'));
BEGIN DBMS_LOGMNR.START_LOGMNR(STARTTIME => TO_DATE('30-Sep-2005 10:30:00', 'DD-MON-YYYY HH:MI:SS'),
ENDTIME => TO_DATE('30-Sep-2005 11:30:00', 'DD-MON-YYYY HH:MI:SS')); END;

*
ERROR at line 1:
ORA-01291: missing logfile
ORA-06512: at "SYS.DBMS_LOGMNR", line 53
ORA-06512: at line 1

Why its saying missing logfile ?

Hi Tom,

Is there a possibility to have PK columns appearing in my logminer contents, instead of only rowid ?

I try to execute the (9i) Following Command :

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

of course, it failed with ORA-01900 error ...

I didn't find anything on this subject in Oracle Documentation.

In fact, i want to be sure it's impossible to do that with Oracle 8i.

Thank in advance

Tom,

The Undo and Redo info from Logminer get returned in VARCHAR2 columns.

Obviously, they can be longer than 4K. In those cases Logminer returns multiple rows. If you paste them together you get the undo and redo DML.

Question: How can you know what order to paste them in?

We're doing complex joins with the Logminer view so we can't assume we can just query from it and get back results in the right order.

We're hoping to filter and transform in our initial query by doing a join against some other tables.

Then we will put the data into its final location where we will store it in a permanent table.

We really don't want the performance hit of getting all the rows we aren't interested in and putting them in a temporary table.

Do you know any way to order the rows so we can paste the redo data back together?

Tom,

I work in a large team; and someone truncated a table which took me 3 days to populate :(

Is there a way to find the culprit?

Does Oracle maintain any activity logs?

Thanks

Tom
We have a large DB and most of the tables have more that 30 million records. Daily transactions hit almost a 2million records.
The application will get a transaction log file which is a list of records in a csv or text file. These records are read one after the other and compared with the DB values. In all the case the present image and the previous image of a record needs to be compared and audited based on some logic. Data gets flushed out on some logic ( i.e DB has only 3 months of live data, rest is archived)

The DBA says we can use Logminer to get the previous image of the record. Can we use the Logminer to achive it.

It will be a great help if you can give a high level explanation of this with pros and cons

Thanks in advance

Tom,
Thanks for the response above. I did have the same opinion for NOT using the logminer to do the audit. Can you provide me some important points as to why we cannot use this.
The only point I see is that , sql_undo does have a equivalent statements rather than the exact copy of previous statements. But the DBA counteracts saying we can get all column values by supplemental log data.
Can you help me by providing some key points as to " Why we should not be using Logminer for this auditing operation "

Tom,
Forgive me for bugging you on this topic
Here is the business logic on high level

1) Audit will only be done by comparing the present image and the previous image of the record. We go only one level down , the present and the previous always

2) Our application runs on oracle 9i and above

3) Luckily,.....Luckily there are NO unsupported (by Logminer) data types used

DBA says we can use this because of the above scenario
Please,..Please comment

Tom,
I hope I was clear with the objective of the auditing above. Sorry I have come again on this as the DBA wants this , but I am the one who implements this!

1) With redo LOG-mining we get the **changed column ** values ( present + previous+ primary key column).

We are interested in present + previous + primary key columns **which are changed**

2) We take those values and do some logic and if "ok" we write to a file

This happens on 64 million records on a 10 hour window

Can you please give me some real reasons to defend myself to say NO to this approach

Thanks in advance

BDA -- Business dev audit is the name of the application
Please Please read this and reply

We are interested in present + previous + primary key columns **which are changed** on a daily basis

I want to get only the changed data and the primary columns of the records

Only The present values + previous values + Primary key columns


Here is what my DBA says in support of the LOGMINER



I would want to process one days worth of data at most and the requirements actually
specify a trickle mode that would require you to look at data in almost real-time.

If you ask it for just committed data then LogMiner performance is terrible (like streams).
From your comments, I pretty confident that this is what you are seeing with your tests.
Conversely, if you ask LogMiner for data in the original log order, then performance is terrific,
but the application must handle the data in the raw order (changes before commit/rollback).

The poor performance of Oracle Steams reflects it's use of "commit-order" queries from LogMiner.
We're in the process of using LogMiner to extract data from Oracle into Hermesto take advantage
of terrific raw-order selection speed available whenLogMiner is used in raw-order mode.

We've discussed use of Hermes as a "re-ordering" tool for feeds requireing the commit order.
It's clearly feasible

Database :
----------
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
--

We are building up a datawarehouse project with retrospective effect. ie. The client wants the baseline as of January 2007 and track changes in few selected tables and build downstream datamarts. A procedure using logminer was designed and tested to mine archived logs.
In a day we may be able to mine at most 150 logs. As of now we have around 16000 log files to be mined. By the time we mine the pending archived log files, we may have generated another 3000 files or so. Somewhere down the line we may come in sync with current data. Once we come in sync, we have planned to setup streams and capture the changes.

My question is :
1. Is this a sensible solution to capture changes using logminer utility ?
2. Is there any other way / technology to achieve the change-data-capture from archived log files ?

Regards
Jeeva

Thanks Tom,
Your assumption is correct. We are mining last year's archived logs are catching up.
The reason I posed the question was that the present one is painfully slow and was wondering is there any other tool.
We will proceed with the current solution. Thanks for your input.

ps.
"Not sure that I've see someone with the last years worth of archives that often"
I was working in a gov project which mandated us not to clear even udump / bdump / cdump files for three years!

Hi,
In relation to the question asked below about automating a logswitch for logminer, I have tried also to automate logminer using a linux shell script as below:

My questions are how do i automate the logswitch since i cannot add manually all the archivelogs created?,

How do i extract the archivelogs from the directory and populate the v$logmnr_contents view in this shell script?

#!/bin/ksh
echo " Type the STARTTIME(DD-MM-YYYY HH24:MI:SS): "
read STARTTIME

echo "Type User ENDTIME(DD-MM-YYYY HH24:MI:SS): "
read ENDTIME

echo $STARTTIME

sqlplus "/as sysdba" << EOF
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/u02/arch/1_50662_581101112.
arc',OPTIONS => DBMS_LOGMNR.NEW);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/u02/arch/1_50706_581101112.
arc');

EXECUTE DBMS_LOGMNR.START_LOGMNR(-
OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

set lines 100
set pages 10
col USERNAME format a10
col SQL_REDO format a10
col SQL_UNDO format a10

select username,sql_redo,sql_undo FROM v$logmnr_contents;

EXIT
EOF

kindly help out,

Thanks.

Hi,
In relation to the question asked below about automating a logswitch for logminer, I have tried also to automate logminer using a linux shell script as below:

My questions are how do i automate the logswitch since i cannot add manually all the archivelogs created?,

How do i extract the archivelogs from the directory and populate the v$logmnr_contents view in this shell script?

#!/bin/ksh
echo " Type the STARTTIME(DD-MM-YYYY HH24:MI:SS): "
read STARTTIME

echo "Type User ENDTIME(DD-MM-YYYY HH24:MI:SS): "
read ENDTIME

echo $STARTTIME

sqlplus "/as sysdba" << EOF
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/u02/arch/1_50662_581101112.
arc',OPTIONS => DBMS_LOGMNR.NEW);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/u02/arch/1_50706_581101112.
arc');

EXECUTE DBMS_LOGMNR.START_LOGMNR(-
OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

set lines 100
set pages 10
col USERNAME format a10
col SQL_REDO format a10
col SQL_UNDO format a10

select username,sql_redo,sql_undo FROM v$logmnr_contents;

EXIT
EOF

kindly help out,

Thanks.

Hi,
I creating a loop in the shell script so that the archivelogs could be picked and generated automatically to populate the v$logmnr_contents




Thank you.

Hi,
I creating a loop in the shell script so that the archivelogs could be picked and generated automatically to populate the v$logmnr_contents




Thank you.

Hi,

Thanks for the reply
Using a oracle 10g database with the online archive mode option,
I actually want to load all the archivelogs generated automatically to populate the V$logmnr_contents view using a procedure or a shell script as you said,

I just need your help on a clue to go about it and 

2.why is it that when i run this below i get the following error, even though i have granted select to the user and also the role:


SQL> select * from v$logmnr_contents;
select * from     v$logmnr_contents
                  *
ERROR at line 1:
ORA-00942: table or view does not exist

Kindly help out

thanks

Hi Tom,

Your script below has been very helpful, keep up the good work of knowledge sharing.

But how do spool name from  v$archived_log and populate v$logmnr_contents with it so that i can get my output in txt file, it is not working.

Kindly help out.
Below is my script:



#!/bin/ksh
echo " Type the STARTTIME(01-jan-2009 08:30:00): "
read STARTTIME



echo "Type User ENDTIME(31-mar-2009 08:45:00): "
read ENDTIME
sqlplus "/ as sysdba" << EOF

CURSOR c_logs IS
SELECT name
FROM v$archived_log
WHERE sequence# BETWEEN v_first_log_seq AND v_last_log_seq
ORDER BY sequence#;

BEGIN
FOR i IN c_logs LOOP
v_asm_logname := SUBSTR(i.name, 16);
v_log_seq := SUBSTR(v_asm_logname,4,5);
v_win_logname := 'orcl_arc'||v_log_seq||'.log';


ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MM-YYYY HH24:MI:SS';
SPOOL C:\Listing_41.log



EXECUTE DBMS_LOGMNR.START_LOGMNR(STARTTIME => '02/20/2009 06:00'-
        ,ENDTIME => '02/20/3000 12:00'-
        ,OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.CONTINUOUS_MINE);






set lines 100
set pages 10
col USERNAME format a10
col SQL_REDO format a10
col SQL_UNDO format a10

SELECT username,-
SQL_REDO, SQL_UNDO ,to_char(timestamp,'DD/MM/YYYY HH24:MI:SS') timestamp-
FROM V\$LOGMNR_CONTENTS;
EXIT;
EOF
01-
SPOOL OFF

attached below is the detail for my archive logs:

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u02/arch
Oldest online log sequence     55317
Next log sequence to archive   55326
Current log sequence           55326

Hai,
We have a medical departement database in this all database users are complaints me about the perfomence issues like when the user try to connect the database it will very slow connect and when they are retriving data it also very slow.. please can you suggest how to tune like this issues.

Hello Tom,
If I'm interpreting your response above correctly, loading archive logs for LogMiner is a tedious, labor-intensive task.
Would your opinion be different if we used the "dbms_logmnr.continous_mine" option? If not, can you explain why this approach is not preferred.
Also, I'm trying to use LogMiner and the query to view v$logmnr_contents is hanging even after the "endtime" of the session. Is this a byproduct of the "dbms_logmnr.continuous_mine" option? If so, this doesn't seem to be the correct approach to providing near real-time auditing.
As always, thanks for the forum.

Hi Tom,
Thanks for the alternatives.  Our customers are on standard edition and we want to implement asynchronous cdc.  Also, some of their db hosts don't have the capacity to create a staging db.  For the same standard/enterprise edition limitations, we can't implement streams.
Basically, we're looking to implement either triggers on each table or parse LogMiner data.  Triggers will probably cause a performance issue and LogMiner will allow us to schedule parsing of change data as well as parse past archive logs.
I'm now concerned that real-time capture of audit data using LogMiner may not be possible.  My query below on the v$logmnr_contents view has not returned for almost 24 hours:

13:03:03 SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-Mon-YYYY HH24:MI:SS';

Session altered.

Elapsed: 00:00:00.00
13:03:13 SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR( -
13:03:13 >    STARTTIME => '17-Feb-2010 13:05:00', -
13:03:13 >    ENDTIME => '17-Feb-2010 13:30:00', -
13:03:13 >    OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + -
13:03:13 >    DBMS_LOGMNR.CONTINUOUS_MINE);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03
13:03:13 SQL> select to_char(sysdate,'DD-Mon-YYYY hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'DD-
--------------------
17-Feb-2010 13:03:18

Elapsed: 00:00:00.03
13:03:17 SQL> select to_char(sysdate,'DD-Mon-YYYY hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'DD-
--------------------
17-Feb-2010 13:05:38

Elapsed: 00:00:00.01
13:05:38 SQL> select count(*) from v$logmnr_contents where rownum < 10;

Can you let me know how I can query the v$logmnr_contents view to populate a staging table and if my assumptions above are correct?

Thank you.

Tom,
Thanks again for the feedback. Your points (of course) are valid and to-the-point.

Hi Tom,

I understand while fetching the data via V$logmnr_contents,files are read sequentially and thats why the response will be very very slow. Is there any way that i can overcome this, as i will be using log miner almost every other day or another solution is also highly appreciated.

Thanks !