Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: oracle 9i LogMiner

RE: oracle 9i LogMiner

From: Ian Cary <Ian.Cary_at_ons.gsi.gov.uk>
Date: Tue, 3 Aug 2004 12:07:17 +0100
Message-ID: <OF8B78C17E.343775D9-ON80256EE5.003AA39C-80256EE5.003D1750@ons.gov.uk>

I haven't had a problem in analyzing log files in other instances, in fact I regard this as one of the main benefits as production logs can be anaylysed by a test instance without any impact on the production instance which can be extremely useful for things like versioning and auditing.

To enable this analysys in other instances you have to create a dictionary file which is basically a dump of the data dictionary (of the instance owing the logfiles) to a flat file. This operation has some interesting 'features' but can be done as follows;

begin
  dbms_logmnr_d.build(dictionary_filename=>'<filename>',

dictionary_location=>'<directoryname');
end;

The interesting part was specifying the directory (on 9.2.0.4) as this had to be derived from the old utl_file_dir parameter and not by a "create directory" created directory. Also the file had to contain a trailing '/' on unix i.e /u01/logmnr/ and not /u01/logmnr (N.B. the utl_file_dir parameter has to contain the trailing '/' also)

You can also save yourself hours (well minutes) of fun by making sure that Oracle (or the instance process owner) has write permissions on the target file!

One other thing is that in 9.2 supplemental logging is turned off whereas it previously used be turned on by default. This is necessary to log direct path inserts and ddl changes and is invoked by the command;

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; Undo and redo information in the logs is referenced by rowid which may not always be useful. It is possible to add the supplemental logging information such that primary and unique key information is logged rather than by rowid;

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE INDEX) COLUMNS; however the docs state that the latter option "does impose significant overhead and effects performance" so should be used with caution.

Hope this helps,

Cheers,

Ian

|---------+----------------------------->

| | DWILLIAMS_at_LIFETOUC|
| | H.COM |
| | Sent by: |
| | oracle-l-bounce_at_fr|
| | eelists.org |
| | |
| | |
| | 02/08/2004 21:25 |
| | Please respond to |
| | oracle-l |
| | |
|---------+-----------------------------> >------------------------------------------------------------------------------------------------------------------------------| | | | To: oracle-l_at_freelists.org | | cc: | | Subject: RE: oracle 9i LogMiner | >------------------------------------------------------------------------------------------------------------------------------|

Greg

   DON'T try to read log files on a different instance (read production files on a test instance) even if the one is a clone of the other. To Logminer they are different databases and so it shows you everything in hexadecimal, which can be amusing and challenging for awhile but the enchantment quickly wears thin.

Dennis Williams
DBA
Lifetouch, Inc.

"We all want progress, but if you're on the wrong road, progress means doing an about-turn and walking back to the right road; in that case, the man who turns back soonest is the most progressive." -- C.S. Lewis

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Loughmiller, Greg Sent: Monday, August 02, 2004 2:49 PM
To: 'oracle-l_at_freelists.org'
Subject: oracle 9i LogMiner

folks,

does anyone have a list of "do's and dont's" for the use of logminer? Naturally, I'm still surfing the manuals and web to gather my notes.. But thought I'd blast the request out there anyway. Looking in terms of a "best practices" sort of thing.

thanks in advance...

Greg Loughmiller
Sr Manager - Enterprise Data Architecture Office: 678.893.3217
ips: gloughmiller
This email, and any attachments, are intended only for use by the addressee(s) named herein and may contain legally privileged and/or confidential information. It is the property of Cingular Wireless. If you are not the intended recipient of this email, you are hereby notified that any dissemination, distribution or copying of this email, any attachments thereto, and any use of the information contained is strictly prohibited. If
you have received this email in error, please notify me at 678-893-3217 and permanently delete the original and any copy thereof.



Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
______________________________________________________________________







For the latest data on the economy and society 
consult National Statistics at http://www.statistics.gov.uk

**********************************************************************
Please Note:  Incoming and outgoing email messages
are routinely monitored for compliance with our policy
on the use of electronic communications
**********************************************************************
Legal Disclaimer  :  Any views expressed by
the sender of this message are not necessarily
those of the Office for National Statistics
**********************************************************************

______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email 
______________________________________________________________________
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Received on Tue Aug 03 2004 - 06:03:10 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US