This chapter describes the best practices for applying changes with an apply process in a Streams replication environment. This chapter includes these topics:
In a Streams replication environment, a destination database is a database where an apply process applies changes. This section contains these topics:
The apply user is the user in whose security domain an apply process performs the following actions:
Dequeues messages that satisfy its rule sets
Runs custom rule-based transformations configured for apply process rules
Applies messages directly to database objects
Runs apply handlers configured for the apply process
The apply user for an apply process is configured when you create an apply process, and the apply process can be modifies when you alter an apply process. Grant explicit privileges to the apply user on each table for which the apply process applies changes. Specifically, grant the following privileges to the apply user:
If the apply process applies data manipulation language (DML) changes to a table, then grant INSERT
, UPDATE
, and DELETE
privileges on the table to the apply user.
If the apply process applies data definition language (DDL) changes to a table, then grant CREATE
TABLE
or CREATE
ANY
TABLE
, CREAT
INDEX
or CREATE
ANY
INDEX
, and CREATE
PROCEDURE
or CREATE
ANY
PROCEDURE
to the apply user.
Grant EXECUTE
privilege on the rule sets used by the apply process.
Grant EXECUTE
privilege on all custom rule-based transformation functions specified for rules in the positive rule set of the apply process.
Grant EXECUTE
privilege on any apply handlers used by the apply process.
Grant privileges to dequeue messages from the apply process queue.
See Also:
Oracle Database Security Guide for general information about granting privileges
Oracle Streams Concepts and Administration for information about granting privileges on rule sets
An instantiation SCN value must be set for each database object to which an apply process applies changes. Confirm that an instantiation SCN is set for all such objects, and set any required instantiation SCN values that are not set.
Instantiation SCN values can be set in various ways, including during export/import, by Recovery Manager (RMAN), or manually. To set instantiation SCN values manually, use one of the following procedures in the DBMS_APPLY_ADM
package:
For example, to set the instantiation SCN manually for each table in the a schema, run the SET_SCHEMA_INSTANTIATION_SCN
procedure with the recursive
parameter set to true
. If an apply process applies DDL changes, then set the instantiation SCN values at a level higher than table level using either the SET_SCHEMA_INSTANTIATION_SCN
or SET_GLOBAL_INSTANTIATION_SCN
procedure.
See Also:
Chapter 10, "Performing Instantiations" for more information about instantiation and setting instantiation SCN valuesIf updates will be performed at multiple databases for the same shared database object, then make sure you configure conflict resolution for that object. To simplify conflict resolution for tables with LOB columns, create an error handler to handle errors for the table. When registering the error handler using the DBMS_APPLY_ADM.SET_DML_HANDLER
procedure, make sure you set the assemble_lobs
parameter to true
.
If you configure conflict resolution at a destination database, then additional supplemental logging is required at the source database. Specifically, the columns specified in a column list for conflict resolution during apply must be conditionally logged if more than one column at the source database is used in the column list at the destination database.
The following sections describe best practices for configuring apply processes:
Set the parallelism of an apply process by specifying the parallelism
parameter in the DBMS_APPLY_ADM.SET_PARAMETER
procedure. The parallelism
parameter controls the number of parallel execution servers that concurrently apply changes. The default setting for the parallelism
apply process parameter is 1
.
Typically, apply process parallelism is set to either 1
, 4
, 8
, or 16
. The setting that is best for a particular apply process depends on the load applied and the processing power of the computer system running the database. Follow these guidelines when setting apply process parallelism:
If the load is heavy for the apply process and the computer system running the database has excellent processing power, then set apply process parallelism to 8
or 16
. Multiple high-speed CPUs provide excellent processing power.
If the is light for the apply process, then set apply process parallelism to 1
or 4
.
If the computer system running the database has less than optimal processing power, then set apply process parallelism to 1
or 4
.
Make sure the PROCESSES
and PARALLEL_MAX_SERVERS
initialization parameters are set appropriately when you set the parallelism
apply process parameter.
In addition, if parallelism is greater than 1 for an apply process, then make sure any database objects whose changes are applied by the apply process have the appropriate settings for the INITRANS
and PCTFREE
parameters. The INITRANS
parameter specifies the initial number of concurrent transaction entries allocated within each data block allocated to the database object. Set the INITRANS
parameter to the parallelism of the apply process or higher. The PCTFREE
parameter specifies the percentage of space in each data block of the database object reserved for future updates to rows of the object. The PCTFREE
parameter should be set to 10 or higher. You can modify these parameters for a table using the ALTER
TABLE
statement
See Also:
Oracle Database SQL Reference for more information about the ALTER
TABLE
statement
When the disable_on_error
apply process parameter is set to Y
, the apply process is disabled on the first unresolved error, even if the error is not fatal. When the disable_on_error
apply process parameter is set to N
, the apply process continues regardless of unresolved errors. The default setting for this parameter is Y
. If you do not want an apply process to become disabled when it encounters errors, then set the disable_on_error
parameter to N
.
The following section describes best practices for operating existing apply processes.
The error queue contains all of the current apply errors for a database. If there are multiple apply processes in a database, then the error queue contains the apply errors for each apply process. If an apply process encounters an error when it tries to apply a logical change record (LCR) in a transaction, then all of the LCRs in the transaction are moved to the error queue. To view information about apply errors, query the DBA_APPLY_ERROR
data dictionary view or use Enterprise Manager.
The MESSAGE_NUMBER
column in the DBA_APPLY_ERROR
view indicates the LCR within the transaction that resulted in the error. When apply errors are encountered, correct the problem(s) that caused the error(s), and either retry or delete the error transaction in the error queue.
See Also:
Oracle Streams Concepts and Administration for information about managing apply errors and for information about displaying detailed information for the column values of each LCR in an error transaction