Hi
I am having some issues with Materialized views that I would like some help on if possible. It's Oralce 10g I'm having a problem with.
We have a database called DB1 which has several tables on it that replicate to a database called DB2 (using Materialized views). This is our live environment and it works fine, no problems here.
We have a separate dev environment for the DB1 database, and when restoring this we rename it to DB1_DEV. This is on a separate server, and it can not communicate with the live server. We have no dev environment for DB2.
The problem occurs on DB1_DEV when we try to modify the table structure of a table that is replicated to a MV on DB2 (which DB1_DEV cannot communicate with). We are able to make a DDL change to the table, but then after this when an insert into the table is attempted, it errors with:
ORA-23474: definition of "SCOTT"."TABLE1" has changed since generation of replication support
In the dev environment it is not necessary to have replication set up, and so I simply want to remove it. This is where I've been tearing my hair out. I have tried this command to suspend the replication (REP_1 is the name of the
replication group):
EXECUTE Dbms_Repcat.Suspend_Master_Activity(gname => 'REP_1');
This yeilds the following error:
Error at line 2
ORA-23312: not the masterdef according to DB1_DEV.WORLD
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_REPCAT_MAS", line 891
ORA-06512: at "SYS.DBMS_REPCAT_MAS", line 4194
ORA-06512: at "SYS.DBMS_REPCAT", line 946
ORA-06512: at line 1
I believe I'm getting this error because DB1_DEV is not the master definition site (this would be the live environment DB1). I can confirm the name of the master definition site with this query:
select gname, dblink, masterdef, master
from sys.dba_repsites;
This gives the following results:
GNAME DBLINK MASTERDEF MASTER
REP_1 DB1.WORLD Y Y
Because I get "not the masterdef according to DB1_DEV.WORLD", I reckon I need to relocate the masterdef to the dev environment DB1_DEV. I have tried to do this with the following query, logged in as sys/as sysdba:
BEGIN
DBMS_REPCAT.RELOCATE_MASTERDEF (
gname => 'REP_1',
old_masterdef => 'DB1.WORLD',
new_masterdef => 'DB1_DEV.WORLD',
notify_masters => TRUE,
include_old_masterdef => FALSE);
END;
Unfortunately this gives me another error:
ORA-23313: object group "PUBLIC"."REP_1" is not mastered at DB1_DEV.WORLD
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_REPCAT_UTL", line 2902
ORA-06512: at "SYS.DBMS_REPCAT_UTL4", line 892
ORA-06512: at "SYS.DBMS_REPCAT_UTL4", line 2862
ORA-06512: at "SYS.DBMS_REPCAT_MAS", line 3310
ORA-06512: at "SYS.DBMS_REPCAT", line 818
ORA-06512: at line 2
This is the error that puzzles me the most. I'm not sure what "PUBLIC" has to do with anything here. Is anybody aware of anything that I might be missing or doing wrong?
Thankyou for any help in advance
Regards