Set Up Snapshot Sites

Figure 2-3 Set Up Snapshot Sites

Text description of the illustration createra.gif.

/*************************************************************************
STEP 1:
CONNECT AS SYSTEM AT SNAPSHOT SITE
*************************************************************************/
--You must connect as SYSTEM to the database that you want to 
--set up as a snapshot site.

CONNECT system/manager@snap1.world

/*************************************************************************
STEP 2:
CREATE SNAPSHOT SITE USERS
*************************************************************************/

--Several users need to be created at the snapshot site. These users are:
--  SNAPSHOT ADMINISTRATOR
--  PROPAGATOR
--  REFRESHER

--STEP 2a: CREATE SNAPSHOT ADMINISTRATOR
--The snapshot administrator is responsible for creating and managing
--the snapshot site. Execute the GRANT_ADMIN_ANY_SCHEMA
--procedure to grant the snapshot administrator the appropriate privileges.

create user SNAPADMIN identified by SNAPADMIN;

BEGIN
   DBMS_REPCAT_ADMIN.GRANT_ADMIN_ANY_SCHEMA (
      username => 'SNAPADMIN');
END;
/

--STEP 2b: CREATE PROPAGATOR
--The propagator is responsible for propagating the deferred transaction
--queue to the target master site.

CREATE USER propagator IDENTIFIED BY propagator;

BEGIN
   DBMS_DEFER_SYS.REGISTER_PROPAGATOR (
      username => 'propagator');
END;
/

--STEP 2c: CREATE REFRESHER
--The refresher is responsible for "pulling" changes made to the replicated
--tables at the target master site to the snapshot site.

create user REFRESHER identified by REFRESHER;

GRANT CREATE SESSION TO refresher;
GRANT ALTER ANY SNAPSHOT TO refresher;

/*************************************************************************
STEP 3:
CREATE DATABASE LINKS TO MASTER SITE
*************************************************************************/

--STEP 3A: CREATE PUBLIC DATABASE LINK

CONNECT system/manager@snap1.world

CREATE PUBLIC DATABASE LINK orc1.world USING 'orc1.world';

--STEP 3b: CREATE SNAPSHOT ADMINISTRATOR DATABASE LINK
--You need to create a database link from the snapshot administrator at
--the snapshot site to the proxy snapshot administrator at
--the master site.

CONNECT snapadmin/snapadmin@snap1.world;

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

--STEP 3c: CREATE PROPAGATOR/RECEIVER DATABASE LINK
--You need to create a database link from the propagator at the
--snapshot site to the receiver at the master site. The receiver was defined
--when you created the master group - see "REGISTER RECEIVER"
--for more information.

CONNECT propagator/propagator@snap1.world

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

/*************************************************************************
STEP 4:
SCHEDULE PURGE AT SNAPSHOT SITE

For additional information about the SCHEDULE_PURGE API, see "SCHEDULE_PURGE 
procedure".
*************************************************************************/

--In order to keep the size of the deferred transaction queue in check,
--you should purge successfully completed deferred transactions. The
--SCHEDULE_PURGE API automates the purge process for you. If your snapshot 
--site only contains "read-only" snapshots, then you do not need to
--execute this procedure.

CONNECT snapadmin/snapadmin@snap1.world

BEGIN
   DBMS_DEFER_SYS.SCHEDULE_PURGE (
   next_date => SYSDATE,
   interval => 'sysdate + 1/24',
   delay_seconds => 0,
   rollback_segment => '');
END;
/

/*************************************************************************
STEP 5:
SCHEDULE PUSH AT SNAPSHOT SITE

For additional information about the SCHEDULE_PUSH API, see "SCHEDULE_PUSH 
procedure".
*************************************************************************/

--The SCHEDULE_PUSH API schedules when the deferred transaction queue 
--should be propagated to the target master site.

CONNECT snapadmin/snapadmin@snap1.world

BEGIN
   DBMS_DEFER_SYS.SCHEDULE_PUSH (
      destination => 'ORC1.WORLD',
      interval => 'sysdate + 1/24',
      next_date => SYSDATE,
      stop_on_error => FALSE,
      delay_seconds => 0,
      parallelism => 0);
END;
/