OraFAQ Forum: Server Utilities

Home » RDBMS Server » Server Utilities » Log miner (Oracle 10g R2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Log miner [message #610420]

Wed, 19 March 2014 22:14

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

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

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.

Report message to a moderator

Re: Log_minor [message #610433 is a reply to message #610431]

Thu, 20 March 2014 01:17

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

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.

Report message to a moderator

Re: Log_minor [message #610486 is a reply to message #610433]

Thu, 20 March 2014 10:11

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

Report message to a moderator

Re: Log_minor [message #610491 is a reply to message #610486]

Thu, 20 March 2014 11:25

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

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.

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jun 05 05:08:36 CDT 2025