Performing an Offline Instantiation Using Export/Import

Expanding established replicated environments can cause 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 through the network to the new replicated site.

To minimize 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 allow you to create an export file and transfer the data to the new site through another storage medium, such as CD-ROM, tape, and so on.

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 caused 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

/*********************************************************************
SET UP NEW MASTER SITE

You need to complete the steps illustrated in the "Set Up Master Sites" section. Make sure the appropriate schema and database links have been
created before you perform the offline instantiation of your new master
site. Be sure to create the database links from the new master site to
each of the existing masters sites. Also, 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 9: 
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 pushing any outstanding deferred transactions, 
resolving any error transactions, and/or pushing any administrative 
transactions. This step must 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 or wait for them to be executed automatically. You may need 
--to execute the DBMS_REPCAT.DO_DEFERRED_REPCAT_ADMIN API several times, 
--because 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 should wait until the DBA_REPCATLOG view is empty. This view has 
--temporary information that is cleared after successful execution. Execute 
--the following SELECT statement in another SQL*Plus session to monitor 
--the DBA_REPCATLOG view:
--
--SELECT * FROM dba_repcatlog WHERE gname = 'SCOTT_MG';

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

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

exp scott/tiger@orc1.world 

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

Because it may take you some time to complete the offline instantiation
process, you can resume replication activity for the remaining
master sites (excluding the new master site) by executing the 
DBMS_OFFLINE_OG.RESUME_SUBSET_OF_MASTERS procedure after the export is 
complete. In the DBMS_OFFLINE_OG.RESUME_SUBSET_OF_MASTERS procedure below, 
replication activity is resumed at all master sites except the
new master site -- orc4.world.
*********************************************************************/

--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. The following procedure is to be executed 
at the new master site.
*********************************************************************/

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 have transferred the data from your master definition site
to your new master site.
*********************************************************************/

imp scott/tiger@orc4.world FULL=y IGNORE=y

/*********************************************************************
COMPLETE 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 the
DBMS_OFFLINE_OG.END_LOAD procedure prepares 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 completed the steps at the new master site, you are ready
to complete the offline instantiation process. Executing the 
DBMS_OFFLINE_OG.END_INSTANTIATION procedure completes the process
and resumes normal replication activity at all master sites. The 
following procedure is to be executed at the master definition site.
*********************************************************************/
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

/*******************************************************************************
SET UP SNAPSHOT SITE

You need to complete the steps illustrated in the "Set Up Snapshot Sites" 
section. 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, 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

Create temporary snapshots at the master site that will contain the
data that you 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

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 Oracle8i Utilities for additional information.

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

exp 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, create the 
schema that will contain the snapshots at the new snapshot site (which 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, create snapshot TO scott;

CONNECT scott/tiger@snap2.world

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

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

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

Use the Oracle import utility to import the file that you exported earlier. 
Make sure that you import your data as the same user who exported the data (that 
is, scott/tiger).
*********************************************************************/

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