[Chapter 14] 14.4 DBMS_REPCAT: Managing Snapshot Replication GroupsChapter 14Snapshots 14.4 DBMS_REPCAT: Managing Snapshot Replication GroupsAlthough 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.14.4.1 Getting Started with DBMS_REPCATThe 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.14.4.1.1 DBMS_REPCAT programsTable 14.5 lists only the DBMS_REPCAT snapshot-related programs; see Tables 15-3 and 16-10 for the remaining DBMS_REPCAT programs.Table 14.5: DBMS_REPCAT Programs (Snapshots Only)NameDescriptionUse in SQLALTER_SNAPSHOT_PROPAGATIONChanges a snapshot replication group's propagation modeNoCREATE_SNAPSHOT_REPGROUPCreates a snapshot replication groupNoCREATE_SNAPSHOT_REPOBJECTAdds an object to a snapshot replication groupNoDROP_SNAPSHOT_REPGROUPDrops a snapshot replication groupNoDROP_SNAPSHOT_REPOBJECTDrops an object from a snapshot replication groupNoREFRESH_SNAPSHOT_REPGROUPRefreshes a snapshot replication groupNoSWITCH_SNAPSHOT_MASTERRemasters a snapshot site to another master siteNo14.4.1.2 DBMS_REPCAT exceptionsDBMS_REPCAT defines the following exceptions for the programs listed in Table 14.6.Table 14.6: DBMS_REPCAT Exceptions NameNumberDescriptioncommfailure-23317Unable to communicate with masterdbnotcompatible-23375Attempt to use SYNCHRONOUS propagation in Pre-7.3 databaseddlfailure-23318Unable to perform DDLduplicateobject-23309Object oname already existsduplicaterepgroup-23374Replication group gname already existsmissingobject-23308Object oname does not exist in master's replication group gnamemissingremoteobject-23381Master site has not generated replication support for onamemissingrepgroup-23373Replication group gname does not existmissingschema-23306Schema sname does not existmisssnapobject-23355Object oname does not exist at masternonmaster-23312Master site associated with snapshot group is no longer a master sitenonsnapshot-23314Calling site is not a snapshot sitenorepoption-23364Replication option not installedtypefailure-23319propagation_mode not specified correctly14.4.2 Creating and Dropping Snapshot Replication GroupsThe CREATE_SNAPSHOT_REPGROUP and DROP_SNAPSHOT_REPGROUP procedures allow you to create and destroy snapshot replication groups.14.4.2.1 The DBMS_REPCAT.CREATE_SNAPSHOT_REPGROUP procedureThis 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.NameDescriptiongnameName of the new snapshot groupmasterGlobal name of master sitecommentComment for the snapshot group; visible in DBA_REPSITES data dictionary viewpropagation_modeSnapshot propagation mode (SYNCHRONOUS, or ASYNCHRONOUS)14.4.2.1.1 ExceptionsThe CREATE_SNAPSHOT_REPGROUP procedure raises the following exceptions:NameNumberDescriptioncommfailure-23317Unable to communicate with masterdbnotcompatible-23375Attempt to use SYNCHRONOUS propagation in pre-7.3 databaseduplicaterepgroup-23374Replication group gname already existsnonmaster-23312The master parameter is not a master sitenorepoption-23364Replication option not installedtypefailure-23319propagation_mode not specified correctly14.4.2.1.2 RestrictionsNote 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.14.4.2.2 The offline snapshot instantiation procedureThe 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 [e-mail]@TCPIPPerform 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;14.4.2.3 The DBMS_REPCAT.DROP_SNAPSHOT_REPGROUP procedureThe 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.NameDescriptiongnameName of the snapshot group.drop_contentsIf TRUE, objects in gname are dropped. If FALSE (the default) they are simply no longer replicated.14.4.2.3.1 ExceptionsThe DROP_SNAPSHOT_REPGROUP procedure raises the following exceptions:NameNumberDescriptionmissingrepgroup-23373Replication group gname does not existnonmaster-23313Calling site is not a snapshot site14.4.2.3.2 RestrictionsIf drop_contents is set to FALSE, the triggers created to support snapshot modifications remain. 14.4.2.3.3 ExampleThe 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. 14.4.3 Adding and Removing Snapshot Replication Group ObjectsThe 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. 14.4.3.1 The DBMS_REPCAT.CREATE_SNAPSHOT_REPOBJECT procedureThe 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.NameDescriptionsnameName of schema to which oname belongs.onameName of object to be added.typeObject type. Supported types are PACKAGE, PACKAGE BODY, PROCEDURE, SNAPSHOT, SYNONYM, and VIEW.ddl_textDDL used to create object (for type SNAPSHOT only).commentComment on object, visible in DBA_REPOBJECT data dictionary view.gnameName of snapshot group to which object is being added. Defaults to sname if not specified.gen_objs_ownerName of the schema in which to create the generated trigger and trigger package or procedure wrapper for the object. Defaults to sname.drop_objectsIf 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.14.4.3.1.1 Exceptions.CREATE_SNAPSHOT_REPGROUP raises the following exceptions:NameNumberDescriptioncommfailure-23317Unable to communicate with master siteddlfailure-23318Unable to perform DDLduplicateobject-23309Object oname already existsmissingobject-23308Object oname does not exist in master's replication group gnamemissingremoteobject-23381Master site has not generated replication support for onamemissingschema-23306Schema sname does not existmisssnapobject-23355Object oname does not exist at masternonmaster-23312Master site associated with snapshot group is no longer a master sitenonsnapshot-23314Calling site is not a snapshot sitetypefailure-23319Invalid value for typeNOTE: 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).14.4.3.1.2 ExampleThe 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 [e-mail]' , gnam => 'SPROCKET', gen_objs_owner => 'SPROCKET') END; BEGIN14.4.3.2 The DBMS_REPCAT.DROP_SNAPSHOT_REPOBJECT procedureThe 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.14.4.3.2.1 ExceptionsDROP_SNAPSHOT_REPOBJECT raises the following exceptions:NameNumberDescriptionmissingobject-23308Object oname does not exist in master's replication group gnamenonsnapshot-23314Calling site is not a snapshot sitetypefailure-23319Invalid value for type14.4.3.2.2 RestrictionsIf 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. 14.4.3.2.3 ExampleThe 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;14.4.4 Altering a Snapshot Replication Group's Propagation ModeThe 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 groupLocks the snapshot base tables (SNAP$_<tablename>)Regenerates replication triggers and packages for the objects in the group14.4.4.1 The DBMS_REPCAT.ALTER_SNAPSHOT_PROPAGATION procedureCall 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.NameDescriptiongnameName of the replication group to be alteredpropagation_modeThe new propagation mode to use (SYNCHRONOUS or ASYNCHRONOUS)commentComment visible in DBA_REPPROP data dictionary viewexecute_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 user14.4.4.1.1 ExceptionsALTER_SNAPSHOT_PROPAGATION raises the following exceptions:NameNumberDescriptiondbnotcompatible-23375Database version is not 7.3 or latermissingrepgroup-23373Replication group gname does not existtypefailure-23319Invalid propagation_mode14.4.4.1.2 RestrictionsALTER_SNAPSHOT_PROPAGATION must be called from a snapshot site.14.4.4.1.3 ExampleThe 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;14.4.5 Manually Refreshing a Snapshot Replication GroupYou 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.14.4.5.1 The DBMS_REPCAT.REFRESH_SNAPSHOT_REPGROUP procedureCall 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.NameDescriptiongnameName of the replication group.drop_missing_contentsIf TRUE, drop schema objects that are no longer in the snapshot group. If FALSE (the default), objects are simply no longer replicated.refresh_snapshotsIf TRUE, force a refresh of snapshots in gname. Default is FALSE.refresh_other_objectsIf 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.14.4.5.1.1 ExceptionsREFRESH_SNAPSHOT_REPGROUP raises the following exceptions:NameNumberDescriptioncommfailure-23317Unable to communicate with the master sitenonmaster-23313Master is no longer a master databasenonsnapshot-23314Calling site is not a snapshot site14.4.5.1.2 RestrictionsREFRESH_SNAPSHOT_REPGROUP must be called from a snapshot site.14.4.5.1.3 ExampleThe 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.14.4.6 Switching the Master of a Snapshot Replication GroupShould 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.14.4.6.1 The DBMS_REPCAT.SWITCH_SNAPSHOT_MASTER procedureThe 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. NameDescriptiongnameName of the snapshot groupmasterName of the new master siteexecute_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 usersname(Oracle7 only)Not used14.4.6.1.1 ExceptionsThe SWITCH_SNAPSHOT_MASTER procedure may raise the following exceptions:NameNumberDescriptioncommfailure-23317Unable to communicate with master sitenonmaster-23312The master parameter is not a master sitenonsnapshot-23314Calling site is not a snapshot site14.4.6.1.2 RestrictionsNote 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.14.4.6.1.3 ExampleThe following call remasters snapshot group SPROCKET to D7NY.BIGWHEEL.COM:BEGIN DBMS_REPCAT.SWITCH_SNAPSHOT_MASTER( gname => 'SPROCKET', master => 'D7NY.BIGWHEEL.COM') ENDNOTE: 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.Figure 14.3: Using DBMS_REPCAT.SWITCH_SNAPSHOT_MASTER14.3 DBMS_OFFLINE_SNAPSHOT: Performing Offline Snapshot Instantiation15. Advanced Replication Copyright (c) 2000 O'Reilly & Associates. All rights reserved.