Oracle Built-in Packages

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.

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