16 Best Practices for Capture

Grant the Required Privileges to the Capture User

The capture user is the user in whose security domain a capture process captures changes that satisfy its rule sets and runs custom rule-based transformations configured for capture process rules.

The capture user for a capture process is configured when you create a capture process, and the capture user can be modified when you alter a capture process. Grant the following privileges to the apply user:

• EXECUTE privilege on the rule sets used by the capture process

• EXECUTE privilege on all rule-based transformation functions used in the positive rule set

These privileges can be granted directly to the capture user, or they can be granted through roles.

In addition, the capture user must be granted EXECUTE privilege on all packages, including Oracle-supplied packages, that are invoked in rule-based transformations run by the capture process. These privileges must be granted directly to the capture user. They cannot be granted through roles.

Set Capture Process Parallelism

Set the parallelism of each capture process by specifying the parallelism parameter in the DBMS_CAPTURE_ADM.SET_PARAMETER procedure. The parallelism parameter controls the number of processes that concurrently mine the redo log for changes.

The default setting for the parallelism capture process parameter is 1, and the default parallelism setting is appropriate for most capture process configurations. Ensure that the PROCESSES initialization parameter is set appropriately when you set the parallelism capture process parameter.

Set the Checkpoint Retention Time

Set the checkpoint retention time for each capture process. Periodically, a capture process takes a checkpoint to facilitate quicker restart. These checkpoints are maintained in the SYSAUX tablespace by default. The checkpoint retention time for a capture process controls the amount of checkpoint data it retains. The checkpoint retention time specifies the number of days before the required checkpoint SCN to retain checkpoints. When a checkpoint is older than the specified time period, the capture process purges the checkpoint.

When checkpoints are purged, the first SCN for the capture process moves forward, and Oracle Database writes a message including the text "first scn changed" to the alert log. The first SCN is the lowest possible SCN available for capturing changes. The checkpoint retention time is set when you create a capture process, and it can be set when you alter a capture process. When the checkpoint retention time is exceeded, the first SCN is moved forward, and the Oracle Streams metadata tables before this new first SCN are purged. The space used by these tables in the SYSAUX tablespace is reclaimed. To alter the checkpoint retention time for a capture process, use the ALTER_CAPTURE procedure in the DBMS_CAPTURE_ADM package and specify the new retention time with the checkpoint_retention_time parameter.

The default value for the checkpoint retention time is 60 days. If checkpoints are available for a time in the past, then the capture process can recapture changes to recover a destination database. You should set the checkpoint retention time to an appropriate value for your environment. A typical setting is 7 days.

Configure a Heartbeat Table at Each Source Database in an Oracle Streams Environment

You can use a heartbeat table to ensure that changes are being replicated in an Oracle Streams replication environment. Specifically, you can check the APPLIED_SCN value in the DBA_CAPTURE data dictionary view at the capture database to ensure that it is updated periodically. A heartbeat table is especially useful for databases that have a low activity rate because you can ensure that the replication environment is working properly even if there are few replicated changes.

An Oracle Streams capture process requests a checkpoint after every 10 MB of generated redo. During the checkpoint, the metadata for Oracle Streams is maintained if there are active transactions. Implementing a heartbeat table ensures that there are open transactions occurring regularly in the source database, thereby providing additional opportunities for the metadata to be updated frequently. Additionally, the heartbeat table provides quick feedback to the database administrator about the health of the Oracle Streams replication environment.

To implement a heartbeat table, complete the following steps:

1. Create a table at the source database that includes a date or time stamp column and the global name of the source database.

2. Instantiate the table at the destination database. If there are multiple destination databases, then instantiate the heartbeat table at each destination database.

3. Add a rule to the positive rule set for the capture process that captures changes to the source database. The rule instructs the capture process to capture changes to the heartbeat table.

4. Add a rule to the positive rule set for the propagation that propagates changes from the source database to the destination database. The rule instructs the propagation to propagate LCRs for the heartbeat table. If there are multiple propagations, then add the rule to the rule set for each propagation. If your environment uses directed networks, then you might need to add rules to propagations at several databases.

5. Add a rule to the positive rule set for the apply process that applies changes that originated at the source database. The rule instructs the apply process to apply changes to the heartbeat table. If there are multiple apply processes at multiple databases that apply the changes that originated at the source database, then add a rule to each the apply process.

6. Configure an automated job to update the heartbeat table at the source database periodically. For example, the table might be updated every minute.

7. Monitor the Oracle Streams replication environment to verify that changes to the heartbeat table at the source database are being replicated to the destination database.

Perform a Dictionary Build and Prepare Database Objects for Instantiation Periodically

Perform a data dictionary build in the source database redo periodically. Run the DBMS_CAPTURE_ADM.BUILD procedure to build a current copy of the data dictionary in the redo log. Ideally, database objects should be prepared for instantiation after a build is performed. Run one or more of the following procedures in the DBMS_CAPTURE_ADM package to prepare database objects for instantiation:

• PREPARE_GLOBAL_INSTANTIATION

• PREPARE_SCHEMA_INSTANTIATION

• PREPARE_TABLE_INSTANTIATION

Each of the database objects for which a capture process captures changes should be prepared for instantiation periodically. You can reduce the amount of redo data that must be processed if additional capture process are created or if an existing capture process must be re-created by performing a build and preparing shared objects for instantiation periodically.

Minimize the Performance Impact of Batch Processing

For best performance, the commit point for a batch processing job should be kept low. Also, if a large batch processing job must be run at a source database, then consider running it at each Oracle Streams replication database independently. If this technique is used, then ensure that the changes resulting from the batch processing job are not replicated. To accomplish this, run the DBMS_STREAMS.SET_TAG procedure in the session that runs the batch processing job, and set the session tag to a value that will not be captured by a capture process.