Topic:            7.3 Sample Script To Setup Updatable Snapshots
Author:            FTODD
Modified:           13 Dec 96 



7.3 Updatable Snapshot Example
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Verified on RDBMS 7.3.2.2

Recommendations
~~~~~~~~~~~~~~~
We recommend that you read the relevant information:
Oracle7 Server Distributed Systems Volume II (Replicated Data) manual,
Snapshot Replication chapter.

You should also attend the Oracle Symmetric Replication course if you
have not already done so.

Explanation
~~~~~~~~~~~
This script is an example setup script for 7.3 updatable snapshots.
It firstly sets up the replicated environment on the master site (where
the master table resides, then sets up the snapshot site. The snapshot
site, snapshots data from the master site and replicates changes from the
snapshot back to the master site.

STEP 1: Script to create the master site
        Executed at master site rep1.world.

connect sys/manager

grant connect,resource to repsys1 identified by repsys1;

execute dbms_repcat_auth.grant_surrogate_repcat('repsys1');

create public database link rep2.world using 't_rep2';

create database link rep2.world connect to repsys1 identified by repsys1
using 't_rep2';

grant connect,resource to repadmin1 identified by repadmin1;

execute dbms_repcat_admin.grant_admin_any_repgroup(userid=>'repadmin1');

grant connect, resource to USER1 identified by USER1;

execute dbms_repcat_admin.grant_admin_repgroup(userid=>'USER1');

grant execute on dbms_defer to USER1;

connect repadmin1/repadmin1;

drop database link rep2.world;
create database link rep2.world
connect to repadmin1 identified by repadmin1 using 't_rep2';

connect USER1/USER1;
drop database link rep2.world;
create database link rep2.world
connect to USER1 identified by USER1 using 't_rep2';

-- To setup user1 DEPT table, run
 
@?/sqlplus/demo/demobld.sql

connect repadmin1/repadmin1
execute dbms_repcat.create_master_repgroup('GROUP1');
execute dbms_repcat.create_master_repobject('USER1','DEPT','TABLE',gname=>
'GROUP1');
execute dbms_repcat.generate_replication_support('USER1','DEPT','TABLE');

connect USER1/USER1
select sname,oname,type,status,gname from user_repobject;

--    SNAME           ONAME           TYPE            STATUS     GNAME
--    --------------- --------------- --------------- ---------- ----------
--    USER1           DEPT            TABLE           VALID      GROUP1
--    USER1           DEPT$RP         PACKAGE         VALID      GROUP1
--    USER1           DEPT$RP         PACKAGE BODY    VALID      GROUP1
--    USER1           DEPT$RR         PACKAGE         VALID      GROUP1
--    USER1           DEPT$RR         PACKAGE BODY    VALID      GROUP1
--    USER1           DEPT$RT         TRIGGER         VALID      GROUP1
--    USER1           DEPT$TP         PACKAGE         VALID      GROUP1
--    USER1           DEPT$TP         PACKAGE BODY    VALID      GROUP1

connect user1/user1;
create snapshot log on dept;

STEP 2: Script to Create the Snapshot Site
        Executed at snapshot site - rep2.world.

connect sys/manager

grant connect,resource to repsys1 identified by repsys1;

execute dbms_repcat_auth.grant_surrogate_repcat('repsys1');

create public database link rep1.world using 't_rep';

create database link rep1.world connect to repsys1 identified by repsys1 using 
't_rep';

grant connect,resource to repadmin1 identified by repadmin1;

execute dbms_repcat_admin.grant_admin_any_repgroup(userid=>'repadmin1');

grant connect, resource to USER1 identified by USER1;

execute dbms_repcat_admin.grant_admin_repgroup(userid=>'USER1');

grant execute on dbms_defer to USER1;

connect repadmin1/repadmin1;
drop database link rep1.world;
create database link rep1.world
connect to repadmin1 identified by repadmin1 using 't_rep';

connect USER1/USER1
drop database link rep1.world
create database link rep1.world
connect to USER1 identified by USER1 using 't_rep1';

execute dbms_repcat.create_snapshot_repgroup('USER1','rep1.world');

drop table dept;
drop snapshot dept;
drop view dept;

execute dbms_repcat.create_snapshot_repobject('USER1','DEPT','SNAPSHOT', 
'create snapshot dept refresh fast start with sysdate next sysdate+(1/144) 
for update as select * from dept@rep1.world',gname=>'GROUP1');

select * from USER1.dept;
select * from USER1.dept@rep1.world;

select sname,oname,type,status,gname from user_repobject;

--   SNAME           ONAME           TYPE            STATUS     GNAME
--   --------------- --------------- --------------- ---------- ----------
--   USER1           DEPT            SNAPSHOT        VALID      GROUP1
--   USER1           DEPT$RP         PACKAGE         VALID      GROUP1
--   USER1           DEPT$RT         TRIGGER         VALID      GROUP1
--   USER1           DEPT$TP         PACKAGE         VALID      GROUP1
--   USER1           DEPT$TP         PACKAGE BODY    VALID      GROUP1

select object_name,object_type,status from user_objects
where object_name like '%LOG%';

-- OBJECT_NAME          OBJECT_TYPE   STATUS
-- -------------------- ------------- ----------
-- USLOG$_DEPT          TABLE         VALID

STEP 3:
Executed at master site rep1.world.
connect repadmin1/repadmin1

   select * from dba_repcat;   
   execute DBMS_REPCAT.RESUME_MASTER_ACTIVITY('GROUP1',true);

   -- verify a status of NORMAL
   select * from dba_repcat;   

Executed at snapshot site rep2.world.
connect repadmin1/repadmin1

   -- verify a status of NORMAL
   select * from dba_repcat;

STEP 4: Make changes at snapshot site

connect USER1/USER1
insert into user1.dept values(90,'ADMIN','LONDON');
commit;
select * from user1.dept;
select * from user1.dept@rep1.world;

connect repadmin1/repadmin1
select * from deftran;
select * from defcall;
execute DBMS_DEFER_SYS.EXECUTE('rep1.world');
select * from deftran;
select * from defcall;
select * from user1.dept;
select * from user1.dept@rep1.world;

STEP 5: Make changes at master site  

connect USER1/USER1
delete from user1.dept where deptno = 10;
commit;
select * from user1.dept;
select * from user1.dept@rep2.world;

Wait to see change in 10 minutes time or:
execute dbms_snapshot.refresh('user1.dept','?');