Release 2 (8.1.6)
Part Number A76959-01
This chapter explains the concepts and architecture of Oracle snapshots. This chapter covers the following topics:
Oracle uses snapshots, also referred to as materialized views, to replicate data to non-master sites in a replicated environment and to cache "expensive" queries in a data warehouse environment. This chapter, and this Oracle8i Replication manual in general, discusses snapshots for use in a replicated environment.
Oracle8i Data Warehousing Guide to learn more about materialized views for data warehousing.
A snapshot is a replica of a target master table from a single point in time. Whereas in multimaster replication tables are continuously being updated by other master sites, snapshots are updated from one or more master tables through individual batch updates, known as a refreshes, from a single master site, as illustrated in Figure 3-1.
Snapshots can also contain a WHERE clause so that snapshot sites can contain customized data sets. Such snapshots can be helpful for regional offices or sales forces that do not require the complete corporate data set.
When a snapshot is refreshed, Oracle must examine all of the changes to the master table to see if any apply to the snapshot. Therefore, if any changes where made to the master table since the last refresh, a snapshot refresh will take some time, even if the refresh does not apply any changes to the snapshot. If, however, no changes at all were made to the master table since the last refresh of a snapshot, the snapshot refresh should be very quick.
You might use a snapshot to achieve one or more of the following goals:
If one of your goals is to reduce network loads, you can use snapshots to distribute your corporate database to regional sites. Instead of the entire company accessing a single database server, user load is distributed across multiple database servers. Also, a snapshot can be a subset of a master table, which decreases the amount of data that is replicated.
While multimaster replication also distributes a corporate database to multiple sites, the networking requirements are greater than those for replicating with snapshots because of the transaction by transaction nature of multimaster replication. Also, the ability of multimaster replication to provide real-time or near real-time results causes greater network traffic, and might require a dedicated network link.
Snapshots are updated through an efficient batch process from a single master site. They have lower network requirements and dependencies than multimaster replication because of the point in time nature of snapshot replication. Whereas multimaster replication requires constant communication over the network, snapshot replication requires only periodic refreshes. In addition to not requiring a dedicated network connection, replicating data with snapshots increases data availability by providing local access to the target data. These benefits, combined with mass deployment and data subsetting (both of which also reduce network loads), greatly enhance the performance and reliability of your replicated database.
Deployment templates allow you to precreate a snapshot environment locally. You can then use deployment templates to quickly and easily deploy snapshot environments to support sales force automation and other mass deployment environments. Parameters allow you to create custom data sets for individual users without changing the deployment template. This technology allows you to rollout a database infrastructure to hundreds or thousands of users.
Snapshots allow you to replicate data based on column- and/or row-level subsetting, while multimaster replication requires replication of the entire table. Data subsetting allows you to replicate information that pertains only to a particular site. For example, if you have a regional sales office, you might replicate only the data that is needed in that region, thereby cutting down on unnecessary network traffic.
Unlike multimaster replication, snapshots do not require a dedicated network link. Though you have the option of automating the refresh process by scheduling a job, you can manually refresh your snapshot on-demand. This is an ideal solution for sales applications running on a laptop. For example, a developer can integrate the replication management API for refresh on-demand into the sales application. When the salesperson has completed the day's order, the salesperson simply dials-up the network and uses the integrated mechanism to refresh the database, thus transferring the orders to the main office.
Oracle offers several types of snapshots to meet the needs of many different replication (and non-replication) situations. The following sections describe each type of snapshot and also describe some environments for which they are best suited.
The following sections contain examples of creating different types of snapshots. Whenever you create a snapshot, regardless of its type, always specify the schema name of the table owner in the query for the snapshot. For example, consider the following CREATE SNAPSHOT statement:
Here, the schema SCOTT is specified in the query.
Primary key snapshots are the default type of snapshot. They are updateable if the snapshot was created as part of a snapshot group and "FOR UPDATE" was specified when defining the snapshot. Changes are propagated according to the row-level changes that have occurred, as identified by the primary key value of the row (not the ROWID). The SQL statement for creating an updateable, primary key snapshot might look like:
Primary key snapshots may contain a subquery so that you can create a horizontally partitioned subset of data at the remote snapshot site. This subquery may be as simple as a basic WHERE clause or as complex as a multilevel WHERE EXISTS clause. Primary key snapshots that contain a selected class of subqueries can still be incrementally or fast refreshed. The following is a subquery snapshot with a WHERE clause containing a subquery:
CREATE SNAPSHOT sales.orders REFRESH FAST AS SELECT * FROM email@example.com o WHERE EXISTS (SELECT 1 FROM firstname.lastname@example.org c WHERE o.c_id = c.c_id AND zip = 19555);
For backwards compatibility, Oracle supports ROWID snapshots in addition to the default primary key snapshots. A ROWID snapshot is based on the physical row identifiers (ROWIDs) of the rows in a master table. ROWID snapshots should be used only for snapshots based on master tables from an Oracle7 database, and should not be used when creating new snapshots based on master tables from Oracle release 8.0 or greater databases.
"Snapshot Log" for more information on the differences between a ROWID and Primary Key snapshot.
To be fast refreshed, the defining query for a snapshot must observe certain restrictions. If you require a snapshot whose defining query is more general and cannot observe the restrictions, then the snapshot is complex and cannot be fast refreshed.
Specifically, a snapshot is considered complex when the defining query of the snapshot contains:
The following statement is an example of a complex snapshot CREATE statement:
CREATE SNAPSHOT scott.snap_employees AS SELECT emp.empno, emp.ename FROM email@example.com UNION ALL SELECT new_emp.empno, new_emp.ename FROM firstname.lastname@example.org;
For certain applications, you might want to consider using a complex snapshot. Figure 3-2 and the following text discuss some issues that you should consider.
In summary, to decide which method to use:
Any of the previously described types of snapshots can be made read-only by omitting the FOR UPDATE clause or disabling the equivalent checkbox in the Replication Manager interface. Read-only snapshots use many of the same mechanisms as updateable snapshots, except that they do not need to belong to a snapshot group.
"Snapshot Groups" for more information.
In addition, using read-only snapshots eliminates the possibility of a snapshot introducing data conflicts at the master site, although this convenience means that updates cannot be made at the remote snapshot site. You might define a read-only snapshot as:
In certain situations, you may want your snapshot to reflect a horizontal or vertical subset of the data in the master table. If you use deployment templates to build your snapshots, you can define vertical data subsets to replicate data along column boundaries. For additional information on vertical partitioning, see "Vertical Partitioning" . Some reasons to consider partitioning data are:
In many instances, the above objectives can be met by using a simple WHERE clause. For example, the following statement creates a snapshot that contains information about customers who are in the 19555 zip code:
The above example works well for individual snapshots that do not have any referential constraints to other snapshots. But, if you want more than just the customer information, maintaining and defining these snapshots could be difficult.
Consider the scenario where you have three tables called CUSTOMER, ORDERS, and ORDER_LINE, and you want to create three corresponding snapshots that maintain the referential integrity of these master tables. If a salesperson wants to retrieve the customer information, pending orders, and the associated order lines for all customers in the 19555 zip code, the most efficient method is to create snapshots with one or more subqueries in the defining query of the snapshot.
The CUSTOMER snapshot has a very simple defining query because the CUSTOMER master table is at the top of the hierarchy:
When you create the ORDERS snapshot, you want to retrieve all of the orders for the customers located in the 19555 zip code. Look at the relationships in Figure 3-3 below, and note that the CUSTOMER and ORDERS table are related through the
C_ID column. The following statement creates the ORDERS snapshot with the appropriate data set:
CREATE SNAPSHOT sales.orders AS SELECT * FROM email@example.com o WHERE EXISTS (SELECT c_id FROM firstname.lastname@example.org c WHERE o.c_id = c.c_id AND c.zip = 19555);
Creating the ORDER_LINE snapshot uses the same approach as the ORDERS snapshot, except that you have one additional subquery. Notice in Figure 3-3 that the ORDER_LINE and the ORDERS tables are related through the O_ID row. The following statement creates the ORDER_LINE snapshot with the appropriate data set:
CREATE SNAPSHOT sales.order_line AS SELECT * FROM email@example.com ol WHERE EXISTS (SELECT o_id FROM firstname.lastname@example.org o WHERE ol.o_id = o.o_id AND EXISTS (SELECT c_id FROM email@example.com c WHERE o.c_id = c.c_id AND c.zip = 19555));
The snapshots created by these three DDL statements are each fast refreshable. If new customers are identified in the target zip code, the new data will be propagated to the snapshot site during the subsequent refresh process. Likewise, if a customer is removed from the target zip code, the appropriate data also will be removed from the snapshot during the subsequent refresh process.
The subqueries in these snapshot examples walk up the many-to-one references from the child to the parent tables. The snapshots are populated with data that satisfies the defining query for each of these snapshots, and are refreshed only with data that satisfies these defining queries.
While the previous subquery examples demonstrate greater flexibility for snapshots, there are still certain limitations in the above example. For example, if the salesperson changed territories or the existing territory was assigned an additional zip code, the above snapshot definitions would need to be altered or recreated because the zip code 19555 was "hard coded" in the previous snapshot definitions.
With this in mind, if assignment tables are used in conjunction with subquery subsetting, changes to a snapshot environment can easily be controlled by the DBA. For example, consider the customer/salesperson relationship in Figure 3-4.
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 enables this easy administration to remain secure. 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 3-4, if the ORDERS snapshot's defining query was specified as the following:
CREATE SNAPSHOT sales.orders AS SELECT * FROM firstname.lastname@example.org o -- conditions for customers WHERE EXISTS ( SELECT c_id FROM email@example.com c WHERE o.c_id = c.c_id AND EXISTS ( SELECT * FROM firstname.lastname@example.org a WHERE a.c_id = c.c_id AND EXISTS ( SELECT * FROM email@example.com s WHERE a.s_id = s.s_id AND s.s_id = 'gsmith')));
Then, the ORDERS snapshot is populated with order data for the customers that are assigned to salesperson 'gsmith'. Notice the 'gsmith' value 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 a DBA 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 fast snapshot refresh, the data for these two customers will be 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 would be removed from the snapshot site after the next refresh and the salesperson would no longer be able to access that information.
"Refresh Types" for more information.
Snapshots with a subquery must be of the primary key type. Additionally, the defining query of a snapshot with a subquery is subject to several other restrictions to preserve the snapshot's fast refresh capability.
"Primary Key" for more information about primary key snapshots.
Note: To determine whether a snapshot's subquery satisfies the many restrictions detailed in Table 3-1, create the snapshot with fast refresh. Oracle returns errors if the snapshot violates any restrictions for simple subquery snapshots. If you specify force refresh, you may not receive any errors because, when a force refresh is requested, Oracle automatically performs a complete refresh if it cannot perform a fast refresh.
At the master site, an Oracle database automatically registers information about a snapshots based on its master table(s). The following sections explain more about Oracle's snapshot registration mechanism.
You can query the DBA_REGISTERED_SNAPSHOTS data dictionary view to list the following information about a remote snapshot:
You can also query the DBA_SNAPSHOT_REFRESH_TIMES view at the master site to obtain the last refresh times for each snapshot. Administrators can use this information to monitor snapshot activity from master sites and coordinate changes to snapshot sites if a master table needs to be dropped, altered, or relocated.
Oracle automatically registers a snapshot at its master database when you create the snapshot, and unregisters the snapshot when you drop it.
Oracle cannot guarantee the registration or unregistration of a snapshot at its master site during the creation or drop of the snapshot, respectively. If Oracle cannot successfully register a snapshot during creation, Oracle completes snapshot registration during a subsequent refresh of the snapshot. If Oracle cannot successfully unregister a snapshot when you drop the snapshot, the registration information for the snapshot persists in the master database until it is manually unregistered. Complex snapshots might not be registered.
If necessary, you can maintain registration manually. Use the REGISTER_SNAPSHOT and UNREGISTER_SNAPSHOT procedures of the DBMS_SNAPSHOT package at the master site to add, modify, or remove snapshot registration information.
The REGISTER_SNAPSHOT and UNREGISTER_SNAPSHOT procedures are described in the Oracle8i Replication Management API Reference.
The mechanisms used in snapshot replication are depicted in Figure 3-5. Some of these mechanisms are optional and are used only as needed to support the created snapshot environment. For example, if you have a read-only snapshot, then you do not have an updateable snapshot log or an internal trigger at the remote site. Also, if you have a complex snapshot that cannot be fast refreshed, then you may not have a snapshot log at the master site.
The three mechanisms displayed in Figure 3-6 are required at the master site to support fast refreshing of snapshots.
The master table is the basis for the snapshot and is located at the target master site. This table may be involved in both snapshot replication and multimaster replication (remember that a snapshot points to only one master site).
Changes made to the master table, as recorded by the snapshot log, will be propagated to the snapshot during the refresh process.
When changes are made to the master table using DML, an internal trigger records information about the affected rows in the snapshot log. This information includes the values of the primary key and/or the ROWID, and the values of the filter columns. This is an internal trigger that is automatically activated when you create a snapshot log for the target master table.
When you create a snapshot log for a master table, Oracle creates an underlying table as the snapshot log. A snapshot log holds the primary keys and/or the ROWIDs of rows that have been updated in the master table. A snapshot log can also contain filter columns to support fast refreshes of snapshots with subqueries. The name of a snapshot log's table is MLOG$_master_table_name. The snapshot log is created in the same schema as the target master table. One snapshot log can support multiple snapshots on its master table.
As described in the previous section, the internal trigger adds change information to the snapshot log whenever a DML transaction has taken place on the target master table.
There are three types of snapshot logs:
A combination snapshot log works in the same manner as the primary key and ROWID snapshot log, except that both the primary key and the ROWID of the affected row are recorded.
Though the difference between snapshot logs based on primary keys and ROWIDs is small (one records affected rows using the primary key, while the other records affected rows using the physical ROWID), the practical impact is large. Using ROWID snapshots and snapshot logs makes reorganizing and truncating your master tables difficult because it prevents your ROWID snapshots from being fast refreshed. If you reorganize or truncate your master table, your ROWID snapshot must be COMPLETE refreshed because the ROWIDs of the master table have changed.
Snapshots and snapshot logs are exported with the schema name explicitly given in the DDL statements. Therefore, snapshots and snapshot logs cannot be imported into a schema that is different than the schema from which they were exported. If you attempt to use the FROMUSER/TOUSER import options to import an export dump file that contains snapshots or snapshot logs, an error will be written to the Import log file and the items will not be imported.
When a snapshot is created, several additional mechanisms are created at the snapshot site to support the snapshot. Specifically, a base table, at least one index, and possibly a view are created. If you create an updateable snapshot, an internal trigger and a local log (the updateable snapshot log) are also created at the snapshot site.
Beginning with Oracle8i release 8.1.5, the base table is the actual snapshot (no view is required). The base table has the name that you specified during snapshot creation.
When the snapshot site compatibility setting is less than 8.1.0, the base table is the underlying support mechanism for a view. The compatibility setting is defined by the COMPATIBLE initialization parameter in the initialization parameter file. When the base table is the support mechanism for the view, it has the name SNAP$_Snapshot_Name. Any indexes generated when you create the snapshot are created on the base table.
|Datatype Considerations for Snapshots|
Oracle also supports snapshots of master table columns that use the following large object types: binary LOBs (BLOBs), character LOBs (CLOBs), and national character LOBs (NCLOBs). However, you cannot reference LOB columns in a WHERE clause of a snapshot's defining query. The deferred and synchronous remote procedure call mechanism used for replication propagates only the piece-wise changes to the supported LOB datatypes when piece-wise updates and appends are applied to these LOB columns.
Oracle also does not support user-defined object types and external or file-based LOBs (BFILEs). Attempts to configure snapshots containing columns of these datatypes return an error message.
A view is created only to support snapshot replication with Oracle release 8.0 and earlier, or if a release 8.1 snapshot site's compatibility setting is less than 8.1.0. If a view is created, the view has the same name specified in the CREATE SNAPSHOT statement. For example, a CREATE SNAPSHOT SALES.SNAP_CUSTOMER AS .... statement creates a view named SNAP_CUSTOMER.
At least one index is created at the remote snapshot site for each primary key snapshot. This index corresponds to the primary key of the target master table and has the name I_SNAP$_Snapshot_Name. Additional indexes may be created by Oracle at the remote snapshot site to support fast refreshing of snapshots with subqueries.
An updateable snapshot log (USLOG$_Snapshot_Name) is used to determine data that must be pulled from the target master table. A read-only snapshot does not require this log.
Just like the internal trigger at the master site, the internal trigger at the snapshot site records DML changes applied to an updateable snapshot in the USLOG$_Snapshot_Name log.
In addition to the snapshot mechanisms described in the previous section, there are several other mechanisms that organize the snapshots at the snapshot site. These mechanisms maintain organizational consistency between the snapshot site and the master site as well as transactional (read) consistency with the target master group. These mechanisms are snapshot groups and refresh groups.
A snapshot group in a replication system maintains a partial or complete copy of the objects at the target master group. Snapshot groups cannot span master group boundaries. Figure 3-7 displays the correlation between Groups A and B at the master site and Groups A and B at the snapshot site.
Group A at the snapshot site (see Figure 3-7) contains only some of the objects in the corresponding Group A at the master site. Group B at the snapshot site contains all objects in Group B at the master site. Under no circumstances, however, could Group B at the snapshot site contain objects from Group A at the master site. As illustrated in Figure 3-7, a snapshot group has the same name as the master group on which the snapshot group is based. For example, a snapshot group based on a "PERSONNEL" master group is also named "PERSONNEL."
In addition to maintaining organizational consistency between snapshot sites and master sites, snapshot groups are required for supporting updateable snapshots. If a snapshot does not belong to a snapshot group, then it must be a read-only snapshot.
If you need to support multiple users within the same database at a snapshot site, you may want to create multiple snapshot groups for the target master group. Doing so enables you to define different subqueries for your snapshot definitions in each snapshot group, and allows each user to access only his or her subset of the data.
Defining multiple data sets with different snapshot groups is more secure than defining different WHERE clauses for multiple views supporting different users. When you define multiple data sets with different snapshot groups, you can grant users access to individual snapshot objects, and you can control what the user views, deletes, and inserts. With a WHERE clause in a view, you can only control what a user views, but not what a user the deletes or inserts.
Defining multiple snapshot groups gives you the ability to control data sets at a group level. For example, if you create different snapshot groups for the HR, PERSONNEL, and MANUFACTURING departments, you can administer each department as a group, instead of as individual objects. For example, you can refresh the snapshots as a departmental group, and you can drop the objects as a group.
To accommodate multiple snapshot groups at the same snapshot site that are based on a single master group, you can specify a group owner as an additional identifier when defining your snapshot group.
After you have defined your snapshot group with the addition of a group owner, you add your snapshot objects to the target snapshot group by defining the same group owner. When using a group owner, remember that each snapshot object must have a unique name. If a single snapshot site has multiple snapshot groups based on the same master group, a snapshot group's object names cannot have the same name as snapshot objects in another snapshot group. To avoid conflicting names, you might want to append the group owner name to the end of your object name. For example, if you have group owners "HR" and "PERSONNEL", you might name the "EMP" snapshot object as "EMP_HR" and "EMP_PERSONNEL," respectively.
Additionally, all snapshot groups that are based on the same master group at a single snapshot site must "point" to the same master site. For example, if the SCOTT_MG snapshot group owned by HR is based on the associated master group at the ORC1.WORLD master site, then the SCOTT_MG snapshot group owned by PERSONNEL must also be based on the associated master group at ORC1.WORLD, assuming that the HR and PERSONNEL owned groups are at the same snapshot site.
The "Using a Group Owner" section in Chapter 7 of the Oracle8i Replication Management API Reference manual for more information on defining a group owner using the replication management API.
To preserve referential integrity and transactional (read) consistency among multiple snapshots, Oracle has the ability to refresh individual snapshots as part of a refresh group. After refreshing all of the snapshots in a refresh group, the data of all snapshots in the group correspond to the same transactionally consistent point in time.
As illustrated in Figure 3-8, a refresh group can contain snapshots from more than one snapshot group to maintain transactional (read) consistency across master group boundaries.
While you may want to define a single refresh group per snapshot group, it may be more efficient to use one large refresh group that contains objects from multiple snapshot groups. Such a configuration reduces the amount of "overhead" needed to refresh your snapshots. A refresh group can contain up to 400 snapshots, which is an increase from earlier versions of Oracle server.
One configuration that you want to avoid is using multiple refresh groups to refresh the contents of a single snapshot group. Using multiple refresh groups to refresh the contents of a single snapshot group may introduce inconsistencies in the snapshot data, which may cause referential integrity problems at the snapshot site. This type of configuration should be used only when you have in-depth knowledge of the database environment and can prevent any referential integrity problems.
There are a few trade-offs to consider when you are deciding on the size of your refresh groups. Oracle is optimized for large refresh groups. So, large refresh groups refresh faster than an equal number of snapshots in small refresh groups, assuming that the snapshots in the groups are similar. For example, refreshing a refresh group with 100 snapshots is faster than refreshing five refresh groups with 20 snapshots each. Also, large refresh groups enable you to refresh a greater number of snapshots with only one call to the replication management API.
During the refresh of a refresh group, each snapshot in the group is locked at the snapshot site for the amount of time required to refresh all of the snapshots in the refresh group. This locking is required to prevent users from updating the snapshots during the refresh operation, as updates might make the data inconsistent. Therefore, having smaller refresh groups means that the snapshots are locked for less time when you perform a refresh.
Network connectivity must be maintained while performing a refresh. If the connectivity is lost or interrupted during the refresh, all changes are rolled back so that the database remains consistent. Therefore, in cases where the network connectivity is difficult to maintain, consider using smaller refresh groups.
Release 8.1 supports null refresh optimization. That is, if there were no changes to the master tables since the last refresh for a particular snapshot, almost no extra time is required for the snapshot during snapshot group refresh. However, for snapshots in a database prior to release 8.1, consider separating snapshots of master tables that are not updated often into a separate refresh group of their own. Doing so shortens the refresh time required for other snapshot groups that contain snapshots of master tables that are updated frequently.
Table 3-2 summarizes the advantages of large and small refresh groups.
|Advantages of Large Refresh Groups||Advantages of Small Refresh Groups|
A snapshot's data does not necessarily match the current data of its master table at all times. A snapshot is a transactionally (read) consistent reflection of its master table as the data existed at a specific point in time (that is, at creation or when a refresh occurs). To keep a snapshot's data relatively current with the data of its master table, the snapshot must be periodically refreshed. A snapshot refresh is an efficient batch operation that makes a snapshot reflect a more current state of its master table.
You must decide how and when to refresh each snapshot to make it more current. For example, snapshots based on master tables that applications update often may require frequent refreshes. In contrast, snapshots based on relatively static master tables usually require infrequent refreshes. In summary, you must analyze application characteristics and requirements to determine appropriate snapshot refresh intervals.
To refresh snapshots, Oracle supports several refresh types and methods of initiating a refresh.
Oracle can refresh a snapshot using either a fast, complete, or force refresh.
To perform a complete refresh of a snapshot, the server that manages the snapshot executes the snapshot's defining query. The result set of the query replaces the existing snapshot data to refresh the snapshot. Oracle can perform a complete refresh for any snapshot. Depending on the amount of data that satisfies the defining query, a complete refresh can take a substantially longer amount of time to perform than a fast refresh.
To perform a fast refresh, the server that manages the snapshot first identifies the changes that occurred in the master since the most recent refresh of the snapshot and then applies them to the snapshot. Fast refreshes are more efficient than complete refreshes when there are few changes to the master because the participating server and network replicate a smaller amount of data. You can perform fast refreshes of snapshots only when the master table has a snapshot log.
After a direct path load on a master table using SQL*Loader, a fast refresh does not apply the changes that occurred during the direct path load. Also, fast refresh does not apply changes that result from other types of bulk load operations on master tables. Examples of these operations include some INSERT statements with an APPEND hint and some INSERT ... SELECT * FROM statements.
To perform a force refresh of a snapshot, the server that manages the snapshot tries to perform a fast refresh. If a fast refresh is not possible, then Oracle performs a complete refresh. Use the force setting when you want a snapshot to refresh if a fast refresh is not possible.
When creating a refresh group, administrators may configure the group so that Oracle can automatically refresh the group's snapshots at scheduled intervals. Conversely, administrators may omit scheduling information so that the refresh group needs to be refreshed manually or "on-demand". Manual refresh is an ideal solution when the refresh is performed with a dial-up network connection.
When you create a refresh group for scheduled refreshing, you must specify a scheduled refresh interval for the group during the creation process. When setting a group's refresh interval, consider the following characteristics:
Scheduled snapshot refreshes may not always be the appropriate solution for your environment/situation. For example, immediately following a bulk data load into a master table, dependent snapshots no longer represent the master table's data. Rather than wait for the next scheduled automatic group refreshes, you might want to manually refresh dependent snapshot groups to immediately propagate the new rows of the master table to associated snapshots.
You may also want to refresh your snapshots on-demand when your snapshots are integrated with a sales force automation system located on a disconnected laptop. Developers designing the sales force automation software can create an application control, such as a button, that a salesperson can use to refresh the snapshots when they are ready to transfer the day's orders to the server after establishing a dial-up network connection.
Most problems encountered with snapshot replication come from not preparing the environment properly. There are four essential tasks that you must perform before you begin creating your snapshot environment:
The Replication Manager Setup Wizard automatically performs the tasks that are described below. The following discussion is provided to help you understand the replication environment and to help users who use the replication management API. After running Setup Wizard, create the necessary snapshot logs. See the Replication Manager online help for instructions on using Replication Manager to set up your snapshot site. You are encouraged to use Replication Manager whenever possible.
If you are not able to use Replication Manager, review the "Set Up Snapshot Sites" section in Chapter 2 of the Oracle8i Replication Management API Reference for detailed instructions on setting up your snapshot site using the replication management API.
The following sections describe what the Replication Manager Setup Wizard or the script in the Oracle8i Replication Management API Reference does to set up your snapshot site.
Each snapshot site needs several users to perform the administrative and refreshing activities at the snapshot site. You must create and grant the necessary privileges to the snapshot administrator and to the refresher.
You need equivalent proxy users at the target master site to perform tasks on behalf of the snapshot site users. Usually, a proxy snapshot administrator and a proxy refresher are created.
A schema containing a snapshot in a remote database must correspond to the schema that contains the master table in the master database. Therefore, identify the schemas that contain the master tables that you want to replicate with snapshots. Once you have identified the target schemas at the master database, create the corresponding accounts with the same names at the remote database. For example, if all master tables are in the SALES schema of the DB1 database, create a corresponding SALES schema in the snapshot database DB2.
If you are reviewing the steps in Oracle8i Replication Management API Reference, the necessary schemas are created as part of the script described in Chapter 5, "Create Snapshot Group".
The defining query of a snapshot may use one or more database links to reference remote table data. Before creating snapshots, the database links you plan to use must be available. Furthermore, the account that a database link uses to access a remote database defines the security context under which Oracle creates and subsequently refreshes a snapshot.
To ensure proper behavior, a snapshot's defining query must use a database link that includes an embedded user name and password in its definition; you cannot use a public database link when creating a snapshot. A database link with an embedded name and password always establishes connections to the remote database using the specified account. Additionally, the remote account that the link uses must have the SELECT privileges necessary to access the data referenced in the snapshot's defining query.
Before creating your snapshots, you need to create several administrative database links. Specifically, you should create a PUBLIC database link from the snapshot site to the master site; doing so makes defining your private database links easier because you do not need to include the USING clause in each link. You also need private database links from the snapshot administrator to the proxy administrator and from the propagator to the receiver, but, if you use the Replication Manager Setup Wizard, these database links are created for you automatically.
Appendix A, "Security Options" in Oracle8i Replication Management API Reference for more information.
After the administrative database links have been created, a private database link must be created connecting each replicated snapshot schema at the snapshot database to the corresponding schema at the master database. Be sure to embed the associated master database account information in each private database link at the snapshot database. For example, the SALES schema at a snapshot database DB2 should have a private database link to database DB1 that connects using the SALES username and password.
Both the creator and the owner of the snapshot must be able to issue the defining SELECT statement of the snapshot. The owner is the schema that contains the snapshot. If a user other than the replication or snapshot administrator creates the snapshot, then that user must have the CREATE SNAPSHOT privilege and the appropriate SELECT privileges to execute the defining SELECT statement.
If you are reviewing the steps in Oracle8i Replication Management API Reference, the necessary privileges are granted as part of the script described in Chapter 5, "Create Snapshot Group".
To keep the size of the deferred transaction queues in check, you need to schedule a purge operation to remove all successfully completed deferred transactions from the deferred transaction queue. This operation may have already been performed at the master site; scheduling the purge operation again does not harm the master site, but may change the purge scheduling characteristics.
Often referred to as a scheduled link, scheduling a push at the snapshot site automatically propagates the deferred transactions at the snapshot site to the associated target master site. Typically, there is only a single scheduled link per snapshot group at a snapshot site, because a snapshot group only has a single target master site.
It is important that you have allocated sufficient SNP (or job queue) background processes to handle the automatic refreshing of your snapshots. Because your snapshot site typically has only a single scheduled link to the target master site, the snapshot site only requires a single SNP process, but to handle additional activity, such as scheduled jobs, you may want to allocate at least two SNP processes at the snapshot site. Also, you need at least one SNP process for each degree of parallelism.
The SNP processes are defined using the JOB_QUEUE_PROCESSES initialization parameter in the initialization parameter file for your database. To set up your SNP processes, you can either use Instance Manager, a component of Oracle Enterprise Manager, or manually edit the initialization parameter file.
The SNP job interval determines how often your SNP processes "wake up" to execute any pending operations, such as pushing a queue. While the default value of 60 seconds is adequate for most replicated environments, you may need to adjust this value to maximize performance for your individual requirements. For example, if you want to propagate changes to the target master site every 20 seconds, a job interval of 60 seconds would not be sufficient. On the other hand, if you need to propagate your changes once a day, you may want your SNP process to check for a pending operation only once an hour.
You will often use the Edit Database dialog box of Instance Manager to configure the SNP processes and the SNP job interval at the snapshot site if you have a dedicated network link to the snapshot site or if you are able to schedule the network link. This is required because Instance Manager is not installed at the snapshot site in most cases and thus the configuration must be done remotely from the master site. If remote configuration is not possible, see the next section.
Complete the following to set your job processes using Instance Manager:
You can save this configuration, which is helpful if you use Instance Manager to manage your database.
Oracle Enterprise Manager Administrator's Guide and the Instance Manager online help for more information on using Instance Manager.
If you do not have access to Instance Manager, you can manually edit the initialization parameter file. Use a text editor to modify the contents of your initialization parameter file.
In most cases, all of the initialization parameters used in replication are grouped together under an "Oracle replication" heading in your initialization parameter file.
After you have modified the contents of your initialization parameter file, restart your database with these new settings.
Before creating snapshot groups and snapshots for a remote snapshot site, make sure to create the necessary snapshot logs at the master site. A snapshot log is necessary for every master table that supports at least one snapshot with fast refreshes.
To create a snapshot log, you need the following privileges:
When you create a snapshot log, you can specify whether you are using filter columns. Filter columns are an essential component when using subquery snapshots. A filter column must be defined in a snapshot log that is supporting a snapshot that references a column that is in a WHERE clause and is not part of the equijoin columns.
Consider the following DDL:
1) CREATE SNAPSHOT sales.orders AS 2) SELECT * FROM firstname.lastname@example.org o 3) WHERE EXISTS 4) (SELECT c_id FROM email@example.com c 5) WHERE o.c_id = c.c_id AND zip = 19555);
Notice in line 5 of the above DDL that three columns are referenced in the WHERE clause. Columns O.C_ID and C.C_ID are referenced as part of the equijoin clause; the column ZIP is an additional filter column. Therefore, create a filter column in the snapshot log for the ZIP column of the SALES.CUSTOMER table.
You are encouraged to analyze the defining queries of your planned snapshots and identify which filter columns must be created in your snapshot logs. If you try to create or refresh a snapshot that requires a filter column before creating the snapshot log containing the filter column, your snapshot creation or refresh may fail.
Snapshot environments can be created in several different ways and from several different locations. In most cases, you should use deployment templates at the master site to locally pre-create a snapshot environment that will be individually deployed to the target snapshot site.
You can also individually create the snapshot environment by establishing a connection to the snapshot site and building the snapshot environment directly.
See the Replication Manager online help for information on using deployment templates to centrally create a snapshot environment using Replication Manager.
See the Replication Manager online help for information on individually creating the snapshot environment with a direct connection to the remote snapshot site using Replication Manager.
See Chapter 4, "Create Deployment Template" of the Oracle8i Replication Management API Reference manual for information on using deployment templates to centrally pre-create a snapshot environment using the replication management API.
See Chapter 5, "Create Snapshot Group" of the Oracle8i Replication Management API Reference manual for information on individually creating the snapshot environment with a direct connection to the remote snapshot site using the replication management API.