Adding a new Master Node in an existing Multi Master Cluster

There are 2 ways to add a new master while the replication sites are suspended.
1. Adding Using ADD_MASTER_DATABASE Procedure
2. Adding with Offline Instantiation Using Export/Import

Using ADD_MASTER_DATABASE Procedure
1. Connect to the master definition site and if replication status is normal, change the status to quiesced. We need to switch off all applications before switching to Quiesced mode.

BEGIN
DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY (
gname => ‘XYZ);
END;

3. Shutdown the Master defn site or one of the master used for building new master site
4. Break the mirror and startup mount new database
5. Rename the database at init.ora and by re-creating new controlfile
6. Drop all database links at new database
7. Create fresh DB links from existing master sites to new one and vice versa

8. Use ADD_MASTER_DATABASE procedure to add the new master site.
BEGIN
DBMS_REPCAT.ADD_MASTER_DATABASE (
gname => ‘XYZ’,
master => ‘new_master.world’,
use_existing_objects => FALSE,
copy_rows => FLASE,
propagation_mode => ‘ASYNCHRONOUS’);
END;
9. Resume replication activity
BEGIN
DBMS_REPCAT.RESUME_MASTER_ACTIVITY (
gname => ‘hr_repg’);
END;

Advantage:
a. Can almost guarantee consistency of database since no updates are going through the system while new master being added
b. Less complicated than adding a new master group online

Disadvanage :
Full outage on replication cluster needed until entire work is done

Adding with Offline Instantiation Using Export/Import
1. Connect to the master definition site and if replication status is normal, change the status to quiesced. We need to switch off all applications before switching to Quiesced mode.

BEGIN
DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY (
gname => ‘XYZ);
END;

2. Begin offline instantiation procedure.
BEGIN
DBMS_OFFLINE_OG.BEGIN_INSTANTIATION (
gname => ‘XYZ’,
new_site => ‘new_site.world’);
END;

3. Shutdown the Master defn site or one of the master used for building new master site
4. Break the mirror and startup mount new database
5. Resume partial replication activity.
Because it may take some time to complete the offline instantiation process, we can resume replication activity for
the remaining master sites (excluding the new master site) by executing the RESUME_SUBSET_OF_MASTERS procedure in the DBMS_OFFLINE_OG

BEGIN
DBMS_OFFLINE_OG.RESUME_SUBSET_OF_MASTERS (
gname => ‘XYZ’,
new_site => ‘new_site.world’);
END;

6. Rename the database at init.ora and by re-creating new controlfile
7. Drop all database links at new database
8. Create fresh DB links from existing master sites to new one and vice versa
9. Complete instantiation process.
After completing the steps at the new master site, you are ready to complete the offline instantiation process.
Executing the END_INSTANTIATION procedure in the DBMS_OFFLINE_OG package completes the process and resumes normal replication activity at all master sites. Make sure you execute the following procedure at the master definition site.

BEGIN
DBMS_OFFLINE_OG.END_INSTANTIATION (
gname => ‘XYZ’,
new_site => ‘new_site.world’);
END;

Advantage : Less outage than first method is required

Both these method are documented with oracle export/import utility and since we are carrying disk backup to create new masters , it will need a good testing.