Home » RDBMS Server » Server Administration » Logminer not showing recent actions
Logminer not showing recent actions [message #269924] Tue, 25 September 2007 02:36 Go to next message
Kingfisher
Messages: 40
Registered: August 2007
Location: Oslo, Norway
Member
I basically follow this procedure:
The logminer-package is already compiled.
I add the logfiles (adding all with dbms_logmnr.add_logfile())
Then I start the logminer - typically dbms_logmnr.start_logmnr(options => dbms_logmnr.dict_from_online_catalog + dbms_logmnr.print_pretty_sql)

I do a search through v$logmnr_contents and find the expected.

Now I do some inserts, updates and deletes. Should I expect them to show up when searching v$logmnr_contents or do I have to "start" the logminer again?

Here's my problem:
Even if I do restart the logminer (stop it, then start it again), the new inserts, updates and deletes mentioned above do not show in v$logmnr_contents. It still just contains the old ones. Some statements from the 18th, for instance.

Will they only show after a while? Or what is going on here?

The code I am using (run one by one, not as a script):
@D:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\dbmslm.sql
grant execute_catalog_role to system
create public synonym dbms_logmnr for sys.dbms_logmnr
select distinct member logfilename from v$logfile
begin
  sys.dbms_logmnr.add_logfile('D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG');
  sys.dbms_logmnr.add_logfile('D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG');
  sys.dbms_logmnr.add_logfile('D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG');
end;
 
execute sys.dbms_logmnr.START_LOGMNR -
(options => sys.dbms_logmnr.dict_from_online_catalog + sys.dbms_logmnr.print_pretty_sql);
 
select username, to_char(timestamp,'dd/mm/yyyy hh24:mi:ss') timestamp, seg_type_name, 
table_name, table_space, session# SID, serial#, sql_undo, sql_redo
from v$logmnr_contents where table_name='RAB_TEST';

Thanks for any help.

[Updated on: Thu, 31 January 2008 04:59] by Moderator

Report message to a moderator

Re: Logminer not showing recent actions [message #297369 is a reply to message #269924] Thu, 31 January 2008 04:51 Go to previous messageGo to next message
NandKumar
Messages: 92
Registered: June 2007
Location: v$hyderabad
Member
Friends,

Iam also facing the same problem.

I started a logminer session, and was able to view the logminer contents. After some time, i did some more transactions, swithched the logfile, added it to the logminer sesion, but now unable to get the new operations. Its only showing the previous one.

I retried by starting from scratch. ended the previous session, still unable to get the new operations.

If you want me to post the steps, please let me know, Iam eagerly waiting for that.

NandKumar
Re: Logminer not showing recent actions [message #297374 is a reply to message #297369] Thu, 31 January 2008 05:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64108
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
If you want me to post the steps, please let me know

Yes, it would be useful.
Before please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code.
Use the "Preview Message" button to verify.
Also post your Oracle version (4 decimals).

Regards
Michel
Re: Logminer not showing recent actions [message #297403 is a reply to message #297374] Thu, 31 January 2008 06:13 Go to previous messageGo to next message
NandKumar
Messages: 92
Registered: June 2007
Location: v$hyderabad
Member
Here are the steps I followed

SQL> exec dbms_logmnr.add_logfile
(logfilename=>'d:\jeeva\archives\ARC00015_0644871648.001',
options=>dbms_logmnr.new);

PL/SQL procedure successfully completed.

SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog
+dbms_logmnr.committed_data_only);

PL/SQL procedure successfully completed.

SQL> exec dbms_logmnr.add_logfile(logfilename=>'d:\archives\ARC00016_0644871648.001');

PL/SQL procedure successfully completed.

SQL> exec dbms_logmnr.add_logfile(logfilename=>'d:\archives\ARC00017_0644871648.001');

PL/SQL procedure successfully completed.

SQL> exec dbms_logmnr.add_logfile(logfilename=>'d:\archives\ARC00018_0644871648.001');

PL/SQL procedure successfully completed.

SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog
+dbms_logmnr.committed_data_only+dbms_logmnr.print_pretty_sql);

PL/SQL procedure successfully completed.

SQL> col operation for a15
SQL> col sql_redo for a25
SQL> col sql_undo for a25
SQL> select operation, sql_redo, sql_undo from v$logmnr_contents where seg_name=
'NANDY' and seg_owner='JEEVADB';

OPERATION       SQL_REDO                  SQL_UNDO
--------------- ------------------------- -------------------------
DDL             create table nandy (no nu
                mber, name varchar2(10));

UPDATE          update "JEEVADB"."NANDY"  update "JEEVADB"."NANDY"
                  set                       set
                    "NAME" = 'ALONSO'         "NAME" = 'KUMAR'
                  where                     where
                    "NAME" = 'KUMAR' and      "NAME" = 'ALONSO' and
                    ROWID = 'AAANO0AAIAAA     ROWID = 'AAANO0AAIAAA
                AOMAAB';                  AOMAAB';

I ended this session using
SQL> exec dbms_logmnr.end_logmnr()

PL/SQL procedure successfully completed.

Now on another terminal, I inserted and deleted a few rows from the 'NANDY' table then, switched logfile and started the whole process again...this time I added the newly added archive log to the logminer session and started the session. Then queried the v$logmnr_contents only to find the previous output.

[Updated on: Thu, 31 January 2008 06:17] by Moderator

Report message to a moderator

Re: Logminer not showing recent actions [message #297406 is a reply to message #297403] Thu, 31 January 2008 06:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64108
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Then queried the v$logmnr_contents only to find the previous output.

And then?

Quote:
I inserted and deleted a few rows from the 'NANDY' table

Did you commit?

Regards
Michel
Re: Logminer not showing recent actions [message #297408 is a reply to message #297406] Thu, 31 January 2008 06:24 Go to previous messageGo to next message
NandKumar
Messages: 92
Registered: June 2007
Location: v$hyderabad
Member
Yes I did commit.
Re: Logminer not showing recent actions [message #297409 is a reply to message #297408] Thu, 31 January 2008 06:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64108
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And you say that after that you don't have the new operations in log miner view?
What is your version with 4 decimals.

Regards
Michel
Re: Logminer not showing recent actions [message #297410 is a reply to message #297409] Thu, 31 January 2008 06:28 Go to previous messageGo to next message
NandKumar
Messages: 92
Registered: June 2007
Location: v$hyderabad
Member
Yes...Exactly

Mine is Oracle 10.2.0.1.0
Re: Logminer not showing recent actions [message #297413 is a reply to message #297410] Thu, 31 January 2008 06:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64108
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Ok this is "normal" (an expected bug Smile ).
This is because of IMU (In Memory Undo), a new mechanism introduced to optimize change vectors management.
In short, Oracle records in log buffer change vectors (the operations you made on data) by batches instead of once at a time. More if you roll back before these in memory change vectors are flushed into log buffer then they are simply discarded and so there are less accesses to this log buffer.

These new record types are not recognized by Log Miner, so it skip them.

Regards
Michel
Re: Logminer not showing recent actions [message #297416 is a reply to message #297413] Thu, 31 January 2008 06:48 Go to previous messageGo to next message
NandKumar
Messages: 92
Registered: June 2007
Location: v$hyderabad
Member
So...Michel, is there any workaround for this...or we have to apply a patch?

I assume, there might be any other way out to get the new operations as well, since oracle has many options to do the same thing.

Anyways...thank you for your prompt reply.

NandKumar
Re: Logminer not showing recent actions [message #297420 is a reply to message #297416] Thu, 31 January 2008 07:11 Go to previous message
Michel Cadot
Messages: 64108
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The only workaround is to disable the new mechanism and lose performances improvements.

I don't know if this is fixed in any 10.2 patchset.

Regards
Michel
Previous Topic: Error during starting a database
Next Topic: Delete data from v$log_history
Goto Forum:
  


Current Time: Mon Dec 05 05:00:02 CST 2016

Total time taken to generate the page: 0.08989 seconds