Oracle8i Replication
Release 2 (8.1.6)

Part Number A76959-01

Library

Product

Contents

Index

Go to previous page Go to next page

4
Deployment Templates Concepts & Architecture

This chapter introduces deployment templates and describes how to use them to easily and efficiently distribute snapshot environments. The chapter covers the following topics:

Mass Deployment Challenge

Oracle deployment templates provide you with the tools to efficiently deploy and administer a widely distributed snapshot environment. Before learning about the concepts, architecture, and use of deployment templates, examine the challenges of a mass deployment environment.

The need to have accurate information at any time and at any place continues to grow rapidly. At the same time, information is becoming decentralized and users are often disconnected from the network, requiring the information to be distributed to the active points-of-usage.

Consider the mobile sales force. Potentially hundreds, if not thousands, of professionals need accurate information about their customers on a laptop in a manner that causes the salesperson very little inconvenience. The challenge, therefore, is for the database administrator to roll out the data and the database infrastructure (tables, indexes, constraints, triggers, and so on) to all sites in an efficient and timely manner.

Traditionally, the DBAs have been required to develop a deployment method of their own. This usually means that the DBA was responsible for developing a very complex script to create the snapshot environment at the remote snapshot site. In addition to building the script, the DBA was often forced to customize data sets at the snapshot site. Once the DBA completed engineering the script, deploying the script required manual packaging and implementation, both of which often required extensive troubleshooting.

The problems encountered in the above scenario have spawned technologies and resources dedicated to the art of efficient mass deployment. Mass deployment is the term used to describe the process of distributing database infrastructure, data, and front-end applications to a large number of users. For the purposes of Oracle replication, the discussion of mass deployment is limited to the delivery of data and data infrastructure.

Deployment Templates and the Mass Deployment Goal

Mass deployment tools and technologies should aid the database administrator in delivering the data and database infrastructure. The goal is to define the environment once and create as many instances of the deployment template as necessary, while still maintaining the ability to customize individual sites.

To support this goal, Oracle's deployment templates enable you to:

Define Snapshot Environment Once 

You define the structure of a snapshot environment once using a deployment template so that each user (site) receives the database infrastructure to support the front-end application. 

Customize Snapshot Sites Individually 

You use deployment template parameters to customize each snapshot environment so that each user receives the particular data subset needed. 

Mass deployment has many applications, such as distributing information to mobile sales forces, field technicians, retail stores, remote inventory collection sites, and so on. Such environments use deployment templates to build the database infrastructure at the remote site, largely because deployment templates support data subsetting, disconnected replication, and lower resource requirements, making them ideal for laptops users.

Oracle Deployment Templates Concepts

Oracle offers deployment templates to allow the database administrator to package a snapshot environment for easy, custom, and secure deployment. A deployment template can be as simple as a single snapshot with a fixed data set, or as complex as hundreds of snapshots with a dynamic data set based on one or more variables.

Deployment template features include the following:

To prepare a snapshot environment for deployment, you create a deployment template at the master site. This template stores all of the information needed to deploy a snapshot environment, including the data definition language (DDL) to create the objects at the remote site and the target refresh group. This template also maintains links to user security information and template parameters for custom snapshot creation.

Deployment Template Elements

Each deployment template contains the "blueprint" for creating the necessary snapshots and related objects at a snapshot site. Specifically, you create the deployment template at the master site, adding the necessary snapshots, triggers, views, and so on to the template as needed to create the snapshot environment. You can optionally define template parameters and authorized users, giving the template greater flexibility and security during the instantiation process.

Deployment template elements can be divided into the following four categories:

General Template Information

Oracle deployment templates center around the general template information, which consists of the template name, target refresh group, and private/public status. As illustrated in Figure 4-1, the REFRESH_TEMPLATE_NAME is used in all aspects of deployment template data dictionary views. You add the snapshot environment objects to the template prior to releasing the template for distribution according to the specified template identification (see Figure 4-2).

A deployment template is defined at a single master site. While you cannot have two deployment templates at the master site with the same name, you can copy a deployment template to another site using the same deployment template name.

Figure 4-1 Refresh Group Template View Relationships


Figure 4-2 Deployment Template Elements Added to Template


Object Definitions

After the template has been defined, you add objects to the template. When the template is instantiated at the snapshot site, the object DDL (that is, CREATE SNAPSHOT, CREATE TABLE, and so on) is executed to create the appropriate objects at the snapshot site.

Objects added to a deployment template can be created based on an existing master object, but if necessary, you can create a new template object by defining DDL to create the object. Oracle checks any new object DDL to make sure that it is lexically correct, which prevents the execution of faulty DDL. Updateable snapshots added to a deployment template must be based on a table in a master group, but other objects, such as read-only snapshots, can be based on objects that are not in master groups.

In most cases, you add snapshots to the template, but if necessary, you can add other objects. For example, constraints can be added to enforce data integrity at the snapshot site, views can be added for displaying data, or tables can be added for local data storage. In some cases, you may even want to include all objects for an application in a deployment template. Snapshots created using a deployment template are automatically added to the refresh group defined for the template.

Deployment templates that are instantiated at Oracle8i Lite snapshot sites only build snapshots. If snapshots and other types of objects are included in a deployment template instantiated at an Oracle8i Lite site, the snapshots are built when the template is instantiated, but the other objects are not built.

See Also:

"General Template Information" for more information about the refresh group. 

Template Parameters

If each target snapshot site requires a data set unique to its site, you can define variables in the object DDL. These variables create a parameterized template that allows for custom data sets when the template is instantiated, allowing different snapshot sites to have different data sets. These parameters are embedded in the object DDL. During template instantiation, the individual user values for these parameters are substituted.

Oracle allows you to specify default values and user-specific parameter values for a template. You can enter the parameter values during the creation of the deployment template or after the template is created, but you must enter the parameter values before the template is instantiated. Users cannot enter values for parameters during instantiation.

If user-specific parameter values exist, then these values are automatically used when the specified user instantiates the template. For example, consider the variable REGION. Suppose you establish the following user-specific parameter values for template SALES_TEMP:

USER  REGION 
SCOTT  EAST 
LARRY  WEST 

The defining SELECT statement for the snapshot is the following:

SELECT cust_id, sales_to_date, status FROM table_x WHERE region_id=:region;

When users SCOTT and LARRY instantiate template SALES_TEMP, their resulting snapshot data sets are the following:

User SCOTT    User LARRY 
CUST ID  REGION    CUST ID  REGION 
A123  EAST    B123  WEST 
A234  EAST    B234  WEST 
A345  EAST    B345  WEST 
A456  EAST    B456  WEST 

Template Parameters in the WHERE Clause and Security

In addition to creating customized data subsets, you can use template parameters in the WHERE clause of a CREATE SNAPSHOT statement to securely limit the snapshot site to viewing and changing only the data that satisfies the WHERE clause. For example, suppose you have specified the following for the REGION parameter in the user specific parameters list:

USER  REGION 
SCOTT  EAST 
LARRY  WEST 

Users accessing the snapshot instantiated by user SCOTT only see data for region EAST and can only view, update, or delete data that complies with this WHERE clause. In other words, a user of this snapshot cannot view, update, or delete data for region WEST, because the snapshot only contains data for region EAST.

User Authorization

Deployment templates can be either public or private. You set this when you create the template. If a template is public, any user with access to the master site can instantiate the template.

If a template has been created for private use, then only authorized users can instantiate the target template. To enforce private use, you create a list of authorized users at the master site. If an unauthorized user attempts to instantiate the target template, the instantiation process fails.

Deployment Sites

Maintaining the emphasis on centralized control, you can monitor and manage certain characteristics of the instantiated environment at the remote snapshot site. Specifically, you have the ability to view the sites that have instantiated a deployment template, which includes the deployment template name, authorized user, and status of the instantiated environment.

Packaging and Instantiating Deployment Templates

When you have completed defining your deployment template, the template needs to be packaged to prepare it for instantiation at the remote snapshot site. When the packaged deployment template is instantiated at a snapshot site, the snapshot site objects are created and the snapshots are populated with data. Remote snapshot sites can be created either through online or offline instantiation.

Online Instantiation

Online instantiation allows a snapshot site to instantiate a deployment template while connected to the target master site. During the online instantiation process, the structure of the snapshot site is created, and the specified data subset is pulled from the master site and stored in the appropriate snapshots.

Figure 4-3 Online Instantiation


For Oracle8i Enterprise Edition, Oracle8i Standard Edition, or Oracle8i Personal Edition snapshot clients, packaging a deployment template for online instantiation means generating a script file that, when run at the snapshot site, creates the snapshot objects and connects to the master site to populate the snapshots with data. SQL statements such as CREATE SNAPSHOT ... AS SELECT are used to populate the snapshots with data over a network from the master site.

When you package a template for online instantiation of an Oracle8i Lite snapshot client, no script or binary file is required for instantiation. A user connects to the master site with a client application, such as the Oracle Client Replication Tool, to both package and instantiate a deployment template in one operation.

One of the benefits of online instantiation is that the data subset is current as of the instantiation process. This data currency, however, comes at a cost. Online instantiation requires a "live" connection between the snapshot and master sites, which, depending on the size of the snapshot environment created, may increase network traffic.

Furthermore, laptop users connected by a modem may need to stay connected for a long time. The duration of the connection depends on the number of objects created, the complexity of the snapshot subqueries, and the amount of data transmitted, especially over low bandwidth modem lines.

Offline Instantiation

To decrease server loads during peak usage periods and reduce remote connection times, you may choose offline instantiation of the template for your environment. Packaging a template for offline instantiation means generating a script or a binary file that contains the DDL and data manipulation language (DML) to build the snapshot environment defined in the deployment template and populate the environment with data. You package the script or binary file and save the file to some type of storage media (such as tape, CD-ROM, and so on), and then provide a means of transferring the script or binary file to the snapshot site. Each snapshot site requires a separate offline instantiation script.

When you package a template for instantiation, the snapshot logs for each master table on which a snapshot is based in the template begin to log changes. The snapshot log for a particular master table does not clear these changes until every snapshot based on the master table refreshes after instantiation. Therefore, to prevent the snapshot log from growing large, the template should be instantiated, and the snapshots should be refreshed as soon as possible after packaging.

During instantiation, the template and data are pulled from the storage media, instead of being pulled from the master site. This has the benefit of reducing network traffic and eliminating the need for a constant network connection. However, after instantiation, the data in the snapshot site reflects the master site data at packaging time and must be made current by a refresh.

Figure 4-4 Offline Instantiation


Offline instantiation is an ideal solution for mass deployment situations where many laptops and other disconnected computers are instantiating the target template.

Scenarios for Instantiating a Deployment Template

The target instantiation site must run one of the following Oracle database clients:

The most typical mass deployment scenario has Oracle8i Enterprise Edition at the master site and Oracle8i Lite at the remote site, which is often on a laptop.

Table 4-2 summarizes the scenarios for instantiating of a deployment template.

Table 4-1 Scenarios for Instantiating of a Deployment Template
Type of Instantiation  Type of Snapshot Client  Description 

Offline 

Oracle8i Enterprise Edition

Oracle8i Standard Edition

Oracle8i Personal Edition 

The user runs the offline instantiation script with SQL*Plus. The offline instantiation script contains both CREATE statements to create snapshot site objects and INSERT statements to populate the snapshots with data.  

Offline 

Oracle8i Lite
(Java RepAPI) 

A user opens the binary file for offline instantiation with a client application tool, such as the Oracle Client Replication Tool. This tool then uses the binary file to create the snapshot site objects and populate the snapshots with data. 

Online 

Oracle8i Enterprise Edition

Oracle8i Standard Edition

Oracle8i Personal Edition 

The user runs the online instantiation script with SQL*Plus. The online instantiation script contains CREATE statements to create snapshot site objects. When snapshot objects are created, the online instantiation script connects to the master site and uses CREATE SNAPSHOT ... AS SELECT statements to create the snapshots and populate them with data.  

Online 

Oracle8i Lite
(Java RepAPI) 

A user connects to the master site with a client application tool, such as the Oracle Client Replication Tool. This tool packages and instantiates the template in one operation. No file is necessary at the client. 

Either you (the DBA) or the target user can package the deployment template. Either use Replication Manager's Offline Instantiation Wizard to package a template for offline instantiation, or the replication management API to package a template for offline or online instantiation. End-users use the public API to package a deployment template, while DBAs generally use the private API for packaging. Typically, when a deployment template will be instantiated offline, the DBA performs the packaging, but when the deployment template will be instantiated online, the user may perform the packaging. However, there are no restrictions on users or DBAs performing either online or offline packaging, other than the use of different API calls.

The following replication management API functions can be used to package a deployment template.

Private functions (DBA use only):

Public functions:

Deployment Template Architecture

Oracle uses standard snapshot architecture with deployment templates to distribute snapshot environments quickly and effectively. Deployment templates use the same methods in creating snapshot definitions, refresh characteristics, conflict resolution, and grouping as used when manually building a snapshot environment. The distinction to remember is that instead of executing the DDL to create the object immediately, the object DDL is simply contained in a deployment template and will be executed when the template is instantiated.

Template Definitions Stored in System Tables

Instead of executing DDL at the snapshot site to immediately create a snapshot environment, the snapshot and other related object definitions are stored within the deployment template. After all of the object definitions have been added to the deployment template, the template can be instantiated to execute all of the stored DDL at the remote snapshot site, which creates the necessary snapshot environment.

All of these object definitions are stored in system tables maintained at the deployment template definition site, keyed on the deployment template name. When the deployment template is packaged, the stored object DDL is pulled from these system tables to create the instantiation script of binary file. The only exception to this is that online instantiation of an Oracle8i Lite snapshot site does not require a local file because a client application tool, such as the Oracle Client Replication Tool, performs the packaging automatically when it connects to the master site.

Use of Standard DDL

Template object definitions are created using the same DDL that is used to create the objects locally at the snapshot site. For example, you execute the following to create a snapshot locally:

CREATE SNAPSHOT foo_snap AS SELECT empid, region, dept, salary 
   FROM scott.foo@hq.com;

To add this same snapshot to a deployment template, you use the Replication Manager's Deployment Template Wizard, or you execute the CREATE_TEMPLATE_OBJECT function, as shown in the following example:

DECLARE
a NUMBER;
BEGIN
   a := DBMS_REPCAT_RGT.CREATE_TEMPLATE_OBJECT(
      refresh_template_name => 'dt_snapenv',
      object_name => 'foo_snap',
      object_type => 'snapshot',
      ddl_text => 'CREATE SNAPSHOT foo_snap 
                     AS SELECT empid, region, dept, salary
                     FROM scott.foo@hq.com');
END;
/


Note:

Do not place a terminating semi-colon in the DDL statement inside the single quotation marks for the DDL_TEXT parameter. 


Executing the above function adds the snapshot definition to the deployment template named DT_SNAPENV. When this particular snapshot is instantiated, the snapshot FOO_SNAP is created. In addition to creating snapshots, you can add table, trigger, procedure, index, and other object definitions to the deployment template.

Whenever you create a snapshot, always specify the schema name of the table owner in the query for the snapshot. In the example above, SCOTT is specified as the owner of the FOO table.

See Also:

DBMS_REPCAT_RGT.CREATE_TEMPLATE_OBJECT in the Oracle8i Replication Management API Reference for information about using this function. 

Packaging and Instantiation Process

When a deployment template is packaged in preparation for remote snapshot site instantiation, the template is being prepared for online or offline instantiation. The instantiation procedure creates the remote snapshot environment and populates the environment with data.

Packaging a Deployment Template for Online Instantiation

When a deployment template is packaged for online instantiation, the resulting DDL that is required to create the remote snapshot environment is generated and all template parameter substitutions are performed. Where this generated DDL is stored depends on the type of snapshot client.

Packaging a Deployment Template for Offline Instantiation

When a deployment template is packaged for offline instantiation, the DDL that is required to create the remote snapshot environment and the DML that is required to populate the environment with the data are both stored in a generated file. Also, during packaging, all template parameter substitutions are performed.

When a template is packaged, a script or binary file is created for offline instantiation and is saved to a storage device, such as hard disk, CD-ROM, tape, and so on. Either Replication Manager's Offline Instantiation Wizard or the replication management API can be used to package a deployment template for offline instantiation.

When the remote snapshot site instantiates the template, the script or binary file is executed from the storage media or from the local hard drive. This execution creates the snapshot environment and populates the environment according to the data set defined during the packaging process. Recall that any template parameters that define the data set for individual sites are defined during the packaging process.

Online Instantiation

During the online instantiation process, the structure of the snapshot site is created, and the specified data subset is pulled from the master site and stored in the appropriate snapshots. Also, once the remote snapshot site begins the online instantiation process, Oracle evaluates the parameters that have been defined for the deployment template. Any values defined for these parameters will be used when the object DDL in the template is executed so that custom data sets can be installed at the remote snapshot site. At the same time, the snapshots are registered at the master site, and the snapshot logs begin logging the changes to the master tables.

There are two possible methods that can be used to define template parameter values: default parameter values and user parameter values. Oracle checks to see if these parameter values exist and then uses them according to the hierarchy:

  1. User Parameter Values

  2. Default Parameter Values

If user parameter values have been defined and a listed user is instantiating the template, the user parameter values are used when instantiating the template. If no user parameter values have been defined, Oracle uses the default parameter values. Figure 4-5 shows the parameter checking process.

Figure 4-5 Checking for Parameters During Online Instantiation


After the parameters are checked, the objects created by the template are added to the refresh group specified when the template was created.

Offline Instantiation

In a mass deployment environment, most snapshot environments use the offline instantiation method to create the necessary snapshot environment. When you package the deployment template, a script or binary file is created to store the DDL needed to create the snapshot environment, the parameter values used during the instantiation process, and the DML necessary to populate the snapshot environment with data.

The script or binary file can be copied to a CD-ROM, floppy disk, or other storage media or can be posted on a Web or FTP site to be downloaded to the remote snapshot site. The flexibility in delivery mechanisms allows you and your users to choose the most effective method for instantiating a deployment template.

Packaging and Instantiation Options

There are a number of possibilities for deployment template packaging and instantiation. Table 4-2 illustrates the possibilities, identifies the mechanism for packaging and instantiation, and lists the documentation to use when you perform an operation.

Table 4-2 Packaging and Instantiation Options (Page 1 of 2)
Type of Instantiation  Type of Client for Instantiation  Package Template Using  Packaging Documentation  Instantiate Template Using  Instantiating Documentation 

Offline 

Oracle8i Enterprise Edition

Oracle8i Standard Edition

Oracle8i Personal Edition 

Replication Manager Offline Instantiation Wizard 

See Replication Manager online help topic "Package for Offline Instantiation: Overview" under "Deployment Templates" > "Packaging and Instantiation" in the the Help Contents.

 

Offline Instantiation Script and SQL*Plus 

See Replication Manager online help topic "Instantiate at Remote Snapshot Site" under "Deployment Templates" > "Packaging and Instantiation" in the the Help Contents. 

Offline 

Oracle8i Enterprise Edition

Oracle8i Standard Edition

Oracle8i Personal Edition 

The Replication Management API (PL/SQL Packages and SQL*Plus) 

See the "Package for Instantiation" section of Chapter 4 in Oracle8i Replication Management API Reference

Offline Instantiation Script and SQL*Plus 

See the "Instantiate Deployment Template" section of Chapter 4 in Oracle8i Replication Management API Reference

Online 

Oracle8i Enterprise Edition

Oracle8i Standard Edition

Oracle8i Personal Edition 

The Replication Management API (PL/SQL Packages and SQL*Plus) 

See the "Package for Instantiation" section of Chapter 4 in Oracle8i Replication Management API Reference

Online Instantiation Script and SQL*Plus 

See the "Instantiate Deployment Template" section of Chapter 4 in Oracle8i Replication Management API Reference

Offline 

Oracle8i Lite
(Java RepAPI) 

Replication Manager Offline Instantiation Wizard 

See Replication Manager online help topic "Package for Offline Instantiation: Overview" under "Deployment Templates" > "Packaging and Instantiation" in the the Help Contents. 

Binary File for Offline Instantiation and a Client Application Tool, such as the Oracle Client Replication Tool

 

See the Oracle8i Lite documentation for information about instantiating the template. 

Offline 

Oracle8i Lite
(Java RepAPI) 

The Replication Management API (PL/SQL Packages and SQL*Plus) 

See the "Package for Instantiation" section of Chapter 4 in Oracle8i Replication Management API Reference

Binary File for Offline Instantiation and a Client Application Tool, such as the Oracle Client Replication Tool 

See the Oracle8i Lite documentation for information about instantiating the template. 

Online 

Oracle8i Lite
(Java RepAPI)

 

A Client Application Tool, such as the Oracle Client Replication Tool 

See the Oracle8i Lite documentation for information about packaging the template. 

A Client Application Tool, such as the Oracle Client Replication Tool 

See the Oracle8i Lite documentation for information about instantiating the template. 

Post-Instantiation

After instantiating a deployment template at a remote snapshot site, the structure created is exactly the same as if you had created the snapshot environment locally at the snapshot site. Specifically, Oracle creates the snapshot, with the specified name, and an index based on the primary key to maintain constraint consistency. Other objects in the template are also created as if they were created manually at the snapshot site.

With respect to offline instantiations, the longer the duration between the packaging at the server and the instantiation at the remote site, the longer it takes for the first refresh after instantiation at the remote snapshot site. The snapshot site uses the snapshot log at the master site to perform the fast refresh from the time that the template was packaged. Recall that changes made to the master table are logged to the snapshot log as soon as you package the deployment template.

See Also:

"Snapshot Architecture" for more information. 

Snapshot Groups

Objects created by an instantiated deployment template are added automatically to a snapshot group with a name derived from the object's master group. For example, if you instantiated the DT_SNAPENV deployment template, which contains objects from the PERSONNEL and TECHNICAL master groups, your template objects are added to snapshot groups PERSONNEL01 and TECHNICAL01, respectively (the numbered suffix may change if multiple templates contain objects from the same master group). Remember that a snapshot group helps to maintain organizational consistency with the target master group and, more importantly, is required for updateable snapshots.

See Also:

"Snapshot Groups" for more information. 

Refresh Groups

When you first begin building a deployment template, you define the name of the refresh group to which the template's snapshot objects will be added. After the instantiation process is finished, you can specify that the snapshots in the refresh group be refreshed automatically at set intervals, assuming a constant network connection to the master site.

For Oracle8i Enterprise Edition, Oracle8i Standard Edition, or Oracle8i Personal Edition, Replication Manager or DBMS_REFRESH.CHANGE procedure can be used to change the refresh interval and next refresh data of a refresh group. To change these settings in Replication Manager, select the refresh group and edit the Next Date and Interval fields. To change these settings with the DBMS_REFRESH.CHANGE procedure, set the interval and next_date parameters appropriately. If snapshot sites do not have a constant network connection to the master site, they can refresh their refresh groups on-demand.


Note:

Refresh groups at Oracle8i Lite sites can only be refreshed on-demand, not at set intervals. Also, the name of the refresh group must be the same as the name of the deployment template when building deployment templates to be instantiated at an Oracle8i Lite site. 


Deployment Template Design

Before you begin assembling your deployment template, you should consider how you want to build your templates. The combination of deployment template parameters and subquery subsetting gives the database administrator a powerful tool to administer a widely distributed database environment using assignment tables and horizontally partitioned data. Additional design consideration must be given to vertical partitioning requirements and data sets needed for a replicated environment.

See Also:

"Data Subsetting with Snapshots" for more information on data subsetting, and see "Vertical Partitioning" for more information on vertical partitioning. 

Vertical Partitioning

Deployment templates offer the additional advantage of allowing you to build updateable snapshots that are vertically partitioned. Vertical partitioning is also referred to as column subsetting. For example, in a mass deployment environment with many "lightweight" clients, you may need to replicate tables that contain LOB data without actually replicating the LOB data itself. This can be achieved by excluding the LOB column from the selected columns to be replicated when defining the vertical partition.

The vertically partitioned snapshot that you add to your deployment template must contain the following:

Figure 4-6 Replicate Vertically Partitioned Data


If you are adding a snapshot object that replicates columns PK, EmpID, Salary, and Level (illustrated in Figure 4-6), you also need to include the Time Stamp column because it is used for conflict resolution for columns contained in Column Group A.


Note:

Vertical partitioning is only available when you add a snapshot object to a deployment template using Oracle Replication Manager. Vertical partitioning is not available when using the replication management API. 



Note:

The master definition site must be available when defining a vertical partition. If your deployment template contains vertically partitioned snapshots from multiple master groups, the master definition site for each group must be available. 


Horizontal Partitioning with Assignment Tables

As discussed in the previous section, snapshot data sets are defined based on the snapshot's query, meaning that the user only sees data that complies with the snapshot's defining query.

Figure 4-7 Customer/Salesperson Relationship


With this in mind, if "assignment" tables are used in conjunction with subquery subsetting, you can easily control changes to the snapshot environment.

For example, consider the customer/salesperson relationship in Figure 4-7. In this example, a salesperson is assigned customers based on the Assignment table. If new salespersons are hired or other salespersons leave, the existing customers can be assigned to their new salesperson by simply modifying the contents of the assignment table. Besides creating a single point of administration, assignment tables, used in conjunction with subquery subsetting, make this administration easy while ensuring security. For example, salesperson #1001 cannot view the customer information of other salespersons, which is very important if the customer information contains sensitive data.

Considering the relationships pictured in Figure 4-7 above, if the ORDERS snapshot's defining query was specified as the following:

CREATE SNAPSHOT sales.orders AS
 SELECT * FROM sales.orders@hq.acme.com o
    -- conditions for customers
  WHERE EXISTS
  ( SELECT c_id FROM sales.customer@hq.acme.com c
    WHERE o.c_id = c.c_id
      AND EXISTS
    ( SELECT * FROM sales.assignment@hq.acme.com a
     WHERE a.c_id = c.c_id
     AND EXISTS
     ( SELECT * FROM sales.salesperson@hq.acme.com s
       WHERE s.s_id = ':salesperson_id')));

Then, the ORDERS snapshot is populated with order data for the customers that are assigned to the salesperson specified for the :salesperson_id variable. Notice the :salesperson_id variable in the last line of the CREATE SNAPSHOT statement.

With this flexibility, managers can easily control snapshot data sets by making simple changes to the assignment table, without requiring you to modify any SQL. For example, if the specified salesperson was assigned two new customers, the manager would simply assign these two new customers to the salesperson in the assignment table. After the next snapshot refresh, the data for these two customers is propagated to the target snapshot site, such as the salesperson's laptop. Conversely, if a customer was taken away from the specified salesperson, all data pertaining to the specified customer is removed from the snapshot site after the next refresh and the salesperson can no longer access that information.

Data Sets

When designing your deployment templates, you need to consider the different sets of users that need to access the target data. For example, both salespersons and technicians need customer information, but the technicians may not need sales information.

Figure 4-8 Some Users Require All Data


You do not want users to instantiate deployment templates that may contain extraneous data, because it will only require extra storage space and additional refresh times.

On the other hand, if you have users that require both sales and customer support information, you do not want users to have to instantiate multiple deployment templates that may share redundant data. Instantiating multiple templates may cause data consistency problems; each deployment template uses a different refresh group, which means that data in the two deployment templates may be refreshed at different times, possibly causing data consistency problems.

In this case, the best solution would be to have one deployment template for salespersons, one for customer service technicians, and one for users that require both sets of data.

To save time and effort, the best way to create the above three templates is to create the template with both sets of data first, copy the template twice, deleting unneeded items to create the other deployment templates.

Figure 4-9 The Different Needs of Salespersons And Customer Support Technicians


Another design consideration you should take into account is the usage of parameters. If many of the tables above use the Customer ID field, you could define the same parameter in each of the template objects. By using the same parameter, you would only need to define the default parameter value once, and it would be used for all objects during the instantiation process.

Using a single template parameter is even more useful when used with snapshots that use subquery subsetting. One parameter would allow a user to receive only the data for the customers that they need. Consider the following CREATE SNAPSHOT statements:

CREATE SNAPSHOT sales.orders AS
 SELECT * FROM sales.orders@hq.acme.com o
    -- conditions for customers
  WHERE EXISTS
  ( SELECT c_id FROM sales.customer@hq.acme.com c
    WHERE o.c_id = c.c_id
      AND EXISTS
    ( SELECT * FROM sales.assignment@hq.acme.com a
     WHERE a.c_id = c.c_id
     AND EXISTS
     ( SELECT * FROM sales.salesperson@hq.acme.com s
       WHERE s.s_id = ':salesperson_id')));

CREATE SNAPSHOT sales.customer AS
SELECT c_id FROM sales.customer@hq.acme.com c
  -- conditions for customers
  WHERE EXISTS
   ( SELECT * FROM sales.assignment@hq.acme.com a
     WHERE a.c_id = c.c_id
     AND EXISTS
     ( SELECT * FROM sales.salesperson@hq.acme.com s
       WHERE s.s_id = ':salesperson_id')));

Even though the two snapshots being created do not explicitly contain the SALESPERSON_ID field, using subquery subsetting makes using parameters very effective for instantiating only required data sets. Using a single parameter (:salesperson_id) makes managing and instantiating these snapshots easier for both the DBA and the user instantiating the deployment template.

Additional Design Considerations

Finally, you should consider what other objects need to be created at the remote snapshot site. Consider the following questions:

Local Control of Snapshot Creation

A deployment template is the most effective method of building and distributing a snapshot environment. Even if distribution is limited to only two or three sites, you still significantly reduce the amount of steps needed to build a snapshot environment by using deployment templates as opposed to individually creating the snapshot environment at those two or three sites. With deployment templates, you build once and distribute as needed.

However, one question remains: If a deployment template is the most effective means for building and distributing a snapshot environment, when should you locally build the snapshot environment at the remote snapshot site? In most cases, you should build a snapshot environment using the Snapshot Group Wizard or locally at the snapshot site when local control must be maintained at the snapshot site.

One scenario where you might find local control of snapshot creation helpful is when it is desirable for the snapshot site to control what data it receives. For example, this is especially true of decision support sites (DSS), which are typically read-only snapshot sites. A DDS site may occasionally need to run complex queries and they do not want to slow the OLTP site, and/or bother the DBA at the OLTP site.

Local Snapshot Control

One of the major benefits of deployment templates is that control is maintained centrally by the DBA building the deployment template. There are instances, however, when the snapshot site must retain some control.

Local control may be required if the snapshot site:

Because snapshot groups are created with Replication Manager's Snapshot Group Wizard locally at the snapshot site by its DBA, or perhaps a systems analyst with SQL knowledge, control can also be maintained at the snapshot site.

Consider the following as a perfect example for maintaining local control. Because multimaster replication does not allow for vertical and horizontal data partitioning, updateable snapshot sites are sometimes created primarily for their ability to partition data. These sites are typically secure, have experienced DBAs, and require the ability to maintain control locally to meet user and application requirements. Snapshot groups created with the Snapshot Group Wizard or with the replication management API allow for the localized control necessary to meet the requirements of the secure updateable snapshot sites.

Also, remember that when a snapshot environment is created with a deployment template, all objects in the snapshot environment are added to the same refresh group. While this might be fine for most installations, certain situations may require that the objects in a snapshot group are assigned to several different refresh groups.


Go to previous page Go to next page
Oracle
Copyright © 1996-2000, Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index