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
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
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:
gname => 'REP_1',
old_masterdef => 'DB1.WORLD',
new_masterdef => 'DB1_DEV.WORLD',
notify_masters => TRUE,
include_old_masterdef => FALSE);
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
I think that correct step order is following:
Thanks for the response spajdy!
I followed the steps up until 3 where I am unsure. Step 1 has already been done, and I have stopped all the jobs as suggested (step 2). In terms of removing replication support for all tables, do you have the command to do this? Every attempt I have made so far has ended in ORA-23312: not the masterdef according to DB1_DEV.WORLD (or something similar).
Thank you very much for your help so far
I miss one important point.
Don't rename DB.
In other words: Create copy of your production DB and left DB same name as it have in production environment.
I you receive an error
not the masterdef according to DB1_DEV.WORLD
try rename dev DB back to production name and then continue in step 3.
The command to drop is
begin DBMS_REPCAT.DROP_MASTER_REPGROUP( gname => '...', drop_contents => TRUE); end; /