3 Replies Latest reply: Aug 5, 2013 1:36 PM by spajdy RSS

    Removing Replication / Materialized views in a copy of a database




      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:



      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