7
Manage Replicated Environment with APIs

This chapter illustrates how to manage your replication environment using the Replication API set. The following topics will be discussed:

Managing Master Sites

As your data delivery needs change due to growth, shrinkage, or emergencies, you are undoubtedly going to need to change the configuration of your replication environment. This section is devoted to managing the master sites of your replication environment, which will help you alter and reconfigure your master sites.

Change Master Definition Site

Many replication administrative tasks can only be performed from the master definition site. Use the DBMS_REPCAT.RELOCATE_MASTERDEF procedure to move the master definition site to another master site. This API is especially useful when the master definition site becomes unavailable and you need to specify a new master definition site (see "Option 2" below).

Option 1

If all master sites are available, complete the following:

Executed As: Replication Administrator

Executed At: Any Master Site

Replication Status: Normal


CONNECT repadmin/repadmin@orc1.world

BEGIN
   DBMS_REPCAT.RELOCATE_MASTERDEF (
      gname => 'SCOTT_MG',
      old_masterdef => 'ORC1.WORLD',
      new_masterdef => 'ORC2.WORLD',
      notify_masters => TRUE,
      include_old_masterdef => TRUE);
END;
/

Option 2

If the old master definition site is NOT available, complete the following:

Executed As: Replication Administrator

Executed At: Any Master Site

Replication Status: Normal

CONNECT repadmin/repadmin@orc3.world

BEGIN
   DBMS_REPCAT.RELOCATE_MASTERDEF (
      gname => 'SCOTT_MG',
      old_masterdef => 'ORC1.WORLD',
      new_masterdef => 'ORC2.WORLD',
      notify_masters => TRUE,
      include_old_masterdef => FALSE);
END;
/

See "RELOCATE_MASTERDEF procedure" for more information on 
using this procedure.

Add a Master Site

As your replicated environment expands, you will need to use the ADD_MASTER_DATABASE procedure to add additional master sites to an existing master group. Executing this procedure will replicate existing master object to the new site.

Before you add a new master site, be sure that you properly setup your new master site for replication. Make sure that you follow the steps described in the "Setup Master Site" section .

Executed As: Replication Administrator

Executed At: Master Definition Site

Replication Status: Quiesced


CONNECT repadmin/repadmin@orc1.world

BEGIN
   DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY (
      gname => 'SCOTT_MG');
END;
/

BEGIN
   DBMS_REPCAT.ADD_MASTER_DATABASE (
      gname => 'SCOTT_MG',
      master => 'ORC4.WORLD',
      use_existing_objects => TRUE,
      copy_rows => TRUE,
      propagation_mode => 'ASYNCHRONOUS');
END;
/

--NOTE: You should wait until the DBA_REPCATLOG view is empty. Execute 
--the following SELECT statement in another SQL*Plus session to monitor 
--the DBA_REPCATLOG view:
--
--SELECT * FROM dba_repsites WHERE gname = 'SCOTT_MG';

BEGIN
   DBMS_REPCAT.RESUME_MASTER_ACTIVITY (
      gname => 'SCOTT_MG');
END;
/

Drop a Master Site

When it becomes necessary to remove a master site from a master group, use the REMOVE_MASTER_DATABASES procedure API to drop one or more master sites.

Executed As: Replication Administrator

Executed At: Master Definition Site

Replication Status: Quiesced


CONNECT repadmin/repadmin@orc1.world

BEGIN
   DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY (
      gname => 'SCOTT_MG');
END;
/
BEGIN
   DBMS_REPCAT.REMOVE_MASTER_DATABASES (
      gname => 'SCOTT_MG',
      master_list => 'ORC4.WORLD');
END;
/

--NOTE: You should wait until the DBA_REPCATLOG view is empty. Execute 
--the following SELECT statement in another SQL*Plus session to monitor 
--the DBA_REPCATLOG view:
--
--SELECT * FROM dba_repcatlog WHERE gname = 'SCOTT_MG';

BEGIN
   DBMS_REPCAT.RESUME_MASTER_ACTIVITY (
      gname => 'SCOTT_MG');
END;
/

Managing Snapshot Sites

Snapshot replication provides you with the flexibility to build data sets to meet the needs of your users, security configuration, and front-end applications. The following two sections will describe how you can create multiple data sets of the same target master group at a single snapshot site. You will also learn how to manually push your snapshot's deferred transaction queue.

Using a Group Owner

Specifying a group owner when you define a new snapshot group and its related objects allows you to create multiple snapshot groups based on the same master group at a single snapshot site. See "Organizational Mechanisms" in Chapter 3 of the Oracle8i Replication manual for a complete discussion on using group owners and the advantages of using multiple data sets.

--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.
--The GOWNER parameter allows you to specify an additional identifier that lets
--you create multiple snapshot groups based on the same master group at the same
--snapshot site.

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

--The GOWNER value used when creating your snapshot obects must match the GOWNER 
--value specified when you created the snapshot group (previous procedure). In 
--this example, you will specify BOB as the group owner to add these objects to 
--the SCOTT_MG snapshot group owned by BOB.

--WARNING: You need to make sure that each object created has a unique name.
--When using a GOWNER to create multiple snapshot groups, duplicate object names
--could become a problem. To avoid any object naming conflicts, you may want to
--append the GOWNER value to the end of the object name that you are create, as
--illustrated in the following procedures (i.e. create snapshot SCOTT.BONUS_bob);
--such a naming method will ensure that you do not create any objects with
--conflicting names.

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

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

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

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

--After you have completed building your snapshot group, you should add your
--snapshots to a refresh group. See Chapter  5, "Create a Snapshot Group" 
--(step 6) for more information about adding snapshots to a refresh group.

Pushing the Deferred Transaction Queue

If you do not automatically propagate the transactions in your deferred transaction queue during the refresh of your snapshot, you will need to complete the following steps to propagate changes made to the updateable snapshot to its master table.

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

CONNECT snapadmin/snapadmin@snap1.world

--Propagation of the deferred transaction queue is based on the destination of 
--the transaction. Execute the following SELECT statement to view the deferred 
--transactions and their destinations (each distinct destination and the number
--of transactions pending for the destination wil be displayed):

SELECT DISTINCT(dblink), COUNT(deferred_tran_id) 
   FROM deftrandest GROUP BY dblink;

--You will need to execute the DBMS_DEFER_SYS.PUSH function for each master
--site that is listed as a destination for a deferred transaction.  

DECLARE
   temp INTEGER;
BEGIN
   temp := DBMS_DEFER_SYS.PUSH (
      DESTINATION => 'orc1.world',
      STOP_ON_ERROR => FALSE,
      DELAY_SECONDS => 0,
      PARALLELISM => 0);
END;
/

--Repeat the above procedure for each destination that was returned in the above 
--SELECT statement.

Dropping Snapshot Sites

There may be many different reasons why you need to drop replication activity at a snapshot site. Perhaps the data requirements have changed or an employee has left the company. In any case, as a DBA you will need to drop the replication support for the target snapshot site.

Drop Snapshot Group Created with Deployment Templates

The process for dropping a snapshot group that was created by instantiating a deployment template at a snapshot site is slightly different than the following methods described in the next couple of sections. Before you drop the snapshot group at the remote snapshot site, you need to execute the DROP_SITE_INSTANTIATION procedure at the target master site for snapshot group. In addition to removing the meta data relating to the snapshot group, this procedure will also remove the related deployment template data regarding this site.

There is a public and a private version of the DROP_SITE_INSTANTIATION procedure. The public version allows the owner of the snapshot group to drop the snapshot site, while the private version allows the replication administrator to drop a snapshot site on behalf of the snapshot group owner.

Public

The following steps are to be performed by owner of the snapshot group.

Executed As: Snapshot Group Owner

Executed At: Master Site for Target Snapshot Site

Replication Status: Normal


CONNECT scott/tiger@orc1.world

--If you need to drop a snapshot site that was instantiated on an Oracle8i Lite
--database, see the Oracle8i Lite documentation for information.

DBMS_REPCAT_INSTANTIATE.DROP_SITE_INSTANTIATION( 
   REFRESH_TEMPLATE_NAME =>  'personnel',
   SITE_NAME => 'snap1.world');
/

--After you have executed the DROP_SITE_INSTANTIATION procedure, you should
--connect to the remote snapshot site and drop the snapshot group (if you are
--not able to connect to the remote snapshot site due to loss or theft, the 
--target snapshot group will not be able to refresh, but the existing data will 
--still remain at the snapshot site).
CONNECT snapadmin/snapadmin@snap1.world

--If you want to physically remove the contents of the snapshot group, be sure
--that you specify TRUE for the DROP_CONTENTS parameter.

DBMS_REPCAT.DROP_SNAPSHOT_REPGROUP (
   GNAME => 'scott_mg',
   DROP_CONTENTS => TRUE);
/

Private

The following steps are to be performed by the replication administrator on behalf of the snapshot group owner.

Executed As: Replication Administrator

Executed At: Master Site for Target Snapshot Site

Replication Status: Normal


CONNECT repadmin/repadmin@orc1.world

--If you need to drop a snapshot site that was instantiated on an Oracle8i Lite
--database, see the Oracle8i Lite documentation for information.

DBMS_REPCAT_RGT.DROP_SITE_INSTANTIATION ( 
   REFRESH_TEMPLATE_NAME =>  'personnel',
   USER_NAME => 'scott',
   SITE_NAME => 'snap1.world');
/

--After you have executed the DROP_SITE_INSTANTIATION procedure, you should
--connect to the remote snapshot site and drop the snapshot group (if you are
--not able to connect to the remote snapshot site due to loss or theft, the 
--target snapshot group will not be able to refresh, but the existing data will 
--still remain at the snapshot site).

CONNECT snapadmin/snapadmin@snap1.world

--If you want to physically remove the contents of the snapshot group, be sure
--that you specify TRUE for the DROP_CONTENTS parameter.

DBMS_REPCAT.DROP_SNAPSHOT_REPGROUP (
   GNAME => 'scott_mg',
   DROP_CONTENTS => TRUE);
/

Drop Snapshot Objects at Snapshot Site

The most secure method of removing replication support for a snapshot site is to physically drop the replicated objects and/or groups at the snapshot site. The following two sections will describe how to drop these objects and groups while connected to the snapshot group.

Ideally, these procedures should be executed while the snapshot is connected to its target master site; a connection will ensure that any related metadata at the master site is removed. If a connection to the master site is not possible, be sure to complete the procedure described in the "Cleanup Master Site" to manually remove the related metadata.

Drop Snapshot Group at Snapshot Site

When it becomes necessary to remove a snapshot group from a snapshot site, use the DROP_SNAPSHOT_REPGROUP procedure to drop a snapshot group. When you execute this procedure and are connected to the target master site, the meta data for the target snapshot group at the master site will be removed (if you are not able to be connected, see "Cleanup Master Site" for more information).

Executed As: Snapshot Administrator

Executed At: Remote Snapshot Site

Replication Status: N/A


CONNECT snapadmin/snapadmin@snap1.world

--If you want to physically remove the contents of the snapshot group, be sure
--that you specify TRUE for the DROP_CONTENTS parameter.

DBMS_REPCAT.DROP_SNAPSHOT_REPGROUP (
   GNAME => 'scott_mg',
   DROP_CONTENTS => TRUE);
/

Drop Individual Snapshot at Snapshot Site

When it becomes necessary to remove an individual snapshot from a snapshot site, use the DROP_SNAPSHOT_REPOBJECT procedure API to drop a snapshot. When you execute this procedure, the meta data for the target snapshot at the master site will be removed. When you execute this procedure and are connected to the target master site, the meta data for the target snapshot group at the master site will be removed (if you are not able to be connected, see "Cleanup Master Site" for more information).

Executed As: Snapshot Administrator

Executed At: Remote Snapshot Site

Replication Status: N/A


CONNECT snapadmin/snapadmin@snap1.world

--If you want to physically remove the contents of the snapshot, be sure
--that you specify TRUE for the DROP_CONTENTS parameter.

DBMS_REPCAT.DROP_SNAPSHOT_REPOBJECT (
   SNAME => 'scott', 
   ONAME => 'bonus', 
   TYPE => 'SNAPSHOT', 
   DROP_OBJECTS => TRUE);
/

Cleanup Master Site

If you are unable to drop snapshot group or snapshot object while connected to the target master site, you will need to manually remove the related metadata at the master site. Cleaning up the metadata will also ensure that you are not needlessly maintaining master table changes to a snapshot log. The following sections will help you cleanup your master site after dropping a snapshot group or object.

Cleanup After Dropping Snapshot Group

If you have executed the steps described in the "Drop Snapshot Group at Snapshot Site" section and were not connected to the master site, you are encouraged to complete the following steps to cleanup the target master site.

Executed As: Replication Administrator

Executed At: Master Site for Target Snapshot Site

Replication Status: Normal


CONNECT repadmin/repadmin@orc1.world

DBMS_REPCAT.UNREGISTER_SNAPSHOT_REPGROUP (
   GNAME => 'scott_mg',
   SNAPSITE => 'snap1.world');
/

--After you unregister the snapshot group, you should purge the snapshot logs
--of the entries that were marked for the target snapshots. The
--PURGE_SNAPSHOT_FROM_LOG procedure will need to be executed for each snapshot 
--that was in the snapshot replication group.

--NOTE: If for some reason unregistering the snapshot group fails, you are still
--encouraged to complete the following steps.

DBMS_SNAPSHOT.PURGE_SNAPSHOT_FROM_LOG (
   SNAPOWNER => 'scott',
   SNAPNAME => 'emp', 
   SNAPSITE => 'snap1.world');
/

DBMS_SNAPSHOT.PURGE_SNAPSHOT_FROM_LOG (
   SNAPOWNER => 'scott',
   SNAPNAME => 'dept', 
   SNAPSITE => 'snap1.world');
/

DBMS_SNAPSHOT.PURGE_SNAPSHOT_FROM_LOG (
   SNAPOWNER => 'scott',
   SNAPNAME => 'bonus', 
   SNAPSITE => 'snap1.world');
/

DBMS_SNAPSHOT.PURGE_SNAPSHOT_FROM_LOG (
   SNAPOWNER => 'scott',
   SNAPNAME => 'salgrade', 
   SNAPSITE => 'snap1.world');
/

Cleanup Individual Snapshot Support at Master Site

If you have executed the steps described in the "Drop Individual Snapshot at Snapshot Site" section and were not connected to the master site, you are encouraged to complete the following steps to cleanup the target master site.

Executed As: Replication Administrator

Executed At: Master Site for Target Snapshot Site

Replication Status: Normal


CONNECT repadmin/repadmin@orc1.world

DBMS_SNAPSHOT.UNREGISTER_SNAPSHOT (
   SNAPOWNER => 'scott',
   SNAPNAME => 'bonus',
   SNAPSITE => 'snap1.world');
/

--After you unregister the snapshot, you should purge the associated snapshot
--log of the entries that were marked for the target snapshots.

--NOTE: If for some reason unregistering the snapshot fails, you are still
--encouraged to complete the following step.

DBMS_SNAPSHOT.PURGE_SNAPSHOT_FROM_LOG (
   SNAPOWNER => 'scott',
   SNAPNAME => 'bonus', 
   SNAPSITE => 'snap1.world');
/

Managing the Error Queue

As an administrator of a replication environment, you should regularly monitor the error queue to determine if any deferred transactions were not successfully applied at the target master site.

To check the error queue, issue the following SELECT statement as the replication administrator when connected to the target master site:

SELECT * FROM deferror;

If the error queue contains errors, you should resolve the error condition and re-execute the deferred transaction. You have two options when re-executing a deferred transaction: you can re-execute in the security context of the user who received the deferred transaction or you can re-execute the deferred transaction with an alternate security context.

Re-execute Error Transaction as the Receiver

The procedure below will re-execute a specified deferred transaction in the security context of the user that received the deferred transaction. This procedure should not be executed until the error situation has been resolved.

Executed As: Replication Administrator

Executed At: Site Containing Errors

Replication Status: Normal


CONNECT repadmin/repadmin@orc2.world

BEGIN
   DBMS_DEFER_SYS.EXECUTE_ERROR (
      deferred_tran_id => '128323',
      destination => 'ORC2.WORLD');
END;
/

Re-execute Error Transaction as Alternate User

The procedure below will re-execute a specified deferred transaction in the security context of the currently connected user. This procedure should not be executed until the error situation has been resolved.

Executed As: Connected User

Executed At: Site Containing Errors

Replication Status: Normal


CONNECT scott/tiger@orc2.world

BEGIN
   DBMS_DEFER_SYS.EXECUTE_ERROR_AS_USER (
      deferred_tran_id => '128323',
      destination => 'ORC2.WORLD');
END;
/

Alter Replicated Object

As your database needs change, you may need to modify the characteristics of your replicated objects. It is important that you do not directly execute DDL to alter your replicated objects; doing so may cause your replicated environment to fail.

Use the DBMS_REPCAT.ALTER_MASTER_REPOBJECT procedure to alter the characteristics of your replicated objects. From the example below, you will see that you simply include the necessary DDL within the procedure call (see the DDL_TEXT parameter).

Executed As: Replication Administrator

Executed At: Master Definition Site

Replication Status: Quiesced


CONNECT repadmin/repadmin@orc1.world

BEGIN
   DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY (
      gname => 'SCOTT_MG');
END;
/
BEGIN
   DBMS_REPCAT.ALTER_MASTER_REPOBJECT (
      sname => 'SCOTT',
      oname => 'EMP',
      type => 'TABLE',
      ddl_text => 'ALTER TABLE scott.emp ADD (site VARCHAR2(20))');
END;
/

--After you have inserted a new column into your replicated object,
--you need to make sure that you re-generate replication support for
--the affected object. This step should be performed immmediately
--after you alter the replicated object.

BEGIN 
    DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
      sname => 'SCOTT',
      oname => 'EMP', 
      type => 'TABLE',
      min_communication => TRUE); 
END;
/

--NOTE: You should wait until the DBA_REPCATLOG view is empty. Execute 
--the following SELECT statement in another SQL*Plus session to monitor 
--the DBA_REPCATLOG view:
--
--SELECT * FROM dba_repcatlog WHERE gname = 'SCOTT_MG';

BEGIN
   DBMS_REPCAT.RESUME_MASTER_ACTIVITY (
      gname => 'SCOTT_MG');
END;
/

Offline Instantiation

Expanding established replicated environments can cause extreme network traffic when you add a new master or snapshot site to your replicated environment. This is caused by propagating the entire contents of the table or snapshot via the network to the new replicated site.

To alleviate such network traffic, you can expand your replicated environment by using the offline instantiation procedure. Offline instantiation takes advantage of Oracle's export and import utilities, which allows you to create an export file and transfer the data to the new site via another storage media (i.e. CD-ROM, tape, etc.).

Master Site

The following script is an example of how to perform an offline instantiation of a master site. This script can potentially save large amounts of network traffic cause by the normal method of adding a new master site to an existing master group.

Executed As: Replication Administrator

Executed At: Master Definition Site and New Master Site

Replication Status: Quiesced and Partial


/*********************************************************************
SETUP NEW MASTER SITE

You need to complete the steps illustrated in the 
"Setup Master Site" section. You will need
to make sure that the appropriate schema and database links have been
created before you perform the offline instantiate of your new master
site. Be sure to create the database links from the new master site to
each of the existing masters sites; you will also need to create a
database link from each of the existing master sites to the new
master site.

After the database links have been created, make sure that you also
define the SCHEDULED LINKS for each of the new database links (STEP 8: 
CREATE SCHEDULED LINKS).
*********************************************************************/

/*********************************************************************
SUSPEND MASTER ACTIVITY

You need to suspend master activity for the existing master sites
before you export your master data and begin the offline instantiation
process.
*********************************************************************/

BEGIN
   DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY (
      gname => 'SCOTT_MG');
END;
/

/*********************************************************************
VERIFY THAT THERE ARE NO PENDING TRANSACTIONS

This includes that you push any outstanding deferred transactions, 
resolve any error transactions, and/or push any administrative 
transactions. This step needs to be performed at each of the existing
master sites.
*********************************************************************/

--connect to master definition site

CONNECT repadmin/repadmin@orc1.world

--Check for error transaction queue.

SELECT * FROM deferror;

--If any deferred transactions have been entered into the error queue, then
--you need to resolve the error situation and then manually re-execute the
--deferred transaction.

BEGIN
   DBMS_DEFER_SYS.EXECUTE_ERROR (
      deferred_tran_id => '128323',
      destination => 'ORC1.WORLD');
END;
/

--Check for outstanding administrative requests.

SELECT * FROM dba_repcatlog;

--If any administrative requests remain, then you can manually push these
--transactions and/or wait for them to be executed automatically. You may need 
--to execute the DBMS_REPCAT.DO_DEFERRED_REPCAT_ADMIN API several times, since
--some administrative operations have multiple steps.

BEGIN
   DBMS_REPCAT.DO_DEFERRED_REPCAT_ADMIN (
      gname => 'SCOTT_MG',
      all_sites => TRUE);
END;
/

/*********************************************************************
BEGIN OFFLINE INSTANTIATION PROCEDURE
*********************************************************************/
--Connect as replication administrator to Master Definition Site

connect repadmin/repadmin@orc1.world

BEGIN
   DBMS_OFFLINE_OG.BEGIN_INSTANTIATION (
      gname => 'SCOTT_MG',
      new_site => 'ORC4.WORLD');
END;
/

/*
NOTE: You must wait until the DBA_REPCATLOG view is empty before
continuing the steps in this script. Execute the following SELECT statement
in another SQL*Plus session to monitor your DBA_REPCATLOG view:

SELECT * FROM dba_repcatlog WHERE gname = 'SCOTT_MG';
*/
PAUSE Press <RETURN> to continue.


/*********************************************************************
CONNECT AS SCOTT/TIGER TO EXPORT

You will need to use the Oracle export utility to generate the export
file that you will transfer to the new master site. The export file 
will contain the replicated objects to be added at the new master site.
See the Oracle8i Utilities book for additional information.
*********************************************************************/

EXP80 scott/tiger@orc1.world 

/*********************************************************************
RESUME PARTIAL REPLICATION ACTIVITY

Since it may take you some time to complete the offline instantiation
process, you can resume replication activity for the existing
master sites by executing the DBMS_OFFLINE_OG.RESUME_SUBSET_OF_MASTERS
procedure after the export is complete.
*********************************************************************/

--connect as replication administrator to master definition site.

CONNECT repadmin/repadmin@orc1.world

BEGIN
   DBMS_OFFLINE_OG.RESUME_SUBSET_OF_MASTERS (
      gname => 'SCOTT_MG',
      new_site => 'ORC4.WORLD');
END;
/

/*********************************************************************
PREPARE NEW MASTER SITE

After you have transferred the export file from the master definition
site to the new master site, you must prepare the new site to import
the data in your export file.
*********************************************************************/

CONNECT repadmin/repadmin@orc4.world

BEGIN
   DBMS_OFFLINE_OG.BEGIN_LOAD (
      gname => 'SCOTT_MG',
      new_site => 'ORC4.WORLD');
END;
/

/*********************************************************************
IMPORT DATA FROM EXPORT FILE

Once you have imported the export file that you generated earlier,
you will have transferred the data from your master definition site
to your new master site.
*********************************************************************/
IMP80 scott/tiger@orc4.world FULL=y IGNORE=y

/*********************************************************************
COMLETE LOAD PROCESS AT NEW MASTER SITE

After you have imported the export file, you are ready to complete
the offline instantiation process at the new master site. Executing
DBMS_OFFLINE_OG.END_LOAD procedure will prepare the new site for
normal replication activity.
*********************************************************************/

CONNECT repadmin/repadmin@orc4.world

BEGIN
   DBMS_OFFLINE_OG.END_LOAD (
      gname => 'SCOTT_MG',
      new_site => 'ORC4.WORLD');
END;
/

/*********************************************************************
COMPLETE INSTANTIATION PROCESS

Once you have complete the steps at the new master site, you are ready
to complete the offline instantiation process. Executing the 
DBMS_OFFLINE_OG.END_INSTANTIATION procedure will complete the process
and resume normal replication activity at all master sites.
*********************************************************************/
CONNECT repadmin/repadmin@orc1.world

BEGIN
   DBMS_OFFLINE_OG.END_INSTANTIATION (
      gname => 'SCOTT_MG',
      new_site => 'ORC4.WORLD');
END;
/

Snapshot Site

For the same reasons that you might want to perform an offline instantiation of a master site, you may also want to create a new snapshot group at a snapshot site using the offline instantiation process. In some cases, it is even more useful for snapshots considering that the target computer could very well be a laptop using a modem connection.

The following script describes the process of performing an offline instantiation for a new snapshot group.

Executed As: Replication Administrator and Snapshot Administrator

Executed At: Master Site and New Snapshot Site

Replication Status: Normal


/*********************************************************************
SETUP SNAPSHOT SITE

You need to complete the steps illustrated in the 
"Setup Snapshot Site" section. You will need
to make sure that the appropriate schema and database links have been
created before you perform the offline instantiation of your snapshot.
*********************************************************************/

/***************************************************************************
CREATE SNAPSHOT LOGS

If snapshot logs do not already exist for the target master tables, you 
will need to create them at the target master site.
***************************************************************************/

CONNECT repadmin/repadmin@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;

/***************************************************************************
CREATE TEMPORARY SNAPSHOTS

You will create temporary snapshots at the master site that will contain the
data that you will transfer to your new snapshot site using the export file.

NOTE: If you added any of the conflict resolution routines described in
Chapter  6, "Conflict Resolution", you may have additional columns in your
tables. Be certain to include these additional columns in the SELECT 
statements below; updatable snapshots require that you explicity select all
columns in the master table (no SELECT *).
***************************************************************************/

CREATE SNAPSHOT scott.snap_emp REFRESH FAST WITH PRIMARY KEY FOR UPDATE
   AS SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno
   FROM scott.emp@orc1.world;

CREATE SNAPSHOT scott.snap_dept REFRESH FAST WITH PRIMARY KEY FOR UPDATE
   AS SELECT deptno, dname, loc 
   FROM scott.dept@orc1.world;

CREATE SNAPSHOT scott.snap_bonus REFRESH FAST WITH PRIMARY KEY FOR UPDATE
   AS SELECT ename, job, sal, comm
   FROM scott.bonus@orc1.world;

CREATE SNAPSHOT scott.snap_salgrade REFRESH FAST WITH PRIMARY KEY FOR UPDATE
   AS SELECT grade, losal, hisal
   FROM scott.salgrade@orc1.world;

/*********************************************************************
CONNECT AS SCOTT/TIGER TO EXPORT

You will need to use the Oracle export utility to generate the export
file that you will transfer to the new snapshot site. The export file 
will contain the base tables of your temporary snapshots. See the
Oracle8i Utilities book for additional information.

NOTE: The following example is to be used for Oracle8i databases
only. Base tables in database versions earlier than Oracle8i will
be preceded by the SNAP$ prefix (i.e. SNAP$_SNAP_EMP).
*********************************************************************/

EXP80 scott/tiger@orc1.world TABLES='snap_emp','snap_dept',
'snap_bonus','snap_salgrade'

/*********************************************************************
DELETE THE TEMPORARY SNAPSHOTS

After you have completed your export, you should delete the temporary
snapshots that you created during the beginning of this procedure.
*********************************************************************/

CONNECT scott/tiger@orc1.world

DROP SNAPSHOT snap_emp;
DROP SNAPSHOT snap_dept;
DROP SNAPSHOT snap_bonus;
DROP SNAPSHOT snap_salgrade;

/*********************************************************************
CREATE NECESSARY SCHEMA AND DATABASE LINK

Before you perform the offline instantiation of your snapshots, you
need to create the schema that will contain the snapshots at the new 
snapshot site (they need to be in the same schema that contains the 
master objects at the master site) and the database link from the 
snapshot site to the master site.
*********************************************************************/
CONNECT system/manager@snap2.world

CREATE USER scott IDENTIFIED by tiger;

GRANT connect, resource TO scott;

CONNECT scott/tiger@snap2.world

CREATE DATABASE LINK orc1.world CONNECT TO scott IDENTIFIED by tiger;

/*********************************************************************
CREATE EMPTY SNAPSHOT GROUP

You need to execute the DBMS_REPCAT.CREATE_SNAPSHOT_REPGROUP API at
the new snapshot site to contain an empty snapshot group that you 
will add your snapshots to.
*********************************************************************/

CONNECT snapadmin/snapadmin@snap2.world

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

/*********************************************************************
PREPARE SNAPSHOT SITE FOR OFFLINE INSTANTIATION

The DBMS_OFFLINE_SNAPSHOT.BEGIN_LOAD API creates the necessary support
mechanisms for the new snapshots. This step also adds the new 
snapshots to the snapshot group that you created in the previous step.

Be sure to execute the DBMS_OFFLINE_SNAPSHOT.BEGIN_LOAD API for each
snapshot that you will be importing.
*********************************************************************/

CONNECT system/manager@snap2.world

BEGIN
   DBMS_OFFLINE_SNAPSHOT.BEGIN_LOAD (
      gname => 'SCOTT_MG',
      sname => 'SCOTT',
      master_site => 'ORC1.WORLD',
      snapshot_oname => 'SNAP_EMP');
END;
/

BEGIN
   DBMS_OFFLINE_SNAPSHOT.BEGIN_LOAD (
      gname => 'SCOTT_MG',
      sname => 'SCOTT',
      master_site => 'ORC1.WORLD',
      snapshot_oname => 'SNAP_DEPT');
END;
/

BEGIN
   DBMS_OFFLINE_SNAPSHOT.BEGIN_LOAD (
      gname => 'SCOTT_MG',
      sname => 'SCOTT',
      master_site => 'ORC1.WORLD',
      snapshot_oname => 'SNAP_BONUS');
END;
/

BEGIN
   DBMS_OFFLINE_SNAPSHOT.BEGIN_LOAD (
      gname => 'SCOTT_MG',
      sname => 'SCOTT',
      master_site => 'ORC1.WORLD',
      snapshot_oname => 'SNAP_SALGRADE');
END;
/

/*********************************************************************
CONNECT AS SCOTT/TIGER TO IMPORT AT NEW SNAPSHOT SITE

You will need to use the Oracle import utility to import the file
that you exported earlier. Make sure that you import your data as the
same user that exported the data (i.e. scott/tiger).
*********************************************************************/

IMP80 scott/tiger@snap2.world FULL=y IGNORE=y

/*********************************************************************
COMPLETE THE OFFLINE INSTANTIATION

Execute the DBMS_OFFLINE_SNAPSHOT.END_LOAD API to finish the offline
instantiation of the imported snapshots.
*********************************************************************/

CONNECT system/manager@snap2.world

BEGIN
   DBMS_OFFLINE_SNAPSHOT.END_LOAD (
      gname => 'SCOTT_MG',
      sname => 'SCOTT',
      snapshot_oname => 'snap_emp');
END;
/

BEGIN
   DBMS_OFFLINE_SNAPSHOT.END_LOAD (
      gname => 'SCOTT_MG',
      sname => 'SCOTT',
      snapshot_oname => 'snap_dept');
END;
/

BEGIN
   DBMS_OFFLINE_SNAPSHOT.END_LOAD (
      gname => 'SCOTT_MG',
      sname => 'SCOTT',
      snapshot_oname => 'snap_bonus');
END;
/

BEGIN
   DBMS_OFFLINE_SNAPSHOT.END_LOAD (
      gname => 'SCOTT_MG',
      sname => 'SCOTT',
      snapshot_oname => 'snap_salgrade');
END;
/

/*********************************************************************
REFRESH SNAPSHOTS TO REGISTER AT MASTER SITE

In addition to retreiving the latest changes from the master tables,
refreshing the snapshots at the new snapshot site registers the offline
instantiated snapshots at the target master site.
*********************************************************************/

CONNECT scott/tiger@snap2.world

BEGIN
   DBMS_SNAPSHOT.REFRESH ('snap_emp');
END;
/

BEGIN
   DBMS_SNAPSHOT.REFRESH ('snap_dept');
END;
/

BEGIN
   DBMS_SNAPSHOT.REFRESH ('snap_bonus');
END;
/

BEGIN
   DBMS_SNAPSHOT.REFRESH ('snap_salgrade');
END;
/

7Manage Replicated Environment with APIs

Managing Master Sites

Change Master Definition Site

Option 1

Option 2

Add a Master Site

Drop a Master Site

Managing Snapshot Sites

Using a Group Owner

Pushing the Deferred Transaction Queue

Dropping Snapshot Sites

Drop Snapshot Group Created with Deployment Templates

Public

Private

Drop Snapshot Objects at Snapshot Site

Drop Snapshot Group at Snapshot Site

Drop Individual Snapshot at Snapshot Site

Cleanup Master Site

Cleanup After Dropping Snapshot Group

Cleanup Individual Snapshot Support at Master Site

Managing the Error Queue

Re-execute Error Transaction as the Receiver

Re-execute Error Transaction as Alternate User

Alter Replicated Object

Offline Instantiation

Master Site

Snapshot Site

7
Manage Replicated Environment with APIs