The proper procedure for fixing materialized view replication

materialized-vieworacleoracle-10g

Oracle 10 Standard:

I have a number of materialized views built on pre-built tables.
These are fast refreshed from a remote database over a link from one master db.
They've generally been working fine, but sometimes the view needs re-creating when a database server is bounced.

Comparing the remote (replicated) table with the master table now shows there are a few records missing in the replicated table. Re-creating the materialized view doesn't fix this (or manually refreshing it). There are no errors shown with the jobs that fast refresh these.

What is the procedure to fix this? Do I need to drop & regenerate the materialized view log on the master table?

Additional information:

Ok, dropping and recreating the materialized view log did not fix this.

I suppose I can just copy the records manually, but I thought there'd be a more generic way to fix. If complete refresh is the answer, I understand that it truncates the table first which is something I'd really like to avoid if possible.

As requested, here is the script that creates the materialized view:

CREATE MATERIALIZED VIEW AUTO_EVENT_DEFN 
ON PREBUILT TABLE
USING INDEX 
TABLESPACE DATA1 
PCTFREE 10 
INITRANS 2 
STORAGE 
( 
  INITIAL 81920 
  NEXT 1048576 
  MINEXTENTS 1 
  MAXEXTENTS UNLIMITED 
  BUFFER_POOL DEFAULT 
) 
REFRESH 
NEXT SYSDATE + 1/(24*60)   
FAST 
WITH PRIMARY KEY 
USING DEFAULT LOCAL ROLLBACK SEGMENT 
DISABLE QUERY REWRITE AS 
select   aed_rid, name, description, max_check_freq_sec, max_result_freq_sec, header, footer, subject, html_format, 
report_group, trigger_event, max_lines, html_base_size, ins_or_update, active, valid, defn_type, cust_req, last_modified, 
modified_by, sum_cols, report_delay_secs, process_queue, sql_string, report_security, results_type, multi_loc 
from auto_event_defn@kitlive;

Best Answer

And this is exactly what happens when you create materialized views on a prebuilt table that is already stale.

Recreating the mview the same way will not solve this, why would it? You still use the same incomplete prebuilt table.

Recreating mview logs has nothing to do with it. If you recreate mview logs now, that does not include data that was modified in the past.

Yes, the solution is complete refresh, and that does not necessarily truncate the table.

REFRESH Procedures

DBMS_MVIEW.REFRESH has a parameter called atomic_refresh which defaults to true.

A complete refresh with atomic_refresh set to true refreshes all mviews given as parameter in a single transaction. It is achieved by DELETE-ing the rows from the mview.

A complete refresh with atomic_refresh set to false refreshes all mviews given as parameter in seperate transactions. It is achieved by TRUNCATE-ing the mviews (which is much quicker than DELETE).

So specify atomic_refresh => true, and the mview will not be truncated.

Related Solutions

Materialized view taking too much time

Your materialized is not defined with a NEXT clause, therefore it will only refresh when you ask for it explicitely. You can use either DBMS_MVIEW.REFRESH directly or create a refresh group with DBMS_REFRESH.

In order to automate the refresh, you could program a job with DBMS_SCHEDULER or DBMS_JOB (dbms_job is deprecated in 11g).

You could also define your MV with a NEXT clause, for example this will refresh the MV every hour:

CREATE MATERIALIZED VIEW  C_INFO 
NOLOGGING
BUILD IMMEDIATE
refresh fast with rowid 
on demand
NEXT sysdate + 1/24

Way to alter a table definition (add columns) in Oracle and have it replicate to materialized views on remote databases

As long as you don't add data between the last refresh before the column add and after the column add, you should be fine. If you must allow for this then the easiest thing would be to do a complete refresh at the end of your plan. You shouldn't need to re-create the materialized view log.

Setup

--Create master table.
drop table t1;
create table t1 (id number(10) primary key, datetime date);
insert into t1 values (1, sysdate-1);
create materialized view log on t1 with primary key;

--Create materialized view table.
drop materialized view t2;
drop table t2;
create table t2 as (select * from t1);

--Create materialized view.
create materialized view t2 on prebuilt table with reduced precision
refresh fast on demand
as select id, datetime from t1;

--Show an empty materialized view.
select * from t2;

--Add data to t1.
insert into t1 values (2, sysdate);
commit;

--Refresh
execute dbms_snapshot.refresh('T2','f');

--Show changed data.
select * from t2;

Planned Column Add

--Add Column.
alter table t1 add n varchar2(1);
alter table t2 add n varchar2(1);

--Re-create the materialized view.
drop materialized view t2 preserve table;
--***T1 updates between last refresh and the following statement will be lost.
create materialized view t2 on prebuilt table with reduced precision 
   refresh fast on demand
   as select id, datetime, n from t1;

--Add data.
insert into t1 values (3,sysdate+1,'a');
commit;

--Refresh.
execute dbms_snapshot.refresh('T2','f');

--Show changed data.
select * from t1;
select * from t2;

Best Answer

Related Solutions

Materialized view taking too much time

Way to alter a table definition (add columns) in Oracle and have it replicate to materialized views on remote databases

Related Question