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).

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 .

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.

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).

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).

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.

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.

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.

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).

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;
/