OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » LogMiner Doubt (11.2.0.3.0)

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

LogMiner Doubt [message #586053]

Mon, 03 June 2013 03:10

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

Report message to a moderator

Re: LogMiner Doubt [message #586054 is a reply to message #586053]

Mon, 03 June 2013 03:15

John Watson
Messages: 8989
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?

Report message to a moderator

Re: LogMiner Doubt [message #586056 is a reply to message #586054]

Mon, 03 June 2013 03:25

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?

Report message to a moderator

Re: LogMiner Doubt [message #586057 is a reply to message #586054]

Mon, 03 June 2013 03:25

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

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

Report message to a moderator

Re: LogMiner Doubt [message #586058 is a reply to message #586056]

Mon, 03 June 2013 03:27

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

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

Report message to a moderator