Oracle® Streams Replication Administrator's Guide 11g Release 1 (11.1) Part Number B28322-01 |
|
|
View PDF |
An Oracle Streams replication database is a database that participates in an Oracle Streams replication environment. An Oracle Streams replication environment uses Oracle Streams clients to replicate database changes from one database to another. Oracle Streams clients include capture processes, synchronous captures, propagations, and apply processes. This chapter describes general best practices for Oracle Streams replication databases.
This chapter contains these topics:
For your Oracle Streams replication environment to run properly and efficiently, follow the best practices in this section when you are configuring the environment. This section contains these topics:
Certain initialization parameters are important in an Oracle Streams configuration. Ensure that the initialization parameters are set properly at all databases before configuring an Oracle Streams replication environment.
See Also:
Oracle Streams Concepts and Administration for information about initialization parameters that are important in an Oracle Streams environmentThe following sections describe best practices for database storage in an Oracle Streams database:
Configure a Separate Tablespace for the Oracle Streams Administrator
Use a Separate Queue for Capture and Apply Oracle Streams Clients
Typically, the user name for the Oracle Streams administrator is strmadmin
, but any user with the proper privileges can be an Oracle Streams administrator. The examples in this section use strmadmin
for the Oracle Streams administrator user name.
Create a separate tablespace for the Oracle Streams administrator at each participating Oracle Streams database. This tablespace stores any objects created in the Oracle Streams administrator schema, including any spillover of messages from the buffered queues owned by the schema.
For example, to create a tablespace named streams_tbs
and assign it to the Oracle Streams administrator, log in as an administrative user, and run the following SQL statements:
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;
Specify a valid path on your file system for the datafile in the CREATE
TABLESPACE
statement.
See Also:
Oracle Streams Concepts and Administration for information about configuring an Oracle Streams administratorConfigure a separate queue for each capture process, for each synchronous capture, and for each apply process, and ensure that each queue has its own queue table. Using separate queues is especially important when configuring bidirectional replication between two databases or when a single database receives messages from several other databases.
For example, suppose a database called db1
is using a capture process to capture changes that will be sent to other databases and is receiving changes from a database named db2
. The changes received from db2
are applied by an apply process running on db1
. In this scenario, create a separate queue for the capture process and apply process at db1
, and ensure that these queues use different queue tables.
The following example creates the queue for the capture process. The queue name is capture_queue
, and this queue uses queue table qt_capture_queue
:
BEGIN DBMS_STREAMS_ADM.SET_UP_QUEUE( queue_table => 'strmadmin.qt_capture_queue', queue_name => 'strmadmin.capture_queue'); END; /
The following example creates the queue for the apply process. The queue name is apply_queue
, and this queue uses queue table qt_apply_queue
:
BEGIN DBMS_STREAMS_ADM.SET_UP_QUEUE( queue_table => 'strmadmin.qt_apply_queue', queue_name => 'strmadmin.apply_queue'); END; /
Subsequently, specify the queue strmadmin.capture_queue
when you configure the capture process at db1
, and specify the queue strmadmin.apply_queue
when you configure the apply process at db1
. If necessary, the SET_UP_QUEUE
procedure lets you specify a storage_clause
parameter to configure separate tablespace and storage specifications for each queue table.
To create capture processes, synchronous captures, and apply processes, the Oracle Streams administrator must have DBA privilege. An administrative user must explicitly grant DBA privilege to the Oracle Streams administrator. For example, the following statement grants DBA privilege to an Oracle Streams administrator named strmadmin
:
GRANT DBA TO strmadmin;
In addition, other privileges can be granted to the Oracle Streams administrator on each participating Oracle 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 an Oracle Streams administrator named strmadmin
:
exec DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE('STRMADMIN');
See Also:
Oracle Streams Concepts and Administration for information about configuring an Oracle Streams administratorUse the following procedures in the DBMS_STREAMS_ADM
package to create your Oracle Streams replication environment whenever possible:
MAINTAIN_GLOBAL
configures an Oracle Streams environment that replicates changes at the database level between two databases.
MAINTAIN_SCHEMAS
configures an Oracle 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 Oracle Streams to maintain this tablespace at both databases.
MAINTAIN_TABLES
configures an Oracle 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 Oracle Streams to maintain these tablespaces at both databases.
PRE_INSTANTIATION_SETUP
and POST_INSTANTIATION_SETUP
configure an Oracle 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 Oracle Streams replication configuration.
These procedures automate the entire configuration of the Oracle Streams clients at multiple databases. Further, the configuration follows Oracle 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 Oracle Streams clients, rules sets, and rules:
These procedures minimize the number of steps required to configure Oracle Streams clients. It is also possible to create rules for nonexistent objects, so ensure that 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, and a synchronous capture requires a positive rule set. You can use the ADD_GLOBAL_RULES
procedure to capture DML changes to an entire database with a capture process 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 with a capture process.
The rules in the rule set for a propagation can differ from the rules specified for a capture process or a synchronous capture. 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 or synchronous capture. Similarly, the rules in the rule set for an apply process can differ from the rules specified for the capture process, synchronous capture, and propagation(s) that capture and propagate messages to the apply process.
An Oracle Streams client can process changes for multiple tables or schemas. For the best performance, ensure that the rules for these multiple tables or schemas are simple. Complex rules will impact the performance of Oracle 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 an Oracle Streams replication environment, change cycling should be avoided. Change cycling means sending a change back to the database where it originated. You can use Oracle Streams tags to prevent change cycling.
See Also:
"Configuring Replication Using the DBMS_STREAMS_ADM Package"
"Oracle Streams Tags in a Replication Environment" for information about using Oracle Streams tags to avoid change cycling
Oracle Streams Concepts and Administration for more information about simple and complex rules
After the Oracle Streams replication environment is configured, follow the best practices in this section to keep it running properly and efficiently. This section contains these topics:
Follow the Best Practices for the Global Name of an Oracle Streams Database
Monitor Capture Process and Synchronous Capture Queues for Size
Follow the Best Practices for Removing an Oracle Streams Configuration at a Database
Oracle 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 an Oracle Streams capture process or synchronous capture 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 an Oracle 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 an Oracle 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 Oracle 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.
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 might cause problems if the destination databases are not identical. If you decide not to replicate DDL in your Oracle Streams environment, then any table structure changes must be performed manually at each database in the environment.
For Oracle Database 11g Release 1 (11.1) and later databases, the Oracle Streams Performance Advisor provides information about how Oracle Streams components are performing. You can use this advisor to monitor the performance of multiple Oracle Streams components in your environment and make adjustments to improve performance when necessary.
The UTL_SPADV
package also provides performance statistics for an Oracle Streams environment. This package uses the Oracle Streams Performance Advisor to gather performance statistics. The package enables you to format the statistics in output that can be imported into a spreadsheet for analysis.
For databases prior to Oracle Database 11g Release 1 (11.1), you can use STRMMON
to monitor the performance of an Oracle Streams environment. You can use this tool to obtain a quick overview of the Oracle 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.
See Also:
Oracle Streams Concepts and Administration for information about the Oracle Streams Performance Advisor
Oracle Database PL/SQL Packages and Types Reference for more information about the UTL_SPADV
package
You should monitor the queues used by a capture process to check for queue size. The number of messages in a queue used by a capture process or synchronous capture increases if the messages in the queue cannot be propagated to one or more destination queues. When a source queue becomes large, it often indicates that there is a problem with the Oracle 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 becomes large, 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 can monitor the number of messages in a synchronous capture queue by querying the V$PERSISTENT_QUEUES
or V$AQ
dynamic performance view. This view shows the number of messages in different message states in the persistent queue.
Propagation is implemented using Oracle Scheduler. If a job is unable to execute 16 successive times, the job is marked as "broken" and becomes disabled. Check propagation jobs periodically to ensure that they are running successfully to minimize the size of the source queue.
See Also:
"Restart Broken Propagations"The following sections contain information about best practices for backing up source databases and destination databases in an Oracle Streams replication environment. A single database can be both a source database and a destination database.
A source database is a database where changes captured by a capture process are generated in a redo log or a database where an Oracle Streams synchronous capture is configured. Follow these best practices for backups of an Oracle Streams source database:
Use an Oracle Streams tag in the session that runs the online backup SQL statements to ensure that the capture process which captures changes to the source database will not capture the backup statements. An online backup statement uses the BEGIN
BACKUP
and END
BACKUP
clauses in an ALTER
TABLESPACE
or ALTER
DATABASE
statement. To set an Oracle Streams session tag, use the DBMS_STREAMS.SET_TAG
procedure.
Note:
Backups performed using Recovery Manager (RMAN) do not need to set an Oracle Streams session tag.See Also:
Chapter 4, "Oracle Streams Tags"Do not allow any automated backup of the archived logs that might remove archive logs required by a capture process. It is especially important in an Oracle Streams environment that all required archive log files remain available online and in the expected location until the capture process has finished processing them. If a log required by a capture process is unavailable, then the capture process will abort.
To list each required archive redo log file in a database, run the following query:
COLUMN CONSUMER_NAME HEADING 'Capture|Process|Name' FORMAT A15 COLUMN SOURCE_DATABASE HEADING 'Source|Database' FORMAT A10 COLUMN SEQUENCE# HEADING 'Sequence|Number' FORMAT 99999 COLUMN NAME HEADING 'Required|Archived Redo Log|File Name' FORMAT A40 SELECT r.CONSUMER_NAME, r.SOURCE_DATABASE, r.SEQUENCE#, r.NAME FROM DBA_REGISTERED_ARCHIVED_LOG r, DBA_CAPTURE c WHERE r.CONSUMER_NAME = c.CAPTURE_NAME AND r.NEXT_SCN >= c.REQUIRED_CHECKPOINT_SCN;
Ensure that all archive log files from all threads are available. Database recovery depends on the availability of these logs, and a missing log will result in incomplete recovery.
In situations that result in incomplete recovery (point-in-time recovery) at a source database, follow the instructions in "Performing Point-in-Time Recovery on the Source in a Single-Source Environment" or "Performing Point-in-Time Recovery in a Multiple-Source Environment".
In an Oracle Streams replication environment, a destination database is a database where an apply process applies changes. Follow these best practices for backups of an Oracle Streams destination database:
Ensure that the commit_serialization
apply process parameter is set to the default value of full
.
In situations that result in incomplete recovery (point-in-time recovery) at a destination database, follow the instructions in "Performing Point-in-Time Recovery on a Destination Database".
Every night by default, the optimizer automatically collects statistics on tables whose statistics have become stale. For volatile tables, such as Oracle 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:
Run the DBMS_STATS.GATHER_TABLE_STATS
procedure manually on volatile tables when these tables are at their fullest.
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.
See Also:
Oracle Database Performance Tuning Guide for more information about managing optimizer statisticsBy default, the alert log contains information about why Oracle Streams capture and apply processes stopped. Also, Oracle 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 Oracle 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.
Note:
TheV$STREAMS_TRANSACTION
view does not pertain to synchronous captures.See Also:
Oracle Streams Concepts and Administration for more information about Oracle Streams information in the alert logIf you want to completely remove the Oracle Streams configuration at a database, then complete the following steps:
Connect to the database as an administrative user, and run the DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION
procedure.
Drop the Oracle Streams administrator, if possible.
The following best practices are for Oracle Real Application Clusters (Oracle RAC) databases in Oracle Streams replication environments:
Make Archive Log Files of All Threads Available to Capture Processes
Follow the Best Practices for the Global Name of an Oracle Real Application Clusters Database
Follow the Best Practices for Configuring and Managing Propagations
See Also:
Oracle Streams Concepts and Administration for more information about how Oracle Streams works with Oracle RACThe 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.
The general best practices described in "Follow the Best Practices for the Global Name of an Oracle Streams Database" also apply to Oracle Real Application Clusters (Oracle RAC) databases in an Oracle Streams environment. In addition, if the global name of an Oracle 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, ensure that the CONNECT_DATA
clause in the connect descriptor specifies the global name of the destination database for the SERVICE_NAME
. Also, ensure that the CONNECT_DATA
clause does not include the INSTANCE_NAME
parameter.
If the global name of an Oracle RAC database that contains Oracle Streams propagations is changed, then drop and re-create all propagations. Ensure that 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 an Oracle 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.
See Also:
"Follow the Best Practices for Queue Ownership" for more information about theSERVICE_NAME
parameter in the tnsnames.ora
fileThe general best practices described in "Restart Broken Propagations" also apply to Oracle Real Application Clusters (Oracle RAC) databases in an Oracle 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 an Oracle 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 Oracle 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. Ensure that you re-create these queues when they are empty and no new messages are being enqueued into them.
See Also:
"Use Queue-to-Queue Propagations"All Oracle Streams processing is done at the owning instance of the queue used by the Oracle 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 Oracle Streams is configured in an Oracle Real Application Clusters (Oracle RAC) environment, each queue table has an owning instance. Also, all queues within an individual queue table are owned by the same instance. The following Oracle Streams clients 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)))