18 Best Practices for Apply

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, 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'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.

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:

• SET_TABLE_INSTANTIATION_SCN

• SET_SCHEMA_INSTANTIATION_SCN

• SET_GLOBAL_INSTANTIATION_SCN

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.

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.

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 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.

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

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.

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 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.