Oracle Built-in Packages

Oracle Built-in PackagesSearch this book
Previous: 15.3 DBMS_REPCAT: Replication Environment AdministrationChapter 15
Advanced Replication
Next: 15.5 DBMS_RECTIFIER_DIFF: Comparing Replicated Tables
 

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.


Previous: 15.3 DBMS_REPCAT: Replication Environment AdministrationOracle Built-in PackagesNext: 15.5 DBMS_RECTIFIER_DIFF: Comparing Replicated Tables
15.3 DBMS_REPCAT: Replication Environment AdministrationBook Index15.5 DBMS_RECTIFIER_DIFF: Comparing Replicated Tables

The Oracle Library Navigation

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

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference