A
Troubleshooting Replication Problems

Diagnosing Problems with M aster 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
• DDL Changes Not Propagated to Master Site
• DML Changes Not Asynchronously Propagated to Other Sites
• < /a>DML Cannot be Applied to Replicated Table
• Bulk Updates and Constraint Violations
• Re-creating a Replicated Object
• Una ble to Generate Replication Support for a Table
• P roblems with Replicated Procedures or Triggers

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, the n try the following:

• Ensure that the necessary private database l inks exist between the new master site and the existing master sites. If you used the Replication Management tool's Setup Wizard to s etup your sites, then you should not have any problems. You must have links both to the new site from each existing site, and from th e new site to each existing site.
• Make sure that the administrative requests at a ll sites have completed successfully. If requests have not been executed yet, then you can manually execute pending administrative re quests 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 th e modification is not propagated to a master site, then first ensure that the administrative requests at all sites have completed suc cessfully. 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 th e user who submits the DDL. When a DDL statement applies to an object in a schema other than the submitter's schema, the submitter ne eds 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 procedu re:

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

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

DML Changes Not Asynchronously Propagated to Other Sites

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

• Use the Replication Management tool in the Oracle Enterprise Manager Console to check whether the corresponding deferred transaction has been pushed to the destination. If not, then 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 sc heduled push across a link, then you can execute deferred transaction manually.
• I f a scheduled link's interval has passed and corresponding deferred transactions have not been pushed, then check the corresponding j ob for the link.
• Even after propagating a deferred transaction to a destination, it may not execute because of an error. Check the DEFERROR data dictionary view at the destination site for errors.

  See Also: Oracle Databas e Advanced Replication Management API Reference for information about modifying tables without replicating the modifications , which may be necessary when you need to manually synchronize the data in replicated tables

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 quiescin g or quiesced. To proceed, your replication administrator must resume replication activity for the master group.

• Ensure that ordering conflicts between procedural and row-level u pdates 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 an d 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 replicate d procedure executed at all master sites.

You should perform similar checks on any repli cated triggers that you have defined on replicated tables.

Diagnosing Problems with the Deferred Transaction Queue

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

• Check Jobs for Scheduled Links
• Distri buted Transaction Problems with Synchronous Replication
• Incomplete Database Link Specifications
• In correct Replication Catalog Views

Check Jobs for Scheduled Links

When you cr eate a scheduled link, Oracle adds a corresponding job to the site's job queue. If you have scheduled a link to push deferred transac tions at a periodic interval, and you encounter a problem, then you should first be certain that you are not experiencing a problem w ith 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.

Incomplete Database Link Specif ications

If you notice that transactions are not being pushed to a give n remote site, then you may have a problem with how you have specified the destination for the transaction. When you create a schedul ed 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 DE FTRANDEST views are defined differently in catdefer.sql and catrepc.sql. The definitions in ca trepc.sql should be used whenever replication is used. If catdefer.sql is ever (re)loaded, then ensure that the v iew 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. T he following sections discuss some problems and ways to troubleshoot them:

• Problems Creating Replicated Objects at Materialized View Site
• Refresh Problems
• Advanced Troubleshooting of Refresh Problems

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:

• For an updatable materialized view, chec k that the associated master table or master materialized view has a materialized view log.
• Make sure that you have the necessary privileges to create the object. For a materialized view, you need SELECT privilege on the master table or master materialized view and its materialized view log. See "Assign P rivileges" for more information.
• If you are t rying to add an existing materialized view to a materialized view group, then try re-creating the materialized view when you add it t o the group.
• If you are trying to create a fast refresh primary key or subquery m aterialized view, then make sure that the materialized view log on the master table or master materialized view logs primary keys.
• If you are trying to create a fast refresh rowid materialized view, then make sure that the materialized view log on the master table logs rowids.
• Check if the mate rialized view log has the required columns added for subquery materialized views. See "Logging Columns in the Materialized View Log" for information.
• Check if the materialized view log exists for all tables that are involved in a fast refresh materialized view. If the materialized v iew contains a subquery, then each table referenced in the subquery should have a materialized view log.

Problems Performing Offline Instant iation 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' 
    AUTOEXTEND ON
    NEXT 10M;

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

Refresh P roblems

The following sections explain several common materialized view refresh problems.

Common Problems

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

• The lack of a job queue p rocess at the materialized view database
• An intervening network or server failure
• An intervening server shutdown

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

Automatic Refresh Retries

When Oracle fails to refresh a gr oup automatically, the group remains due for its refresh to complete. Oracle will retry an automatic refresh of a group with the foll owing 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 t he 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 16 attempts to refresh a refresh group Oracle continues to encounter errors, then Oracle cons iders the group broken. The General page of the Refresh Group property sheet in Schema Manager indicates when a refresh group is brok en. You can also query the BROKEN column of the USER_REFRESH and USER_REFRESH_CHILDREN data di ctionary views to see the current status of a refresh group.

The errors causing Oracle to cons ider 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 ca n happen again.

See A lso: 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 sys tem 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 th e 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_ nam e ORA-12034 materialized view log on materialized_view_name younger than last refres h

See Also: "Avoiding Problems When Adding a New Materialized View Site"< /a> for a complete description of how to avoid this problem.

Materialized Views Continually Refreshing

If you encounter a situation where Oracle continually refres hes a group of materialized views, then check the group's refresh interval. Oracle evaluates a group's automatic refresh interval bef ore 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 sit e or master materialized view site from becoming aware that a materialized view has been dropped. You may need to purge part of the m aterialized view log or unregister the unused materialized view site.

See Also: Oracle Database Advanced Replication Management API Reference fo r 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:

• Check the NEXT_DATE value in the DBA_REFRESH_CHILDREN view to determine if the refresh has been scheduled.
• If the refresh interval has passed, then check the DBA_REFRESH view for the associated job number for the materialized view refresh and then diagnos e the problem with job queues.
• Check if there are job queue process running. Chec k the JOB_QUEUE_PROCESSES initialization parameter, query the DBA_JOBS_RUNNING view, and use your operating system to check if the job queue processes are still running.
• You may also encou nter an error if you attempt to define a master detail relationship between two materialized views. You should define master detail r elationships only on the master tables by using declarative referential integrity constraints. The related materialized views should then be placed in the same refresh group to preserve this relationship. However, you can define deferred (or deferrable) constraints on materialized views.
• If there are any outstanding conflicts recorded at the mas ter site or master materialized view site for the materialized views, then you can only refresh the materialized views by setting the parameter REFRESH_AFTER_ERRORS to true. This parameter can be set when you create or alter a materialized view refresh group. There is a corresponding parameter for the Replication Management tool's property sheets.
• Materialized views in the same refresh groups have their rows updated in a single transaction. Such a tr ansaction can be very large, requiring either a large rollback segment at the materialized view site, with the rollback segment speci fied to be used during refresh, or more frequent refreshes to reduce the transaction size.
• If Oracle error ORA-12004 occurs, then the master site or master materialized view site may have run out of ro llback segments when trying to maintain the materialized view log, or the materialized view log may be out of date. For example, the materialized view log may have been purged or re-created.
• Complete refreshes of a single materialized view internally use the TRUNCATE feature to increase speed and reduce rollback segment requirements . However, until the materialized view refresh is complete, users may temporarily see no data in the materialized view. Refreshes of multiple materialized views (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 materialized views to do complete refreshes. Otherwise, the materialized views have incorrect references to master table rowids. You use the BEGIN_TABLE_REORGANIZATION and END_TABLE_REORGANIZATION procedures in the DBMS_MVIEW package to reorganize a master table. See the Oracle Database Advanced Replication Management API Reference for more information .
• If while refreshing you see an ORA-942 (table or view does not exi st), then check your database links and make sure you still have the required privileges on the master table or master materialized v iew and the materialized view log.
• If a fast refresh was succeeding but then fail s, then check whether:
  • The materialized view log was truncated, purg ed, or dropped
  • You still have the required privileges on the materialized view lo g
• If a force refresh takes an inordinately long time, then check if the materialized view log used by the refresh has been dropped.
• If the materialized view was created with BUILD DEFERRED, and its first fast refresh fails, then make sure a previous complete refresh was done successfully before checking for other problems.

  See Also: Oracle Database Advanced Replication Management API Reference for in formation about managing materialized view logs

---

Previous Next

Copyright © 1996, 2003 Oracle Corporation All Rights Reser ved.

Home Book List Contents Index Master Index Feedback