How to Resolve ORA-12034: materialized view log on younger than last refresh

ORA-12034

Found an error ORA-12034 in alert log:

ORA-12034: materialized view log on younger than last refresh

Rationale

ORA-12034 means that your last refresh older than the materialized view log and no way to sync back to the master table due to some reasons. After doing some researches, I found there're several possibilities that can cause the problem:

1. The definition of the master table has been changed. e.g. Columns added.
2. The segment(s) of the master table was reorganized. (rowid will be changed as well)
3. The last refresh failed for some reasons.

In our case, complete refresh can be done on this MV, but it failed to do fast refresh. This is the biggest hint to trace the problem. Just like I said in the post: How to Resolve ORA-12004: REFRESH FAST cannot be used for snapshot

If you are able to do complete refresh, but unable to do fast refresh. I think the major problem is on the materialized view log.

Therefore, we should recreate the materialized view log in order to solve the problem.

Solutions

If the recreation of MV log can't solve ORA-12034, try to recreate the materialized view with IMMEDIATE or ON PREBUILT TABLE which depends on your situation. If you'd like to make sure whether to use ON PREBUILT TABLE or not in the syntax of creating the MV, you can find out the build mode by the statement below:

SQL> select build_mode from dba_mviews where owner = 'OWNER' and mview_name = 'MVIEW_NAME';
BUILD_MOD
---------
PREBUILT

More syntax variations to create materialized view can be found:
CREATE MATERIALIZED VIEW - Oracle 12c Official Documentation

I have to note this down: There's another type of error messages that you could meet in the process of debugging. Same problem, same resolution (that is, recreate the MV log).

ORA-12018: following error encountered during code generation for "OWNER"."MVIEW_NAME"
ORA-00942: table or view does not exist