Setup Oracle Replication (Part 4: 3 Tiers Replication)

3 tiers replication means: after you setup a master site server and a MV site server, you then setup another MV site server that replicate from the middle tier MV site server. Reasons why we want to do this include:

1. If you have several servers need to be setup as MV site servers, replicate from middle tier MV site server might able to lower the resource consumption on the master site server.
2. Because setup a MV site server need the master site server to be static (no data changes during the setup), it might be unpractical for stopping the users from using the master site server. Make use a middle tier MV site server might be a solution for such situation, just stop the refresh job on it.

Steps to setup a 3 tiers replication:

1. Setup the Master Site Server.
2. Setup the Middle Tier MV Site Server.
3. At the Middle Tier MV Site Server, create Materialized View Log on necessary tables:
   create materialized view log on [table name];
4. Setup the Third Tier MV Site Server, and treat the Middle Tier MV Site Server as the Master Site Server.