ORA-12096: error in materialized view log on “SCHEMA”.”MVMASTERTABLE” ..ORA-10632: Invalid rowid

Cause:

This error occur due to prevention of corruption in snapshot MV log while performing DML (insert/delete/update) on master or base table of materialized views

Issue affect versions till 19c

Objects which start with MLOG$ are MV snapshot log table created for a base table. Every time we perform DML operation on the base table, the rows are populated into the MV log during MV refresh. After the rows are deleted from the base table, the inherited MV also shrink the space automatically. If the MV has corruptions in the high water mark, then the corresponding DML fails with the below error

You may see symptoms like below

MVRF: kkzlShrinkMVLog: executed: alter table "<schema name>"."MLOG$_XXXXX_LOGS_TABLE" shrink space
ORA-31934: error occurred while shrinking the materialized view log at kkzlRunSA:execute
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 1

In trace file messages similar to below creep in

ORA-12096: error in materialized view log on "schema name"."base table name"
<error barrier> at 0x7fff8f7e34e5 placed xxxx.y@1284
ORA-10632: Invalid rowid
<error barrier> at 0x7fff8f7e9642 placed delexe.c@5967

Error stack traces related to issue

(kntxslt()+4363 -> kserbc2())

To identify the cause generate a 10046 trace and run the failing DML and look for parse error section and note down the obj# at which the execution fail

WAIT #139836060156120: nam='enq: TM - contention' ela= 47834 name|mode=1414332419 object #=108796 table/partition=0 obj#=108796 tim=13882466772554
EXEC #139836060156120:c=144063,e=192357,p=0,cr=122819,cu=24,mis=0,r=0,dep=1,og=1,plh=4195192068,tim=13882466916886
ERROR #139836060156120:err=12096 tim=13882466916928

Then using the obj# , find out the object on which the error occur

Workaround:

Workaround may differ depending on the type of error. So its always recommended to research the cause before applying any changes

Disable the hidden parameter _mv_refresh_shrink_log . There are some limitations of disabling the parameter.

–Automatic shrink will be disabled by setting the parameter to false. You have to perform manual shrink of MV after each iteration of DML

alter system set "_mv_refresh_shrink_log" = FALSE;

If there are ora-600 errors with ORA-00600 [ktsbnew:objdchk_kcbnew_3]

Call stack –> kcb_objdchk_assert <- kcbnew <- ktsbnew <- ktspfmdb

Then you may need to drop the MV log and recreate it

drop snapshot log on <schema name>.XXXXX_LOGS_TABLE;
create snapshot log on <schema name>.XXXXX_LOGS_TABLE;

Leave a Reply