.comment-link {margin-left:.6em;}

Oracle Sponge -- Now Moved To Wordpress

Please use http://oraclesponge.wordpress.com

Thursday, December 08, 2005

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
select count(*) c_star
from m;

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

insert into m values (1);


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);


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
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> /
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);


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.


At 12:22 AM, Blogger yas said...

This comment has been removed by a blog administrator.

At 2:23 AM, Blogger yas said...

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.

At 6:41 AM, Blogger David Aldridge said...

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?

At 7:08 AM, Blogger yas said...

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

At 7:46 AM, Blogger David Aldridge said...

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.

At 1:38 PM, Blogger Pete_S said...

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.


Post a Comment

Links to this post:

Create a Link

<< Home