5
Create a Snapshot Group

This chapter illustrates how to create a snapshot group at a remote snapshot replication site. The following topics will be discussed:

• Creating a Snapshot Group Overview

• Create Snapshot Group

Creating a Snapshot Group Overview

After you have setup your snapshot site (see Chapter 2, "Create Replication Site") and have created at least one master group (see Chapter 3, "Create a Master Group"), you are ready to begin creating a snapshot group at the remote snapshot site. Figure 5-1 illustrates the process of creating a snapshot group.

Figure 5-1 Setup Snapshot Group

Create Snapshot Group

/*************************************************************************
STEP 1:
CREATE SNAPSHOT LOGS AT MASTER SITE

See the CREATE SNAPSHOT LOG in the Oracle8i SQL Reference for 
detailed information about this SQL statement.
*************************************************************************/

--If you want one of your master sites to support a snapshot site, then 
--you need to create snapshot logs for each master table that will be 
--replicated to a snapshot. If you'll recall from Figure  2-1,
--ORC1.WORLD will serve as the target master site for the SNAP1.WORLD 
--snapshot site. The required snapshot logs need to be created at ORC1.WORLD.

CONNECT scott/tiger@orc1.world

CREATE SNAPSHOT LOG ON scott.emp;
CREATE SNAPSHOT LOG ON scott.dept;
CREATE SNAPSHOT LOG ON scott.bonus;
CREATE SNAPSHOT LOG ON scott.salgrade;


/*************************************************************************
STEP 2:
CREATE REPLICATED SCHEMA AND LINKS
*************************************************************************/

--Before you begin building your snapshot group, you must make sure that
--the replicated schema exists at the remote snapshot site and that the
--necessary database links have been created.

CONNECT system/manager@snap1.world

CREATE USER scott IDENTIFIED BY tiger;
GRANT connect, resource TO scott;

CONNECT scott/tiger@snap1.world

--The owner of the snapshots will need a database link pointing to the 
--proxy_refresher that was created when the snapshot site was setup; see 
--"CREATE MASTER SITE USERS" for information.

CREATE DATABASE LINK orc1.world 
   CONNECT TO proxy_refresher IDENTIFIED BY proxy_refresher;

/*************************************************************************
STEP 3:
CREATE SNAPSHOT GROUP
*************************************************************************/

--The following procedures must be executed by the snapshot administrator
--at the remote snapshot site.

CONNECT snapadmin/snapadmin@snap1.world

--The master group that you specify in the GNAME parameter must match the
--name of the master group that you are replicating at the target master site.

BEGIN
   DBMS_REPCAT.CREATE_SNAPSHOT_REPGROUP (
      gname => 'SCOTT_MG',
      master => 'ORC1.WORLD',
      propagation_mode => 'ASYNCHRONOUS');
END;
/

/*************************************************************************
STEP 4:
CREATE REFRESH GROUP
*************************************************************************/

--All snapshots that are added to a particular refresh group will be 
--refreshed at the same time. This ensures transactional consistency
--between the related snapshots in the refresh group.

BEGIN
   DBMS_REFRESH.MAKE (
      name => 'SNAPADMIN.SCOTT_RG',
      list => '', 
      next_date => SYSDATE, 
      interval => 'sysdate + 1/24',
      implicit_destroy => FALSE, 
      rollback_seg => '',
      push_deferred_rpc => TRUE, 
      refresh_after_errors => FALSE);
END;
/

/*************************************************************************
STEP 5:
ADD OBJECTS TO SNAPSHOT GROUP
*************************************************************************/

BEGIN
   DBMS_REPCAT.CREATE_SNAPSHOT_REPOBJECT (
      gname => 'SCOTT_MG',
      sname => 'SCOTT',
      oname => 'BONUS',
      type => 'SNAPSHOT',
      ddl_text => 'create snapshot SCOTT.BONUS refresh fast with 
                   primary key for update as select * from
                   SCOTT.BONUS@orc1.WORLD',
      min_communication => TRUE);
END;
/

BEGIN
   DBMS_REPCAT.CREATE_SNAPSHOT_REPOBJECT (
      gname => 'SCOTT_MG',
      sname => 'SCOTT',
      oname => 'DEPT',
      type => 'SNAPSHOT',
      ddl_text => 'create snapshot SCOTT.DEPT refresh fast with 
                   primary key for update as select * from
                   SCOTT.DEPT@orc1.WORLD',
      min_communication => TRUE);
END;
/

BEGIN
   DBMS_REPCAT.CREATE_SNAPSHOT_REPOBJECT (
      gname => 'SCOTT_MG',
      sname => 'SCOTT',
      oname => 'EMP',
      type => 'SNAPSHOT',
      ddl_text => 'create snapshot SCOTT.EMP refresh fast with 
                   primary key for update as select * from
                   SCOTT.EMP@orc1.WORLD',
      min_communication => TRUE);
END;
/

BEGIN
   DBMS_REPCAT.CREATE_SNAPSHOT_REPOBJECT (
      gname => 'SCOTT_MG',
      sname => 'SCOTT',
      oname => 'SALGRADE',
      type => 'SNAPSHOT',
      ddl_text => 'create snapshot SCOTT.SALGRADE refresh fast with 
                   primary key for update as select * from
                   SCOTT.SALGRADE@orc1.WORLD',
      min_communication => TRUE);
END;
/

/*************************************************************************
STEP 6:
ADD OBJECTS TO REFRESH GROUP
*************************************************************************/

--Each of the snapshot group objects that you add to the refresh group
--will be refreshed at the same time to preserve referential integrity
--between related snapshots.

BEGIN
   DBMS_REFRESH.ADD (
      name => 'SNAPADMIN.SCOTT_RG',
      list => 'SCOTT.BONUS',
      lax => TRUE);
END;
/

BEGIN
   DBMS_REFRESH.ADD (
      name => 'SNAPADMIN.SCOTT_RG',
      list => 'SCOTT.dept',
      lax => TRUE);
END;
/

BEGIN
   DBMS_REFRESH.ADD (
      name => 'SNAPADMIN.SCOTT_RG',
      list => 'SCOTT.emp',
      lax => TRUE);
END;
/

BEGIN
   DBMS_REFRESH.ADD (
      name => 'SNAPADMIN.SCOTT_RG',
      list => 'SCOTT.salgrade',
      lax => TRUE);
END;
/

---