Replication fast refresh doesn't work on db link?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Replication fast refresh doesn't work on db link?

  1. #1
    Join Date
    Oct 2002
    Location
    Breda, The Netherlands
    Posts
    317

    Replication fast refresh doesn't work on db link?

    Hello,

    I have a test-setup for replication : two db's : one production-db and one replication db. Both db's are on the same node, the final setup will be on two nodes. Oracle version: 8.1.7.4.9

    I created a snapshot-site and a master site. They are connected via a db-link. I use simple select-statements in the snapshots to read the data:
    SELECT * FROM [schema].[tablename]@[sid.company.intra];
    So there are no joins or functions in there.

    After creating snapshots and snapshot logs, I refreshed them with the complete-clause. It works. But the amount of data I have to replicate orders me to refresh with the fast-clause, but this doesn't work.

    I don't receive any ORA's when I run
    - EXECUTE DBMS_MVIEW.REFRESH('[table]','C'); # complete refresh
    or
    - EXECUTE DBMS_MVIEW.REFRESH('[table]','F'); # fast refresh

    A "SELECT MVIEW_NAME,LAST_REFRESH_TYPE,LAST_REFRESH_DATE FROM DBA_MVIEWS" returns "COMPLETE" as refresh type. Even if I refresh fast! Also the date remains the date of yesterday.

    Then: I created a master table, snapshot & snapshot log in one and the same db. And the fast refresh works great!!

    So: the problem lies in the fact that the master table is in a different db? Or because I'm using a db-link?

    I visited Metalink, but there was no sollution. The manual states there are many rules that make the fast refresh impossible, but I don't know what I'm doing wrong here...

    Help would be very appreciated.

    Erik
    Last edited by efrijters; 06-19-2003 at 07:14 AM.
    An expert is one who knows more and more about less and less until he knows absolutely everything about nothing.

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    The concept definitly works over a database link. Since this is a test environment, I would:
    1. insert some data on the master site
    2. check both the master and the mview for the new data (should only be at the master)
    3. perform my fast refresh
    4. check mview for the new data.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Oct 2002
    Location
    Breda, The Netherlands
    Posts
    317
    My method of replication testing:

    1. execute from within the replication db:
    "select 'replication' db, name from schema.emp where employee_id=1
    union
    select 'master' db, name from schema.emp@master where employee_id=1"

    This displays both values of a specific row in both db's.

    2. Update the master value;
    "update schema.emp@master set name='TEST!' where employee_id=1"

    3. execute a refresh
    "EXECUTE DBMS_MVIEW.REFRESH('schema.emp','F')"
    where F=Fast; C=Complete.

    4. Execute te statement under point 1 to see the result

    5. Execute to see the used refresh-type:
    "select mview_name,last_refresh_type from all_mviews"

    The result via a db link is alway 'COMPLETE', when replicating within one db, the result is always 'FAST'!


    Hmmm...When I create a snapshot that refreshes on commit, I receive a ORA-01031 Insufficient privileges (even as SYS)

    Can someone please help me with this fast refresh problem? Metalink has documents about this, but all doc's are about complicated replication environments.
    I only want to duplicate data to a different db...

    Thanks in advance.

    Erik
    Last edited by efrijters; 06-19-2003 at 11:28 AM.
    An expert is one who knows more and more about less and less until he knows absolutely everything about nothing.

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Sigh...

    So what was your result? Did the snapshot correctly reflect what you thought the data should be?

    Some refreshes just can't be done in FAST mode and instead refresh in COMPLETE mode. I wanted to see if your refresh was successful or not.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    Oct 2002
    Location
    Breda, The Netherlands
    Posts
    317
    In both cases the refresh works okay. The data is refresh perfectly.

    The only problem is, that if you refresh fast, the all_mview-view says that a 'COMPLETE' refresh took place (or isn't he telling the truth? In that case it could be working correct and I'm on a wild goose chase...) Is there a different view that states which type of refresh method was used?? That could verify it for me.

    This is really wierd: I specially updated Oracle to the latest patch level (8.1.7.4.9) because there were bugs with replication.

    The data I must replicate is about 9gig and the tables have multiple indexes. (okay, i can recreate indexes on the replicatie-db, I know)

    Is there another way to replicate the data? I thought that snapshots would do the trick. The replication data is only for reporting. But the reports need data that is as current as it can be, so many refreshes during the day will be needed.

    I only have tomorrow to solve this problem... oh-ohhh There goes my evening...

    Thanks sofar, but keep those comments comming!!

    Erik
    Last edited by efrijters; 06-19-2003 at 12:04 PM.
    An expert is one who knows more and more about less and less until he knows absolutely everything about nothing.

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    What is the query that you are using to create the mview?
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  7. #7
    Join Date
    Oct 2002
    Location
    Breda, The Netherlands
    Posts
    317
    Create snapshot statement:

    CREATE SNAPSHOT "REPLITEST"."SN_TEST"
    TABLESPACE "DATA01"
    BUILD IMMEDIATE
    USING INDEX TABLESPACE "DATA01"
    REFRESH FAST
    ON DEMAND
    ENABLE QUERY REWRITE
    AS
    select * from appl1.mastertable@masterdb
    An expert is one who knows more and more about less and less until he knows absolutely everything about nothing.

  8. #8
    Join Date
    Oct 2002
    Location
    Breda, The Netherlands
    Posts
    317
    I passed this question to Oracle Support.

    If they find the answer, I'll post it here...
    An expert is one who knows more and more about less and less until he knows absolutely everything about nothing.

  9. #9
    Join Date
    Oct 2002
    Location
    Breda, The Netherlands
    Posts
    317

    Problem resolved

    Hi guys,

    It's been a while, but the problem is solved.

    I had some assistence, this week, from a Oracle Support guy called Bobby. (I issued a TAR at the metalink-site) He called me from India, because the ODC-connection failed. After a while he gave me the solution:

    Hi Erik,

    Like I mentioned to you over the phone, this is a known Bug in 8i , which is fixed in 9i.
    The Bug number is 1679040.
    According to the Bug, both LAST_REFRESH_TYPE and LAST_REFRESH_DATE columns of DBA_MVIEWS / USER_MVIEWS do not get updated.

    The LAST_REFRESH column in DBA_SNAPSHOT_REFRESH_TIMES / USER_SNAPSHOT_REFRESH_TIMES does get updated with the last refresh date/time.
    However, the last refresh type information is not updated in any view.

    Regards,
    Bobby
    So fast refresh works, but the views display the wrong info.

    Thanks go to Bobby for solving the problem and giving Oracle a huge telephone bill

    Case closed!
    An expert is one who knows more and more about less and less until he knows absolutely everything about nothing.

Posting Permissions

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


Click Here to Expand Forum to Full Width