Getting ORA-23328: mview base table differs from master table

wietman
wietman used Ask the Experts™
on
This may seem obvious but I am only getting this when I try to use Dbms_Repcat.Create_Mview_Repobject() to add my Materialized view to a view group.
I can refresh the view manually, just fine.  This is on a remote database, not on the master site.

tried both of these:
execute Dbms_Repcat.Create_Mview_Repobject (gname => 'isismaster', sname => 'isis', oname => 'abmslocid', type => 'SNAPSHOT', comment => 'Created by Mark W',
            min_communication => TRUE, generate_80_compatible => FALSE);
 
execute Dbms_Repcat.Create_Mview_Repobject (gname => 'isismaster', sname => 'isis', oname => 'abmslocid', type => 'SNAPSHOT', comment => 'Created by Mark W',
            min_communication => TRUE);
 
 
Here's my repgroup def:
select * from dba_registered_mview_groups;
NAME	MVIEW_SITE	GROUP_COMMENT	VERSION	FNAME	OWNER
ISISMASTER	P5011307.WORLD		ORACLE 8		PUBLIC

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2009

Commented:
Is the name of the view at the master site the same (abmslocid)?

Did you create rep support at the master with DBMS_REPCAT.CREATE_MASTER_REPOBJECT() using the same name?

At the master I run

   DBMS_REPCAT.CREATE_MASTER_REPOBJECT()
   DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT()

Top Expert 2009

Commented:
I haven't actively created replication setups since 9i. I have my old scripts. One approach I recommend when deploying replication is to create a "TEST_REP" table. Basically it is just a piggy back table that goes along with the rest of the tables, once I deployed a replication cluster, I had a test script that would run on the master, insert a test row, and then the script ran on the peers to verify the record replicated. You can create any structure you want for the table.

See below for my master script sample.

-- this script must be run at the master site as either schema owner (with privs) or repadmin
 
BEGIN
   DBMS_REPCAT.CREATE_MASTER_REPGROUP(
     gname => '"MY_REPG"',
     qualifier => '',
     group_comment => '');
END;
/
BEGIN
   DBMS_REPCAT.CREATE_MASTER_REPOBJECT(
     gname => '"MY_REPG"',
     type => 'TABLE',
     oname => '"REP_TEST"',
     sname => '"SCOTT"',
     copy_rows => TRUE,
     use_existing_object => TRUE);
END;
/
 
BEGIN
   DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT(
     sname => '"SCOTT"',
     oname => '"REP_TEST"', 
     type => 'TABLE',
     min_communication => TRUE,
     generate_80_compatible => FALSE);
END;
/
 
 
create materialized view log on scott.REP_TEST tablespace data with primary key including new values
/

Open in new window

Author

Commented:
This was from a refresh.  So I will need to look at some of the views to make that determination. as far as this question  :  Did you create rep support at the master with DBMS_REPCAT.CREATE_MASTER_REPOBJECT() using the same name?

I have to leave now but will login and reserach some more later.

This is dev, so I can change whatever I want.  Right now I am picking on ABMSLOCID.
I will also shortly create my own mastergroup and everything....just taking your test_rep table a step further.  

Alos I was wondering if the "ORACLE 8" version of the mview group might be an issue since I'm in 10G now?

Thanks for your suggestions.  Will make use of them shortly.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Top Expert 2009
Commented:
You can probably remove that param. Notice I set it to false anyway, so no it would not matter.

Author

Commented:
Although not completely correct, you were definately in the right area.  It seems that updatable snapshots/Mviews are kind of a hybrid between.  Mviews and multmaster replication.  The solution to my problem was to simply create the repobjects on the Master site and then generate.  You did suggest that.  So I will give you all the points..plus yours was the only attempt.  Thanks for your suggestions. They steered me in the right direction.

Author

Commented:
The only think you left out was that I had to create the MLOG$ table on the master site.
This was because it was an updatable Materialized view and not mulitmaster replication.
Still a good answer. Thanks again

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial