Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2003
    Posts
    46

    Unanswered: Oracle8i Snapshot Replication

    Hi,
    I have set up replication enviroment in the following way:
    One master site
    Two snapshot sites with refresh groups
    When I add records to tables in master site they are replicated properly to snapshot sites.
    if I break connections to snapshot sites from master site then master site is not able to propagate changes from master tables.
    How can I find out which records in master tables are not synchronized with snapshot sites tables ?
    Thanks in advance
    Szalas

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    There are multiple ways ... Look at the defcall and defcalldest ...

    --------------------------------------------------------------------------------
    -- Filename: defcall.sql
    -- Purpose: Reports on all queued calls in defcall
    --------------------------------------------------------------------------------

    col callno heading "Call|No" format 9999
    col deferred_tran_id heading "Deferred|Tran|ID" format a12
    col schemaname heading "Schema|Name" format a8
    col packagename heading "Package|Name" format a25
    col procname heading "Procedure|Name" format a10
    col argcount heading "Arg|Count" format 999
    col dblink heading "Destination" format a17

    SELECT c.callno,
    c.deferred_tran_id,
    c.packagename,
    c.procname,
    c.argcount,
    d.dblink
    FROM defcall c, defcalldest d
    WHERE c.callno = d.callno
    AND c.deferred_tran_id = d.deferred_tran_id
    /

    This should give you some ideas ...

    HTH
    Gregg

  3. #3
    Join Date
    Apr 2002
    Location
    California, USA
    Posts
    482
    You have the list of replicated tables already. Use query like this to find the not replicated delta:

    SQL> select * from local-table MINUS select * from remote-table@dblink;


    HTH,

    clio_usa - OCP 8/8i/9i DBA

  4. #4
    Join Date
    Jul 2003
    Posts
    46
    Connection to snapshot sites is broken so your query is useless

  5. #5
    Join Date
    Jul 2003
    Posts
    46
    with querying views i could only get deffered transaction id but not id of not replicated records so it is not solution
    I'm looking for other ideas
    Szalas

  6. #6
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Using the prior posting .... Once you look at defcall and defcalldest you can
    find the deferred trx that have NOT been replicated ... then you can look at the dbms_defer_query.get_call_args procedure to get the actual row and the columns that have changed (what the were and what they are changed to ...)
    Read up on the procedure .....

    EX: Run script and fill in the information ...

    set serveroutput on size 100000
    set verify off
    undef callno
    undef argcnt
    undef tran_db
    undef tran_id

    DECLARE
    vTypes dbms_defer_query.type_ary;
    vVals dbms_defer_query.val_ary;
    indx NUMBER;
    BEGIN
    dbms_defer_query.get_call_args(
    callno => '&&callno',
    startarg => 1,
    argcnt => &&argcnt,
    argsize => 128,
    tran_db => '&&tran_db',
    tran_id => '&&tran_id',
    date_fmt => 'DD-Mon-YYYY HH24:MIS',
    types => vTypes,
    vals => vVals );

    FOR indx IN 1..&&argcnt LOOP
    dbms_output.put_line('Arg '|| indx || ' Value '|| vVals(indx));
    END LOOP;
    END;
    /

    HTH
    Gregg

  7. #7
    Join Date
    Jul 2003
    Posts
    46
    Thank you very much. It works, but I have another problem. These information are only available from snapshot sites (when I modify rows in snapshots tables). Master site does not contain any rows in table defcall (when I modify rows in master tables). It looks like whole replication is made by snapshot sites.
    What is going on ???
    Thanks in advance
    Szalas

  8. #8
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    On the MASTER site, you have "tables" called MLOG$_*xxx where xxx is
    the actual table. These are the snapshot logs. They are purged thru the
    "purge" routine (supposedly...have seen some problems....). The script below shows time and number of rows for them ...

    SELECT TRUNC(SNAPTIME$$),COUNT(*)
    FROM MLOG$_yourtablename
    GROUP BY TRUNC(SNAPTIME$$);

    HTH
    Gregg

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •