0 Replies Latest reply on Dec 30, 2011 10:27 AM by Kim Berg Hansen

    Row cache lock when attempting dbms_streams_adm.remove_rule

    Kim Berg Hansen

      The setup:

      Bidirectional replication of a few small tables have been setup between two databases:
      Oracle Database 11g Enterprise Edition Release - 64bit Production
      PL/SQL Release - Production
      CORE      Production
      TNS for HPUX: Version - Production
      NLSRTL Version - Production
      The replication has been setup with a capture process, which we have found out is impacting our performance as the capture process has to mine quite a lot of redo logs just to find a fraction of a percent of DML needing to be replicated.

      So I am now preparing to follow the [url http://docs.oracle.com/cd/B28359_01/server.111/b28321/strms_mcap.htm#BEHIDCEB]documented recipe for switching to synchronous capture, which will suit our setup better.

      One of the details is that synchronous capture will not capture DDL. I can live with that, but I have to remove the DDL rules from the rule sets. I have been using dbms_streams_adm.remove_rule to get rid of the DDL rules, which have worked mostly fine. All DDL rules have been removed in one of the bases. In the other the DDL rules for capture and apply have gone, but the DDL rules for propagation linger on.

      When I attempt to remove a DDL rule in a propagation ruleset in that database, the session will hang and wait for wait event "row cache lock". It will continue to time out on that wait event and then wait again, on and on. It worked fine in the other database.

      I have tried stopping anything streams related - stop capture, propagation and apply on both databases - but it does not help.

      I know I could as an alternative create a new ruleset as a copy of the old but just without the DDL rules. But partly I dislike leaving "loose ends" like that ;-) , and partly I am afraid that the "row cache lock" might block me later in the process if something is locking something important?

      Can I identify what is causing that "row cache lock" and do something about it?

      Thanks in advance for any help and hints you might have for me :D

      Edit: Update:

      Using V$ROWCACHE_PARENT view I have isolated that the holder of the row cache lock is the CJQ0 process. Now all I need to do is figure out what to do about it ;-)

      Edited by: Kim Berg Hansen on Dec 30, 2011 11:19 AM

      Edit: Another update:

      OK - discovered I could kill CJQ process on OS level and let PMON restart it. It worked and my rules are now OK.
      Happy new year ;-)

      Edited by: Kim Berg Hansen on Dec 30, 2011 11:26 AM