Skip Headers
Oracle® Streams Concepts and Administration
11g Release 1 (11.1)

B28321-03
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

9 Preparing for and Configuring an Oracle Streams Environment

The following topics describe preparing a database or a distributed database environment to use Oracle Streams and configuring an Oracle Streams replication or messaging environment:

Configuring an Oracle Streams Administrator

To manage an Oracle Streams environment, either create a new user with the appropriate privileges or grant these privileges to an existing user. You should not use the SYS or SYSTEM user as an Oracle Streams administrator, and the Oracle Streams administrator should not use the SYSTEM tablespace as its default tablespace.

Complete the following steps to configure an Oracle Streams administrator at each database in the environment that will use Oracle Streams:

  1. In SQL*Plus, connect as an administrative user who can create users, grant privileges, and create tablespaces. Remain connected as this administrative user for all subsequent steps.

    See Oracle Database Administrator's Guide for information about connecting to a database in SQL*Plus.

  2. Either create a tablespace for the Oracle Streams administrator or use an existing tablespace. For example, the following statement creates a new tablespace for the Oracle Streams administrator:

    CREATE TABLESPACE streams_tbs DATAFILE '/usr/oracle/dbs/streams_tbs.dbf' 
      SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
    
  3. Create a new user to act as the Oracle Streams administrator or use an existing user. For example, to create a new user named strmadmin and specify that this user uses the streams_tbs tablespace, run the following statement:

    CREATE USER strmadmin IDENTIFIED BY password 
       DEFAULT TABLESPACE streams_tbs
       QUOTA UNLIMITED ON streams_tbs;
    

    Note:

    Enter an appropriate password for the administrative user.

    See Also:

    Oracle Database Security Guide for guidelines for choosing passwords
  4. Grant the Oracle Streams administrator DBA role:

    GRANT DBA TO strmadmin;
    

    Note:

    The DBA role is required for a user to create or alter capture processes, synchronous captures, and apply processes. When the user does not need to perform these tasks, DBA role can be revoked from the user.
  5. Run the GRANT_ADMIN_PRIVILEGE procedure in the DBMS_STREAMS_AUTH package.

    A user must have explicit EXECUTE privilege on a package to execute a subprogram in the package inside of a user-created subprogram, and a user must have explicit SELECT privilege on a data dictionary view to query the view inside of a user-created subprogram. These privileges cannot be through a role. You can run the GRANT_ADMIN_PRIVILEGE procedure to grant such privileges to the Oracle Streams administrator, or you can grant them directly.

    Depending on the parameter settings for the GRANT_ADMIN_PRIVILEGE procedure, it either grants the privileges needed to be an Oracle Streams administrator directly, or it generates a script that you can edit and then run to grant these privileges.

    See Also:

    Oracle Database PL/SQL Packages and Types Reference for more information about this procedure

    Use the GRANT_ADMIN_PRIVILEGE procedure to grant privileges directly:

    BEGIN
      DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
        grantee          => 'strmadmin',    
        grant_privileges => TRUE);
    END;
    /
    

    Use the GRANT_ADMIN_PRIVILEGE procedure to generate a script:

    1. Use the SQL statement CREATE DIRECTORY to create a directory object for the directory into which you want to generate the script. A directory object is similar to an alias for the directory. For example, to create a directory object called admin_dir for the /usr/admin directory on your computer system, run the following procedure:

      CREATE DIRECTORY admin_dir AS '/usr/admin';
      
    2. Run the GRANT_ADMIN_PRIVILEGE procedure to generate a script named grant_strms_privs.sql and place this script in the /usr/admin directory on your computer system:

      BEGIN
        DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
          grantee          => 'strmadmin',    
          grant_privileges => FALSE,
          file_name        => 'grant_strms_privs.sql',
          directory_name   => 'admin_dir');
      END;
      /
      

      Notice that the grant_privileges parameter is set to FALSE so that the procedure does not grant the privileges directly. Also, notice that the directory object created in Step a is specified for the directory_name parameter.

    3. Edit the generated script if necessary and save your changes.

    4. Execute the script in SQL*Plus:

      SET ECHO ON
      SPOOL grant_strms_privs.out
      @/usr/admin/grant_strms_privs.sql
      SPOOL OFF
      
    5. Check the spool file to ensure that all of the grants executed successfully. If there are errors, then edit the script to correct the errors and rerun it.

  6. If necessary, grant the following additional privileges:

    • Grant the privileges for a remote Oracle Streams administrator to perform actions in the local database. Grant these privileges using the GRANT_REMOTE_ADMIN_ACCESS procedure in the DBMS_STREAMS_AUTH package. Grant this privilege if a remote Oracle Streams administrator will use a database link that connects to the local Oracle Streams administrator to perform administrative actions. Specifically, grant these privileges if either of the following conditions are true:

      • You plan to configure a downstream capture process at a remote downstream database that captures changes originating at the local source database, and the downstream capture process will use a database link to perform administrative actions at the source database.

      • You plan to configure an apply process at the local database and use a remote Oracle Streams administrator to set the instantiation SCN values for replicated database objects at the local database.

    • If no apply user is specified for an apply process, then grant the Oracle Streams administrator the necessary privileges to perform DML and DDL changes on the apply objects owned by another user. If an apply user is specified, then the apply user must have these privileges.

    • If no apply user is specified for an apply process, then grant the Oracle Streams administrator EXECUTE privilege on any PL/SQL procedure owned by another user that is executed by an Oracle Streams apply process. These procedures can be used in apply handlers or error handlers. If an apply user is specified, then the apply user must have these privileges.

    • Grant the Oracle Streams administrator EXECUTE privilege on any PL/SQL function owned by another user that is specified in a custom rule-based transformation for a rule used by an Oracle Streams capture process, synchronous capture, propagation, apply process, or messaging client. For a capture process or synchronous capture, if a capture user is specified, then the capture user must have these privileges. For an apply process, if an apply user is specified, then the apply user must have these privileges.

    • Grant the Oracle Streams administrator privileges to alter database objects where appropriate. For example, if the Oracle Streams administrator must create a supplemental log group for a table in another schema, then the Oracle Streams administrator must have the necessary privileges to alter the table.

    • If the Oracle Streams administrator does not own the queue used by an Oracle Streams capture process, synchronous capture, propagation, apply process, or messaging client, and is not specified as the queue user for the queue when the queue is created, then the Oracle Streams administrator must be configured as a secure queue user of the queue if you want the Oracle Streams administrator to be able to enqueue messages into or dequeue messages from the queue. The Oracle Streams administrator might also need ENQUEUE or DEQUEUE privileges on the queue, or both. See "Enabling a User to Perform Operations on a Secure Queue" for instructions.

    • Grant the Oracle Streams administrator EXECUTE privilege on any object types that the Oracle Streams administrator might need to access.

    • If Oracle Database Vault is installed, then the user who performs the following actions must be granted the BECOME USER system privilege:

      • Creates a capture process

      • Creates an apply process

      • Alters a capture user

      • Alters an apply user

      Granting the BECOME USER system privilege to the user who performs these actions is not required if Oracle Database Vault is not installed. You can revoke the BECOME USER system privilege from the user after the completing one of these actions, if necessary.

  7. Repeat all of the previous steps at each database in the environment that will use Oracle Streams.

Setting Initialization Parameters Relevant to Oracle Streams

Some initialization parameters are important for the operation, reliability, and performance of an Oracle Streams environment. Set these parameters appropriately for your Oracle Streams environment.

Table 9-1 describes the initialization parameters that are relevant to Oracle Streams. This table specifies whether each parameter is modifiable. A modifiable initialization parameter can be modified using the ALTER SYSTEM statement while an instance is running. Some of the modifiable parameters can also be modified for a single session using the ALTER SESSION statement.

Table 9-1 Initialization Parameters Relevant to Oracle Streams

Parameter Values Description

COMPATIBLE

Default: 11.0.0

Range: 10.0.0 to default release

Modifiable?: No

This parameter specifies the release with which the Oracle server must maintain compatibility. Oracle servers with different compatibility levels can interoperate.

To use the new Oracle Streams features introduced in Oracle Database 11g Release 1, this parameter must be set to 11.0.0 or higher.

GLOBAL_NAMES

Default: false

Range: true or false

Modifiable?: Yes

Specifies whether a database link is required to have the same name as the database to which it connects.

To use Oracle Streams to share information between databases, set this parameter to true at each database that is participating in your Oracle Streams environment.

LOG_ARCHIVE_CONFIG

Default: 'SEND, RECEIVE, NODG_CONFIG'

Range: Values:

  • SEND

  • NOSEND

  • RECEIVE

  • NORECEIVE

  • DG_CONFIG

  • NODG_CONFIG

Modifiable?: Yes

Enables or disables the sending of redo logs to remote destinations and the receipt of remote redo logs, and specifies the unique database names (DB_UNIQUE_NAME) for each database in the Data Guard configuration

To use downstream capture and copy the redo data to the downstream database using redo transport services, specify the DB_UNIQUE_NAME of the source database and the downstream database using the DG_CONFIG attribute. This parameter must be set at both the source database and the downstream database.

LOG_ARCHIVE_DEST_n

Default: None

Range: None

Modifiable?: Yes

Defines up to ten log archive destinations, where n is 1, 2, 3, ... 10.

To use downstream capture and copy the redo data to the downstream database using redo transport services, at least one log archive destination must be at the site running the downstream capture process.

LOG_ARCHIVE_DEST_STATE_n

Default: enable

Range: One of the following:

  • alternate

  • reset

  • defer

  • enable

Modifiable?: Yes

Specifies the availability state of the corresponding destination. The parameter suffix (1 through 10) specifies one of the ten corresponding LOG_ARCHIVE_DEST_n destination parameters.

To use downstream capture and copy the redo data to the downstream database using redo transport services, ensure that the destination that corresponds to the LOG_ARCHIVE_DEST_n destination for the downstream database is set to enable.

LOG_BUFFER

Default: 512 KB or 128 KB * CPU_COUNT, whichever is greater

Range: Operating system-dependent

Modifiable?: No

Specifies the amount of memory (in bytes) that Oracle uses when buffering redo entries to a redo log file. Redo log entries contain a record of the changes that have been made to the database block buffers.

If a capture process is running on the database, then set this parameter properly so that the capture process reads redo log records from the redo log buffer rather than from the hard disk.

MEMORY_MAX_TARGET

Default: 0

Range: 0 to the physical memory size available to Oracle Database

Modifiable?: No

Specifies the maximum systemwide usable memory for an Oracle database.

If the MEMORY_TARGET parameter is set to a nonzero value, then set this parameter to a large nonzero value if you must specify the maximum memory usage of the Oracle database.

See Also: "Configuring the Oracle Streams Pool"

MEMORY_TARGET

Default: 0

Range: 152 MB to MEMORY_MAX_TARGET setting

Modifiable?: Yes

Specifies the systemwide usable memory for an Oracle database.

Oracle recommends enabling the autotuning of the memory usage of an Oracle database by setting MEMORY_TARGET to a large nonzero value (if this parameter is supported on your platform).

See Also: "Configuring the Oracle Streams Pool"

OPEN_LINKS

Default: 4

Range: 0 to 255

Modifiable?: No

Specifies the maximum number of concurrent open connections to remote databases in one session. These connections include database links, as well as external procedures and cartridges, each of which uses a separate process.

In an Oracle Streams environment, ensure that this parameter is set to the default value of 4 or higher.

PROCESSES

Default: 100

Range: 6 to operating system-dependent

Modifiable?: No

Specifies the maximum number of operating system user processes that can simultaneously connect to Oracle.

Ensure that the value of this parameter allows for all background processes, such as locks, slave processes. In Oracle Streams, capture processes and apply processes use background processes, and propagation jobs use Oracle Scheduler slave processes.

SESSIONS

Default: Derived from:

(1.1 * PROCESSES) + 5

Range: 1 to 231

Modifiable?: No

Specifies the maximum number of sessions that can be created in the system.

To run one or more capture processes or apply processes in a database, you might need to increase the size of this parameter. Each background process in a database requires a session.

SGA_MAX_SIZE

Default: Initial size of SGA at startup

Range: 0 to operating system-dependent

Modifiable?: No

Specifies the maximum size of System Global Area (SGA) for the lifetime of a database instance.

If the SGA_TARGET parameter is set to a nonzero value, then set this parameter to a large nonzero value if you must specify the SGA size.

See Also: "Configuring the Oracle Streams Pool"

SGA_TARGET

Default: 0 (SGA autotuning is disabled)

Range: 64M to operating system-dependent

Modifiable?: Yes

Specifies the total size of all System Global Area (SGA) components.

If MEMORY_MAX_TARGET and MEMORY_TARGET are set to 0 (zero), then Oracle recommends enabling the autotuning of SGA memory by setting SGA_TARGET to a large nonzero value.

If this parameter is set to a nonzero value, then the size of the Oracle Streams pool is managed by Automatic Shared Memory Management.

See Also: "Configuring the Oracle Streams Pool"

SHARED_POOL_SIZE

Default:

When SGA_TARGET is set to a nonzero value: If the parameter is not specified, then the default is 0 (internally determined by Oracle Database). If the parameter is specified, then the user-specified value indicates a minimum value for the shared memory pool.

When SGA_TARGET is not set (32-bit platforms): 32 MB, rounded up to the nearest granule size. When SGA_TARGET is not set (64-bit platforms): 84 MB, rounded up to the nearest granule size.

Range: The granule size to operating system-dependent

Modifiable?: Yes

Specifies (in bytes) the size of the shared pool. The shared pool contains shared cursors, stored procedures, control structures, and other structures.

If the MEMORY_MAX_TARGET, MEMORY_TARGET, SGA_TARGET, and STREAMS_POOL_SIZE initialization parameters are set to zero, then Oracle Streams transfers an amount equal to 10% of the shared pool from the buffer cache to the Oracle Streams pool.

See Also: "Configuring the Oracle Streams Pool"

STREAMS_POOL_SIZE

Default: 0

Range: 0 to operating system-dependent limit

Modifiable?: Yes

Specifies (in bytes) the size of the Oracle Streams pool. The Oracle Streams pool contains buffered queue messages. In addition, the Oracle Streams pool is used for internal communications during parallel capture and apply.

If the MEMORY_TARGET or MEMORY_MAX_TARGET initialization parameter is set to a nonzero value, then the Oracle Streams pool size is set by Automatic Memory Management, and STREAMS_POOL_SIZE specifies the minimum size.

If the SGA_TARGET initialization parameter is set to a nonzero value, then the Oracle Streams pool size is set by Automatic Shared Memory Management, and STREAMS_POOL_SIZE specifies the minimum size.

This parameter is modifiable. If this parameter is reduced to zero when an instance is running, then Oracle Streams processes and jobs might not run.

Ensure that there is enough memory to accommodate the following Oracle Streams requirements:

  • 10 MB for each capture process parallelism

  • 10 MB or more for each buffered queue. The buffered queue is where the buffered messages are stored.

  • 1 MB for each apply process parallelism

For example, if parallelism is set to 3 for a capture process, then at least 30 MB is required for the capture process. If a database has two buffered queues, then at least 20 MB is required for the buffered queues. If parallelism is set to 5 for an apply process, then at least 5 MB is required for the apply process.

You can use the V$STREAMS_POOL_ADVICE dynamic performance view to determine an appropriate setting for this parameter.

See Also: "Configuring the Oracle Streams Pool"

TIMED_STATISTICS

Default:

If STATISTICS_LEVEL is set to TYPICAL or ALL, then true

If STATISTICS_LEVEL is set to BASIC, then false

The default for STATISTICS_LEVEL is TYPICAL.

Range: true or false

Modifiable?: Yes

Specifies whether or not statistics related to time are collected.

To collect elapsed time statistics in the dynamic performance views related to Oracle Streams, set this parameter to true. The views that include elapsed time statistics include: V$STREAMS_CAPTURE, V$STREAMS_APPLY_COORDINATOR, V$STREAMS_APPLY_READER, V$STREAMS_APPLY_SERVER.

UNDO_RETENTION

Default: 900

Range: 0 to 232-1 (max value represented by 32 bits)

Modifiable?: Yes

Specifies (in seconds) the amount of committed undo information to retain in the database.

For a database running one or more capture processes, ensure that this parameter is set to specify an adequate undo retention period.

If you are running one or more capture processes and you are unsure about the proper setting, then try setting this parameter to at least 3600. If you encounter "snapshot too old" errors, then increase the setting for this parameter until these errors cease. Ensure that the undo tablespace has enough space to accommodate the UNDO_RETENTION setting.


See Also:

Configuring the Oracle Streams Pool

The Oracle Streams pool is a portion of memory in the System Global Area (SGA) that is used by Oracle Streams. The Oracle Streams pool stores buffered queue messages in memory, and it provides memory for capture processes and apply processes. The Oracle Streams pool always stores LCRs captured by a capture process, and it stores LCRs and messages that are enqueued into a buffered queue by applications.

The Oracle Streams pool is initialized the first time any one of the following actions occur in a database:

The size of the Oracle Streams pool is determined in one of the following ways:

Note:

If the Oracle Streams pool cannot be initialized, then an ORA-00832 error is returned. If this happens, then first ensure that there is enough space in the SGA for the Oracle Streams pool. If necessary, reset the SGA_MAX_SIZE initialization parameter to increase the SGA size. Next, set one or more of the following initialization parameters: MEMORY_TARGET, MEMORY_MAX_TARGET, SGA_TARGET, and STREAMS_POOL_SIZE.

Using Automatic Memory Management to Set the Oracle Streams Pool Size

The Automatic Memory Management feature manages the size of the Oracle Streams pool when the MEMORY_TARGET or MEMORY_MAX_TARGET initialization parameter is set to a nonzero value. When you use Automatic Memory Management, you can still set the following initialization parameters:

  • If the SGA_TARGET initialization parameter also is set to a nonzero value, then Automatic Memory Management uses this value as a minimum for the system global area (SGA).

  • If the STREAMS_POOL_SIZE initialization parameter also is set to a nonzero value, then Automatic Memory Management uses this value as a minimum for the Oracle Streams pool.

The current memory allocated to Oracle Streams pool by Automatic Memory Management can be viewed by querying the V$MEMORY_DYNAMIC_COMPONENTS view.

Note:

Currently, the MEMORY_TARGET and MEMORY_MAX_TARGET initialization parameters are not supported on some platforms.

Using Automatic Shared Memory Management to Set the Oracle Streams Pool Size

The Automatic Shared Memory Management feature manages the size of the Oracle Streams pool when the following conditions are met:

  • The MEMORY_TARGET and MEMORY_MAX_TARGET initialization parameters are both set to 0 (zero).

  • SGA_TARGET initialization parameter is set to a nonzero value.

If you are using Automatic Shared Memory Management and the STREAMS_POOL_SIZE initialization parameter also is set to a nonzero value, then Automatic Shared Memory Management uses this value as a minimum for the Oracle Streams pool. You can set a minimum size if your environment needs a minimum amount of memory in the Oracle Streams pool to function properly. The current memory allocated to Oracle Streams pool by Automatic Shared Memory Management can be viewed by querying the V$SGA_DYNAMIC_COMPONENTS view.

Setting the Oracle Streams Pool Size Manually

The Oracle Streams pool size is the value specified by the STREAMS_POOL_SIZE parameter, in bytes, if the following conditions are met.

  • The MEMORY_TARGET, MEMORY_MAX_TARGET, and SGA_TARGET initialization parameters are all set to 0 (zero).

  • The STREAMS_POOL_SIZE initialization parameter is set to a nonzero value.

If you plan to set the Oracle Streams pool size manually, then you can use the V$STREAMS_POOL_ADVICE dynamic performance view to determine an appropriate setting for the STREAMS_POOL_SIZE initialization parameter.

Using the Default Setting for the Oracle Streams Pool Size

The Oracle Streams pool size is set by default if all of the following parameters are set to 0 (zero): MEMORY_TARGET, MEMORY_MAX_TARGET, SGA_TARGET, and STREAMS_POOL_SIZE. When the Oracle Streams pool size is set by default, the first use of Oracle Streams in a database transfers an amount of memory equal to 10% of the shared pool from the buffer cache to the Oracle Streams pool. The buffer cache is set by the DB_CACHE_SIZE initialization parameter, and the shared pool size is set by the SHARED_POOL_SIZE initialization parameter.

For example, consider the following configuration in a database before Oracle Streams is used for the first time:

  • DB_CACHE_SIZE is set to 100 MB.

  • SHARED_POOL_SIZE is set to 80 MB.

  • MEMORY_TARGET, MEMORY_MAX_TARGET, SGA_TARGET, and STREAMS_POOL_SIZE are all set to zero.

Given this configuration, the amount of memory allocated after Oracle Streams is used for the first time is the following:

  • The buffer cache has 92 MB.

  • The shared pool has 80 MB.

  • The Oracle Streams pool has 8 MB.

Configuring Network Connectivity and Database Links

If you plan to use Oracle Streams to share information between databases, then configure network connectivity and database links between these databases:

See Also:

Configuring Oracle Streams

Oracle Enterprise Manager provides the following configuration options for Oracle Streams:

If these options do not meet your requirements, then you can use SQL*Plus and Oracle-supplied packages to configure an Oracle Streams environment. The DBMS_STREAMS_ADM package includes procedures that configure an Oracle Streams replication environment with one procedure call. These configuration procedures might be the easiest way to configure a replication environment that satisfies your requirements.

These procedures and the configuration wizards in Enterprise Manager currently only configure Oracle Streams replication environments that use capture processes. Alternatively, you might choose to use synchronous captures instead of capture processes. Synchronous captures might be appropriate if you plan to capture changes to a relatively small number of tables.

See the following documentation for instructions about configuring Oracle Streams and for examples that configure different types of Oracle Streams environments:

Note:

Any source database that generates redo data that will be captured by a capture process must run in ARCHIVELOG mode.