14 Best Practices for Streams Replication Databases

Set Initialization Parameters That Are Relevant to Streams

Certain initialization parameters are important in a Streams configuration. Make sure the initialization parameters are set properly at all databases before configuring a Streams replication environment.

Configure Database Storage in a Streams Database

The following sections describe best practices for database storage in a Streams database:

• Configure a Separate Tablespace for the Streams Administrator

• Use a Separate Queue for Each Capture Process and Apply Process

CREATE TABLESPACE streams_tbs DATAFILE  '/usr/oracle/dbs/streams_tbs.dbf'
  SIZE 25 M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

ALTER USER strmadmin DEFAULT TABLESPACE streams_tbs
  QUOTA UNLIMITED ON streams_tbs;

BEGIN
  DBMS_STREAMS_ADM.SET_UP_QUEUE(
    queue_table => 'strmadmin.qt_capture_queue',
    queue_name  => 'strmadmin.capture_queue');
END;
/

BEGIN
  DBMS_STREAMS_ADM.SET_UP_QUEUE(
    queue_table => 'strmadmin.qt_apply_queue',
    queue_name  => 'strmadmin.apply_queue');
END;
/

Grant User Privileges to the Streams Administrator

To create capture and apply processes, the Streams administrator must have DBA privilege. An administrative user must explicitly grant DBA privilege to the Streams administrator. For example, the following statement grants DBA privilege to a Streams administrator named strmadmin:

GRANT DBA TO strmadmin;

In addition, other privileges can be granted to the Streams administrator on each participating Streams database. Use the GRANT_ADMIN_PRIVILEGE procedure in the DBMS_STREAMS_AUTH package to grant these privileges. For example, running the following procedure grants privileges to a Streams administrator named strmadmin:

exec DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE('STRMADMIN');

Automate the Streams Replication Configuration

Use the following procedures in the DBMS_STREAMS_ADM package to create your Streams replication environment whenever possible:

• MAINTAIN_GLOBAL configures a Streams environment that replicates changes at the database level between two databases.

• MAINTAIN_SCHEMAS configures a Streams environment that replicates changes to specified schemas between two databases.

• MAINTAIN_SIMPLE_TTS clones a simple tablespace from a source database at a destination database and uses Streams to maintain this tablespace at both databases.

• MAINTAIN_TABLES configures a Streams environment that replicates changes to specified tables between two databases.

• MAINTAIN_TTS clones a set of tablespaces from a source database at a destination database and uses Streams to maintain these tablespaces at both databases.

• PRE_INSTANTIATION_SETUP and POST_INSTANTIATION_SETUP configure a Streams environment that replicates changes either at the database level or to specified tablespaces between two databases. These procedures must be used together, and instantiation actions must be performed manually, to complete the Streams replication configuration.

These procedures automate the entire configuration of the Streams clients at multiple databases. Further, the configuration follows Streams best practices. For example, these procedures create queue-to-queue propagations whenever possible.

If these procedures are not suitable for your environment, then use the following procedures in the DBMS_STREAMS_ADM package to create Streams clients, rules sets, and rules:

• ADD_TABLE_RULES

• ADD_SUBSET_RULES

• ADD_SCHEMA_RULES

• ADD_GLOBAL_RULES

• ADD_TABLE_PROPAGATION_RULES

• ADD_SUBSET_PROPAGATION_RULES

• ADD_SCHEMA_PROPAGATION_RULES

• ADD_GLOBAL_PROPAGATION_RULES

These procedures minimize the number of steps required to configure Streams clients. It is also possible to create rules for nonexistent objects, so make sure you check the spelling of each object specified in a rule carefully.

Although it is typically not recommended, a propagation or apply process can be used without rule sets or rules if you always want to propagate or apply all of the messages in a queue. However, a capture process requires one or more rule sets with rules. You can use the ADD_GLOBAL_RULES procedure to capture DML changes to an entire database if a negative rule set is configured for the capture process to filter out changes to unsupported objects. You can also use the ADD_GLOBAL_RULES procedure to capture all DDL changes to the database.

The rules in the rule set for a propagation can differ from the rules specified for a capture process. For example, to configure that all captured changes be propagated to a destination database, you can run the ADD_GLOBAL_PROPAGATION_RULES procedure for the propagation even though multiple rules might have been configured using ADD_TABLE_RULES for the capture process. Similarly, the rules in the rule set for an apply process can differ from the rules specified for the capture process and propagation(s) that capture and propagate messages to the apply process.

A Streams client can process changes for multiple tables or schemas. For the best performance, make sure the rules for these multiple tables or schemas are simple. Complex rules will impact the performance of Streams. For example, rules with conditions that include LIKE clauses are complex. When you use a procedure in the DBMS_STREAMS_ADM package to create rules, the rules are always simple.

When you configure multiple source databases in a Streams replication environment, change cycling should be avoided. Change cycling means sending a change back to the database where it originated. You can use Streams tags to prevent change cycling.

Follow the Best Practices for the Global Name of a Streams Database

Streams uses the global name of a database to identify changes from or to a particular database. For example, the system-generated rules for capture, propagation, and apply typically specify the global name of the source database. In addition, changes captured by a Streams capture process automatically include the current global name of the source database. If possible, do not modify the global name of a database that is participating in a Streams replication environment after the environment has been configured. The GLOBAL_NAMES initialization parameter must also be set to TRUE to guarantee that database link names match the global name of each destination database.

If the global name of a Streams database must be modified, then do so at a time when no user changes are possible on the database, the queues are empty, and no outstanding changes must be applied by any apply process. When these requirements are met, you can modify the global name of a database and re-create the parts of the Streams configuration that reference the modified database. All queue subscribers, including propagations and apply processes, must be re-created if the source database global name is changed.

Follow the Best Practices for Replicating DDL Changes

When replicating data definition language (DDL) changes, do not allow system-generated names for constraints or indexes. Modifications to these database objects will most likely fail at the destination database because the object names at the different databases will not match. Also, storage clauses may cause some issues if the destination databases are not identical. If you decide not to replicate DDL in your Streams environment, then any table structure changes must be performed manually at each database in the environment.

Monitor Capture Process Queues for Growth

The number of messages in a queue used by a capture process can grow if the messages in the queue cannot be propagated to one or more destination queues. Source queue growth often indicates that there is a problem with the Streams replication environment. Common reasons why messages cannot be propagated include the following:

• One of the destination databases is down for an extended period.

• An apply process at a destination database is disabled for an extended period.

• The queue is the source queue for a propagation that is unable to deliver the messages to a particular destination queue for an extended period due to network problems or propagation job problems.

When a capture process queue grows, the capture process pauses for flow control to minimize the number of messages that are spilled to disk. You can monitor the number of messages in a capture process queue by querying the V$BUFFERED_QUEUES dynamic performance view. This view shows the number of messages in memory and the number of messages spilled to disk. You should monitor the queues used by a capture process to check for queue growth.

Propagation is implemented using the DBMS_JOB package. If a job is unable to execute 16 successive times, the job is marked as "broken" and becomes disabled. Check propagation jobs periodically to make sure that they are running successfully to minimize source queue growth.

Follow the Streams Best Practices for Backups

The following sections contain information about best practices for backing up source databases and destination databases in a Streams replication environment. A single database can be both a source database and a destination database.

Adjust the Automatic Collection of Optimizer Statistics

Every night by default, the optimizer automatically collects statistics on tables whose statistics have become stale. For volatile tables, such as Streams queue tables, it is likely that the statistics collection job runs when these tables might not have data that is representative of their full load period.

You create these volatile queue tables using the DBMS_AQADM.CREATE_QUEUE_TABLE or DBMS_STREAMS_ADM.SETUP_QUEUE procedure. You specify the queue table name when you run these procedures. In addition to the queue table, the following tables are created when the queue table is created and are also volatile:

• AQ$_queue_table_name_I

• AQ$_queue_table_name_H

• AQ$_queue_table_name_T

• AQ$_queue_table_name_P

• AQ$_queue_table_name_D

• AQ$_queue_table_name_C

Replace queue_table_name with the name of the queue table.

Oracle recommends that you collect statistics on volatile tables by completing the following steps:

1. Run the DBMS_STATS.GATHER_TABLE_STATS procedure manually on volatile tables when these tables are at their fullest.

2. Immediately after the statistics are collected on volatile tables, run the DBMS_STATS.LOCK_TABLE_STATS procedure on these tables.

Locking the statistics on volatile tables ensures that the automatic statistics collection job skips these tables, and the tables are not analyzed.

Use the STRMMON Tool for Monitoring Streams

STRMMON is a monitoring tool for Oracle Streams. You can use this tool to obtain a quick overview of the Streams activity in a database. STRMMON reports information in a single line display. You can configure the reporting interval and the number of iterations to display. STRMMON is available in the rdbms/demo directory in your Oracle home.

Check the Alert Log for Streams Information

By default, the alert log contains information about why Streams capture and apply processes stopped. Also, Streams capture and apply processes report long-running and large transactions in the alert log.

Long-running transactions are open transactions with no activity (that is, no new change records, rollbacks, or commits) for an extended period (20 minutes). Large transactions are open transactions with a large number of change records. The alert log reports whether a long-running or large transaction has been seen every 20 minutes. Not all such transactions are reported, because only one transaction is reported for each 20 minute period. When the commit or rollback is received, this information is reported in the alert log as well.

You can use the following views for information about long-running transactions:

• The V$STREAMS_TRANSACTION dynamic performance view enables monitoring of long running transactions that are currently being processed by Streams capture processes and apply processes.

• The DBA_APPLY_SPILL_TXN and V$STREAMS_APPLY_READER views enable you to monitor the number of transactions and messages spilled by an apply process.

Follow the Best Practices for Removing a Streams Configuration at a Database

If you want to completely remove the Streams configuration at a database, then complete the following steps:

1. Connect to the database as an administrative user, and run the DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION procedure.

2. Drop the Streams administrator, if possible.

Make Archive Log Files of All Threads Available to Capture Processes

The archive log files of all threads from all instances must be available to any instance running a capture process. This requirement pertains to both local and downstream capture processes.

Follow the Best Practices for the Global Name of a Streams RAC Database

The general best practices described in "Follow the Best Practices for the Global Name of a Streams Database" also apply to RAC databases in a Streams environment. In addition, if the global name of a RAC destination database does not match the DB_NAME.DB_DOMAIN of the database, then include the global name for the database in the list of services for the database specified by the SERVICE_NAMES initialization parameter.

In the tnsnames.ora file, make sure the CONNECT_DATA clause in the connect descriptor specifies the global name of the destination database for the SERVICE_NAME. Also, make sure the CONNECT_DATA clause does not include the INSTANCE_NAME parameter.

If the global name of a RAC database that contains Streams propagations is changed, then drop and re-create all propagations. Make sure the new propagations are queue-to-queue propagations by setting the queue_to_queue parameter set to true during creation.

If the global name of a RAC destination database must be changed, then ensure that the queue used by each apply process is empty and that there are no unapplied transactions before changing the global name. After the global name is changed, drop and re-create each apply process queue and each apply process.

Follow the Best Practices for Configuring and Managing Propagations

The general best practices described in "Restart Broken Propagations" also apply to RAC databases in a Streams environment. Use the procedures START_PROPAGATION and STOP_PROPAGATION in the DBMS_PROPAGATION_ADM package to start and stop propagations. These procedures automatically handle queue-to-queue propagation.

Also, on a Real Application Clusters (RAC) database, a service is created for each buffered queue. This service always runs on the owner instance of the destination queue and follows the ownership of this queue upon queue ownership switches, which include instance startup, instance shutdown, and so on. This service is used by queue-to-queue propagations. You can query NETWORK_NAME column of the DBA_SERVICES data dictionary view to determine the service name for a queue-to-queue propagation. If you are running RAC instances, and you have queues that were created prior to Oracle Database 10g Release 2, then drop and re-create these queues to take advantage of the automatic service generation and queue-to-queue propagation. Make sure you re-create these queues when they are empty and no new messages are being enqueued into them.

Follow the Best Practices for Queue Ownership

All Streams processing is done at the owning instance of the queue used by the Streams client. To determine the owning instance of each ANYDATA queue in a database, run the following query:

SELECT q.OWNER, q.NAME, t.QUEUE_TABLE, t.OWNER_INSTANCE
   FROM DBA_QUEUES q, DBA_QUEUE_TABLES t
   WHERE t.OBJECT_TYPE = 'SYS.ANYDATA' AND
         q.QUEUE_TABLE = t.QUEUE_TABLE AND
         q.OWNER = t.OWNER;

When Streams is configured in a RAC environment, each queue table has an owning instance. Also, all queues within an individual queue table are owned by the same instance. The Streams clients all use the owning instance of the relevant queue to perform their work:

• Each capture process is run at the owning instance of its queue.

• Each propagation is run at the owning instance of the propagation's source queue.

• Each propagation must connect to the owning instance of the propagation's destination queue.

• Each apply process is run at the owning instance of its queue.

You can configure ownership of a queue to remain on a specific instance, as long as that instance is available, by running the DBMS_AQADM.ALTER_QUEUE_TABLE procedure and setting the primary_instance and secondary_instance parameters. When the primary instance of a queue table is set to a specific instance, the queue ownership will return to the specified instance whenever the instance is running.

Capture processes and apply processes automatically follow the ownership of the queue. If the ownership changes while process is running, then the process stops on the current instance and restarts on the new owner instance.

Queue-to-queue propagations send messages only to the specific queue identified as the destination queue. Also, the source database link for the destination database connect descriptor must specify the correct service to connect to the destination database. The CONNECT_DATA clause in the connect descriptor should specify the global name of the destination database for the SERVICE_NAME.

For example, consider the tnsnames.ora file for a database with the global name db.mycompany.com. Assume that the alias name for the first instance is db1 and that the alias for the second instance is db2. The tnsnames.ora file for this database might include the following entries:

db.mycompany.com= 
 (description= 
  (load_balance=on)
   (address=(protocol=tcp)(host=node1-vip)(port=1521))
   (address=(protocol=tcp)(host=node2-vip)(port=1521))
  (connect_data=
     (service_name=db.mycompany.com)))
 
db1.mycompany.com=
 (description=
  (address=(protocol=tcp)(host=node1-vip)(port=1521))
  (connect_data= 
    (service_name=db.mycompany.com)
    (instance_name=db1)))
 
db2.mycompany.com= 
 (description= 
  (address=(protocol=tcp)(host=node2-vip)(port=1521))
  (connect_data= 
    (service_name=db.mycompany.com)
    (instance_name=db2)))