Oracle Advanced Replication | Relocating Master Definition Site

I have a live 4 node 4 way cluster between repA,repAA,repB & repBB instances. The node repA is Master which I want to move to repAA.

Database Release : 9.2.0.8 On HP UNIX

All Master Sites Are Available

DBMS_REPCAT.RELOCATE_MASTERDEF  was running for more than 2 hours

Observation :

• Locate which DBMS jobs are running in system
• “select /*+ RULE */ what from dba_jobs where job in( select /*+ RULE */  job  from dba_jobs_running)”
• dbms_repcat.do_deferred_repcat_admin(‘”O5_A-B”‘, FALSE);
• Observed SQLPLUS/TOAD session showing following SQL waiting for ENQUEUE Latch.SELECT  MASTER  FROM SYSTEM.repcat$_repcat WHERE sname = :b1 AND gowner = :b2  FOR UPDATEI wasted half a day to notice that  “Not all dbms_repcat procedures have an auto-commit“ as described at Note:1026571.6A commit after DBMS_REPCAT.RELOCATE_MASTERDEF solved my problem.repA>SELECT gname, substr(dblink,1,20),masterdef “Is MasterDefinition Site”
  FROM dba_repsites WHERE
  gname in(‘O1_A-B_ALL’,’O1_ERROR_A-B’)
  order by 3 desc;

  GNAME SUBSTR(DBLINK,1,20) I
  —————————— ——————– –
  O1_ERROR_A-B REPA.US.ORACLE.COM Y
  O1_A-B_ALL REPA.US.ORACLE.COM Y
  O1_ERROR_A-B REPB.US.ORACLE.COM N
  O1_A-B_ALL REPB.US.ORACLE.COM N
  O1_A-B_ALL REPBB.US.ORACLE.COM N
  O1_A-B_ALL REPAA.US.ORACLE.COM N
  O1_ERROR_A-B REPAA.US.ORACLE.COM N
  O1_ERROR_A-B REPBB.US.ORACLE.COM N

  repA>show user
  USER is “REPADMIN”
  repA>BEGIN
  DBMS_REPCAT.RELOCATE_MASTERDEF (
  gname => ‘O1_ERROR_A-B’,
  old_masterdef => ‘repA’,
  new_masterdef => ‘repAA’,
  notify_masters => TRUE,
  include_old_masterdef => TRUE);
  END;
  /

  COMMIT;

  /

  PL/SQL procedure successfully completed.

  repA>SELECT gname, substr(dblink,1,20),masterdef “Is MasterDefinition Site”
  FROM dba_repsites WHERE
  gname in(‘O1_A-B_ALL’,’O1_ERROR_A-B’)
  order by 3 desc;

  GNAME SUBSTR(DBLINK,1,20) I
  —————————— ——————– –
  O1_ERROR_A-B REPAA.US.ORACLE.COM Y
  O1_A-B_ALL REPA.US.ORACLE.COM Y
  O1_ERROR_A-B REPA.US.ORACLE.COM N
  O1_A-B_ALL REPB.US.ORACLE.COM N
  O1_A-B_ALL REPBB.US.ORACLE.COM N
  O1_A-B_ALL REPAA.US.ORACLE.COM N
  O1_ERROR_A-B REPBB.US.ORACLE.COM N
  O1_ERROR_A-B REPB.US.ORACLE.COM N

  8 rows selected.

  Relocate the master definition site Master Definition site Available

  BEGIN
  DBMS_REPCAT.RELOCATE_MASTERDEF (
  gname => ‘hr_repg’,
  old_masterdef => ‘orc1.world’,
  new_masterdef => ‘orc2.world’,
  notify_masters = > TRUE,
  include_old_masterdef => TRUE);
  END;

  Relocate the master definition site The Old Master Definition Site Not Available

  BEGIN
  DBMS_REPCAT.RELOCATE_MASTERDEF (
  gname => ‘hr_repg’,
  old_masterdef => ‘orc1.world’,
  new_masterdef => ‘orc2.world’,
  notify_masters = > TRUE,
  include_old_masterdef => FALSE);
  END;

  Reference :

  DBMS_REPCAT.RELOCATE_MASTERDEF (
  gname IN VARCHAR2,
  old_masterdef IN VARCHAR2,
  new_masterdef IN VARCHAR2,
  notify_masters IN BOOLEAN : = TRUE,
  include_old_masterdef IN BOOLEAN : = TRUE)

  gname The name of the object group whose master definition your want to relocate.

  old_masterdef The fully qualified database name of the current master definition site.

  new_masterdef The fully qualified database name of the existing master site that you want to make the new master definition site.

  notify_masters If NOTIFY_MASTERS is TRUE, the procedure synchronously multicasts the change to all masters (including OLD_MASTERDEF only if INCLUDE_OLD_MASTERDEF is TRUE). If any master does not make the change, rollback the changes at all masters.

  include_old_masterdef If NOTIFY_MASTERS is TRUE and INCLUDE_OLD_MASTERDEF is also TRUE, the old master definition site is also notified of the change.