3 Replies Latest reply on Aug 5, 2013 6:36 PM by spajdy

    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


        • 1. Re: Removing Replication / Materialized views in a copy of a database

          I think that correct step order is following:

          1. create copy of your DB1 on dev environment
          2. stop all jobs (set job_queue_processe=0 ) if you are using scheduler and not on 11R2 and highger don't start any service for job classes
          3. remove replication support for all tables (stop all master groups, remove all object from replication, drop master groups)
          4. stop DB
          5. rename DB
          • 2. Re: Removing Replication / Materialized views in a copy of a database

            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

            • 3. Re: Removing Replication / Materialized views in a copy of a database

              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

              gname => '...',
                drop_contents => TRUE);