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.
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.
Name | Description | Use in SQL? |
---|---|---|
BEGIN_INSTANTIATION | Call from master definition site to flag beginning of offline instantiation | No |
Call from new master site prior to importing data | No | |
Call from master definition site to flag end of offline instantiation | No | |
Call from new master site after importing data | No | |
Call from master definition site to resume replication activity for existing sites while new site is instantiated | No |
This section describes the programs available in the DBMS_OFFLINE_OG package.
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 |
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 |
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.
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.
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;
Perform export of schema SPROCKET from any existing master site.
Call RESUME_SUBSET_OF_MASTERS at master definition site.
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;
Import the SPROCKET schema into D7NY.BIGWHEEL.COM using the export file created in step 2.
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;
Call END_INSTANTIATION from the master definition site.
BEGIN DBMS_OFFLINE_OG.END_INSTANTIATION( gname => 'SPROCKET', new_site => 'D7NY.BIGWHEEL.COM'); END;
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.
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 |
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.
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.
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 |
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.
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.
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 |
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.
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.
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 |
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.
This HTML Help has been published using the chm2web software. |