Home » RDBMS Server » Server Administration » Incorrect results with LogMiner??
Incorrect results with LogMiner?? [message #255962] Thu, 02 August 2007 10:12 Go to next message
rsharma
Messages: 2
Registered: August 2007
Location: USA
Junior Member
I have to track the inserts made in a specific table, say T during a specified time duration on my Production

server. I am trying to use LogMiner on my development server first to see how it works. If I look at the table,

during a specified span of time, I see 36 records. But when I try to retrieve information through LogMiner, I am

getting just one row (I tried different options but never got the same set of inserts that I actually see in the

actual table). I created the following script:

ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI';

SPOOL C:\DataMining.log

--select the specific files
BEGIN
SYS.DBMS_LOGMNR.ADD_LOGFILE
('/backup/util/dev/1_7798_624975468.dbf', options => sys.dbms_logmnr.new);
SYS.DBMS_LOGMNR.ADD_LOGFILE
('/backup/util/dev/1_7799_624975468.dbf', options => sys.dbms_logmnr.new);
SYS.DBMS_LOGMNR.ADD_LOGFILE
('/backup/util/dev/1_7800_624975468.dbf', options => sys.dbms_logmnr.new);
SYS.DBMS_LOGMNR.ADD_LOGFILE
('/backup/util/dev/1_7801_624975468.dbf', options => sys.dbms_logmnr.new);
END;

-- Start LogMiner, running from the database's online data dictionary and
-- preparing for several mining attempts
BEGIN
SYS.DBMS_LOGMNR.START_LOGMNR(
STARTTIME => '08/01/2007 16:00'
,ENDTIME => '08/01/2007 16:30'
,OPTIONS => SYS.DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + SYS.DBMS_LOGMNR.CONTINUOUS_MINE +

SYS.DBMS_LOGMNR.PRINT_PRETTY_SQL
);
END;

-- Find the desired data
SELECT to_char(timestamp,'mm/dd/yy hh24:mi:ss') timestamp,
seg_name,
sql_redo
FROM v$logmnr_contents
WHERE table_name like 'T'
AND operation = 'INSERT'


-- End the LogMiner session
EXEC SYS.DBMS_LOGMNR.END_LOGMNR;

SPOOL OFF


Can anyone tell me whats happenning here and what I need to do?
Re: Incorrect results with LogMiner?? [message #255967 is a reply to message #255962] Thu, 02 August 2007 10:25 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
What happening where??

Format Your Querry.

take a quick loook at log minor documeentation

http://www.scribd.com/doc/173842/Oracle-Log-Miner
Re: Incorrect results with LogMiner?? [message #255975 is a reply to message #255967] Thu, 02 August 2007 10:39 Go to previous messageGo to next message
rsharma
Messages: 2
Registered: August 2007
Location: USA
Junior Member
When I expect LogMiner to give 36 records, why am I getting just one? Here is my script again:

ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI';

SPOOL C:\DataMining.log

--select the specific files
BEGIN
SYS.DBMS_LOGMNR.ADD_LOGFILE
('/backup/util/dev/1_7798_624975468.dbf', options => sys.dbms_logmnr.new);
SYS.DBMS_LOGMNR.ADD_LOGFILE
('/backup/util/dev/1_7799_624975468.dbf', options => sys.dbms_logmnr.new);
SYS.DBMS_LOGMNR.ADD_LOGFILE
('/backup/util/dev/1_7800_624975468.dbf', options => sys.dbms_logmnr.new);
SYS.DBMS_LOGMNR.ADD_LOGFILE
('/backup/util/dev/1_7801_624975468.dbf', options => sys.dbms_logmnr.new);
END;

-- Start LogMiner, running from the database's online data dictionary and
-- preparing for several mining attempts
BEGIN
SYS.DBMS_LOGMNR.START_LOGMNR(
STARTTIME => '08/01/2007 16:00'
,ENDTIME => '08/01/2007 16:30'
,OPTIONS => SYS.DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + SYS.DBMS_LOGMNR.CONTINUOUS_MINE + SYS.DBMS_LOGMNR.PRINT_PRETTY_SQL
);
END;

-- Find the desired data
/* Formatted on 2007/08/02 11:39 (Formatter Plus v4.8.7) */
SELECT TO_CHAR (TIMESTAMP, 'mm/dd/yy hh24:mi:ss') TIMESTAMP, seg_name,
sql_redo
FROM v$logmnr_contents
WHERE table_name LIKE 'T' AND operation = 'INSERT'



-- End the LogMiner session
EXEC SYS.DBMS_LOGMNR.END_LOGMNR;

SPOOL OFF
Re: Incorrect results with LogMiner?? [message #255999 is a reply to message #255975] Thu, 02 August 2007 12:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow How to format your posts
Make sure that lines of code do not exceed 80 or 100 characters when you format.
Please always post your Oracle version (4 decimals).
Answer depends on version.

Regards
Michel
Re: Incorrect results with LogMiner?? [message #256192 is a reply to message #255962] Fri, 03 August 2007 06:43 Go to previous message
cbruhn2
Messages: 41
Registered: January 2007
Member
Hi rsharma,

try to read my blog entry on the topic of logmining.
http://www.orafaq.com/node/1378
There are some details you have to be aware of befor you can do logmining. For instance you have to setup supplemental logging on the table or database before you will be able to see your expected results

best regards
Carl Bruhn
Previous Topic: 866.92 free KB remains in background dump area.
Next Topic: Undo management "Flashback Version Query"
Goto Forum:
  


Current Time: Wed Dec 07 08:34:27 CST 2016

Total time taken to generate the page: 0.20051 seconds