LogMiner with 8.1.6 on Solaris

[arno]

A problem to an Oracle expert.

Using Logminer to extract the SQL instructions performed on a database the following is happened :

1. The procedure provided by logmnr packages are correctly executed. The dictionary file is generated, the archived log are added and the v$logmnr_contents is populated bye means of dbms_logmnr.start_logmnr procedure.

2. When i try to run a select on sql_redo field from sqlplus session, the program dispalys a certain number of rows, then it stops with the foillowing message : SP2-0642: SQL*Plus internal error state 2133, context 0:0:0
Unsafe to proceed.
In other cases it returns : ORA-3113 End of file on comunication channel, before closing the connection to Oracle.

This happen if the select is performed against sql_redo or sql_undo fields. Using timestamp or scn fields, the select works correctly and all rows are gibven back.

Thanks in advance.

arno

[sambavan]

Curtesy to Metalink:

Most bugs on these errors with logminer are closed as duplicates of bug 1059067:

Here is the information listed in the 8.1.6 readme under known open bugs:

BUG 1059067
Description:
Selecting from V$LOGMNR_CONTENTS for logfiles of databases with
block size > 2K may return ORA-7445 if the logfiles contain redo records
corresponding to LONG datatype.

Workarounds:
1. Do not select SQL_REDO and SQL_UNDO columns from V$LOGMNR_CONTENTS.
2. If SQL_REDO and SQL_UNDO are needed, then use START_SESSION with an SCN range
that does not include the offending records.

Bug 1001917 is still open, but is a port specific Win NT. I don't know if this applies since I don't know the os you are running.

Bug 1152665 is also still open, it is filed as 'generic', however, it mentions getting an ora-7445.

No patches are available for bug 1059067, it may be included in the 8.1.6.2. The 'fixed in' version is listed as 8.1.7.

If this is an urgent for you. You can log an itar and request a backport.

The first step would be to isolate the bug you are hitting. Try the workarounds listed for bug 1059067, etc.


Sam

[arno]

OK.
But how can i know in advance which scn must be avoided ?
May be a way could be doing a start_logminer including all the archived log to be analyzed. Then selecting scn and log_id of those records whose username is not null and is not SYS or SYSTEM.
After that, should be performed a start logminer for those scn and log_id resulting from the precvious query.

I am sure that our application never uses fields with datatype LONG or RAW,....that can be resposible of generating offeding records in v$logmnr_contents.Then i think that excluding the transactions whose username is SYS or SYSTEM, we are sure that all the possible transactions don't contain offendig records.
Let me know if the way is right.
By the way, our OS is solaris and we are using 8.1.6.0.0 version of Oracle.

Many thanks for your help.

[arno]

Hi Sam,

Do you confirm my previous response about the way of avoiding the offending records in v$logmnr_contents ?

Thanks.

[ngwh]

juz to clarify in oracle 7.3.X and 8.* ... is the LOGMINER package still valid ??