Skip Headers

Oracle9i Advanced Replication
Release 2 (9.2)

Part Number A96567-01
Go To Documentation Library
Go To Product List
Book List
Go To Table Of Contents
Go To Index

Master Index


Go to previous page Go to next page

Troubleshooting Replication Problems

This appendix contains troubleshooting guidelines for managing a replication environment. This appendix contains the following topics:

Diagnosing Problems with Database Links

If you think a database link is not functioning properly, then you can drop and re-create it using Oracle Enterprise Manager, SQL*Plus, or another tool.

If you used a connection qualifier in a database link to a given site, then the other sites that link to that site must have the same connection qualifier. For example, suppose you create a database link as follows:

  IDENTIFIED BY secret USING 'connect_string_myethernet'

All the sites, whether masters or materialized views, associated with must include myethernet as the connection qualifier.

See Also:

Oracle9i Database Administrator's Guide for more information database links and connection qualifiers

Diagnosing Problems with Master Sites

Problems can arise in a multimaster replication system. The following sections discuss some problems and ways to solve them:

Replicated Objects Not Created at New Master Site

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

DDL Changes Not Propagated to Master Site

If you create a new master group object or alter the definition of a master group object at the master definition site and the modification is not propagated to a master site, then first ensure that the administrative requests at all sites have completed successfully. If requests are pending execution, then you can manually execute them to complete the operation immediately.

When you execute DDL statements through the replication API, Oracle executes the statements 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 supply the following as the ddl_text parameter to the DBMS_REPCAT.CREATE_MASTER_ REPOBJECT procedure:

CREATE TABLE oe.new_employees AS SELECT * FROM hr.employees WHERE ...;

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


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 asynchronously propagated to the other sites in your replication environment, then try the following:

DML Cannot be Applied to Replicated Table

If you receive the deferred_rpc_quiesce exception when you attempt to modify a replicated table, then the master group to which your replicated object belongs is quiescing or quiesced. To proceed, your replication administrator must resume replication activity for the master 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 may not violate a uniqueness constraint, for example, some equivalent sequence of individual updates may violate uniqueness.

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

Recreating a Replicated Object

If you add an object such as a package, procedure, or view to a master group, then the status of the object must be valid. If the status of an object is invalid, then recompile the object or drop and re-create the object before adding it to a master group. Check the DBA_REPOBJECT data dictionary view for the status of replication objects.

Unable to Generate Replication Support for a Table

When you generate replication support for a table, Oracle activates an internal trigger at the local site. EXECUTE privileges for most of the packages involved with replication, such as DBMS_REPCAT and DBMS_DEFER, need to be granted to replication administrators and users that own replicated objects. The Replication Management tool's Setup Wizard and the DBMS_REPCAT_ADMIN package both perform 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, then carefully review the procedure to ensure that the replicated procedure did not cause the conflict. Complete the following checks:

  • 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.
  • 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.

Problems With ON DELETE CASCADE and Integrity Constraints

ON DELETE CASCADE is not supported for replicated tables with a configuration similar to the following example. This example assumes there are three tables named A, B, and C.

If you use ON DELETE CASCADE in such a configuration, then the deletes may not be propagated in the correct order when you delete a record. In these cases, the following error is returned:

ORA-02292: integrity constraint (HR.DEPT_MGR_FK) violated - child record found

If you encounter this error, then create triggers that are aware of replication to perform this functionality instead of using ON DELETE CASCADE.

Diagnosing Problems with the Deferred Transaction Queue

If deferred transactions at a site are not being pushed to their destinations, then the following sections explain some possible causes for the problem:

Check Jobs for Scheduled Links

When you create a scheduled link, Oracle adds a corresponding job to the site's job queue. If you have scheduled a link to push deferred transactions at a periodic interval, and you encounter a problem, then you should first be certain that you are not experiencing a problem with the job queue.

Distributed Transaction Problems with Synchronous Replication

When you use synchronous replication, Oracle uses a distributed transaction to ensure that the transaction has been properly committed at the remote site. Distributed transactions use two-phase commit. Asynchronous replication does not use two-phase commit.

See Also:

Oracle9i Database Administrator's Guide for information on diagnosing problems with distributed transactions

Incomplete Database Link Specifications

If you notice that transactions are not being pushed to a given remote site, then you may have a problem with how you have specified the destination for the transaction. When you create a scheduled link, you must provide the full database link name.

Incorrect Replication Catalog Views

Having the wrong view definitions can lead to erroneous deferred transaction behavior. The DEFCALLDEST and DEFTRANDEST views are defined differently in catdefer.sql and catrepc.sql. The definitions in catrepc.sql should be used whenever replication is used. If catdefer.sql is ever (re)loaded, then ensure that the view definitions in catrepc.sql are subsequently loaded.

Diagnosing Problems with Materialized Views

There are a number of problems that might happen with materialized view sites in a replication system. The following sections discuss some problems and ways to troubleshoot them:

Problems Creating Replicated Objects at Materialized View Site

If you unsuccessfully attempt to create a new object at a materialized view site, then try the following:

Problems Performing Offline Instantiation of a Deployment Template

If you receive and error stating that Oracle is unable to initialize the extent in the temporary tablespace when you try to instantiate a deployment template offline, then you may need to adjust the datafile for the temporary database so that it auto extends.

For example, issue the following statement to adjust the datafile:

ALTER DATABASE TEMPFILE '/u02/oracle/rbdb1/temp.dbf' 
    NEXT 10M;

After you have made this adjustment, instantiate the deployment template offline at the materialized view site.

Refresh Problems

The following sections explain several common materialized view refresh problems.

Common Problems

Several common factors can prevent the automatic refresh of a group of materialized views:

When a materialized view refresh group is experiencing problems, ensure that none of the preceding situations is preventing Oracle from completing group refreshes.

Automatic Refresh Retries

When Oracle fails to refresh a group automatically, the group remains due for its refresh to complete. Oracle will retry an automatic refresh of a group with the following behavior:

If after 16 attempts to refresh a refresh group Oracle continues to encounter errors, then Oracle considers the group broken. The General page of the Refresh Group property sheet in Schema Manager indicates when a refresh group is broken. You can also query the BROKEN column of the USER_REFRESH and USER_REFRESH_CHILDREN data dictionary views to see the current status of a refresh group.

The errors causing Oracle to consider a materialized view refresh group broken are recorded in a trace file. After you correct the problems preventing a refresh group from refreshing successfully, you must refresh the group manually. Oracle then resets the broken flag so that automatic refreshes can happen again.

See Also:

The name of the materialized view trace file is of the form jn, where n is operating system specific. See the Oracle documentation for your operating system for the name on your system.

Fast Refresh Errors at New Materialized View Sites

In some cases, a materialized view log for a master table or master materialized view might be purged during the creation of a materialized view at a new materialized view site. When this happens, you may encounter the following errors:

ORA-12004 REFRESH FAST cannot be used for materialized view materialized_view_
ORA-12034 materialized view log on materialized_view_name younger than last 

See Also:

"Avoiding Problems When Adding a New Materialized View Site" for a complete description of how to avoid this problem.

Materialized Views Continually Refreshing

If you encounter a situation where Oracle continually refreshes a group of materialized views, then check the group's refresh interval. Oracle evaluates a group's automatic refresh interval before starting the refresh. If a group's refresh interval is less than the amount of time it takes to refresh all materialized views in the group, then Oracle continually starts a group refresh each time the job queue process checks the queue of outstanding jobs.

Materialized View Logs Growing Too Large

If a materialized view log at a master site or master materialized view site is growing too large, then check to see whether a network or site failure has prevented the master site or master materialized view site from becoming aware that a materialized view has been dropped. You may need to purge part of the materialized view log or unregister the unused materialized view site.

See Also:

Oracle9i Replication Management API Reference for more information about managing materialized view logs

Advanced Troubleshooting of Refresh Problems

If you have a problem refreshing a materialized view, then try the following:

Go to previous page Go to next page
Copyright © 1996, 2002 Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Go To Product List
Book List
Go To Table Of Contents
Go To Index

Master Index