18 Best Practices for Apply
This chapter describes the best practices for applying changes with an apply process in an Oracle Streams replication environment. This chapter includes these topics:
18.1 Best Practices for Destination Database Configuration
In an Oracle Streams replication environment, a destination database is a database where an apply process applies changes. This section contains these topics:
18.1.1 Grant Required Privileges to the Apply User
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 user can be modified when you alter an apply process. 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
, andDELETE
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
orCREATE
ANY
TABLE
,CREAT
INDEX
orCREATE
ANY
INDEX
, andCREATE
PROCEDURE
orCREATE
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's queue.
These privileges can be granted directly to the apply user, or they can be granted through roles.
In addition, the apply user must be granted EXECUTE
privilege on all packages, including Oracle-supplied packages, that are invoked in subprograms run by the apply process. These privileges must be granted directly to the apply user. They cannot be granted through roles.
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
18.1.2 Set Instantiation SCN Values
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 data definition language (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:
Instantiation and Oracle Streams Replication for more information about instantiation and setting instantiation SCN values
18.1.3 Configure Conflict Resolution
If updates will be performed at multiple databases for the same shared database object, then ensure that 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, ensure that 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.
18.2 Best Practices for Apply Process Configuration
The following sections describe best practices for configuring apply processes:
18.2.1 Set Apply Process Parallelism
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 processes that concurrently apply changes. The default setting for the parallelism
apply process parameter is 4
.
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
or16
. Multiple high-speed CPUs provide excellent processing power. -
If the is light for the apply process, then set apply process parallelism to
1
or4
. -
If the computer system running the database has less than optimal processing power, then set apply process parallelism to
1
or4
.
Ensure that the PROCESSES
initialization parameter is set appropriately when you set the parallelism
apply process parameter.
In addition, if parallelism is greater than 1 for an apply process, then ensure that 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 Language Reference for more information about the
ALTER
TABLE
statement
18.2.2 Consider Allowing Apply Processes to Continue When They Encounter Errors
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 irrecoverable. 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
.
18.3 Best Practices for Apply Process Operation
The following section describes best practices for operating existing apply processes.
18.3.1 Manage Apply Errors
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 Oracle Enterprise Manager Cloud Control.
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