Oracle7 Server Distributed Systems Volume II: Replicated Data

Diagnosing Problems with Master Sites

Replicated Objects Not Created at New Master Site

If you add a new master site to your replicated environment, and the appropriate replicated objects are not created at the new site, try the following:

Ensure that the necessary private database links exist between the new master site and the existing master sites, as described . You must have links both to the new site from each existing site, and from the new site to each existing site.

Re-execute DBMS_REPCAT.DO_DEFERRED_REPCAT_ADMIN at the new master site.

DDL Changes Not Propagated to Master Site

If you call a procedure in the DBMS_REPCAT package to make a schema-level change at the master definition site that is not propagated to a master site, try the following:

Examine the DBA_REPCATLOG at the master site and at the master definition site.

Call DBMS_REPCAT.DO_DEFERRED_REPCAT_ADMIN at the master site to force this change to be applied at the new site.

DDL submitted to repcat executes on behalf of the user who submits the DDL. When a DDL statement applies to an object in a schema other than the submitter's schema, the submitter needs appropriate privileges to execute the statement. In addition, the statement must explicitly name the schema. For example, assume that you, the replication administrator, supply the following as the ddl_text parameter to the DBMS_REPCAT.CREATE_MASTER_REPOBJECT procedure:

CREATE TABLE scott.new_emp AS SELECT * FROM hr.emp WHERE...;

Because each table name contains a schema name, this statement works whether the replication administrator is SCOTT, HR, or another user--as long as the administrator has the required privileges.

Suggestion: Qualify the name of every schema object with the appropriate schema.

DML Changes Not Asynchronously Propagated to Other Sites

If you make an update to your data at a master site, and that change is not properly asynchronously propagated to the other sites in your replicated environment, try the following:

Check the NEXT_DATE value in the DefSchedule view to determine if the changes have been scheduled to be propagated.

Call DBMS_DEFER_SYS.EXECUTE to force the execution of the DML, or call DBMS_DEFER_SYS.SCHEDULE_EXECUTION to schedule execution at a periodic interval. If the scheduled execution interval has passed, check the DefSchedule view for the job number and follow the instructions for diagnosing problems with job queues .

If you determine that the change was not propagated because of an error, you can also check the DefError view at the destination site to determine the cause of the error. You might also check the DefTran and DefCall views for more information. Additional instructions on how to determine the cause of an error logged in the DefError view are included .

DML Cannot be Applied to Replicated Table

If you receive the DEFERRED_RPC_QUIESCE exception when you attempt to modify a replicated table, one or more replicated object groups at your local site are "quiescing" or "quiesced". To proceed, your replication administrator must either call DBMS_REPCAT.RESUME_MASTER_ACTIVITY, or DBMS_REPCAT.DROP_MASTER_REPSCHEMA for each quiesced, replicated object group.

Bulk Updates and Constraint Violations

A single update statement applied to a replicated table can update zero or more rows. The update statement causes zero or more update requests to be queued for deferred execution, one for each row updated. This distinction is important when constraints are involved, because Oracle effectively performs constraint checking at the end of each statement. While a bulk update might not violate a uniqueness constraint, for example, some equivalent sequence of individual updates might violate uniqueness.

If the ordering of updates is important, update one row at a time in an appropriate order. This lets you define the order of the update requests in the deferred RPC queue.

Re-creating a Replicated Object

If you replicate an object that already exists at the master definition site with DBMS_REPCAT.CREATE_MASTER_REPOBJECT, the status of the object must be VALID. If the status is INVALID, recompile the object, or drop and recreate the object. Then invoke CREATE_MASTER_REPOBJECT with the RETRY argument set to TRUE.

Unable to Generate Replication Support for a Table

When you call GENERATE_REPLICATION_SUPPORT for a replicated table, Oracle generates a trigger at the local site. If the table will be propagating changes asynchronously, this trigger uses the DBMS_DEFER package to build the calls that are placed in the local deferred transaction queue. EXECUTE privileges for most of the packages involved with symmetric replication, such as DBMS_REPCAT and DBMS_DEFER, need to be granted to replication administrators and users that own replicated objects. The DBMS_REPCAT_ADMIN package performs the grants needed by the replication administrators for many typical replication scenarios. When the owner of a replicated object is not a replication administrator, however, you must explicitly grant EXECUTE privilege on DBMS_DEFER to the object owner.

Problems with Replicated Procedures or Triggers

If you discover an unexpected unresolved conflict, and you were mixing procedural and row-level replication on a table, carefully review the procedure to ensure that the replicated procedure did not cause the conflict. Ensure that ordering conflicts between procedural and row-level updates are not possible. Check if the replicated procedure locks the table in EXCLUSIVE mode before performing updates (or uses some other mechanism of avoiding conflicts with row-level updates). Check that row-level replication is disabled at the start of the replicated procedure and re-enabled at the end. Ensure that row-level replication is re-enabled even if exceptions occur when the procedure executes. In addition, check to be sure that the replicated procedure executed at all master sites. You should perform similar checks on any replicated triggers that you have defined on replicated tables.