0 Replies Latest reply: Oct 22, 2012 3:06 AM by AsimDeo RSS

    streams -- archives not applying at downstream

    AsimDeo
      Hi all,

      Environment is DB=10.2.0.5.0 64 bit, OS=windows 2003 64bit

      Three instances (physically separate machines)
      1)Prod - operational (presently no problem)
      2)DR - configured with data guard and operational - presently no problem
      3)Reporting - Database is operational... archives are shipped from Prod and received at Reporting instance but changes not applying

      Our requirement is to configure schema level replication from Prod to Reporting (one way).
      I have followed +How To Setup Schema Level Streams Replication with a Downstream Capture Process with Implicit Log Assignment [ID 733691.1]+

      Configuration are

      Reporting - Destination
      SQL>   select SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI, SUPPLEMENTAL_LOG_DATA_PK from v$database;
      
      SUP SUP SUP
      --- --- ---
      YES YES YES
      
      SQL> SELECT APPLY_NAME, QUEUE_NAME, RULE_SET_NAME, NEGATIVE_RULE_SET_NAME, STATUS FROM DBA_APPLY;
      
      APPLY_NAME                     QUEUE_NAME                     RULE_SET_NAME                  NEGATIVE_RULE_SET_NAME         STATUS
      ------------------------------ ------------------------------ ------------------------------ ------------------------------ --------
      STRMADMIN_APPLY                STREAMS_QUEUE                  RULESET$_9                                            ENABLED
      
      
      SQL> select state, capture_name from V$STREAMS_CAPTURE;
      
      STATE
      ------------------------------------------------------------------------------------
      ------------------------------------------------------------------------------------
      CAPTURE_NAME
      ------------------------------
      PAUSED FOR FLOW CONTROL
      DOWNSTREAM_CAPTURE
      
      
      SQL> SELECT CAPTURE_NAME,((SYSDATE - CAPTURE_MESSAGE_CREATE_TIME)*86400) LATENCY_SECONDS,((SYSDATE - CAPTURE_TIME)*86400) LAST_STATUS,
        2         TO_CHAR(CAPTURE_TIME, 'HH24:MI:SS MM/DD/YY') CAPTURE_TIME, TO_CHAR(CAPTURE_MESSAGE_CREATE_TIME, 'HH24:MI:SS MM/DD/YY') CREATE_TIME
        3         FROM V$STREAMS_CAPTURE;
      
      CAPTURE_NAME                   LATENCY_SECONDS LAST_STATUS CAPTURE_TIME                                                        CREATE_TIME
      ------------------------------ --------------- ----------- --------------------------------------------------------------------------- -----------------
      ------------------------------
      DOWNSTREAM_CAPTURE                       53475         371 12:54:54 10/22/12                                                   22:09:50 10/21/12
      Why archive logs are not applying on downstream database, Capture process is started and enabled

      Regards,
      Asim