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;