Oracle Advanced Replication | Remove or Delete Master Site from a Master Group

Posted By Sagar Patil

Executed At: Master Definition Site

Replication Status: Quiesced

Step 1 Connect to the master definition site as the replication administrator.

CONNECT repadmin/repadmin@repA.world

Make sure the site you want to drop is not Master definition site for any replication group.

SELECT GNAME, DBLINK, MASTERDEF
FROM DBA_REPSITES
WHERE MASTER = 'Y'
AND GNAME NOT IN (SELECT GNAME FROM DBA_REPSITES WHERE SNAPMASTER = 'Y')
ORDER BY GNAME,3;

GNAME                          DBLINK     MASTERDEF
O5_A-B_ALL                     repA       N
O5_A-B_ALL                     repAA      N
O5_A-B_ALL                     repAAA     N
O5_A-B_ALL                     repB       N
O5_A-B_ALL                     repBB      N
O5_A-B_ALL                     repBBB     Y

O5_BROKER_A-B                  repA       N
O5_BROKER_A-B                  repAA      N
O5_BROKER_A-B                  repAAA     Y

Following SQL could be used to confirm number of replication groups was part of repAA  Master

SELECT GNAME, DBLINK, MASTERDEF
FROM DBA_REPSITES
WHERE MASTER = 'Y' AND
DBLINK = ‘repAA’
AND GNAME NOT IN (SELECT GNAME FROM DBA_REPSITES WHERE SNAPMASTER = 'Y')
ORDER BY GNAME,3;

Step 2 If the replication status is normal for the master group, then locate MASTER defn Site & change the status to quiesced.

BEGIN
DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY (gname => ‘&1’);
END;

Enter value for 1: O5_A-B_ALL

Step 3 Remove the master site using the REMOVE_MASTER_DATABASES procedure.

BEGIN
DBMS_REPCAT.REMOVE_MASTER_DATABASES (
gname => ‘O5_A-B_ALL’,
master_list => 'repA.world');
END;

You should wait until the DBA_REPCATLOG view is empty.

Execute following SELECT statement in another SQL*Plus session to monitor the DBA_REPCATLOG view:

SELECT * FROM DBA_REPCATLOG WHERE GNAME = ‘HR_REPG’
BEGIN
DBMS_REPCAT.RESUME_MASTER_ACTIVITY (
gname => ‘O5_A-B_ALL’);
END;

Here we have dropped site repA successfully.

Imagine you now want to add same node at later stage. Under this case you need to remove all replication groups and a replication support created for those tables.

For all groups execute SQL> execute dbms_repcat.drop_master_repgroup(‘&1’);
Enter value for 1: O5_A-B_ALL

PL/SQL procedure successfully completed.

If you want to remove complete replication support then look at this post

Leave a Reply

You must be logged in to post a comment.

Top of Page

Top menu