ORA-12034: materialized view log younger than last refresh

Dabblers in the art of the materialisation of views may be used to seeing this error message, and it's often a portent of doom. Metalink Note 204127.1 tackles it head on with a list of possible causes, but is remiss in advising that a complete refresh is required.

And thank goodness for that. A multi-gigabyte materialized view based on a multi-multi-gigabyte master table is no joke to perform a complete refresh on, and anything we can do to avoid it is welcome.

As far as data warehousing is concerned we can trim the fat out of the Metalink note to shortlist our potential causes:

  • Previous refresh failed
  • Modifying the master table so that changes don't go to the materialized view log (truncate, alter partitions … drop / truncate … etc)
  • Master table reorganization

And here is another prime cause that the note doesn't identify:

  • Issuing an MV log purge.

All of these potential causes appear to be easily fixable through the simple mechanism of dropping and recreating the materialized view. But I'm not talking about dropping the data here. One of the requirements for this method to be successful is that the materialized view was created on a prebuilt table. If this is the case, and it is possible to find out if it is or not based on the BUILD_MODE column of USER_MVIEWS, then in the event of a refresh failure we can:

  1. Drop the materialized view.
  2. Do whatever is required (if anything) to bring the master table and the MV back into synchronization (delete data from master, or add data to MV).
  3. Recreate the MV, again using the ON PREBUILT TABLE clause obviously.
  4. Erm …
  5. That's it.

Doubtless you script hounds will be wanting to see a test of this, huh?

create table m (col1 number);

create materialized view log on m
with rowid (col1)
including new values;

create table
     m_mv_cstar
as
select count(*) c_star
from   m;

create materialized view m_mv_cstar
 on prebuilt table
 refresh fast
 on commit
as
select count(*) c_star
from   m;

insert into m values (1);

commit;

That gets us a successfully fast refreshing materialized view. Now we break it.

drop materialized view log on m;

create materialized view log on m
with rowid (col1)
including new values;

insert into m values (1);

commit;

That ought to get you the ORA-12034 error, and this is the time to go and check whether any other materialized views has successfully refreshed and whether the change to the master table is still intact or whether that has been rolled back. In this case the change to m ought to have been rolled back.

So now we fix the problem of the ORA-12034 without a complete refresh of the materialized view.

drop  materialized view m_mv;

create materialized view m_mv
  on prebuilt table
  refresh fast
  on commit
as
select count(*) c_star
from   m;

If by some chance you had mislaid the definition of your materialized view then use the following to retrieve it.

Set Long 100000 Pagesize 0

Select  DBMS_Metadata.Get_DDL('MATERIALIZED_VIEW','M_MV_CSTAR') From Dual;

Note the underscore in the MATERIALIZED_VIEW there. Forget that and you'll receive a baffling:

1* Select  DBMS_Metadata.Get_DDL('MATERIALIZED VIEW','M_MV_CSTAR') From Dual
SQL> /
ERROR:
ORA-31600: invalid input value MATERIALIZED VIEW for parameter OBJECT_TYPE in
function GET_DDL
ORA-06512: at "SYS.DBMS_METADATA", line 2576
ORA-06512: at "SYS.DBMS_METADATA", line 2627
ORA-06512: at "SYS.DBMS_METADATA", line 4220
ORA-06512: at line 1

So have we successfully recovered from the error?

insert into m values (1);

commit;

select * from m;

select * from m_mv_cstar;

Hopefully that demonstrates that we have.

So, is there a downside to this? Well if you have never run a complete refresh on your materialized view, or created it with a "build immediate" clause, then you will not get the comfort of a STALENESS of "FRESH" and will instead be stuck with the disturbing "UNKNOWN". Relax — it's just that although you know that the materialized view is fresh, Oracle does not trust you. But then there are so many events that can break the "FRESH" staleness of your materialized view that you should treat FRESH MV's as a rare treat and not get used to them.

About these ads

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

  1. Nice feature. But there are a few issues to consider. The first one is; the master table must not be open to dml while the mview log is being dropped and recreated. Otherwise, we have to sync the prebuilt table with the master table to be able to see the modified rows in the snapshot.

    The second one is the Metalink Note:112171.1. It states:
    “If the snapshot and master sites are on different servers, offline
    instantiation using the ‘on prebuilt table’ option will not work.”

    I get this working on different servers also without any configuration. Maybe I am missing something or maybe the note is wrong.

  2. It sounds suspiciously like your use of MV’s is for replication, rather than for data warehousing where MV’s would be col-located with the master table(s), is that right?

  3. Yes, we use some mviews for replication. Mviews are accessed locally instead of accessing the master tables through db links.

  4. I guess different restrictions apply to your situation then — my use of MV’s is exclusively for data warehousing and when you throw something like a db link into the game the rules change quite a bit.

    I’d guess that network considerations aside the problems of rebuildiong the replication mv are not so severe as the aggregation mv’s that data warehouses use, but that’s not a lot of comfort when one of those ORA-12034′s shows up I expect.

  5. This just goes to show how important it is to create MVs on pre-built tables – the DW guide is a little too quiet on that. I learnt quickly that a full refresh of a 100Gb mv is bad news – Oracle tried to build all 116 weeks in a single hit – our DB team were a bit concerned about my need for the 400 Gb temporary tablespace.

  6. You could, depending if this was the mlog was dropped and recreated after a column added to the master just do a complete refresh of the mview, this will not bring in the new column but then the change request can be risen to add it in. At least the data will be as is and up to date. All this depending on the business need.

    Rgds,
    Luis

  7. Dear Sir,

    I’ve also faced the materialized view refreshing problem. Error message is
    “ORA-12034 Materialezed view log younger than last refresh.”

    I’ve created about 50 materialized views in my database which is connected through a radio link with it’s master database server. Materialized view logs are at master side. At the very beginning, I was using one refresh group Group-1 at my database side for refreshing all 50 Materialized views. Once I found that refresh group is not refreshing Materialized views. Then I discover that anout 17 Materialized views are not refreshing but rest of Materialized views are refreshed when attempted manually. Then I’ve altered those 17 Materialized views refresh mode to COMPLETE and then it works. Then I’ve created another Materialized view group Group-2 and assigned those 17 Materialized views under that group (Group-2) and againg altered those 17 Materialized views refresh mode to FAST.
    Now, problem is now and then I got my refresh group (Group-2) is not refreshing where Group-1 is refreshing smoothly. It is also noted that Group-1 having more bulk tables than Group-2. Again, if I alter Materialized views under Group-2 refreshing mode to COMPLTE and try manually to refresh it works. I dont understand why Group-1 is working smoothly where Group-2 is not and what is solution in this circumstances.
    Thank you so much for reading my so long mail with patience.
    Bye-
    Sattar

  8. Does Materialized View_Log get deleted on a refresh, if it is what is the complete procedure behind that

  9. @Yas #2
    Based on the Note: 112171.1. I tested exp/imp and created a MV on the prebuilt table, and did a fast refresh. However, any DML changes during exp/imp on the master table were not reflected in the MV. This is not what I understand about offline instantiation.

    Denis

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s