B
Troubleshooting Replication Problems

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

• Diagnosing Problems with Database Links

• Diagnosing Problems with Master Sites

• Diagnosing Problems with the Deferred Transaction Queue

• Diagnosing Problems with Snapshots

Diagnosing Problems with Database Links

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

• Make sure that the scheduled interval is what you want.

• Make sure that the scheduled interval is not shorter than the required execution time.

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

CREATE DATABASE LINK dbs1.world@myethernet CONNECT TO repadmin
  IDENTIFIED BY secret USING 'connect_string_myethernet'

All the sites, whether masters or snapshots, associated with dbs1.world@myethernet must include myethernet as the connection qualifier.

Diagnosing Problems with Master Sites

There are a number problems that might arise in a multimaster replication system. The next few sections discuss some problems and ways to troubleshoot 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, try the following:

• Ensure that the necessary private database links exist between the new master site and the existing master sites. If you used the Replication Manager Setup Wizard to setup your sites, you should not have any problems. You must have links both to the new site from each existing site, and from the new site to each existing site.

• Make sure that the administration requests at all sites have completed successfully. If requests have not been executed yet, you can manually execute pending administration requests to complete the operation immediately.

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, first ensure that the administration requests at all sites have completed successfully. If requests are pending execution, 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, 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.

Note: 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:

• Use Replication Manager to check whether the corresponding deferred transaction has been pushed to the destination. If not, you can also check to see how much longer it will be before the scheduled link pushes the queue to the destination site. If you do not want to wait for the next scheduled push across a link, you can execute deferred transaction manually.

• If a scheduled link's interval has passed and corresponding deferred transactions have not been pushed, check the corresponding job for the link.

• Even after propagating a deferred transaction to a destination, it might not execute because of an error.

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 master groups at your local site are "quiescing" or "quiesced". To proceed, your replication administrator must resume replication activity for the 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 update requests in the deferred RPC queue.

Re-Creating a Replicated Object

If you add an object such as a package, procedure, or view to a master group, the status of the object must be VALID. If the status of an object is INVALID, recompile the object, or drop and recreate the object before adding it to a master group.

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 Manager Setup Wizard and 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.

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.

• Table B contains a record dependent on a record in Table A because of a foreign key constraint.

• Table C contains a record dependent on a record in Table B because of a foreign key constraint.

• Table C also contains a record dependent on a record in Table A because of a foreign key constraint.

If you use ON DELETE CASCADE in such a configuration, 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 (SCOTT.FK_CB) violated - child record found

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

Diagnosing Problems with the Deferred Transaction Queue

If deferred transactions at a site are not being pushed to their destinations, there can be several reasons for the problem. The following sections explain some possible causes.

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, you should first be certain that you are not experiencing a problem with the job queue.

Distributed Transaction Problems

When Oracle pushes a deferred transaction to a remote site using serial propagation, it uses a distributed transaction to ensure that the transaction has been properly committed at the remote site before the transaction is removed from the queue at the local site. For information on diagnosing problems with distributed transactions (two-phase commit), see the book Oracle8i Distributed Database Systems.

Incomplete Database Link Specifications

If you notice that transactions are not being pushed to a given remote site, 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. If you use Replication Manager, you should not have any problems.

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, ensure that the view definitions in CATREPC.SQL are subsequently loaded.

Diagnosing Problems with Snapshots

There are a number of problems that might happen with snapshot sites in a replication system. The next few sections discuss some problems and ways to troubleshoot them.

Problems Creating Replicated Objects at Snapshot Site

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

• For an updateable snapshot, check that the associated master table has a snapshot log.

• Make sure that you have the necessary privileges to create the object. For a snapshot, you need SELECT privilege on the master table and its snapshot log. See "Privileges" for more information.

• If you are trying to add an existing snapshot to a snapshot group, try recreating the snapshot when you add it to the group.

• If you are trying to create a fast refresh primary key or subquery snapshot, then make sure that the snapshot log on the master table logs primary keys.

• If you are trying to create a fast refresh rowid snapshot, then make sure that the snapshot log on the master table logs rowids.

• Check if the snapshot log has the required filter columns for subquery snapshots.

• Check if the snapshot log exists for all tables that are involved in a fast refresh snapshot. If the snapshot contains a subquery, each table referenced in the subquery should have a snapshot log.

Troubleshooting Refresh Problems

The following sections explain several common snapshot refresh problems.

Common Problems

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

• The lack of an SNP background process at the snapshot database

• An intervening network or server failure

• An intervening server shutdown

When a snapshot refresh group is experiencing problems, ensure that none of the above 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:

• Oracle retries the group refresh first one minute later, then two minutes later, four minutes later, and so on, with the retry interval doubling with each failed attempt to refresh the group.

• Oracle does not allow the retry interval to exceed the refresh interval itself.

• Oracle retries the automatic refresh up to sixteen times.

If after sixteen attempts to refresh a refresh group Oracle continues to encounter errors, 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 snapshot 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 snapshot trace file is of the form SNPn, where n is operating system-specific. See the Oracle documentation for your operating system for the name on your system.

Snapshots Continually Refreshing

If you encounter a situation where Oracle continually refreshes a group of snapshots, 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 snapshots in the group, Oracle continually starts a group refresh each time the SNP background process checks the queue of outstanding jobs.

Snapshot Logs Growing Without Bounds

If a snapshot log is growing without bounds, check to see whether a network or site failure has prevented a master from becoming aware that a snapshot has been dropped. You may need to purge part of the snapshot log or unregister the snapshot.

Advanced Troubleshooting of Refresh Problems

If you have a problem refreshing a snapshot, try the following:

• Check the NEXT value in the DBA_SNAPSHOTS view to determine if the refresh has been scheduled.

• If the refresh interval has passed, check the DBA_REFRESH view for the associated job number for the snapshot refresh and then diagnose the problem with job queues.

• Check if their are job queue process running. Check the JOB_QUEUE_PROCESSES initialization parameter, query the DBA_JOBS_RUNNING view, and use your operating system to check if the SNP background processes are still running.

• You may also encounter an error if you attempt to define a master detail relationship between two snapshots. You should define master detail relationships only on the master tables by using declarative referential integrity constraints; the related snapshots should then be placed in the same refresh group to preserve this relationship.

• If you encounter a situation where your snapshots are being continually refreshed, you should check the refresh interval that you specified. This interval is evaluated before the snapshot is refreshed. If the interval that you specify is less than the amount of time it takes to refresh the snapshot, the snapshot is refreshed each time the SNP background process checks the queue of outstanding jobs.

• If there are any outstanding conflicts recorded at the master site for the snapshots, you can only refresh the snapshots by setting the parameter REFRESH_AFTER_ERRORS to TRUE. This parameter can be set when you create or alter a snapshot refresh group. (There is a corresponding parameter for Replication Manager property sheets.)

• If your snapshot logs are growing too large, see Oracle8i Replication Management API Reference for information about managing snapshot logs.

• Snapshots in the same refresh groups have their rows updated in a single transaction. Such a transaction can be very large, requiring either a large rollback segment at the snapshot site (with the rollback segment specified to be used during refresh) or more frequent refreshes to reduce the transaction size.

• If Oracle error ORA-12004 occurs, the master site may have run out of rollback segments when trying to maintain the snapshot log, or the snapshot log may be out of date. For example, the snapshot log may have been purged or recreated.

• Complete refreshes of a single table internally use the TRUNCATE feature to increase speed and reduce rollback segment requirements. However, until the snapshot refresh is complete, users may temporarily see no data in the snapshot. Refreshes of multiple snapshots (for example, refresh groups) do not use the TRUNCATE feature.

• Reorganization of the master table (for example, to reclaim system resources) should TRUNCATE the master table to force ROWID snapshots to do complete refreshes. Otherwise, the snapshots have incorrect references to master table ROWIDs.

• If while refreshing you see an ORA-942 (table or view does not exist), then check your database links and make sure you still have the required privileges on the master table and the snapshot log.

• If a fast refresh was succeeding but then fails, then check the following:
  • If the snapshot log was truncated, purged, or dropped

  • If you still have the required privileges on the snapshot log

• If a force refresh takes an inordinately long time, then check if the snapshot log used by the refresh has been dropped.

• If the snapshot was created with BUILD DEFERRED, and its first fast refresh fails, make sure a previous complete refresh was done successfully before checking for other problems.

  See Also: Oracle8i Replication Management API Reference for information about managing snapshot logs.