Home » RDBMS Server » Server Utilities » Log miner (Oracle 10g R2)
Log miner [message #610420] Wed, 19 March 2014 22:14 Go to next message
sha_jit
Messages: 4
Registered: May 2011
Junior Member
Here I have tried to get the out put of he LOG Miner, but after all those below steps, I got the out put but all the USERNAME is total blank.

SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;
SUPPLEME
--------
NO

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

SQL> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;
SUPPLEME
--------
YES

EXECUTE dbms_logmnr.add_logfile (logfilename => '/lhj6/appl/arch/dbdctmx3/dbdctmx3arch/1_74893_704244763.arc',options=>sys.dbms_logmnr.new);
EXECUTE dbms_logmnr.add_logfile (logfilename => '/lhj6/appl/arch/dbdctmx3/dbdctmx3arch/1_74894_704244763.arc',options=>sys.dbms_logmnr.new);
EXECUTE dbms_logmnr.add_logfile (logfilename => '/lhj6/appl/arch/dbdctmx3/dbdctmx3arch/1_74895_704244763.arc',options=>sys.dbms_logmnr.new);
EXECUTE dbms_logmnr.add_logfile (logfilename => '/lhj6/appl/arch/dbdctmx3/dbdctmx3arch/1_74896_704244763.arc',options=>sys.dbms_logmnr.new);
EXECUTE dbms_logmnr.add_logfile (logfilename => '/lhj6/appl/arch/dbdctmx3/dbdctmx3arch/1_74897_704244763.arc',options=>sys.dbms_logmnr.new);
EXECUTE dbms_logmnr.add_logfile (logfilename => '/lhj6/appl/arch/dbdctmx3/dbdctmx3arch/1_74898_704244763.arc',options=>sys.dbms_logmnr.new);
EXECUTE dbms_logmnr.add_logfile (logfilename => '/lhj6/appl/arch/dbdctmx3/dbdctmx3arch/1_74899_704244763.arc',options=>sys.dbms_logmnr.new);

EXECUTE DBMS_LOGMNR.START_LOGMNR(options => dbms_logmnr.DICT_FROM_ONLINE_CATALOG);

set lines 200
set pages 0
col USERNAME format a10
col SQL_REDO format a30
col SQL_UNDO format a30

SELECT username,session#,to_char(timestamp,'DD/MM/YYYY HH24:MI:SS') timestamp, (XIDUSN || '.' || XIDSLT || '.' || XIDSQN) AS XID,SQL_REDO, SQL_UNDO
FROM V$LOGMNR_CONTENTS WHERE username = 'HSDM01P'

SQL> select distinct username from V$logmnr_contents;

no rows selected

Could you please help me, I got everything except username. where I m missing ??

Avijit

[EDITED by LF: fixed topic title typo; was "minor"]

[Updated on: Thu, 20 March 2014 01:49] by Moderator

Report message to a moderator

Re: Log_minor [message #610431 is a reply to message #610420] Thu, 20 March 2014 01:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Maybe simply this specific user did not modify anything.
Note that Supplemental logging must be set BEFORE the generation of the logs before there is no supplemental log in them.


Re: Log_minor [message #610433 is a reply to message #610431] Thu, 20 March 2014 01:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator


Also "options=>sys.dbms_logmnr.new" is used for the FIRST log (new means "new list") subsequent ones must be added using "options=>sys.dbms_logmnr.addfile".
So in your example, you have only the last log.

Re: Log_minor [message #610486 is a reply to message #610433] Thu, 20 March 2014 10:11 Go to previous messageGo to next message
sha_jit
Messages: 4
Registered: May 2011
Junior Member
Thanks Michel. I checked old arch file when Supplemental logging not set
Let me try again and I will post here if any issues
Re: Log_minor [message #610491 is a reply to message #610486] Thu, 20 March 2014 11:25 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

First check with "addfile" instead of "new" in "dbms_logmnr.add_logfile" for all but the first log. I'm pretty sure you don't need supplemental logging for the username information.

Previous Topic: date format in control file (sql loader)
Next Topic: sqlldr ORA-01841
Goto Forum:
  


Current Time: Thu Mar 28 14:18:15 CDT 2024