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
DELETEprivileges on the table to the apply user.
If the apply process applies data definition language (DDL) changes to a table, then grant
PROCEDUREto the apply user.
EXECUTEprivilege on the rule sets used by the apply process.
EXECUTEprivilege on all custom rule-based transformation functions specified for rules in the positive rule set of the apply process.
EXECUTEprivilege 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.
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
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
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
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
Typically, apply process parallelism is set to either
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
16. Multiple high-speed CPUs provide excellent processing power.
If the is light for the apply process, then set apply process parallelism to
If the computer system running the database has less than optimal processing power, then set apply process parallelism to
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
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
Oracle Database SQL Language Reference for more information about the
18.2.2 Consider Allowing Apply Processes to Continue When They Encounter Errors
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
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.
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.
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