Sample Application

Now suppose that you decide to replicate these tables to multiple sites. Because you have chosen to asynchronously propagate your changes between sites, you decide to avoid possible update conflicts by partitioning the ownership of the data based on workflow.

To partition ownership, you add a STATUS column to the ORDERS table. The status of an order can be: S (shippable), B (billable), O (outstanding bill), or C (complete).

This example has two order entry sites, so you must take steps to ensure either that conflicts do not occur, or that they can be resolved. In this example, all orders have a unique order ID.

The sequence used to generate this ID is partitioned between the master definition site and this snapshot site. Even if the same customer places one order at the master definition site and another at the snapshot site, each order will have a unique ID and will be treated separately. In this example, only one of the sites is allowed to update the CUSTOMER table, therefore no conflict resolution is required for this table.

This example assumes that the appropriate database links have been created at each site, and that the appropriate privileges have been granted to the replication administrator at each site.

To create your replicated environment, perform the following steps:

1. Start by creating your master definition site, which consists of the following steps:

• Create the replicated object group by issuing the following command:

 	DBMS_REPCAT.CREATE_MASTER_REPGROUP('inventory')

• Then select the objects that you want to replicate. For each table, you should also provide a conflict resolution routine if necessary, and then generate the desired form of replication support.

 -- replicate customer table

DBMS_REPCAT.CREATE_MASTER_REPOBJECT('acct','customer',
                                    'table','inventory',); 

-- insert appropriate calls to conflict resolution methods

DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT('acct',
                                         'customer','table');

 -- replicate orders table

DBMS_REPCAT.CREATE_MASTER_REPOBJECT('acct','orders',
                                    'table','inventory');

DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT('acct',
                                         'orders','table');

 -- replicate order_line table

DBMS_REPCAT.CREATE_MASTER_REPOBJECT('acct','order_line',
                                    'table','inventory');

DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT('acct',
                                         'order_line','table');

 -- replicate item table

DBMS_REPCAT.CREATE_MASTER_REPOBJECT('acct',
                                    'item','table','inventory');

DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT('acct','item',
                                         'table');

 -- replicate stock table

DBMS_REPCAT.CREATE_MASTER_REPOBJECT('acct','stock','table',
                                    'inventory');

DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT('acct','stock',
                                         'table');

2. Now you can create another master site with this command:

 DBMS_REPCAT.ADD_MASTER_DATABASE('inventory', 'dbs2');

Oracle creates a replica of the master definition site at the database associated with the DBS2 link. This master site will be used by the shipping department only. Because no orders will be placed from this site, you do not have to worry about conflicts between this site and the master definition site, which is used for order entry.

3. After confirming that there are no errors in the replication log, you can now begin normal replication activity at these sites by issuing the following command:

 DBMS_REPCAT.RESUME_MASTER_ACTIVITY('inventory');

4. You are now ready to begin creating your snapshot sites. This example assumes that you have already used the CREATE SNAPSHOT LOG command to create logs on your master tables. This allows you to perform a fast refresh on the snapshots you need to create. To create your billing server snapshot replication site, you would need to issue the following commands at the new snapshot site:

• First, you need to indicate what object group you are replicating, and which master site you will be refreshing your snapshot site from.

 DBMS_REPCAT.CREATE_SNAPSHOT_REPGROUP('inventory', 'dbs2',
                                      'asynchronous')

• Now you can populate your snapshot replication object group with a subset of the tables at its associated master site. Remember to use the FOR UPDATE clause of the CREATE SNAPSHOT command when creating updatable snapshots. Oracle automatically generates the appropriate replication support for these updatable snapshots based on the type of replication support that you generated for their associated master tables.

 -- create read-only snapshot of customer table

DBMS_REPCAT.CREATE_SNAPSHOT_REPOBJECT(
      'acct', 'customer', 'snapshot', 
      'CREATE SNAPSHOT customer' || 
      'AS SELECT * FROM customer@dbs2','','inventory');

 -- create updatable snapshot of orders table

DBMS_REPCAT.CREATE_SNAPSHOT_REPOBJECT(
      'acct','orders','snapshot', 
      'CREATE SNAPSHOT orders FOR UPDATE AS' || 
      'SELECT * FROM orders@dbs2 WHERE status = ''B''',
      '','inventory');

 -- create updatable snapshot of order_line table

DBMS_REPCAT.CREATE_SNAPSHOT_REPOBJECT(
      'acct', 'order_line', 'snapshot', 
      'CREATE SNAPSHOT order_line FOR UPDATE AS' || 
      'SELECT * FROM order_line@dbs2','','inventory');

 -- create updatable snapshot of item table

DBMS_REPCAT.CREATE_SNAPSHOT_REPOBJECT(
      'acct', 'item', 'snapshot', 
      'CREATE SNAPSHOT item FOR UPDATE AS' || 
      'SELECT * FROM item@dbs2','','inventory')