Problem with Asynchronous Single Master Multitier Updateable Materialized View Replication

Hi Oracle gurus,

  I have a serious problem, I didn't find out the solution. Please help me if you can!
The problem is complex:

Server: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production
Platform: Debian Linux 3.0

I would like to use Asynchronous Single Master Multitier Updateable Materialized View Replication.
I made the following steps:
1. I created three instances (called ORSZ, IG1 and KI11).In both databases 'global_names' initialization parameter is TRUE, 'db_domain' = 'xxx'. I want ORSZ to be the master site, IG1 to be the master-materialized view site, KI11 to be a materialized view site, the hierarchy is: ORSZ - IG1 - KI11 (from top to bottom). 2. I made subsequent steps in Oracle Enterprise Manager Console
(version

9.2.0.1.0 Production), it runs at a different workstation on Windows XP Professional SP1.
3. I made a new schema in ORSZ (called HTTR) and created a new table
(called TEST) into it. This table has primary key. After it I made
materialized log on this table.
4. I setup replication on master site of ORSZ (from Distributed / Advanced Replication / Materialized View Replication / Master Site). Replication administrator and propagator is REPADMIN. 5. I made a new public master group in ORSZ (called GRP_1) and put HTTR.TEST table in this group. Then I generated the replication support for HTTR.TEST.
6. I setup replication on Materialized View Site of IG1 (from Distributed / Advanced Replication / Materialized View Replication / Materialized View Site). It created a new user called MVADMIN in IG1, a new user MVADMIN_IG1_XXX at ORSZ, database links to ORSZ, etc. 7. I made a new public Materialized View Group using wizard. During this
process I made a new Updateable Materialized View with fast refresh called GRP_1. Its parent is GRP_1 at ORSZ. I generated replication support for this Materialized View, this works. I can create new records in both side and after fast refreshing the materialized view prior changes move between the databases.
8. I setup replication on master site of IG1 (from Distributed / Advanced Replication / Materialized View Replication / Master Site). Replication administrator is REPADMIN, propagator is MVADMIN. Until this point, everything is OK.
9. First problem: I'm unable to create Materialized View Log on HTTR.TEST in IG1, because OEM doesn't show this table. Following SQL is issued by OEM when I select the HTTR schema in the wizard (Distributed / Advanced Replication / Materialized View Replication / Master Site / Materialized View Logs / Create):

select table_name from sys.dba_tables where owner = 'HTTR' minus
(select distinct master from sys.dba_snapshot_logs where log_owner =
'HTTR')
minus
(select log_table from sys.dba_snapshot_logs where log_owner = 'HTTR')
minus
(select mview_name from sys.dba_mviews where owner = 'HTTR')

I don't understand the last subselect. Why am I unable to create materialized view logs on tables that is a table of materialized view? By the way, I can issue the following from SQL Plus:

CREATE MATERIALIZED VIEW LOG ON TEST WITH PRIMARY KEY

1. After this I created a new empty public master group in IG1
   (called
   GRP_1_1). This worked. After this I put HTTR.TEST in this group. It worked too. But when I want to generate replication support for HTTR.TEST, I get an error (ORA-23399: generation of replication support for "HTTR"."TEST" is not complete). The issued statement by OEM was: dbms_repcat.generate_replication_support(sname => 'HTTR', oname => 'TEST', type => 'TABLE', min_communication => TRUE, generate_80_compatible => FALSE); After retry generation, I get another error (ORA-23308: HTTR.TEST does not exist or invalid). The issued statement was the same.
2. This is the problem. The question is: Why am I unable to generate replication for a table resides in a master group when this table belongs to a materialized view which resides in a materialized view group at the same site? If I misunderstood Multitier Materialized View Replication, how could I use this feature correctly?

Thank you for your help in advance,
Bert Gombos (bert_at_REMOVETHISmensa.hu) Received on Thu Feb 19 2004 - 14:47:56 CST