
Snapshot Site Replication
This chapter explains how to use create and maintain snapshot sites. The topics discussed include the following:
Note that most of the activities described in this chapter can be accomplished much more easily by using Oracle's Replication Manager, a GUI interface for replication. See the documentation for Oracle Replication Manager for more information.
Replication Support at Snapshot Sites
A snapshot site can contain a subset of the information stored at its master site or all the objects at a master site. A snapshot site can contain the following objects:
- read-only snapshots of master tables in the replicated object group
- updatable snapshots of master tables in the replicated object group
- replicated triggers used to queue deferred remote procedure calls from the snapshot site to its associated master site
- objects that are replicated only in the sense that their SQL definitions are replicated (procedures, packages, functions, synonyms, and views)
Replication Support for Snapshots
Read-only snapshots require no special support from the symmetric replication facility. Read-only snapshots are created and refreshed in the same manner as described in Chapter 3.
Attention: This chapter describes only the differences between updatable and read-only snapshots. If you are not already familiar with snapshots, snapshot refresh groups, and the refresh mechanism, you must read Chapter 3 before proceeding.
Similar to read-only snapshots, an updatable snapshot is a full copy of a table or a subset of a table that reflects a recent state of the master table. Unlike read-only snapshots, updatable snapshots must be derived from a single master table; that is, they must be simple snapshots.
Also, like read-only snapshots, updatable snapshots must be periodically refreshed to apply the changes made to the master table. However, unlike read-only snapshots, when you refresh an updatable snapshot, the changes to the snapshot must also be taken into account.
When you create your updatable snapshots using the procedures provided with the symmetric replication facility, the changes made to the updatable snapshot are either synchronously or asynchronously applied at the master site, in much the same manner as changes are propagated between two master sites.
Changes from the master site, however, are asynchronously propagated to the snapshot site in the form of a refresh, in much the same manner as read-only snapshots are refreshed from their masters. The refresh mechanism for updatable snapshots is described in detail
.
Updatable Snapshot Architecture
In addition to the objects created for read-only snapshots that are described
, when you create an updatable snapshot, two additional objects are created at the snapshot site:
- Oracle creates a table, named USLOG$_snapshot_name, to store the ROWID and timestamp of rows updated in the snapshot. The timestamp column is not updated until the log is first used by a snapshot refresh.
When you create the snapshot as a replicated object, Oracle creates an additional trigger and associated package on the snapshot base table to call the generated procedures at the master site to apply the changes.
If you are propagating your changes synchronously, the trigger package directly executes the generated procedures at the master site, if you are using asynchronous propagation, the trigger package inserts the necessary deferred transactions into the deferred transaction queue at the snapshot site.
Of course, the primary difference between the architecture of read-only and updatable snapshots is that for read-only snapshots, Oracle creates a read-only view of the underlying base table, while for updatable snapshots, this view is writable.
If your CREATE SNAPSHOT statement includes a restriction in the where clause, this restriction is reflected in the values that are displayed when you create or refresh the snapshot. For example, if you issue the following statement:
CREATE SNAPSHOT emp FOR UPDATE
AS SELECT * FROM scott.emp@sales.ny.com
WHERE empno > 500;
your EMP snapshot will only display employees with employee numbers greater than 500. This behavior is identical to read-only snapshots. However, you are not restricted from updating this number, or inserting an employee with an employee number less than 500.
These values will remain in the table, and can be updated or deleted, until the next time that you refresh the snapshot. The refresh will remove any remaining rows with an employee number less than 500 for the snapshot. For more information on how these changes are propagated between the snapshot and its associated master table, see page 5 - 12.
If you want to restrict the data in the updatable snapshot to always satisfy the requirements specified in the WHERE clause of the original CREATE statement, you should define your own view on the snapshot base table, using a CHECK constraint.
Replication Support for Non-snapshot Objects
Do not alter non-snapshot objects at the snapshot site. These objects must be altered only at the master definition site by using the DBMS_REPCAT package.
If your snapshot site contains any non-snapshot replicated objects that were altered using the DBMS_REPCAT package at its associated master site (these would typically result from DDL changes propagated from the master definition site), these changes can be applied at the snapshot site the next time that you refresh the replicated schema with REFRESH_OTHER_OBJECTS set to TRUE.
Before Creating a Snapshot Site
Before creating a snapshot site, you must already have created at least one master site, as described in Chapter 4. If you have multiple master sites in your replicated environment, you should select which master site your snapshot site will be created from.
This master site will also be used to refresh any read-only or updatable snapshots at your new snapshot site. You can change a snapshot site's master site if required.
Creating Database Links for Snapshot Sites
A snapshot site for a replicated object group must have a database link to its associated master site that contains a username (for example, SCOTT), a password (for example, TIGER), and the fully qualified database name of the master. The appropriate database links must be created before you call CREATE_SNAPSHOT_REPGROUP or SWITCH_SNAPSHOT_MASTER.
Granting the Necessary Privileges
The user at the master site that was specified in the CONNECT TO clause of the database link from the snapshot site must either be granted replication administrator privileges or be the owner of the schemas in the replicated object group in order for deferred transactions to be propagated to the master site. Additionally, for the refresh of any updatable snapshots to succeed, this user must either own the snapshots or be SYS, or have the ALTER ANY SNAPSHOT privilege.
Creating a Snapshot Replication Site
This section outlines the procedure that you must perform to create a snapshot site containing updatable snapshots. Each of these steps is described in more detail later in this chapter.
Optionally, you may perform the following step at the master site:
- Create a snapshot log for the master table using the CREATE SNAPSHOT LOG command. This allows you to perform fast refreshes of your snapshots. Refer to page 3 - 8 for information on creating and using snapshot logs.
Perform the following steps at each snapshot site:
- Create the necessary snapshots using the FOR UPDATE clause of the CREATE SNAPSHOT command, as described
.
- Create the necessary links between the snapshot site and its master site, as described
.
- Create the replicated object group at the snapshot site by calling CREATE_SNAPSHOT_REPGROUP.
- Create the replicated objects, including updatable snapshots, at the snapshot site by calling CREATE_SNAPSHOT_REPOBJECT.
This procedure generates the necessary replication support for each object.
- Ensure that multiple snapshots will be refreshed to a single point in time by using the DBMS_REFRESH.MAKE procedure to maintain transactional consistency and preserve master detail relationships.
Creating the Replicated Object Group
Create a new empty snapshot replicated object group in your local database by calling the CREATE_SNAPSHOT_REPGROUP procedure in the DBMS_REPCAT package, as shown in the following example:
DBMS_REPCAT.CREATE_SNAPSHOT_REPGROUP(
gname => 'accts',
master => 'acct_hq.hq.com',
comment => 'created on ...',
propagation_mode => 'asynchronous');
Attention: Notice that the replicated object group name must match the master group name.
In this example, the ACCTS object group is created in the current database. When you add replicated objects to this object group by calling CREATE_SNAPSHOT_REPOBJECT, they will be refreshed using the ACCT_HQ database as their master. Changes from the snapshot site will be asynchronously propagated to its associated master site as described
.
Because each snapshot site may contain a different subset of the objects at its associated master site, there is no snapshot equivalent to the DBMS_REPCAT.ADD_MASTER_DATABASE procedure. If you will be creating multiple snapshot sites with similar members, you may want to create them using a script, which can be modified and re-executed at each site.
Additional Information: The parameters for the CREATE_SNAPSHOT_REPGROUP procedure are described in Table 12 - 105, and the procedures are listed in Table 12 - 106.
Creating a Replicated Object
Add a replicated object to your snapshot site by calling the procedure CREATE_SNAPSHOT_REPOBJECT in the DBMS_REPCAT package, as shown in the following example:
DBMS_REPCAT.CREATE_SNAPSHOT_REPOBJECT(
sname => 'accts_rec',
oname => 'emp',
type => 'snapshot',
ddl_text => 'CREATE SNAPSHOT accts_rec.emp FOR UPDATE AS
SELECT * FROM emp@acct_hq.hq.com WHERE
deptno > 500',
comment => 'created on ...',
gname => 'acct')
gen_obj_owner ==> 'REPADMIN';
In this example, Oracle creates a snapshot at the snapshot site of the EMP table located at the master site. This snapshot contains a subset of the rows in the EMP table; that is, it only contains the rows for those employees whose department numbers are larger than 500. For example, this might be all sales staff in the western region.
Attention: Notice that the object name and updatable snapshot name must be identical. The master table must be a replicated object registered at the master site.
In this example, the SQL statement necessary to create the snapshot is passed as an argument to this procedure. Alternatively, you could have created the snapshot before calling this procedure and simply have omitted the DDL.
Warning: If you create an updatable snapshot using the FOR UPDATE clause of the CREATE SNAPSHOT command, but do not create the snapshot as a replicated object by calling CREATE_SNAPSHOT_REPOBJECT, any changes that you make to the updatable snapshot will be lost when you refresh the snapshot.
In addition to creating the snapshot as a replicated object at the snapshot site, Oracle also adds the object name and type to the RepObject view at the local site. This view is used to determine which objects need to push their changes to the master site.
Because the replicated object in this example is of type SNAPSHOT and its associated master table uses row-level replication, Oracle installs the appropriate replication support at the snapshot site. For snapshots of tables using procedural replication, be sure to replicate the associated procedure or package at the snapshot site.
Attention: Although not required, you will typically want all snapshots at a given snapshot site to be in the same snapshot refresh group. To ensure that snapshots in the same refresh group are refreshed consistently, their associated master tables must be located at the same master site.
Additional Information: The parameters for the CREATE_SNAPSHOT_REPOBJECT procedure are shown in Table 12 - 107, and the exceptions are listed in Table 12 - 108.
Creating Non-Snapshot Objects
For objects other than snapshots, you must not supply the DDL. Oracle copies the object from the master site that you designated when you created the snapshot site. If the object already exists at the snapshot site, Oracle compares the two objects and raises a duplicateobject exception if they do not match.
Creating Snapshots
For snapshots, you can either precreate the snapshot, or supply the DDL as part of the CREATE_SNAPSHOT_REPOBJECT call. For information on creating read-only snapshots, refer to Chapter 3.
Updatable snapshots are created and deleted in the same manner as read-only snapshots. See Chapter 3. To create an updatable snapshot, simply add the FOR UPDATE clause to the CREATE SNAPSHOT statement as shown in the following example:
CREATE SNAPSHOT emp FOR UPDATE
AS SELECT * FROM scott.emp@sales.ny.com;
Restrictions on Updatable Snapshots
Declarative constraints on snapshots and snapshot logs are not supported.
Snapshots of LONG columns are not supported.
Updatable snapshots must be simple snapshots; that is each row in the snapshot is based on a single row in a single remote table. A simple snapshot's defining query has no distinct or aggregate functions, GROUP BY or CONNECT BY clauses, subqueries, joins, or set operations.
Symmetric replication does not support replication of a subset of columns. All CREATE statements must be of the form
CREATE SNAPSHOT . . . FOR UPDATE
AS SELECT * FROM . . .;
The following SQL statement is not supported:
CREATE SNAPSHOT . . . FOR UPDATE
AS SELECT empno, ename FROM . . .;
Naming Updatable Snapshots
Naming conventions for updatable snapshots are the same as for read-only snapshots. See page page 3 - 4.
Privileges Required to Create Updatable Snapshots
To create an updatable snapshot, you must have the following sets of privileges:
- To create a snapshot in your own schema, you must have the CREATE SNAPSHOT, CREATE TABLE, CREATE TRIGGER, and CREATE VIEW system privileges, as well as SELECT privilege on the master tables.
- To create a snapshot in another user's schema, you must have the CREATE ANY SNAPSHOT system privilege, as well as SELECT privilege on the master table. Additionally, the owner of the snapshot must have been able to create the snapshot.
- To create the snapshot as a replicated object, you must have been granted EXECUTE privileges on SYS.DBMSOBJGWRAPPER at the master site.
Transaction Ownership
Under synchronous propagation, a transaction is owned by the owner of the trigger and will be propagated to the remote sit with that owner's privileges. You can change the ownership of the transaction, usually to the replication administrator who has full privileges at the remote site, by using GEN_OBJ_OWNER.
Dropping a Replicated Object
To drop a replicated object from a snapshot site, call the DROP_SNAPSHOT_REPOBJECT procedure in the DBMS_REPCAT package at that snapshot site, as shown in the following example:
DBMS_REPCAT.DROP_SNAPSHOT_REPOBJECT(
sname => 'acct_rec',
oname => 'emp',
type => 'snapshot',
drop_objects => TRUE);
In this example, the EMP snapshot will no longer be replicated, all supporting objects will be dropped, and the snapshot itself will be dropped. Had DROP_OBJECTS been set to FALSE, the snapshot would no longer be replicated, but the snapshot would remain in the schema until you removed it using the DROP SNAPSHOT command. The trigger and associated package used to add transactions to the deferred transaction queue, as well as any queued transactions, are not dropped until you drop the snapshot itself.
Additional Information: The parameters for the DROP_SNAPSHOT_REPOBJECT procedure are described in Table 12 - 137, and the exceptions are listed in Table 12 - 138.
Dropping a Snapshot Object Group
To drop a snapshot object group from your replicated environment, call the DROP_SNAPSHOT_REPGROUP procedure in the DBMS_REPCAT package, as shown in the following example:
DBMS_REPCAT.DROP_SNAPSHOT_REPGROUP(
gname => 'accts',
drop_contents => TRUE);
In this example, the ACCTS replicated object group is dropped from your current snapshot site. All objects generated to support the replicated object group are dropped, and the replicated objects in the object group no longer propagate changes to their former master site.
To drop these objects completely, you must set DROP_CONTENTS to TRUE, as shown in the example. If you set DROP_CONTENTS to FALSE, the trigger generated to support replication of snapshot modifications remains.
Additional Information: The parameters for the DROP_SNAPSHOT_REPGROUP procedure are described in Table 12 - 135, and the exceptions are listed in Table 12 - 136.
Offline Instantiation
Offline instantiation of a snapshot site is primarily useful for those sites with a very large amount of snapshot data where the time required to transfer the data through network links to the new site would be prohibitive.
Creating a snapshot site using offline instantiation requires that you first create a snapshot for each table in a new snapshot replication group at the master site, then do an export of the base tables to a file or files that can then be transported (via tape or another medium) to the new site and used to instantiate the new snapshot site.
Perform the following steps at the specified sites:
- It is recommended that you create a snapshot log for each master site before instantiating the new snapshot. You must also be the owner of the schema at the master site from which the snapshots will be derived.
- Create a snapshot for each of the tables in the same schema as the master schema, but give the snapshot a name that is different from the corresponding master table. You must also include the database link from the snapshot site to the master site.
For example:
CREATE SNAPSHOT FOOITEM AS SELECT * FROM ioug1.item@dbs1
CREATE SNAPSHOT FOOITEM AS SELECT * FROM ioug1.ITEM
Attention: Before creating your snapshots, ensure that you have the necessary storage space available at the master site.
- As schema owner, use the Export utility to export the snapshots' base tables (those prefixed with SNAP$_). The export file(s) can then be transported to the new snapshot site.
- Mount the tape containing the export file.
- Use the procedure CREATE_SNAPSHOT_REPGROUP (gname, master_site) to create the replicated object group for the snapshot that you plan to import from the master site. Note that a replicated snapshot group must have the same name as the object group at the master site.
- For each schema and snapshot, use the procedure DBMS_OFFLINE_SNAPSHOT.BEGIN_LOAD (gname, sname, snapshot_oname, master_site) to create empty snapshots in the specified schema and object group, as well as all the necessary supporting objects.
- You can now import the base tables from the Export file(s).
- When the import is complete, for each schema and snapshot, use the procedure DBMS_OFFLINE_SNAPSHOT.END_LOAD (gname, sname, snapshot_oname).
- Use the DROP SNAPSHOT statement to drop the snapshots at the master site that were created for offline instantiation.
For more information about using the Import/Export utilities, see
page 7 - 12 and Oracle7 Server Utilities.
Propagating DML Changes
This section describes how updates made to a replicated snapshot are propagated to its associated master table, and how updates to the master table are, in turn, propagated to the snapshots. While master table changes are always propagated to the snapshot site asynchronously, in the form of a refresh, snapshot site changes can be propagated either synchronously or asynchronously. The PROPAGATION_MODE parameter of the CREATE_SNAPSHOT_REPGROUP and ALTER_SNAPSHOT_PROPAGATION commands determines how changes from the snapshot site are propagated to the master site.
How Changes are Propagated
The method that you choose to refresh and/or propagate your snapshot updates will be determined by the frequency of changes that you make to the data at the snapshot and master sites.
For example, you might want communication from the snapshot to the master to seem event-driven. By frequently propagating changes to the master site (such as every 10 seconds), you can ensure that shortly after each modification to an updatable snapshot, the change is forwarded to its associated master table. Yet, you might only refresh the snapshot once, at the start of each day, or you may never refresh the snapshot, if updates are performed only at the snapshot site.
Asynchronously Replicating Snapshot Updates to the Master Site
As shown in Figure 5 - 1, whenever you apply a change to a replicated updatable snapshot that is asynchronously propagating changes to its associated master site, the following events occur:

Figure 5 - 1. Applying Changes to an Updatable Snapshot
- The change is applied to the snapshot base table. This change is also reflected in the user view of the snapshot.
- The ROWID of any rows updated or deleted is recorded in the snapshot log. This information is used during a snapshot refresh to determine what changes to apply to the snapshot.
- A call is added to the deferred transaction queue. These calls are pushed to the associated master table at whatever interval you specify.
When Changes are Propagated
Updates are asynchronously propagated from the snapshot site to its master site whenever one of the following actions occurs:
- The updatable snapshot is refreshed by calling one of the following procedures, with the PUSH_DEFERRED_RPC argument set to TRUE:
Each of these methods of snapshot refresh is described, starting
. You may also want to set the REFRESH_AFTER_ERRORS argument to TRUE if you want the refresh to continue even if there are errors logged in the DefError table at the master definition site.
- The deferred transaction queue at the snapshot site is pushed by calling either DBMS_DEFER_SYS.EXECUTE or DBMS_DEFER_SYS.SCHEDULE_EXECUTION. The deferred transaction queue at a snapshot site is pushed in the same manner as the queue at an asynchronous master site is pushed; see page 4 - 26 for details. For snapshot sites, you should set the BATCH_SIZE parameter to 0.
Conflict Detection
When you asynchronously push changes from a snapshot to its master, Oracle compares the old values for the row at the snapshot site (that is, the values before any changes were applied) to the current values for the row at the master site. If the values are different, a conflict has occurred. If any conflicts are detected at the master site, Oracle invokes the appropriate conflict resolution routine, if any was specified. For example, you might create a routine to resolve a conflict between two rows by selecting the row with the most recent timestamp, or by combining the column values of the conflicting rows.
Note that if conflict resolution routines are employed, the values of some of the rows in your snapshot may change or even be removed after a refresh is performed. If you did not specify a conflict resolution routine at the master site, or if the routine specified is unable to resolve the conflict, the conflict is logged and must be resolved manually at the master site.
Additional Information: For additional information on conflict detection and resolution, refer to Chapter 6.
How Snapshot Changes are Applied to the Master Table
The DML changes propagated to the master table are applied in the same manner as changes are replicated from one master site to another master site. As shown in Figure 5 - 2, the deferred call is pushed from the snapshot site. The package at the master site applies the change to the master table. If this change results in a conflict, it must either be resolved by the appropriate conflict resolution routine, or logged in an error table.
Figure 5 - 2. Applying Changes to a Master Table 3
Two additional steps occur when changes are propagated from an updatable snapshot to the master table:
- The ROWID of any rows inserted, updated, or deleted is logged in the master snapshot log. This information is used during a fast snapshot refresh to determine what changes to apply to the snapshot.
- A call is added to the deferred transaction queue at the master site. These calls are pushed to the other master tables in the replicated environment. It is this final step that ultimately allows changes from one snapshot site to propagate to all other sites in the replicated environment.
Because changes from the master site are applied at the snapshot site using the refresh mechanism, there is no need for conflict detection or resolution at the snapshot site. All conflict detection and resolution occurs at the master site.
Refreshing a Snapshot
As shown in Figure 5 - 3, when you refresh an updatable snapshot the following events occur:
Figure 5 - 3. Snapshot Refresh
1. Any changes that you made to the snapshot site will have been added to the deferred transaction queue associated with the snapshot.
2. Any transactions queued for the snapshot are pushed to its associated master table (assuming PUSH_DEFERRED_RPC is TRUE), where they are applied, using the appropriate replication mechanism. If you used synchronous propagation for the snapshot, there should be not deferred transactions to propagate.
3. Oracle next determines which rows from the master table need to be updated at the snapshot. Conceptually, Oracle uses the view of the master table to create a list of the rows in the master snapshot log and the updatable snapshot log that are relevant for the updatable snapshot.
Although you might think that all changes in the snapshot log would be recorded in the master log when the changes from the snapshot were pushed to the master, it is possible that a change to the snapshot would not be applied to the master. For example, if a conflict was detected when the change was pushed to the master, application of a conflict resolution routine might have resulted in a change not being applied to the master table.
4. These changes are applied to the snapshot by copying the values of the changed rows in the master table to the snapshot base table.
Propagating Changes Between Snapshot Sites
As shown in Figure 5 - 4, snapshot sites never communicate with one another directly. Instead, they must communicate through their associated master sites. In order for a snapshot to see a change made to a snapshot at another snapshot site, the following series of events illustrates what must occur:
Figure 5 - 4. How Changes Propagate Between Snapshot Sites
1. The change is first applied to snapshot EMP at snapshot site A.
2. This change is either synchronously (using remote procedure calls) or asynchronously (by pushing the deferred transaction queue, for example as the result of a refresh) applied to the master table of snapshot EMP at site A. For changes propagated asynchronously, any conflicts between the snapshot and master table are detected and possibly resolved during this refresh.
3. Later when master site A communicates with master site B, the EMP table at site B is updated with this change. Any conflicts between the EMP table at site A and the EMP table at site B are detected and possibly resolved at this time.
4. Finally, the EMP snapshot at site B is updated with this change when it is refreshed.
Synchronously Replicating Snapshot Updates to the Master Site
If you choose to synchronously propagate your snapshot site changes to the associated master site, Oracle performs the following actions each time you modify an updatable snapshot:
1. Oracle obtains a lock on the local row and performs the update.
2. The AFTER ROW generated trigger for the snapshot fires, and its associated package makes the necessary remote procedure call to the generated package at the master site to apply the change.
3. The generated procedure at the master site locks the row at the master site and performs the update.
4. If the master detects a conflict that it cannot resolve, an error is raised immediately.
5. Using two-phase commit, Oracle commits or rolls-back (if an error occurred), the transaction at the snapshot and master sites and releases the locks.
The snapshot logs at both the updatable snapshot site and the master site, as well as the view and base table at the snapshot site, are updated in the same manner as if you had propagated the changes asynchronously.
Propagating DDL Changes
When you use the procedures in the DBMS_REPCAT package to make changes to your replicated environment, these changes are not visible to a snapshot site until you refresh the snapshot replicated object group. Refreshing a snapshot site updates the objects contained in the replicated object group, whereas refreshing a snapshot updates the values of the rows of a snapshot object in the replicated object group.
Refreshing a Snapshot Site
To refresh a snapshot site with the most recent information from its associated master site, call the REFRESH_SNAPSHOT_REPGROUP procedure in the DBMS_REPCAT package at that snapshot site, as shown in the following example:
DBMS_REPCAT.REFRESH_SNAPSHOT_REPGROUP(
gname => 'accts',
drop_missing_objects => TRUE,
refresh_snapshots => TRUE,
refresh_other_objects => TRUE
execute_as_user => FALSE);
In this example, all of the snapshots and other objects in the ACCTS object group of your current snapshot site will be refreshed, and any objects dropped at the associated master site will be dropped from the snapshot site. All snapshots in the replicated object group are consistently refreshed using the FORCE refresh option.
By default, only the RepGroup views for the given replicated object group are updated. Refreshing the RepGroup view implies that any changes made to the replicated environment, such as the addition of a new master site, or the removal of an object from the object group, are made visible to the snapshot site.
You can optionally choose to refresh the replicated snapshots and non-snapshot objects, such as views and procedures. Snapshots are refreshed as described
. Oracle refreshes the non-snapshot objects, if necessary, by dropping and re-creating them using the definition of the object at the associated master site.
Additional Information: The parameters for the REFRESH_SNAPSHOT_REPGROUP procedure are described in Table 12 - 155, and the exceptions are listed in Table 12 - 156.
Attention: During a snapshot refresh, Oracle locks the base table of the snapshot in exclusive mode. Because other users are prevented from updating a snapshot during a refresh (queries are still available), you should schedule your refreshes to occur when the expected activity on the snapshot is low.
Altering a Replicated Snapshot
If you must alter the shape of a snapshot as the result of a change to its master, you must drop and recreate the snapshot by calling the DBMS_REPCAT.DROP_SNAPSHOT_REPOBJECT procedure and then the DBMS_REPCAT.CREATE_SNAPSHOT_REPOBJECT procedure.
Altering the Propagation Method of a Replicated Snapshot
To alter the method used to propagate changes from the snapshot site to its associated master site, use the DBMS_REPCAT.ALTER_SNAPSHOT_PROPAGATION procedure, as shown in the following example:
DBMS_REPCAT.ALTER_SNAPSHOT_PROPAGATION(
gname => 'accts',
propagation_mode => 'synchronous'
execute_as_user => 'REPADMIN');
In this example, all of the updatable snapshots in the ACCTS object group will now synchronously propagate their changes to their associated master tables.
When you call this procedure from the snapshot site, Oracle pushes the deferred transaction queue at the snapshot site, locks the snapshot base tables, and regenerates any triggers and their associated packages.
Additional Information: The parameters for the ALTER_SNAPSHOT_PROPAGATION procedure are described in Table 12 - 84, and the exceptions are listed in Table 12 - 85.
Forcing Ownership of a Transaction
You can change the ownership of a transaction, usually to the replication administrator who has full privileges at the remote site, by using EXECUTE_AS_USER.
Listing Snapshot Information
Query the DBA_SNAPSHOTS catalog view to obtain a listing of all of the snapshots in a database. For information on snapshot refresh groups, query the USER_REFRESH and USER_REFRESH_CHILDREN views. Query DBA_SNAPSHOT_LOGS at the master site to see master log information.
Sample Application
Suppose that you have the following tables in your INVENTORY database: CUSTOMER, ORDERS, ORDER_LINE, ITEM, and STOCK.
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')