We would then call the same package as before but a different procedure to give this new user full replication power, thus making the user a global replication administrator:

dbms_repcat_admin.grant_admin_any_schema('REP_GOD’);

At this point, we have created a replication administrator with the username 'rep_god’. But before we plunge ahead and set up replication, we must set up database links for the two offices in Hong Kong and London; thus we need N - 1 database links for a replication of N nodes.

At each of the two physically remote sites, we create a surrogate replication administrator. This is a user at the remote site that initiates all the low-level replication processing for the SYS account that is necessary. We now create a user called surrogate_hong_kong for our Hong Kong office:

Create User surrogate_hong_kong Identified by <password>

Now we grant this user surrogate status within our replication sub-system:

DBMS_REPCAT_AUTH.GRANT_SURROGATE_REPCAT('surrogate_hong_kong’);

Back at the central node in New York, we create a database link for each surrogate:

create database link new_york.sales.com CONNECT to surrogate

IDENTIFIED BY <password> USING hongkong.sales_com;

At this point, we will replicate our schema. For this example, the schema is simply the owner of the sales data that we are replicating: SALES. We need to register the SALES database as a candidate for replication; to do this we call on the DBMS_REPCAT package again:

DBMS.REPCAT.CREATE_MASTER_REPGROUP('SALES’,’new_york.sales.com’,

'comment’, 'asynchronous’));

After we have made possible the replication of all database objects owned by SALES, we now specify which objects we intend to replicate. In this case, we need to replicate the central sale table. Here’s the following PL/SQL package call needed with example parameters followed by comments defining them:

DBMS_REPCATE.CREATE_MASTER_REPOBJECT(

'SALES’,

/* Owner of object to be replicated */

'SALES’,

/* Object name replicated */

'TABLE’,

/* Type of Object */

'TRUE’,

/* Use Existing Object */

'’,

/* DDL Text if object does not already exist at master site */

'HI THERE’,

/* Comment */

FALSE,

/*  Retry, yes or no? */



TRUE,

/* copy rows, yes or no? */

'SALES’,

/* Object Gropu */

);

At a low level, Oracle replication consists of a series of PL/SQL packages, procedures, and triggers that are either PL/SQL or new internal triggers that are simply C language functions. These objects are generated as you define your replication. With Oracle8, we can use a front-end tool that executes many of these calls automatically. The example of the Sales table being replicated in Figure 16.8 shows that some sort of trigger needs to fire and propagate our replication when a user changes a row in the table.

Figure 16.8.  Just as with standard development, replication uses triggers to propagate changes with one table across a set of other tables; the difference is that the replication triggers fire across a distributed database network to tables residing on remote nodes.

The actual command that initiates the creation of the proper trigger can be accessed as a PL/SQL package call. Now create these low-level objects for our Sales table using this call:

DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT('SALES’,’SALES’,’TABLE, NULL,

NULL, TRUE, 'SALES’, TRUE);

This procedure is given in the general form: The last variable, a Boolean, is set to true to indicate that we are in a distributed environment.

Now say that our corporation opens a West Coast operation in San Francisco (see Figure 16.9). We now want to integrate a new sales database application to our existing replication schema and create a four-node replicated network.

Figure 16.9.  Oracle replication enables us to enter new nodes after we have an operating replicated environment.

---

Note:  
Redundant communication services allow for any city’s communication failure without affecting other sites.

---

To do this, we must first create a replication user on the local site and a proper database link for the site. Then we call the following procedure from our central node:

DBMS_REPCAT.ADD_MASTER_DATABASE(

'SALES’,                              /* Schema */

'sales@sales.sanfrancisco.com’,       /* database link */

TRUE,                                 /* Use existing objects*/

TRUE,                                 /* copy row */

'Hi there’,                           /* Comment */

);

Managing Your Replication and the Mystery of the Two-Phase Commit

After you create your replication components based on your replication strategy, you must now make another decision regarding the day-to-day operation of the replicated databases. This regards either manual or automatic methods to push synchronized data between sites.

Automatic—To accomplish automatic refreshes of your different databases, use the SCHEDULE_EXECUTION procedure.
An example of this is to set up a refresh every hour. Do this by calling SCHEDULE_EXECUTION:

DBMS_DEFER_SYS.SCHEDULE_EXECUTION(

'sales.sanfrancisco.com’,   /* the node */

SYSDATE + 1/24,             /* the interval */

SYSDATE                     /* start date */

);

Manual—Simply call the EXECUTE procedure for each manual replication task that is needed. Here is a sample call to this procedure:

DBMS_DEFER_SYS.EXECUTE('sales.sanfrancisco.com’);

This command synchronizes sales in San Francisco with the other three nodes, whenever it is called.

---

Note:  
If you use the Replication Manager, You do not need to memorize any of these replication calls, but instead can use a GUI interface to execute them.

---

The place where Oracle stores transaction information as users enter sales at each of the four sites is a deferred transaction queue. With this queue, we accomplish the magical task of the two-phase commit.

The two-phase commit is a trendy name, but replication is more similar to a “commit and flush” because after your data is committed to your database, the transaction is not final until the remote site successfully accepts the transaction. At this point, the deferred transaction queue is flushed, as in Figure 16.10.

Figure 16.10.  The deferred transaction queue makes sure that our transaction is not complete until we receive confirmation from the remote site(s).