Home » Other » General » log miner (oracle 11.2.0.3.0, windows 7)
log miner [message #586993] Wed, 12 June 2013 06:12 Go to next message
zeeshan047
Messages: 99
Registered: June 2010
Location: PAKISTAN
Member

hi,

I am using log miner to check wrong update statement executed, when i execute the query to read data from log files the result shown in the format which is difficult to understand , how can i use log miner to retrieve data in human readable format.

[code]

select scn, sql_redo from v$logmnr_contents;

update "UNKNOWN"."OBJ# 12320" set "COL 28" = HEXTORAW('00'), "COL 70" = NULL where "COL 1" = HEXTORAW('414c4552545f515545')

[code]
Re: log miner [message #587000 is a reply to message #586993] Wed, 12 June 2013 06:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is the case if you didn't provide a catalog to Log Miner.
See DBMS_LOGMNR_D package.

Regards
Michel
Re: log miner [message #587022 is a reply to message #587000] Wed, 12 June 2013 07:43 Go to previous messageGo to next message
zeeshan047
Messages: 99
Registered: June 2010
Location: PAKISTAN
Member

Thanks it works.

one more query i have retrieve time stamp and scn for update query using log miner. Now whats the best option to undo the change occur in one table.

Is there any option other than incomplete database recovery to just change the effect on only one table rather than whole database.

Thanks.

Re: log miner [message #587032 is a reply to message #587022] Wed, 12 June 2013 07:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can execute the statements that are in the SQL_UNDO field of V$LOGMNR_CONTENTS.

Regards
Michel
Re: log miner [message #587062 is a reply to message #587032] Wed, 12 June 2013 08:45 Go to previous messageGo to next message
zeeshan047
Messages: 99
Registered: June 2010
Location: PAKISTAN
Member

one last question , did we get any information regarding drop table with purge command.

As i use drop table dept purge , and can't see any information in logmnr_contents view.

What will be the option left to recovery table.

Thanks.
Re: log miner [message #587067 is a reply to message #587062] Wed, 12 June 2013 08:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
As i use drop table dept purge , and can't see any information in logmnr_contents view.


I don't know, never tried to search such statement.
If you drop with "purge" then you have a delete on sys.obj$ otherwise you have an update on this table.
This is the only clue I can give you if Log Miner does not give the complete DROP statement.

Regards
Michel

[Updated on: Wed, 12 June 2013 08:58]

Report message to a moderator

Re: log miner [message #587069 is a reply to message #587062] Wed, 12 June 2013 08:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
What will be the option left to recovery table.


If you purge a table then the only thing is to recover (part of) the database.
This is explained in Database Backup and Recovery Advanced User's Guide (iirc).

Regards
Michel

[Edit: typo and missing word, good grief!]

[Updated on: Wed, 12 June 2013 12:07]

Report message to a moderator

Re: log miner [message #587127 is a reply to message #587069] Wed, 12 June 2013 11:07 Go to previous messageGo to next message
zeeshan047
Messages: 99
Registered: June 2010
Location: PAKISTAN
Member

thanks for the mention link covers advance level backup and recovery concepts, also can you send me the link which cover advance level performance and tuning concepts specially using AWR reports.

Thanks for your support.
Re: log miner [message #587128 is a reply to message #587127] Wed, 12 June 2013 11:28 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.oracle.com/pls/db112/homepage
Previous Topic: Can valid object refer to invalid objects?
Next Topic: Sync MySQL table data with Oracle tables
Goto Forum:
  


Current Time: Thu Mar 28 17:49:41 CDT 2024