 | Chapter 14 Snapshots |  |
Although most of the procedures in the DBMS_REPCAT package are used to create and maintain the advanced replication environment, some of the procedures let you manipulate snapshot replication groups. This section describes only the snapshot-related programs. The bulk of the DBMS_REPCAT programs are described in Chapters 15 and 16.
The procedures in DBMS_REPCAT used for manipulating snapshot replication groups are in large measure analogous to the procedures DBMS_REFRESH provides for manipulating simple snapshot groups.
The DBMS_REPCAT package is created when the Oracle database is installed. The dbmsrepc.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 script creates the public synonym DBMS_REPCAT. The package procedure DBMS_REPCAT_AUTH.GRANT_SURROGATE_REPCAT grants EXECUTE privileges on the package to the specified grantee. In addition, the package owner (SYS) and users with the EXECUTE ANY PROCEDURE system privilege may execute it.
Table 14.5 lists only the DBMS_REPCAT snapshot-related programs; see Tables 15-3 and 16-10 for the remaining DBMS_REPCAT programs.
Name | Description | Use in SQL |
|---|---|---|
Changes a snapshot replication group's propagation mode | No | |
Creates a snapshot replication group | No | |
Adds an object to a snapshot replication group | No | |
Drops a snapshot replication group | No | |
Drops an object from a snapshot replication group | No | |
Refreshes a snapshot replication group | No | |
Remasters a snapshot site to another master site | No |
DBMS_REPCAT defines the following exceptions for the programs listed in Table 14.6.
Name | Number | Description |
|---|---|---|
commfailure | -23317 | Unable to communicate with master |
dbnotcompatible | -23375 | Attempt to use SYNCHRONOUS propagation in Pre-7.3 database |
ddlfailure | -23318 | Unable to perform DDL |
duplicateobject | -23309 | Object oname already exists |
duplicaterepgroup | -23374 | Replication group gname already exists |
missingobject | -23308 | Object oname does not exist in master's replication group gname |
missingremoteobject | -23381 | Master site has not generated replication support for oname |
missingrepgroup | -23373 | Replication group gname does not exist |
missingschema | -23306 | Schema sname does not exist |
misssnapobject | -23355 | Object oname does not exist at master |
nonmaster | -23312 | Master site associated with snapshot group is no longer a master site |
nonsnapshot | -23314 | Calling site is not a snapshot site |
norepoption | -23364 | Replication option not installed |
typefailure | -23319 | propagation_mode not specified correctly |
The CREATE_SNAPSHOT_REPGROUP and DROP_SNAPSHOT_REPGROUP procedures allow you to create and destroy snapshot replication groups.
This procedure creates a new, empty snapshot replication group. You must invoke it from the snapshot site. The program specification follows:
PROCEDURE DBMS_REPCAT.CREATE_SNAPSHOT_REPGROUP
(gname IN VARCHAR2,
master IN VARCHAR2,
comment IN VARCHAR2 := '',
propagation_mode IN VARCHAR2 := 'ASYNCHRONOUS');Parameters are summarized in the following table.
Name | Description |
|---|---|
gname | Name of the new snapshot group |
master | Global name of master site |
comment | Comment for the snapshot group; visible in DBA_REPSITES data dictionary view |
propagation_mode | Snapshot propagation mode (SYNCHRONOUS, or ASYNCHRONOUS) |
The CREATE_SNAPSHOT_REPGROUP procedure raises the following exceptions:
Name | Number | Description |
|---|---|---|
commfailure | -23317 | Unable to communicate with master |
dbnotcompatible | -23375 | Attempt to use SYNCHRONOUS propagation in pre-7.3 database |
duplicaterepgroup | -23374 | Replication group gname already exists |
nonmaster | -23312 | The master parameter is not a master site |
norepoption | -23364 | Replication option not installed |
typefailure | -23319 | propagation_mode not specified correctly |
Note the following restrictions on calling CREATE_SNAPSHOT_REPGROUP:
You must be connected to the replication administrator account (typically REPADMIN) to call the CREATE_SNAPSHOT_REPGROUP procedure.
The snapshot group name must match the name of the master replication group.
The procedure for performing offline instantiation of snapshots in an advanced replication environment (using the CREATE SNAPSHOT command and the DBMS_OFFLINE_SNAPSHOT and DBMS_REPCAT packages) follows:
Create a snapshot log for each master table if one does not already exist.
Create a snapshot of each master table in the master database, and in the same schema as the master table. Of course, the name of the snapshot will have to be different from the name of the master table. The CREATE SNAPSHOT statement must also include a loopback database link qualifier.
CREATE SNAPSHOT snp_countries AS SELECT * FROM [email protected]@TCPIP
Perform user exports of all schema that own master tables. You should be logged on to the schema owner account for these exports. The only tables that you need to export are the snapshot base tables -- that is, those whose names begin with "SNAP$_".
Copy the export dump file(s) to the new snapshot site(s).
Use CREATE_SNAPSHOT_REPGROUP at the snapshot sites to create a new snapshot replication object group. The name of this object group should be the same as the name of the replication group of which the master tables are members.
BEGIN DBMS_REPCAT.CREATE_SNAPSHOT_REPGROUP( gname => 'SPROCKET', master => 'D7CA.BIGWHEEL.COM', comment => 'Group created on '||sysdate|| ' by '||user, propagation_mode=> 'ASYNCHRONOUS'); END;
Call DBMS_OFFLINE_SNAPSHOT.BEGIN_LOAD to begin loading the data from the export file(s). You must call the procedure for every snapshot you plan to import.
BEGIN DBMS_OFFLINE_SNAPSHOT.BEGIN_LOAD( gname => 'SPROCKET', sname => 'SPROCKET', master_site => 'D7CA.BIGWHEEL.COM' snapshot_oname => 'SNP_COUNTRIES' storage_c => 'TABLESPACE sprocket_data STORAGE (INITIAL 64K)' comment => 'Load of COUNTRIES snapshot begun at '||sysdate); END;
Import the snapshot base table(s) from the export file(s) created in step 4.
Call DBMS_OFFLINE_SNAPSHOT.END_LOAD for each snapshot when the load is complete.
BEGIN DBMS_OFFLINE_SNAPSHOT.END_LOAD( gname => 'SPROCKET' sname => 'SPROCKET' snapshot_oname => 'SNP_COUNTRIES'); END;
The DBMS_REPCAT package's DROP_SNAPSHOT_REPGROUP procedure is the counterpart to the CREATE_SNAPSHOT_REPGROUP procedure. As you would suspect, this procedure drops an existing snapshot replication group, and optionally, all member snapshots. Here's the specification:
PROCEDURE DBMS_REPCAT>DROP_SNAPSHOT_REPGROUP
(gname IN VARCHAR2,
drop_contents IN BOOLEAN := FALSE);Parameters are summarized in the following table.
Name | Description |
|---|---|
gname | Name of the snapshot group. |
drop_contents | If TRUE, objects in gname are dropped. If FALSE (the default) they are simply no longer replicated. |
The DROP_SNAPSHOT_REPGROUP procedure raises the following exceptions:
Name | Number | Description |
|---|---|---|
missingrepgroup | -23373 | Replication group gname does not exist |
nonmaster | -23313 | Calling site is not a snapshot site |
If drop_contents is set to FALSE, the triggers created to support snapshot modifications remain.
The following example illustrates the dropping of a snapshot replication group with the DROP_SNAPSHOT_REPGROUP procedure:
BEGIN DBMS_REPCAT.DROP_SNAPSHOT_REPGROUP( gname => 'SPROCKET', drop_objects => TRUE); END;
Here, we drop the snapshot replication group SPROCKET and drop the member snapshots as well.
The CREATE_SNAPSHOT_REPOBJECT and DROP_SNAPSHOT_REPOBJECT procedures add and remove objects, respectively, from a snapshot replication group. These objects may be snapshots, packages, package bodies, procedures, synonyms, or views.
The CREATE_SNAPSHOT_REPOBJECT procedure adds an object to a snapshot replication group. For new snapshot objects, this procedure generates row-level replication triggers for snapshots if the master table uses row-level replication. The specifications differ for Oracle7 and Oracle8 as follows.
Here is the Oracle7 specification:
PROCEDURE DBMS_REPCAT. CREATE_SNAPSHOT_REPOBJECT
(sname IN VARCHAR2,
oname IN VARCHAR2,
type IN VARCHAR2,
ddl_text IN VARCHAR2 := '',
comment IN VARCHAR2 := '',
gname IN VARCHAR2 := '',
gen_objs_owner IN VARCHAR2 := '');Here is the Oracle8 specification:
PROCEDURE DBMS_REPCAT.CREATE_SNAPSHOT_REPOBJECT
(sname IN VARCHAR2,
oname IN VARCHAR2,
type IN VARCHAR2,
ddl_text IN VARCHAR2 := '',
comment IN VARCHAR2 := '',
gname IN VARCHAR2 := '',
gen_objs_owner IN VARCHAR2 := '',
min_communication IN BOOLEAN := TRUE);
NOTE: The only difference between the Orace7 and Oracle8 implementations is the addition of the min_communication parameter in Oracle8.
Parameters are summarized in the following table.
Name | Description |
|---|---|
sname | Name of schema to which oname belongs. |
oname | Name of object to be added. |
type | Object type. Supported types are PACKAGE, PACKAGE BODY, PROCEDURE, SNAPSHOT, SYNONYM, and VIEW. |
ddl_text | DDL used to create object (for type SNAPSHOT only). |
comment | Comment on object, visible in DBA_REPOBJECT data dictionary view. |
gname | Name of snapshot group to which object is being added. Defaults to sname if not specified. |
gen_objs_owner | Name of the schema in which to create the generated trigger and trigger package or procedure wrapper for the object. Defaults to sname. |
drop_objects | If set to TRUE, object is dropped too. If FALSE (the default), object is only removed from the snapshot group. |
min_communication (Oracle8 only) | Must be FALSE if any master site is running Oracle7. TRUE, the default setting, uses the minimum communication algorithm. |
CREATE_SNAPSHOT_REPGROUP raises the following exceptions:
Name | Number | Description |
|---|---|---|
commfailure | -23317 | Unable to communicate with master site |
ddlfailure | -23318 | Unable to perform DDL |
duplicateobject | -23309 | Object oname already exists |
missingobject | -23308 | Object oname does not exist in master's replication group gname |
missingremoteobject | -23381 | Master site has not generated replication support for oname |
missingschema | -23306 | Schema sname does not exist |
misssnapobject | -23355 | Object oname does not exist at master |
nonmaster | -23312 | Master site associated with snapshot group is no longer a master site |
nonsnapshot | -23314 | Calling site is not a snapshot site |
typefailure | -23319 | Invalid value for type |
NOTE: CREATE_SNAPSHOT_REPOBJECT is called from the replication administrator (typically REPADMIN) account. If you are creating an snapshot with ddl_text, be sure to specify the schema in which it should be created (if other than the replication administrator account).
The following example illustrates how to add an object to an existing snapshot replication group:
BEGIN
DBMS_REPCAT.CREATE_SNAPSHOT_REPOBJECT(
sname => 'SPROCKET',
oname => 'PRICE_LIST',
type => 'SNAPSHOT' ,
ddl_text=>
'CREATE SNAPSHOT SPROCKET.PRICES AS SELECT * FROM
[email protected]' ,
gnam => 'SPROCKET',
gen_objs_owner => 'SPROCKET')
END;
BEGINThe DROP_SNAPSHOT_REPOBJECT procedure drops an object from a snapshot replication group.
PROCEDURE DBMS_REPCAT.DROP_SNAPSHOT_REPOBJECT
(sname IN VARCHAR2,
oname IN VARCHAR2,
type IN VARCHAR2,
drop_objects IN BOOLEAN := FALSE);.For parameter descriptions, see the table in the Section 14.4.3.1, "The DBMS_REPCAT.CREATE_SNAPSHOT_REPOBJECT procedure"" section.
DROP_SNAPSHOT_REPOBJECT raises the following exceptions:
Name | Number | Description |
|---|---|---|
missingobject | -23308 | Object oname does not exist in master's replication group gname |
nonsnapshot | -23314 | Calling site is not a snapshot site |
typefailure | -23319 | Invalid value for type |
If the type parameter in DROP_REPOBJECT is SNAPSHOT and you do not set the drop_objects parameter to TRUE, replication triggers and associated packages remain in the schema, and deferred transactions (if any) remain in the DEFTRANS queue.
The following example drops an object from a snapshot replication group:
DBMS_REPCAT.FTOP_SNAPSHOT_REPOBJECT( sname => 'SPROCKET', oname => 'PRICE_LIST', type => 'SNAPSHOT', drop_objects=> TRUE); END;
The ALTER_SNAPSHOT_PROPAGATION procedure changes a snapshot replication group's propagation mode. The propagation mode can be either SYNCHRONOUS or ASYNCHRONOUS. When you call this procedure, Oracle does the following:
Pushes any transactions that exist in the deferred RPC queue for the group
Locks the snapshot base tables (SNAP$_<tablename>)
Regenerates replication triggers and packages for the objects in the group
Call the ALTER_SNAPSHOT_PROPAGATION procedure to change the propagation mode of a particular snapshot. Specifications for Oracle7 and Oracle8 differ as follows.
Here is the Oracle7 specification:
PROCEDURE DBMS_REPCAT.ALTER_SNAPSHOT_PROPAGATION
(alter_snapshot_propagation(
gname IN VARCHAR2,
propagation_mode IN VARCHAR2,
comment IN VARCHAR2 := '',
execute_as_user IN BOOLEAN := FALSE);Here is the Oracle8 specification:
PROCEDURE DBMS_REPCAT.ALTER_SNAPSHOT_PROPAGATION
(gname IN VARCHAR2,
propagation_mode IN VARCHAR2,
comment IN VARCHAR2 := '' );Parameters are summarized in the following table.
Name | Description |
|---|---|
gname | Name of the replication group to be altered |
propagation_mode | The new propagation mode to use (SYNCHRONOUS or ASYNCHRONOUS) |
comment | Comment visible in DBA_REPPROP data dictionary view |
execute_as_user (Oracle7 only) | FALSE (default) indicates that remote system will authenticate calls using authentication context user who originally queued the RPC; TRUE indicates that remote system will use authentication context of the session user |
ALTER_SNAPSHOT_PROPAGATION raises the following exceptions:
Name | Number | Description |
|---|---|---|
dbnotcompatible | -23375 | Database version is not 7.3 or later |
missingrepgroup | -23373 | Replication group gname does not exist |
typefailure | -23319 | Invalid propagation_mode |
ALTER_SNAPSHOT_PROPAGATION must be called from a snapshot site.
The following example shows how to switch from asynchronous to synchronous propagation for a given snapshot replication group:
BEGIN DBMS_REPCAT.ALTER_SNAPSHOT_PROPAGATION( gname => 'SPROCKET', propagation_mode => 'ASYNCHRONOUS', comment => 'Mode set to asynchronous on ' ||sysdate|| 'by '||user); END;
You can use the REFRESH_SNAPSHOT_REPGROUP to refresh manually a snapshot replication group. The procedure can optionally drop objects that are no longer in the group, and/or refresh the snapshots and other objects.
NOTE: The REFRESH_SNAPSHOT_REPGROUP procedure replaces the REFRESH_SNAPSHOT_REPSCHEMA procedure. Although REFRESH_SNAPSHOT_REPSCHEMA still exists (as of Oracle 7.3.3), do not use it; it does not exist in Oracle 8.0.3.
Call the DBMS_REPCAT.REFRESH_SNAPSHOT_REPGROUP procedure to refresh a snapshot replication group manually. Specifications differ for Oracle7 and Oracle8 as follows.
Here is the Oracle7 specification:
PROCEDURE DBMS_REPCAT.REFRESH_SNAPSHOT_REPGROUP
(gname IN VARCHAR2,
drop_missing_contents IN BOOLEAN := FALSE,
refresh_snapshots IN BOOLEAN := FALSE,
refresh_other_objects IN BOOLEAN := FALSE,
execute_as_user IN BOOLEAN:= FALSE);Here is the Oracle8 specification:
PROCEDURE DBMS_REPCAT.REFRESH_SNAPSHOT_REPGROUP
(gname IN VARCHAR2,
drop_missing_contents IN BOOLEAN := FALSE,
refresh_other_objects IN BOOLEAN := FALSE )Parameters are summarized in the following table.
Name | Description |
|---|---|
gname | Name of the replication group. |
drop_missing_contents | If TRUE, drop schema objects that are no longer in the snapshot group. If FALSE (the default), objects are simply no longer replicated. |
refresh_snapshots | If TRUE, force a refresh of snapshots in gname. Default is FALSE. |
refresh_other_objects | If TRUE, refresh non-snapshot objects in gname, such as views and procedures. Non-snapshot objects are refreshed by dropping and recreating them. Default is FALSE. |
execute_as_user (Oracle7 only) | FALSE (default) indicates that the remote system will authenticate calls using the authentication context user who originally queued the RPC; TRUE indicates that remote system will use authentication context of the session user. |
REFRESH_SNAPSHOT_REPGROUP raises the following exceptions:
Name | Number | Description |
|---|---|---|
commfailure | -23317 | Unable to communicate with the master site |
nonmaster | -23313 | Master is no longer a master database |
nonsnapshot | -23314 | Calling site is not a snapshot site |
REFRESH_SNAPSHOT_REPGROUP must be called from a snapshot site.
The following example illustrates how REFRESH_SNAPSHOT_REPGROUP is commonly used:
BEGIN DBMS_REPCAT.REFRESH_SNAPSHOT_REPGROUP( gname => 'SPROCKET', drop_missing_contents => TRUE, refresh_snapshots => TRUE, refresh_other_objects => TRUE) END;
This example refreshes all snapshots in group SPROCKET, drops schema objects that are no longer in the group, and recreates any views, procedures, or synonyms that have been created or altered at the master site.
Should it ever become necessary to point a snapshot replication group to a different master site, you can do it with the SWITCH_SNAPSHOT_MASTER procedure.
The SWITCH_SNAPSHOT_MASTER procedure lets you switch a snapshot replication group to a different master site. This procedure changes the master site for the specified snapshot group. The new master site must contain a replica of the replication group gname. The next time the snapshot group refreshes, Oracle performs a full refresh. The specifications for SWITCH_SNAPSHOT_MASTER differ for Oracle7 and Oracle8 as follows.
Here is the Oracle7 specification:
PROCEDUREDBMS_REPCAT.SWITCH_SNAPSHOT_MASTER
(gname IN VARCHAR2 := '',
master IN VARCHAR2,
execute_as_user IN BOOLEAN = FALSE,
sname IN VARCHAR2 := '');Here is the Oracle8 specification:
PROCEDURE DBMS_REPCAT.SWITCH_SNAPSHOT_MASTER
(gname IN VARCHAR2 := '',
master IN VARCHAR2)Parameters are summarized in the following table.
Name | Description |
|---|---|
gname | Name of the snapshot group |
master | Name of the new master site |
execute_as_user (Oracle7 only) | FALSE (default) indicates that the remote system will authenticate calls using the authentication context user who originally queued the RPC; TRUE indicates that remote system will use authentication context of the session user |
sname (Oracle7 only) | Not used |
The SWITCH_SNAPSHOT_MASTER procedure may raise the following exceptions:
Name | Number | Description |
|---|---|---|
commfailure | -23317 | Unable to communicate with master site |
nonmaster | -23312 | The master parameter is not a master site |
nonsnapshot | -23314 | Calling site is not a snapshot site |
Note the following restrictions on calling SWITCH_SNAPSHOT_MASTER:
The new master site must contain a replica of the replication group gname.
Snapshots whose query is greater than 32K cannot be remastered.
The following call remasters snapshot group SPROCKET to D7NY.BIGWHEEL.COM:
BEGIN DBMS_REPCAT.SWITCH_SNAPSHOT_MASTER( gname => 'SPROCKET', master => 'D7NY.BIGWHEEL.COM') END
NOTE: Put snapshot logs on the master tables at the new master site so that you can use fast refreshes.
Figure Figure 14.3 graphically illustrates this example.
|  | |
| 14.3 DBMS_OFFLINE_SNAPSHOT: Performing Offline Snapshot Instantiation |  | 15. Advanced Replication |
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.