Oracle8i Replication Management API Reference Release 2 (8.1.6) A76958-01 |
|
Manage Replicated Environment with APIs, 3 of 9
Snapshot replication provides you with the flexibility to build data sets to meet your users' needs, your security configuration needs, and your front-end applications' needs. The following two sections describe how to manage snapshot sites with the replication management API.
Specifying a group owner when you define a new snapshot group and its related objects allows you to create multiple snapshot groups based on the same master group at a single snapshot site. At a snapshot site, specifying group owners enables you to create multiple snapshot groups that are based on the same master group. You accomplish this by creating the snapshot groups under different schemas at the snapshot site.
See Also:
The "Organizational Mechanisms" sections in Chapter 3 of Oracle8i Replication for a complete discussion on using group owners and the advantages of using multiple data sets. |
--The following procedures must be executed by the snapshot administrator --at the remote snapshot site. CONNECT snapadmin/snapadmin@snap1.world --The master group that you specify in the GNAME parameter must match the --name of the master group that you are replicating at the target master site. --The GOWNER parameter allows you to specify an additional identifier that lets --you create multiple snapshot groups based on the same master group at the same --snapshot site. --In this example, snapshot groups are created for the group owners BOB and --JANE, and these two snapshot groups are based on the same master group. --Create snapshot group with group owner (GOWNER) BOB. BEGIN DBMS_REPCAT.CREATE_SNAPSHOT_REPGROUP ( gname => 'SCOTT_MG', master => 'ORC1.WORLD', propagation_mode => 'ASYNCHRONOUS', GOWNER => 'bob'); END; / --Create snapshot group with group owner (GOWNER) JANE. BEGIN DBMS_REPCAT.CREATE_SNAPSHOT_REPGROUP ( gname => 'SCOTT_MG', master => 'ORC1.WORLD', propagation_mode => 'ASYNCHRONOUS', GOWNER => 'jane'); END; / --The GOWNER value used when creating your snapshot objects must match the --GOWNER value specified when you created the snapshot group (previous --procedures). After you have created the snapshot groups, you can create --snapshots of the same master table in the SCOTT_MG snapshot group owned by BOB --and JANE. --WARNING: You need to make sure that each object created has a unique name. --When using a GOWNER to create multiple snapshot groups, duplicate object names --could become a problem. To avoid any object-naming conflicts, you may want to --append the GOWNER value to the end of the object name that you create, as --illustrated in the following procedures (that is, create snapshot --SCOTT.BONUS_bob). Such a naming method ensures that you do not create any --objects with conflicting names. --Create snapshot based on the scott.bonus table in the SCOTT_MG snapshot --group owned by BOB. BEGIN DBMS_REPCAT.CREATE_SNAPSHOT_REPOBJECT ( gname => 'SCOTT_MG', sname => 'SCOTT', oname => 'BONUS_bob', type => 'SNAPSHOT', ddl_text => 'create snapshot SCOTT.BONUS_bob refresh fast with primary key for update as select * from SCOTT.BONUS@orc1.WORLD', min_communication => TRUE, GOWNER => 'bob'); END; / --Create snapshot based on the same scott.bonus table in the SCOTT_MG snapshot --group owned by JANE. BEGIN DBMS_REPCAT.CREATE_SNAPSHOT_REPOBJECT ( gname => 'SCOTT_MG', sname => 'SCOTT', oname => 'BONUS_jane', type => 'SNAPSHOT', ddl_text => 'create snapshot SCOTT.BONUS_jane refresh fast with primary key for update as select * from SCOTT.BONUS@orc1.WORLD', min_communication => TRUE, GOWNER => 'jane'); END; / --Create snapshot based on the scott.dept table in the SCOTT_MG snapshot --group owned by BOB. BEGIN DBMS_REPCAT.CREATE_SNAPSHOT_REPOBJECT ( gname => 'SCOTT_MG', sname => 'SCOTT', oname => 'DEPT_bob', type => 'SNAPSHOT', ddl_text => 'create snapshot SCOTT.DEPT_bob refresh fast with primary key for update as select * from SCOTT.DEPT@orc1.WORLD', min_communication => TRUE, GOWNER => 'bob'); END; / --Create snapshot based on the scott.emp table in the SCOTT_MG snapshot --group owned by JANE. BEGIN DBMS_REPCAT.CREATE_SNAPSHOT_REPOBJECT ( gname => 'SCOTT_MG', sname => 'SCOTT', oname => 'EMP_jane', type => 'SNAPSHOT', ddl_text => 'create snapshot SCOTT.EMP_jane refresh fast with primary key for update as select * from SCOTT.EMP@orc1.WORLD', min_communication => TRUE, GOWNER => 'jane'); END; / --Create snapshot based on the scott.salgrade table in the SCOTT_MG snapshot --group owned by BOB. BEGIN DBMS_REPCAT.CREATE_SNAPSHOT_REPOBJECT ( gname => 'SCOTT_MG', sname => 'SCOTT', oname => 'SALGRADE_bob', type => 'SNAPSHOT', ddl_text => 'create snapshot SCOTT.SALGRADE_bob refresh fast with primary key for update as select * from SCOTT.SALGRADE@orc1.WORLD', min_communication => TRUE, GOWNER => 'bob'); END; / --After you have finished building your snapshot groups, you should add your --snapshots to a refresh group. See Chapter 5, "Create Snapshot Group" --(Step 6) for more information about adding snapshots to a refresh group.
To change the master site of a snapshot group to another master site, call the SWITCH_SNAPSHOT_MASTER procedure in the DBMS_REPCAT package, as shown in the following example:
BEGIN DBMS_REPCAT.SWITCH_SNAPSHOT_MASTER( gname => 'sales', master => 'dbs2.acme.com' execute_as_user => 'FALSE'); END; /
In this example, the master site for the SALES object group is changed to the DBS2 master site. You must call this procedure at the snapshot site whose master site you want to change. The new database must be a master site in the replicated environment. When you call this procedure, Oracle uses the new master to perform a full refresh of each snapshot in the local snapshot group.
The entries in the SYS.SLOG$ table at the old master site for the switched snapshot are not removed. As a result, the MLOG$ table of the switched updatable snapshot at the old master site has the potential to grow indefinitely, unless you purge it by calling DBMS_SNAPSHOT.PURGE_LOG.
You may need to drop replication activity at a snapshot site for a number of reasons. Perhaps the data requirements have changed or an employee has left the company. In any case, as a DBA you will need to drop the replication support for the target snapshot site.
The process for dropping a snapshot group that was created by instantiating a deployment template at a snapshot site is slightly different than the methods described in the following sections. Before you drop the snapshot group at the remote snapshot site, you need to execute the DROP_SITE_INSTANTIATION procedure at the target master site for snapshot group. In addition to removing the metadata relating to the snapshot group, this procedure also removes the related deployment template data regarding this site.
The DROP_SITE_INSTANTIATION procedure has a public and a private version. The public version allows the owner of the snapshot group to drop the snapshot site, while the private version allows the replication administrator to drop a snapshot site on behalf of the snapshot group owner.
The following steps are to be performed by owner of the snapshot group.
Executed As: Snapshot Group Owner
Executed At: Master Site for Target Snapshot Site
Replication Status: Normal
CONNECT scott/tiger@orc1.world --If you need to drop a snapshot site that was instantiated on an Oracle8i Lite --database, see the Oracle8i Lite documentation for information. BEGIN DBMS_REPCAT_INSTANTIATE.DROP_SITE_INSTANTIATION( REFRESH_TEMPLATE_NAME => 'personnel', SITE_NAME => 'snap1.world'); END; / --After you have executed the DROP_SITE_INSTANTIATION procedure, you should --connect to the remote snapshot site and drop the snapshot group. If you are --not able to connect to the remote snapshot site due to loss or theft, the --target snapshot group cannot refresh, but the existing data --still remains at the snapshot site. CONNECT snapadmin/snapadmin@snap1.world --If you want to physically remove the contents of the snapshot group, be sure --that you specify TRUE for the DROP_CONTENTS parameter. BEGIN DBMS_REPCAT.DROP_SNAPSHOT_REPGROUP ( GNAME => 'scott_mg', DROP_CONTENTS => TRUE); END; / --After you remove the snapshot group, you should remove the refresh group. BEGIN DBMS_REFRESH.DESTROY ( NAME => 'personnel01'); END; /
The following steps are to be performed by the replication administrator on behalf of the snapshot group owner.
Executed As: Replication Administrator
Executed At: Master Site for Target Snapshot Site
Replication Status: Normal
CONNECT repadmin/repadmin@orc1.world --If you need to drop a snapshot site that was instantiated on an Oracle8i Lite --database, see the Oracle8i Lite documentation for information. BEGIN DBMS_REPCAT_RGT.DROP_SITE_INSTANTIATION ( REFRESH_TEMPLATE_NAME => 'personnel', USER_NAME => 'scott', SITE_NAME => 'snap1.world'); END; / --After you have executed the DROP_SITE_INSTANTIATION procedure, you should --connect to the remote snapshot site and drop the snapshot group. If you are --not able to connect to the remote snapshot site due to loss or theft, the --target snapshot group cannot refresh, but the existing data --still remains at the snapshot site. CONNECT snapadmin/snapadmin@snap1.world --If you want to physically remove the contents of the snapshot group, be sure --that you specify TRUE for the DROP_CONTENTS parameter. BEGIN DBMS_REPCAT.DROP_SNAPSHOT_REPGROUP ( GNAME => 'scott_mg', DROP_CONTENTS => TRUE); END; /
The most secure method of removing replication support for a snapshot site is to physically drop the replicated objects or groups at the snapshot site. The following two sections describe how to drop these objects and groups while connected to the snapshot group.
Ideally, these procedures should be executed while the snapshot is connected to its target master site. A connection ensures that any related metadata at the master site is removed. If a connection to the master site is not possible, be sure to complete the procedure described in the "Clean Up Master Site" to manually remove the related metadata.
When it becomes necessary to remove a snapshot group from a snapshot site, use the DROP_SNAPSHOT_REPGROUP procedure to drop a snapshot group. When you execute this procedure and are connected to the target master site, the metadata for the target snapshot group at the master site is removed. If you cannot connect, see "Clean Up Master Site" for more information.
Executed As: Snapshot Administrator
Executed At: Remote Snapshot Site
Replication Status: N/A
CONNECT snapadmin/snapadmin@snap1.world --If you want to physically remove the contents of the snapshot group, be sure --that you specify TRUE for the DROP_CONTENTS parameter. BEGIN DBMS_REPCAT.DROP_SNAPSHOT_REPGROUP ( GNAME => 'scott_mg', DROP_CONTENTS => TRUE); END; /
When it becomes necessary to remove an individual snapshot from a snapshot site, use the DROP_SNAPSHOT_REPOBJECT procedure API to drop a snapshot. When you execute this procedure, the metadata for the target snapshot at the master site is removed. When you execute this procedure and are connected to the target master site, the metadata for the target snapshot group at the master site is removed. If you cannot connect, see "Clean Up Master Site" for more information.
Executed As: Snapshot Administrator
Executed At: Remote Snapshot Site
Replication Status: N/A
CONNECT snapadmin/snapadmin@snap1.world --If you want to physically remove the contents of the snapshot, be sure --that you specify TRUE for the DROP_CONTENTS parameter. BEGIN DBMS_REPCAT.DROP_SNAPSHOT_REPOBJECT ( SNAME => 'scott', ONAME => 'bonus', TYPE => 'SNAPSHOT', DROP_OBJECTS => TRUE); END; /
If you are unable to drop a snapshot group or snapshot object while connected to the target master site, you must remove the related metadata at the master site manually. Cleaning up the metadata also ensures that you are not needlessly maintaining master table changes to a snapshot log. The following sections help you clean up your master site after dropping a snapshot group or object.
If you have executed the steps described in the "Drop Snapshot Group at Snapshot Site" section and were not connected to the master site, you are encouraged to complete the following steps to clean up the target master site.
Executed As: Replication Administrator
Executed At: Master Site for Target Snapshot Site
Replication Status: Normal
CONNECT repadmin/repadmin@orc1.world BEGIN DBMS_REPCAT.UNREGISTER_SNAPSHOT_REPGROUP ( GNAME => 'scott_mg', SNAPSITE => 'snap1.world'); END; / --After you unregister the snapshot group, you should purge the snapshot logs --of the entries that were marked for the target snapshots. The --PURGE_SNAPSHOT_FROM_LOG procedure must be executed for each snapshot --that was in the snapshot replication group. --NOTE: If for some reason unregistering the snapshot group fails, you are still --encouraged to complete the following steps. BEGIN DBMS_SNAPSHOT.PURGE_SNAPSHOT_FROM_LOG ( SNAPOWNER => 'scott', SNAPNAME => 'emp', SNAPSITE => 'snap1.world'); END; / BEGIN DBMS_SNAPSHOT.PURGE_SNAPSHOT_FROM_LOG ( SNAPOWNER => 'scott', SNAPNAME => 'dept', SNAPSITE => 'snap1.world'); END; / BEGIN DBMS_SNAPSHOT.PURGE_SNAPSHOT_FROM_LOG ( SNAPOWNER => 'scott', SNAPNAME => 'bonus', SNAPSITE => 'snap1.world'); END; / BEGIN DBMS_SNAPSHOT.PURGE_SNAPSHOT_FROM_LOG ( SNAPOWNER => 'scott', SNAPNAME => 'salgrade', SNAPSITE => 'snap1.world'); END; /
If you have executed the steps described in the "Drop Individual Snapshot at Snapshot Site" section and were not connected to the master site, you are encouraged to complete the following steps to clean up the target master site.
Executed As: Replication Administrator
Executed At: Master Site for Target Snapshot Site
Replication Status: Normal
CONNECT repadmin/repadmin@orc1.world BEGIN DBMS_SNAPSHOT.UNREGISTER_SNAPSHOT ( SNAPOWNER => 'scott', SNAPNAME => 'bonus', SNAPSITE => 'snap1.world'); END; / --After you unregister the snapshot, you should purge the associated snapshot --log of the entries that were marked for the target snapshots. --NOTE: If for some reason unregistering the snapshot fails, you are still --encouraged to complete the following step. BEGIN DBMS_SNAPSHOT.PURGE_SNAPSHOT_FROM_LOG ( SNAPOWNER => 'scott', SNAPNAME => 'bonus', SNAPSITE => 'snap1.world'); END; /
The following sections explain how to manage snapshot logs. Topics include:
After you create a snapshot log, you can alter its storage parameters and support for corresponding snapshots. The following sections explain more about altering snapshot logs. Only the following users can alter a snapshot log:
To alter a snapshot log's storage parameters, use the ALTER SNAPSHOT LOG statement. For example, the following statement alters a snapshot log on the CUSTOMERS table in the SALES schema:
ALTER SNAPSHOT LOG ON sales.customers PCTFREE 25 PCTUSED 40;
To add new filter columns to a snapshot log, use the SQL statement ALTER SNAPSHOT LOG. For example, the following statement alters a snapshot log on the CUSTOMERS table in the SALES schema:
ALTER SNAPSHOT LOG ON sales.customers ADD (zip);
Oracle automatically tracks which rows in a snapshot log have been used during the refreshes of snapshots, and purges these rows from the log so that the log does not grow endlessly. Because multiple simple snapshots can use the same snapshot log, rows already used to refresh one snapshot may still be needed to refresh another snapshot. Oracle does not delete rows from the log until all snapshots have used them.
For example, Oracle refreshes the CUSTOMERS snapshot at the SPDB1 database. However, the server that manages the master table and associated snapshot log does not purge the snapshot log rows used during the refresh of this snapshot until the CUSTOMERS snapshot at the SPDB2 database also refreshes using these rows.
As a result of how Oracle purges rows from a snapshot log, unwanted situations can occur that cause a snapshot log to grow indefinitely when multiple snapshots are based on the same master table. For example, such situations can occur when more than one snapshot is based on a master table and one of the following conditions is true:
Always try to keep a snapshot log as small as possible to minimize the database space that it uses. To remove rows from a snapshot log and make space for newer log records, you can perform one of the following actions:
To manually purge rows from a snapshot log, execute the PURGE_LOG stored procedure of the DBMS_SNAPSHOT package at the database that contains the log. For example, to purge entries from the snapshot log of the CUSTOMERS table that are necessary only for the least recently refreshed snapshot, execute the following procedure:
BEGIN DBMS_SNAPSHOT.PURGE_LOG ( master => 'sales.customers', num => 1, flag => 'DELETE'); END; /
Only the owner of a snapshot log or a user with the EXECUTE privilege for the DBMS_SNAPSHOT package can purge rows from the snapshot log by executing the PURGE_LOG procedure.
If a snapshot log grows and allocates many extents, purging the log of rows does not reduce the amount of space allocated for the log. To reduce the space allocated for a snapshot log:
LOCK TABLE sales.customers IN EXCLUSIVE MODE;
CREATE TABLE sales.templog AS SELECT * FROM sales.mlog$_customers;
TRUNCATE sales.mlog$_customers;
INSERT INTO sales.mlog$_customers SELECT * FROM sales.templog; DROP TABLE sales.templog;
ROLLBACK;
Only the owner of a snapshot log or a user with the DELETE ANY TABLE system privilege can truncate a snapshot log.
To improve performance and optimize disk use, you can periodically reorganize tables. This section discusses how to reorganize a master table and preserve the fast refresh capability of associated snapshots.
When you reorganize a table, any ROWID information of the snapshot log must be invalidated. Oracle detects a table reorganization automatically only if the table is truncated as part of the reorganization. See "Method 2 for Reorganizing Table T".
If the table is not truncated, Oracle must be notified of the table reorganization. To support table reorganizations, two procedures, DBMS_SNAPSHOT.BEGIN_TABLE_REORGANIZATION and DBMS_SNAPSHOT.END_TABLE_REORGANIZATION, notify Oracle that the specified table has been reorganized. The procedures perform clean-up operations, verify the integrity of the logs and triggers that the fast refresh mechanism needs, and invalidate the ROWID information in the table's snapshot log. The inputs are the owner and name of the master table to be reorganized. There is no output.
When a table is truncated, its snapshot log is also truncated. However, for primary key snapshots, you can preserve the snapshot log, allowing fast refreshes to continue. Although the information stored in a snapshot log is preserved, the snapshot log becomes invalid with respect to ROWIDs when the master table is truncated. The ROWID information in the snapshot log will seem to be newly created and cannot be used by ROWID snapshots for fast refresh.
If you specify the PRESERVE SNAPSHOT LOG option or no option, the information in the master table's snapshot log is preserved, but current ROWID snapshots can use the log for a fast refresh only after a complete refresh has been performed. This is the default behavior.
Note: To ensure that any previously fast refreshable snapshot is still refreshable, follow the guidelines in "Methods of Reorganizing a Database Table". |
If the PURGE SNAPSHOT LOG option is specified, the snapshot log is purged along with the master table.
Either of the following two statements preserves snapshot log information when the master table named ORDERS is truncated:
TRUNCATE TABLE orders PRESERVE SNAPSHOT LOG; TRUNCATE TABLE orders;
The following statement truncates the snapshot log along with the master table:
TRUNCATE TABLE orders PURGE SNAPSHOT LOG;
Oracle provides four table reorganization methods that preserve the capability for fast refresh. These appear in the following sections. Other reorganization methods require an initial complete refresh to enable subsequent fast refreshes.
Method 1 for Reorganizing Table T
Ensure that no transaction is issued against the reorganized table between calling DBMS_SNAPSHOT.BEGIN_TABLE_REORGANIZATION and DBMS_SNAPSHOT.END_TABLE_REORGANIZATION.
Method 2 for Reorganizing Table T
Ensure that no transaction is issued against the reorganized table between calling DBMS_SNAPSHOT.BEGIN_TABLE_REORGANIZATION and DBMS_SNAPSHOT.END_TABLE_REORGANIZATION.
Method 3 for Reorganizing Table T
Ensure that no transaction is issued against the reorganized table between calling DBMS_SNAPSHOT.BEGIN_TABLE_REORGANIZATION and DBMS_SNAPSHOT.END_TABLE_REORGANIZATION.
Method 4 for Reorganizing Table T
Ensure that no transaction is issued against the reorganized table between calling DBMS_SNAPSHOT.BEGIN_TABLE_REORGANIZATION and DBMS_SNAPSHOT.END_TABLE_REORGANIZATION.
You can delete a snapshot log regardless of its master table or any existing snapshots. For example, you might decide to drop a snapshot log if one of the following conditions is true:
To delete a snapshot log, execute the DROP SNAPSHOT LOG SQL statement in SQL*Plus. For example, the following statement deletes the snapshot log for a table named CUSTOMERS in the SALES schema:
DROP SNAPSHOT LOG ON sales.customers;
Only the owner of the master table or a user with the DROP ANY TABLE system privilege can drop a snapshot log.
|
![]() Copyright © 1999 Oracle Corporation. All Rights Reserved. |
|