This column group is now called MYGROUP as specified for the third parameter. If later we want to add another column, this is not difficult: just another call to a PL/SQL procedure. In this next example, we are adding a column called cash_back; it obviously must exist in our Sales table.

DBMS_REPCAT.ADD_GROUPED_COLUMN(‘SALES’, ‘SALES’, ‘MYGROUP’, ‘cash_back’);

We can also remove a name from a column group by using the following command to remove the column unit_price from the group:

DBMS_REPCAT.DROP_GROUPED_COLUMN(‘SALES’,‘SALES’,‘MYGROUP’, ‘unit_price’);

To drop this complete column group that we have created, we call another package:

DBMS_REPCAT.DROP_COLUMN_GROUP(‘SALES’,‘SALES’,‘MYGROUP’);

Defining a Conflict Resolution Method for a Column Group

Above we defined the three major conflicts of replication; Oracle has created three procedures to handle the three scenarios of update, uniqueness, and delete conflicts. The three procedures in the DBMS_REPCAT package to define conflict resolution routines are respectively named ADD_UPDATE_RESOLUTION, ADD_UNIQUE_RESOLUTION, and ADD_DELETE_RESOLUTION. They also exist as internal triggers with version 8 that give the user more security against unwanted changes to them.

For instance, say that we want to make sure that the latest prices and unit sales figures are entered into the system, we would use our column_group ‘MYGROUP’ in the following PL/SQL replication call:

DBMBS_REPCAT.ADD_UPDATE_RESOLUTION

(

    ‘SALES’, /* Table Owner */

    ‘SALES’, /* Table Name */

    ‘MYGROUP’, /* Column Group */

    1, /* Sequence */

LATEST_TIMESTAMP, /* Update Method */

    ‘purchase_timestamp’, /* parameter column name*/

NULL, /* priority group */

NULL, /* function name */

‘the latest purchase timestamp will decide the conflict’ /* comment */

);

In this case, we are instructing Oracle’s replication mechanism to resolve all update conflicts for the mentioned Column Group using the greatest timestamp on the column purchase_timestamp.

There are many other types of conflict resolution and the use of priority levels for a column_group. We will not go into all these variations. The basic principle of setting up these different conflict resolution strategies through calls to the package DBMS_REPCAT remains the same or through Oracle’s replication manager remains the same.

One major benefit of Oracle’s strategy in using PL/SQL as the foundation of replication is that this enables the user to create customized conflict resolution logic. Logic such as this can be in the form of a user-defined PL/SQL function that returns a Boolean value, TRUE or FALSE. Here a more complex strategy can be defined using the PL/SQL language. This might be useful because the default resolution strategies might not be adequate for your replicated environment.

Survivability

One of the attractions of replication is survivability. This refers to the capability of the distributed database to remain online with current data from even a site that has gone down.

For instance, if California fell into the sea and our fourth sales site, San Francisco, suddenly disappeared into the surf, we would still have all the data that had been refreshed from that site on any other master site. The only data that would be missing would be data that had been logged in San Francisco yet not transferred. Transactions that needed to send data from other sites to San Francisco could either be “rolled back” from the second phase of our two-phase commit, or these transactions could instead be accepted manually using the packaged PL/SQL or the Replication Manager.

Survivability can be easily obtained using Oracle’s Parallel Server. Never move into replication for this feature alone, unless your two database sites are physically separate. If you plan to create a highly available system, the Parallel Server is a better choice. If one node goes down on the parallel server, the server will fail-over without any lost transactions.

The drawback to the Parallel Server is that this configuration is characterized by many CPU’s and/or operating system instances sharing the same disks. If your disks crash, you can have all the survivalist procedures in place yet you will not have access to your data or your database. In a replicated system, the hardware is totally physically separated, connected only by a network, so a hard-disk crash on one system will still not bring the system down and might only affect a few transactions.

Figure 16.12 shows the differences in architecture between survivability in a replicated environment and survivability in a parallel environment.

Figure 16.12.  Because data and hardware are physically separated in remote replication, if one location fails, the system will survive.

If you do decide to use replication as a survivability tool, here are a few tips to remember when setting up your replication strategy:

•  The replicated sites must keep up with the master site or sites. If, for instance, our New York office does 10 times the volume and we can only refresh the other sites at night, a crash in the day will make inaccessible all the orders in New York for that day.

•  If a failure does occur, it is important not to panic because transactions at both ends of the replicated network, meaning transactions at the failed site and at the site that is still running, might appear to be lost because they either have failed in the second phase of the two-phase commit or they were lost at the primary site. They are not lost if we chose to ignore the second phase of our commit.

•  A strategy must be in place that database administrators can follow for the recovery of sites that have gone down. When the failed site returns, simply pushing across all the old transactions can cause conflicts because much of this shared data has changed or has been duplicated in the time after the fail-over.

•  Consider using a read-only fail-over site if you need survivability. In this case, bringing that site back online will not cause conflicts. And furthermore, the refresh rate for that site can be faster because the demands on it are lighter.

•  Consider a third-party tool for survivability instead of replication. Many vendors offer these tools along with their hardware. These tools mimic a parallel server. They are usually referred to as high availability options that can be purchased for specific hardware/OS configurations.