Home » RDBMS Server » Performance Tuning » LogMiner Doubt (11.2.0.3.0)
LogMiner Doubt [message #586053] Mon, 03 June 2013 03:10 Go to next message
rishwinger
Messages: 132
Registered: November 2011
Senior Member
Hi Experts,

I am using Oracle Logminer to analyze the sql's executed 2 days back
In LogMiner I'm extracting LogMiner Dictionary to the Redo Log Files.

EXEC DBMS_LOGMNR_D.BUILD ( OPTIONS => DBMS_LOGMNR_D.STORE_IN_REDO_LOGS );

Above command will extract and place the dictionary of source DB in redo log files lets say in "1_115_816259673.dbf" file

I want to analyze sql executed in specific redolog i.e lets say in "1_112_816259673.dbf" file

Problem is to analyze sql executed in "1_112_816259673.dbf" i would required all the files till the file which contain dictionary of source DB


1_112_816259673.dbf -- This need to be analyzed
1_113_816259673.dbf -- Unnecessary
1_114_816259673.dbf -- Unnecessary
1_115_816259673.dbf -- this contains the dictionary of Source DB

If i add only 1_112_816259673.dbf , 1_115_816259673.dbf

select filename from v$logmnr_logs;

FILENAME
----------------------------------------------------------
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch/1_112_816259673.dbf
Missing log file(s) for thread 1, sequence(s) 113 to 114
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch/1_115_816259673.dbf


So i execute below command

BEGIN
    DBMS_LOGMNR.START_LOGMNR (
        options => DBMS_LOGMNR.DICT_FROM_REDO_LOGS +
                   DBMS_LOGMNR.DDL_DICT_TRACKING +
                   DBMS_LOGMNR.PRINT_PRETTY_SQL
    );
END;
Error report:
ORA-01291: missing logfile
ORA-06512: at "SYS.DBMS_LOGMNR", line 58
ORA-06512: at line 2


Can't use flat files or Online Catalog, Is it possible only those files which need to be analyzed and which contain dictionary are only added and logimer could work out?

Please help
Re: LogMiner Doubt [message #586054 is a reply to message #586053] Mon, 03 June 2013 03:15 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I would think that you will need to use the online catalog in your circumstances. Why do you say you cannot do this?
Re: LogMiner Doubt [message #586056 is a reply to message #586054] Mon, 03 June 2013 03:25 Go to previous messageGo to next message
rishwinger
Messages: 132
Registered: November 2011
Senior Member
Thanks John for quick reply, Its a production DB and that also in customer site, they won't let me touch there db so can't use Online catalog , As far as flat file is concerned UTL_FILE_DIR might not be set so to set that DB bounce is required which is also out of scope.

Actually issue occurred sometime back, if i use LogMiner Dictionary to the Redo Log Files then that would mean all the redolog files from the time issue occurred till the time dictionary added in redo logs are required for analysis?

If my understanding is correct then i am talking in terms of X GB data.

Please let me know is their is any workaround?
Re: LogMiner Doubt [message #586057 is a reply to message #586054] Mon, 03 June 2013 03:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Or if you can't do it (for instance you don't analyze in the current database), you can extract the dictionary into a file and use this file during analysis.

Regards
Michel
Re: LogMiner Doubt [message #586058 is a reply to message #586056] Mon, 03 June 2013 03:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Actually issue occurred sometime back, if i use LogMiner Dictionary to the Redo Log Files then that would mean all the redolog files from the time issue occurred till the time dictionary added in redo logs are required for analysis?


The problem is not where is the dictionary, it is there must be no hole in the log file list.

If you need this in a regular basis, I advise you to regularly dump the dictionary into the log files.

Regards
Michel
Re: LogMiner Doubt [message #586059 is a reply to message #586058] Mon, 03 June 2013 03:36 Go to previous messageGo to next message
rishwinger
Messages: 132
Registered: November 2011
Senior Member
Thanks Michel for replying, so i guess there is no work around to this problem

thanks all
Re: LogMiner Doubt [message #586060 is a reply to message #586059] Mon, 03 June 2013 03:46 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't see any.

Regards
Michel
Previous Topic: High System I/O Wait Class on AWR
Next Topic: Unable to update newly added column in existing table
Goto Forum:
  


Current Time: Thu Mar 28 12:48:27 CDT 2024