Tales From A Lazy Fat DBA

Its all about Databases & their performance, troubleshooting & much more …. ¯\_(ツ)_/¯

  • Prashant Dixit is the 'FatDBA' ...
  • Follow me on Twitter

ORA-12034: materialized view log younger than last refresh

Posted by FatDBA on January 7, 2022

Recently I have encountered an issue where frequent errors were captured in the alert log file about the on demand materialized view refresh failure. The main error reported was ORA-12034: materialized view log on “DIXDROID”.”TEST_STATS” younger than last refresh.

On demand
MV DIXDROID.PRAS_ID_MV was not refreshed successfully.
Number of MV refresh failures: 1.
Encountered error ORA-12034.
kkzifr3g: Encountered error ORA-12034.
2022-01-06T11:14:49.885045+02:00
Errors in file /monkeydb/can/ontadb/rdbms/ontadb/ONTADB/trace/ONTADB_j000_5663.trc:
ORA-12012: error on auto execute of job 1
ORA-12034: materialized view log on "DIXDROID"."TEST_STATS" younger than last refresh
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2960
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2378
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 85
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 245
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2360
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2916
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3199
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3255
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 41
ORA-06512: at "SYS.DBMS_IREFRESH", line 703
ORA-06512: at "SYS.DBMS_REFRESH", line 214
ORA-06512: at line 1

It was pointing to the Materialized View Log, about materialized view logs, they are created for a master table, and a materialized view has been created with the REFRESH FAST option, the following timestamps will be used when validating log age.

About potential causes, there could be many i.e.

  • Definition of the master table is altered.
  • The last refresh was failed for some reasons.
  • Altering the master table so that changes don’t go to the materialized view log i.e. truncate, alter partitions drop or truncate
  • Master table reorganization or when when issuing an MV log purge

Now about the solution, in our case this issue happens when a complete refresh is required before the next fast refresh. So I did the COMPLETE refresh for the MV in question and did the FAST refresh soon after that and that has fixed the issue.

SQL*Plus: Release 12.2.0.1.0 Production on Thu Jan 6 11:14:07 2022

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Enter user-name: / as sysdba

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL>

-- Following command will perform first time COMPLETE refresh on the materialized views
SQL> execute dbms_snapshot.refresh ('DIXDROID.PRAS_ID_MV', 'C');

PL/SQL procedure successfully completed.

-- After this first time COMPLETE refresh, the successive FAST refresh will be successful.
SQL> execute dbms_snapshot.refresh ('DIXDROID.PRAS_ID_MV', 'F');

PL/SQL procedure successfully completed.

SQL>

Hope It Helped!
Prashant Dixit

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

 
%d bloggers like this: