#

| Chapter 15 Advanced Replication |

## 15.4 DBMS_OFFLINE_OG: Performing Site Instantiation

When you add a new site to your replicated environment, you must not only create the replicated objects, but also populate snapshots and replicated tables with a copy of the current data. Although you can set the copy_rows parameter to TRUE in your call to the DBMS_REPCAT package's CREATE_MASTER_REPOBJECT or ADD_MASTER_DATABASE procedure, this option is not practical for schemas that are large or complex.

The DBMS_OFFLINE_OG package provides a more feasible method of site instantiation. The general idea is that you export data from an existing master site and import it into the new master site. While the import is taking place, the existing master sites queue data updates to the new site, but do not actually send the updates until the load is complete.

### 15.4.1 Getting Started with DBMS_OFFLINE_OG

The DBMS_OFFLINE_OG package is created when the Oracle database is installed. The dbmsofln.sql script (found in the built-in packages source directory, as described in Chapter 1) contains the source code for this package's specification. This script is called by catrep.sql, which must be run to install the advanced replication packages. The wrapped sql script prvtofln.plb creates the public synonym DBMS_OFFLINE_OG. No EXECUTE privileges are granted on DBMS_OFFLINE_OG; only the owner (SYS) and those with the EXECUTE ANY PROCEDURE system privilege may execute the package.

The programs in DBMS_OFFLINE_OG are listed in Table 15.5.

Table 15.5: DBMS_OFFLINE_OG: Programs
Name | Description | Use in SQL?
--- | --- | ---
BEGIN_INSTANTIATION | Call from master definition site to flag beginning of offline instantiation | No
BEGIN_LOAD | Call from new master site prior to importing data | No
END_INSTANTIATION | Call from master definition site to flag end of offline instantiation | No
END_LOAD | Call from new master site after importing data | No
RESUME_SUBSET_OF_MASTERS | Call from master definition site to resume replication activity for existing sites while new site is instantiated | No

### 15.4.2 DBMS_OFFLINE_OG Interface

This section describes the programs available in the DBMS_OFFLINE_OG package.

#### 15.4.2.1 The DBMS_OFFLINE_OG.BEGIN_INSTANTIATION procedure

The BEGIN_INSTANTIATION procedure is called from the master definition site to flag the beginning of offline instantiation. Here's the specification:

>     PROCEDURE DBMS_OFFLINE_OG.BEGIN_INSTANTIATION
>        (gname IN VARCHAR2,
>         new_site IN VARCHAR2);

Parameters are summarized in the following table.

Name | Description
--- | ---
gname | The replication group to which the site is being added
new_site | The global_name of the new_site

#### 15.4.2.1.1 Exceptions

The BEGIN_INSTANTIATION procedure may raise the following exceptions:

Name | Number | Description
--- | --- | ---
badargument | -23430 | gname is NULL or ` '
missingrepgroup | -23373 | Group gname does not exist
nonmasterdef | -23312 | Routine is not being called from master definition site
sitealreadyexists | -23432 | New_site already exists
wrongstate | -23431 | Group gname is not in NORMAL state at master definition site

#### 15.4.2.1.2 Restrictions

The procedures in DBMS_OFFLINE_OG must be called in the appropriate order from the appropriate sites. The following example illustrates the proper use of this package.

#### 15.4.2.1.3 Example

The following table summarizes the steps you should follow when you use the procedures in the DBMS_OFFLINE_OG package.

Step | Where Performed | Activity
--- | --- | ---
1 | Master definition site | DBMS_REPCAT.ADD_MASTER_DATABASE
2 | Master definition site | DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY
3 | Master definition site | DBMS_OFFLINE_OG.BEGIN_INSTANTIATION
4 | Any master site | Export replicated schema
5 | Master definition site | DBMS_OFFLINE_OG.RESUME_SUBSET_OF_MASTERS
6 | New site | DBMS_OFFLINE_OG.BEGIN_LOAD
7 | New site | Import data from step 4
8 | New site | DBMS_OFFLINE_OG.END_LOAD
9 | Master definition site | DBMS_OFFLINE_OG.END_INSTANTIATION

The following scenario shows how instantiate a new site. Here we add the site D7NY.BIGWHEEL.COM to the replication group SPROCKET using DBMS_OFFLINE_OG. Assume that the master definition site is D7CA.BIGWHEEL.COM.

1. From master definition site D7CA.BIGWHEEL.COM, we add the new master site, quiesce the replication group, and call DBMS_OFFLINE_OG.BEGIN_INSTANTIATION.

   >     	BEGIN
   >     		DBMS_REPCAT.ADD_MASTER_DATABASE(
   >     			gname		=> 'SPROCKET',
   >     			master		=>'D7NY.BIGWHEEL.COM');;
   >     		DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY(gname => 'SPROCKET');
   >     		DBMS_OFFLINE_OG.BEGIN_INSTANTIATION(
   >     			gname		=>	'SPROCKET',
   >     			new_site		=> 'D7NY.BIGWHEEL.COM');
   >     	END;

2. Perform export of schema SPROCKET from any existing master site.

3. Call RESUME_SUBSET_OF_MASTERS at master definition site.

4. Call BEGIN_LOAD from the new master site D7NY.BIGWHEEL.COM.

   >     	BEGIN
   >     		DBMS_OFFLINE_OG.BEGIN_LOAD(
   >     			gname	=> 'SPROCKET',
   >     			new_site=> 'D7NY.BIGWHEEL.COM');
   >     	END;

5. Import the SPROCKET schema into D7NY.BIGWHEEL.COM using the export file created in step 2.

6. Call END_LOAD from the new master site, D7NY.BIGWHEEL.COM.

   >     	BEGIN
   >     		DBMS_OFFLINE_OG.END_LOAD(
   >     			gname	=> 'SPROCKET',
   >     			new_site=> 'D7NY.BIGWHEEL.COM');
   >     	END;

7. Call END_INSTANTIATION from the master definition site.

   >     	BEGIN
   >     		DBMS_OFFLINE_OG.END_INSTANTIATION(
   >     			gname => 'SPROCKET',
   >     			new_site => 'D7NY.BIGWHEEL.COM');
   >     	END;

#### 15.4.2.2 The DBMS_OFFLINE_OG.BEGIN_LOAD procedure

Call the BEGIN_LOAD procedure from the new master site before you begin importing data. The specification follows:

>     PROCEDURE DBMS_OFFLINE_OG.BEGIN_LOAD
>        (gname IN VARCHAR2,
>         new_site IN VARCHAR2);

These parameters are identical to those described for the BEGIN_INSTANTIATION procedure. See that section as well for an example of using the procedures in the DBMS_OFFLINE_OG procedure.

#### 15.4.2.2.1 Exceptions

The BEGIN_LOAD procedure may raise the following exceptions:

Name | Number | Description
--- | --- | ---
badargument | -23430 | gname is NULL or ` '
missingrepgroup | -23373 | Group gname does not exist
wrongsite | -23433 | Raised if BEGIN_LOAD or END_LOAD is executed at a site other than new_site
wrongstate | -23431 | Group gname is not in NORMAL state at the master definition site

#### 15.4.2.2.2 Restrictions

The procedures in DBMS_OFFLINE_OG must be called in the appropriate order from the appropriate sites. The example under BEGIN_INSTANTIATION illustrates the proper use of this package.

#### 15.4.2.3 The DBMS_OFFLINE_OG.END_INSTANTIATION procedure

You call the END_INSTANTIATION procedure from the master definition site to flag the end of offline instantiation. The specification follows:

>     PROCEDURE DBMS_OFFLINE_OG.END_INSTANTIATION
>        (gname IN VARCHAR2,
>         new_site IN VARCHAR2);

These parameters are identical to those described for the BEGIN_INSTANTIATION procedure. See that section as well for an example of using the procedures in the DBMS_OFFLINE_OG package.

#### 15.4.2.3.1 Exceptions

The END_INSTANTIATION procedure may raise the following exceptions:

Name | Number | Description
--- | --- | ---
badargument | -23430 | gname is NULL or ` '
missingrepgroup | -23373 | Group gname does not exist
nonmasterdef | -23312 | Routine is not being called from master definition site
sitealreadyexists | -23432 | New_site already exists
wrongstate | -23431 | Group gname is not in NORMAL state at the master definition site

#### 15.4.2.3.2 Restrictions

The procedures in DBMS_OFFLINE_OG must be called in the appropriate order from the appropriate sites. The example under BEGIN_INSTANTIATION illustrates the proper use of this package.

#### 15.4.2.4 The DBMS_OFFLINE_OG.END_LOAD procedure

Call the END_LOAD procedure from the new master site when you are finished importing data. The specification follows:

>     PROCEDURE DBMS_OFFLINE_OG.END_LOAD
>        (gname IN VARCHAR2,
>         new_site IN VARCHAR2);

These parameters are identical to those described for the BEGIN_INSTANTIATION procedure. See that section as well for an example of using the procedures in the DBMS_OFFLINE_OG package.

#### 15.4.2.4.1 Exceptions

The END_LOAD procedure may raise the following exceptions:

Name | Number | Description
--- | --- | ---
badargument | -23430 | gname is NULL or ` '
missingrepgroup | -23373 | Group gname does not exist
wrongsite | -23433 | Raised if BEGIN_LOAD or END_LOAD is executed at a site other than new_site
wrongstate | -23431 | Group gname is not in NORMAL state at master definition site

#### 15.4.2.4.2 Restrictions

The procedures in DBMS_OFFLINE_OG must be called in the appropriate order from the appropriate sites. The example under BEGIN_INSTANTIATION illustrates the proper use of this package.

#### 15.4.2.5 The DBMS_OFFLINE_OG.RESUME_SUBSET_OF_MASTERS procedure

Call this procedure from the master definition site to resume replication activity for existing sites while the new site is instantiated. The specification follows:

>     PROCEDURE DBMS_OFFLINE_OG.RESUME_SUBSET_OF_MASTERS
>        (gname IN VARCHAR2,
>         new_site IN VARCHAR2);

These parameters are identical to those described for the BEGIN_INSTANTIATION procedure. See that section as well for an example of using the procedures in the DBMS_OFFLINE_OG package.

#### 15.4.2.5.1 Exceptions

The RESUME_SUBSET_OF_MASTERS procedure may raise the following exceptions:

Name | Number | Description
--- | --- | ---
badargument | -23430 | gname is NULL or ` '
missingrepgroup | -23373 | Group gname does not exist
nonmasterdef | -23312 | Routine is not being called from master definition site
sitealreadyexists | -23432 | New_site already exists
wrongstate | -23431 | Group gname is not in NORMAL state at master definition site

#### 15.4.2.5.2 Restrictions

The procedures in DBMS_OFFLINE_OG must be called in the appropriate order from the appropriate sites. The example under BEGIN_INSTANTIATION illustrates the proper use of this package.

15.3 DBMS_REPCAT: Replication Environment Administration |  | 15.5 DBMS_RECTIFIER_DIFF: Comparing Replicated Tables

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.