Re: convert regular snapshots to snapshot groups

From: Paul Brewer <paul_at_paul.brewers.org.uk>
Date: Mon, 22 Jul 2002 20:12:05 +0100
Message-ID: <3d3c59e4_3@mk-nntp-1.news.uk.worldonline.com>

"NetComrade" <andreyNSPAM_at_bookexchange.net> wrote in message news:3d3c3e91.425414403_at_news.globix.com...
> I haven't taken advantage of what snapshot groups have to offer, and
> would like to covert my current snapshots to snapshot groups, w/o
> rebuilding snapshots from scratch. Some of my snapshots are
> incremental (fast) some are full (mostly small tables).
>
> From what I can see so far from the docs, the procedure should be
> something like
>
> --create 'refresh group' via DBMS_REFRESH.MAKE
> --associate my snapshots via DBMS_REFRESH.ADD w/ the snapshot group
> --DBMS_REPCAT.CREATE_SNAPSHOT_REPOBJECT seems unnecessary if snapshots
> have already been built (plus i highly doubt the usefullness of this
> proc since it takes snapshot creation DDL as an arguement and executes
> it)
>
>
> If anybody ever done it, any advice is appreciated.
>
> Thanx.
> .......
> We use Oracle 8.1.7.3 on Solaris 2.7 boxes
> remove NSPAM to email

NetComrade:

I'll try. This is a fairly lengthy post, since there doesn't seem to be much discussion on replication.

If you're only doing 'simple' replication; i.e one-way read-only from master site to snapshot site(s) then as far as I'm aware this is what you need to do:

1. At the master site, create snapshot logs on the master tables (with primary key preferably) for the fast ones. It sounds as though you've already done this, since you already have some fast refresh snapshots, but this advice is for the benefit of others who may be reading, as well as for any who are kind enough to correct or suggest improvements to my methods.
2. Again at the master site, grant select on MY_TABLE and MLOG$_MY_TABLE to the account on the master site which will be used by the snapshot site for its database link.
3. At the snapshot site, create database link to the master site.
4. At the snapshot site, create snapshot MY_TABLE (refresh fast with primary key if it's eligible) as select whatever from SCHEMANAME.MY_TABLE_at_DB_LINK. NB: The schema containing the table at the master site has to be hardcoded; do NOT rely on synonyms.
5. (I hope I haven't lost you: you've probably done this much already): At the snapshot site, use DMBS_REFRESH.MAKE to create your refresh group (this can contain both fast and complete refreshes, always provided all are from the same master site). The idea behind this is to ensure that all members of the group are refreshed at a consistent point relative to one another; thus ensuring logical referential integrity and so forth, if you see what I mean.

You can use DBMS_REFRESH.ADD to add new members (snapshots) to an existing group at any subsequent time as and when the need arises, or indeed you could create an empty refresh group as a start, then add the members one by one if you prefer. It doesn't make a lot of difference.

6 to 8 are suggestions:

6) At this point review xxx_JOBS and (as the snapshot owner) delete any database JOBS which Oracle may or may not have created for you: begin DBMS_JOB.REMOVE(job_id); commit; end;

7) Create a table to act as a log, and a procedure something like:

create or replace procedure my_job as

    v_errmsg varchar2(500);
begin

    dbms_refresh('MY_GROUP');
    insert into my_log values ('My Job','Completed successfully', sysdate);     commit;
exception

    when others then

        v_errmsg := substr(sqlerrm, 1, 500);
        insert into my_log values ('My Job',v_errmsg, sysdate);
        commit;

end my_job;
/

8) Submit a DBMS_JOB to run MY_JOB at the intervals required

That's it. If you're only doing the kind of replication I allude to, then as far as I'm aware you don't need any of the DBMS_REPCAT stuff.

Hope this helps, and if anyone can suggest improvements, suggestions would be welcome.

Regards,
Paul Received on Mon Jul 22 2002 - 14:12:05 CDT