3 Replies Latest reply: Jan 11, 2012 1:46 AM by 734707 RSS

    data streems replication

    User347578
      Hi Guru's

      Just thought of checking with you all for the possible solution to a problem.

      The problem is with the strems replication.

      Client has come back stating that data is not replicating to the target database .

      could some put please come up with the possible factors for this failure .

      I checked the db links are working fine .
      apart from this any suggestion on this will be highly apprciated .

      Thanks in Advance .
        • 1. Re: data streems replication
          CKPT
          790072 wrote:
          Hi Guru's

          Just thought of checking with you all for the possible solution to a problem.

          The problem is with the strems replication.

          Client has come back stating that data is not replicating to the target database .

          could some put please come up with the possible factors for this failure .

          I checked the db links are working fine .
          apart from this any suggestion on this will be highly apprciated .

          Thanks in Advance .
          Refer this document for troubleshooting & performance on Streams Replication

          http://docs.oracle.com/cd/B28359_01/server.111/b28322/troub_rep.htm#CEGDFJCD
          • 2. Re: data streems replication
            Helios-GunesEROL
            Hi;

            For your issue i suggest close your thread here as changing thread status to answered and move it to Forum Home » Database »  Streams which you can get more quick response

            Regard
            Helios
            • 3. Re: data streems replication
              734707
              Not much informations, so let's grasp whatever we can.
              Can you post the results of the following queries (
              (Please, enclosed the response with the tags [ code] and [ /code]
              (otherwise we simply cannot read the resonse):
              set lines 190 pages 66 feed off pause off verify off
              
              col rsn format A28 head "Rule Set name"
              col rn format A30 head "Rule name"
              col rt format A64 head "Rule text"
              COLUMN DICTIONARY_BEGIN HEADING 'Dictionary|Build|Begin' FORMAT A10
              COLUMN DICTIONARY_END HEADING 'Dictionary|Build|End' FORMAT A10
              col CHECKPOINT_RETENTION_TIME head "Checkpoint|Retention|time" justify c
              col LAST_ENQUEUED_SCN for 999999999999 head "Last scn|enqueued" justify c
              col las format 999999999999 head "Last remote|confirmed|scn Applied" justify c
              col REQUIRED_CHECKPOINT_SCN for 999999999999 head "Checkpoint|Require scn" justify c
              col nam format A31 head 'Queue Owner and Name'
              col table_name format A30 head 'table Name'
              col queue_owner format A20 head 'Queue owner'
              col table_owner format A20 head 'table Owner'
              col rsname format A34 head 'Rule set name'
              col cap format A22 head 'Capture name'
              col ti format A22 head 'Date'
              col lct format A18 head 'Last|Capture time' justify c
              col cmct format A18 head 'Capture|Create time' justify c
              col emct format A18 head 'Last enqueued|Message creation|Time' justify c
              col ltme format A18 head 'Last message|Enqueue time' justify c
              col ect format 999999999 head 'Elapsed|capture|Time' justify c
              col eet format 9999999 head 'Elapsed|Enqueue|Time' justify c
              col elt format 9999999 head 'Elapsed|LCR|Time' justify c
              col tme format 999999999999 head 'Total|Message|Enqueued' justify c
              col tmc format 999999999999 head 'Total|Message|Captured' justify c
              col scn format 999999999999 head 'Scn' justify c
              col emn format 999999999999 head 'Enqueued|Message|Number' justify c
              col cmn format 999999999999 head 'Captured|Message|Number' justify c
              col lcs format 999999999999 head 'Last scn|Scanned' justify c
              col AVAILABLE_MESSAGE_NUMBER format 999999999999 head 'Last system| scn'  justify c
              col capture_user format A20 head 'Capture user'
              col ncs format 999999999999 head 'Captured|Start scn' justify c
              col capture_type format A10 head 'Capture |Type'
              col RULE_SET_NAME format a15 head "Rule set Name"
              col NEGATIVE_RULE_SET_NAME format a15 head "Neg rule set"
              col status format A8 head 'Status'
              
              -- For each table in APPLY site, given by this query
              col SOURCE_DATABASE format a30
              set linesize 150
              select distinct SOURCE_DATABASE,
                     source_object_owner||'.'||source_object_name own_obj,
                     SOURCE_OBJECT_TYPE objt, instantiation_scn, IGNORE_SCN,
                     apply_database_link lnk
              from  DBA_APPLY_INSTANTIATED_OBJECTS order by 1,2;
               
              -- do
              execute DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(  source_object_name=> 'owner.table', 
                         source_database_name => 'source_database' ,  instantiation_scn => NULL );
               
              
              -- List instantiation objects at source
              select TABLE_OWNER, TABLE_NAME,SCN, to_char(TIMESTAMP,'DD-MM-YYYY HH24:MI:SS') ti
              from dba_capture_prepared_tables  order by table_owner
              /
              
              col LOGMINER_ID head 'Log|ID'  for 999
              select  LOGMINER_ID, CAPTURE_USER,  start_scn ncs,  to_char(STATUS_CHANGE_TIME,'DD-MM HH24:MI:SS') change_time
                  ,CAPTURE_TYPE,RULE_SET_NAME, negative_rule_set_name , status from dba_capture
              order by logminer_id
              /
              
              set lines 190
              col rsname format a22 head 'Rule set name'
              col delay_scn head 'Delay|Scanned' justify c
              col delay2 head 'Delay|Enq-Applied' justify c
              col state format a24
              col process_name format a8 head 'Process|Name' justify c
              col LATENCY_SECONDS head 'Lat(s)'
              col total_messages_captured head 'total msg|Captured'
              col total_messages_enqueued head 'total msg|Enqueue'
              col ENQUEUE_MESG_TIME format a17 head 'Row creation|initial time'
              col CAPTURE_TIME head 'Capture at'
              select a.logminer_id , a.CAPTURE_NAME cap, queue_name , AVAILABLE_MESSAGE_NUMBER, CAPTURE_MESSAGE_NUMBER lcs,
                    AVAILABLE_MESSAGE_NUMBER-CAPTURE_MESSAGE_NUMBER delay_scn,
                    last_enqueued_scn , applied_scn las , last_enqueued_scn-applied_scn delay2
                    from dba_capture a, v$streams_capture b where a.capture_name = b.capture_name (+)
              order by logminer_id
              /
              
              SELECT c.logminer_id,
                       SUBSTR(s.program,INSTR(s.program,'(')+1,4) PROCESS_NAME,
                       c.sid,
                       c.serial#,
                       c.state,
                       to_char(c.capture_time, 'HH24:MI:SS MM/DD/YY') CAPTURE_TIME,
                       to_char(c.enqueue_message_create_time,'HH24:MI:SS MM/DD/YY') ENQUEUE_MESG_TIME ,
                      (SYSDATE-c.capture_message_create_time)*86400 LATENCY_SECONDS,
                      c.total_messages_captured,
                      c.total_messages_enqueued
                 FROM V$STREAMS_CAPTURE c, V$SESSION s
                 WHERE c.SID = s.SID
                AND c.SERIAL# = s.SERIAL#
              order by logminer_id ;
              
              
               
              -- Which is lowest requeired archive : 
              -- this query assume you have only one logminer_id or your must add ' and session# = <id_nn>  '
               
              set serveroutput on
              DECLARE
               hScn number := 0;
               lScn number := 0;
               sScn number;
               ascn number;
               alog varchar2(1000);
              begin
                select min(start_scn), min(applied_scn) into sScn, ascn
                  from dba_capture ;
               
                DBMS_OUTPUT.ENABLE(2000);
               
                for cr in (select distinct(a.ckpt_scn)
                           from system.logmnr_restart_ckpt$ a
                           where a.ckpt_scn <= ascn and a.valid = 1 
                             and exists (select * from system.logmnr_log$ l
                                 where a.ckpt_scn between l.first_change# and
                                   l.next_change#)
                            order by a.ckpt_scn desc)
                loop
                  if (hScn = 0) then
                     hScn := cr.ckpt_scn;
                  else
                     lScn := cr.ckpt_scn;
                     exit;
                  end if;
                end loop;
               
                if lScn = 0 then
                  lScn := sScn;
                end if;
                 -- select min(name) into alog from v\$archived_log where lScn between first_change# and next_change#;
                -- dbms_output.put_line('Capture will restart from SCN ' || lScn ||' in log '||alog);
                  dbms_output.put_line('Capture will restart from SCN ' || lScn ||' in the following file:');
                 for cr in (select name, first_time  , SEQUENCE#
                             from DBA_REGISTERED_ARCHIVED_LOG
                             where lScn between first_scn and next_scn order by thread#)
                loop
               
                   dbms_output.put_line(to_char(cr.SEQUENCE#)|| ' ' ||cr.name||' ('||cr.first_time||')');
                end loop;
              end;
              / 
               
               
              -- List all archives
               
              prompt If 'Name' is empty then the archive is not on disk anymore
              prompt
              set linesize 150 pagesize 0 heading on embedded on
              col name          form A55 head 'Name' justify l
              col st    form A14 head 'Start' justify l
              col end    form A14 head 'End' justify l
              col NEXT_CHANGE#   form 9999999999999 head 'Next Change' justify c
              col FIRST_CHANGE#  form 9999999999999 head 'First Change' justify c
              col SEQUENCE#     form 999999 head 'Logseq' justify c
               
              select thread#, SEQUENCE# , to_char(FIRST_TIME,'MM-DD HH24:MI:SS') st,
                     to_char(next_time,'MM-DD HH24:MI:SS') End,FIRST_CHANGE#,
                     NEXT_CHANGE#, NAME name
                      from ( select thread#, SEQUENCE# , FIRST_TIME, next_time,FIRST_CHANGE#,
                               NEXT_CHANGE#, NAME name
                               from v$archived_log  order by first_time desc  )
                      where rownum <= 30
              / 
               
              
              --
              -- APPLY status
              
              
              col apply_tag format a8 head 'Apply| Tag'
              col QUEUE_NAME format a24
              col DDL_HANDLER format a20
              col MESSAGE_HANDLER format a20
              col NEGATIVE_RULE_SET_NAME format a20 head 'Negative|rule set'
              col apply_user format a20
              col uappn format A30 head "Apply name"
              col queue_name format A30 head "Queue name"
              col apply_captured format A14 head "Type of|Applied Events" justify c
              col rsn format A24 head "Rule Set name"
              col sts format A8 head "Apply|Process|Status"
              col apply_tag format a8 head 'Apply| Tag'
              col QUEUE_NAME format a24
              col DDL_HANDLER format a20
              col MESSAGE_HANDLER format a20
              col NEGATIVE_RULE_SET_NAME format a20 head 'Negative|rule set'
              col apply_user format a20
              
              
              
              set linesize 150
              
                select apply_name uappn, queue_owner, DECODE(APPLY_CAPTURED, 'YES', 'Captured', 'NO',  'User-Enqueued') APPLY_CAPTURED,
                     RULE_SET_NAME rsn , apply_tag, STATUS sts  from dba_apply;
              
                select QUEUE_NAME,DDL_HANDLER,MESSAGE_HANDLER, NEGATIVE_RULE_SET_NAME, APPLY_USER, ERROR_NUMBER,
                       to_char(STATUS_CHANGE_TIME,'DD-MM-YYYY HH24:MI:SS')STATUS_CHANGE_TIME
                from dba_apply ;
              
              set head off
              select  ERROR_MESSAGE from  dba_apply;
              
              -- propagation status
              
              set head on
              col rsn format A28 head "Rule Set name"
              col rn format A30 head "Rule name"
              col rt format A64 head "Rule text"
              
              col d_dblk format A40 head 'Destination dblink'
              col nams format A41 head 'Source queue'
              col namd format A66 head 'Remote queue'
              col prop format A40 head 'Propagation name '
              col rsname format A20 head 'Rule set name'
              COLUMN TOTAL_TIME HEADING 'Total Time Executing|in Seconds' FORMAT 999999
              COLUMN TOTAL_NUMBER HEADING 'Total Events Propagated' FORMAT 999999999
              COLUMN TOTAL_BYTES HEADING 'Total mb| Propagated' FORMAT 9999999999
              COL PROPAGATION_NAME format a26
              COL SOURCE_QUEUE_NAME format a34 head "Source| queue name" justify c
              COL DESTINATION_QUEUE_NAME format a24 head "Destination| queue name" justify c
              col QUEUE_TO_QUEUE format a9 head "Queue to| Queue"
              col RULE_SET_NAME format a18
              
              set linesize 125
              prompt
              set lines 190
              select PROPAGATION_NAME prop,  RULE_SET_NAME rsname , nvl(DESTINATION_DBLINK,'Local to db') d_dblk,NEGATIVE_RULE_SET_NAME
                            from dba_propagation ;
                select SOURCE_QUEUE_OWNER||'.'|| SOURCE_QUEUE_NAME nams , DESTINATION_QUEUE_OWNER||'.'|| DESTINATION_QUEUE_NAME||
                        decode( DESTINATION_DBLINK,null,'','@'|| DESTINATION_DBLINK) namd, status , QUEUE_TO_QUEUE
                            from dba_propagation ;
              
              
              -- Archive numbers
              
              set linesize 150 pagesize 0 heading on embedded on
              col name          form A55 head 'Name' justify l
              col st    form A14 head 'Start' justify l
              col end    form A14 head 'End' justify l
              col NEXT_CHANGE#   form 9999999999999 head 'Next Change' justify c
              col FIRST_CHANGE#  form 9999999999999 head 'First Change' justify c
              col SEQUENCE#     form 999999 head 'Logseq' justify c
              
              select thread#, SEQUENCE# , to_char(FIRST_TIME,'MM-DD HH24:MI:SS') st,
                     to_char(next_time,'MM-DD HH24:MI:SS') End,FIRST_CHANGE#,
                     NEXT_CHANGE#, NAME name
                      from ( select thread#, SEQUENCE# , FIRST_TIME, next_time,FIRST_CHANGE#,
                               NEXT_CHANGE#, NAME name
                               from v$archived_log  order by first_time desc  )
                      where rownum <= 30
              /
              
              -- List queues
              
              col queue_table format A26 head 'Queue Table'
              col queue_name format A32 head 'Queue Name'
              col primary_instance format 9999 head 'Prim|inst'
              col secondary_instance format 9999 head 'Sec|inst'
              col owner_instance format 99 head 'Own|inst'
              COLUMN MEM_MSG HEADING 'Messages|in Memory' FORMAT 99999999
              COLUMN SPILL_MSGS HEADING 'Messages|Spilled' FORMAT 99999999
              COLUMN NUM_MSGS HEADING 'Total Messages|in Buffered Queue' FORMAT 99999999
              select
                   a.owner||'.'|| a.name nam, a.queue_table,
                            decode(a.queue_type,'NORMAL_QUEUE','NORMAL', 'EXCEPTION_QUEUE','EXCEPTION',a.queue_type) qt,
                            trim(a.enqueue_enabled) enq, trim(a.dequeue_enabled) deq, (NUM_MSGS - SPILL_MSGS) MEM_MSG, spill_msgs, x.num_msgs msg,
                            x.INST_ID owner_instance
                            from dba_queues a , sys.gv_$buffered_queues x
                      where
                             a.qid = x.queue_id (+) and a.owner not in ( 'SYS','SYSTEM','WMSYS','SYSMAN')  order by a.owner ,qt desc
              /
              
              
              
              -- List instantiated objects
              
              set linesize 150
              select distinct SOURCE_DATABASE,
                     source_object_owner||'.'||source_object_name own_obj,
                     SOURCE_OBJECT_TYPE objt, instantiation_scn, IGNORE_SCN,
                     apply_database_link lnk
              from  DBA_APPLY_INSTANTIATED_OBJECTS
              order by 1,2;
              
              
              -- Propagation senders : to run on source site
              
              prompt
              prompt ++ EVENTS AND BYTES PROPAGATED FOR EACH PROPAGATION++
              prompt
              COLUMN Elapsed_propagation_TIME HEADING 'Elapsed |Propagation Time|(Seconds)' FORMAT 9999999999999999
              COLUMN TOTAL_NUMBER HEADING 'Total Events|Propagated' FORMAT 9999999999999999
              COLUMN SCHEDULE_STATUS HEADING 'Schedule|Status'
              column elapsed_dequeue_time HEADING 'Total Dequeue|Time (Secs)'
              column elapsed_propagation_time HEADING 'Total Propagation|Time (Secs)' justify c
              column elapsed_pickle_time HEADING 'Total Pickle| Time(Secs)' justify c
              column total_time HEADING 'Elapsed|Pickle Time|(Seconds)' justify c
              column high_water_mark HEADING 'High|Water|Mark'
              column acknowledgement HEADING 'Target |Ack'
              prompt pickle : Pickling is the action of building the messages, wrap the LCR before enqueuing
              prompt
              set linesize 150
              SELECT p.propagation_name,q.message_delivery_mode queue_type, DECODE(p.STATUS,
                              'DISABLED', 'Disabled', 'ENABLED', 'Enabled') SCHEDULE_STATUS, q.instance,
                              q.total_number TOTAL_NUMBER, q.TOTAL_BYTES/1048576 total_bytes,
                              q.elapsed_dequeue_time/100 elapsed_dequeue_time, q.elapsed_pickle_time/100 elapsed_pickle_time,
                              q.total_time/100 elapsed_propagation_time
                FROM  DBA_PROPAGATION p, dba_queue_schedules q
                      WHERE   p.DESTINATION_DBLINK = NVL(REGEXP_SUBSTR(q.destination, '[^@]+', 1, 2), q.destination)
                AND q.SCHEMA = p.SOURCE_QUEUE_OWNER
                AND q.QNAME = p.SOURCE_QUEUE_NAME
                order by q.message_delivery_mode, p.propagation_name;
              
              
              -- propagation receiver : to run on apply site
               COLUMN SRC_QUEUE_NAME HEADING 'Source|Queue|Name' FORMAT A20
              COLUMN DST_QUEUE_NAME HEADING 'Target|Queue|Name' FORMAT A20
              COLUMN SRC_DBNAME HEADING 'Source|Database' FORMAT A15
              COLUMN ELAPSED_UNPICKLE_TIME HEADING 'Unpickle|Time' FORMAT 99999999.99
              COLUMN ELAPSED_RULE_TIME HEADING 'Rule|Evaluation|Time' FORMAT 99999999.99
              COLUMN ELAPSED_ENQUEUE_TIME HEADING 'Enqueue|Time' FORMAT 99999999.99
              
              SELECT SRC_QUEUE_NAME,
                     SRC_DBNAME,DST_QUEUE_NAME,
                     (ELAPSED_UNPICKLE_TIME / 100) ELAPSED_UNPICKLE_TIME,
                     (ELAPSED_RULE_TIME / 100) ELAPSED_RULE_TIME,
                     (ELAPSED_ENQUEUE_TIME / 100) ELAPSED_ENQUEUE_TIME, TOTAL_MSGS,HIGH_WATER_MARK
                FROM V$PROPAGATION_RECEIVER;
              
              -- Apply reader 
              
              col rsid format 99999 head "Reader|Sid" justify c
              COLUMN CREATION HEADING 'Message|Creation time' FORMAT A17 justify c
              COLUMN LATENCY HEADING 'Latency|in|Seconds' FORMAT 9999999
              col deqt format A15 head "Last|Dequeue Time" justify c
              
              SELECT APPLY_NAME, sid rsid , (DEQUEUE_TIME-DEQUEUED_MESSAGE_CREATE_TIME)*86400 LATENCY,
                      TO_CHAR(DEQUEUED_MESSAGE_CREATE_TIME,'HH24:MI:SS MM/DD') CREATION, TO_CHAR(DEQUEUE_TIME,'HH24:MI:SS MM/DD') deqt,
                      DEQUEUED_MESSAGE_NUMBER  FROM V$STREAMS_APPLY_READER;
              
              
              -- Do we have any library cache lock ?
              
              set head on pause off feed off linesize 150
              
              column event format a48 head "Event type"
              column wait_time format 999999 head "Total| waits"
              column seconds_in_wait format 999999 head " Time waited "
              column sid format 9999 head "Sid"
              column state format A17 head "State"
              col seq# format 999999
              
              select
                w.sid, s.status,w.seq#, w.event
                , w.wait_time, w.seconds_in_wait , w.p1 , w.p2 , w.p3 , w.state
              from
                v$session_wait w , v$session s
              where
                s.sid = w.sid and
                w.event != 'pmon timer'                  and
                w.event != 'rdbms ipc message'           and
                w.event != 'PL/SQL lock timer'           and
                 w.event != 'SQL*Net message from client' and
                w.event != 'client message'              and
                w.event != 'pipe get'                    and
                w.event != 'Null event'                  and
                w.event != 'wakeup time manager'         and
                w.event != 'slave wait'                  and
                w.event != 'smon timer'
                and w.event != 'class slave wait'
                and w.event != 'LogMiner: wakeup event for preparer'
                and w.event != 'Streams AQ: waiting for time management or cleanup tasks'
                and w.event != 'LogMiner: wakeup event for builder'
                and w.event != 'Streams AQ: waiting for messages in the queue'
                and w.event != 'ges remote message'
                and w.event != 'gcs remote message'
                and w.event != 'Streams AQ: qmn slave idle wait'
                and w.event != 'Streams AQ: qmn coordinator idle wait'
                and w.event != 'ASM background timer'
                and w.event != 'DIAG idle wait' 
                and w.seconds_in_wait > 0 
              /