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

Part Number B28321-01
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
View PDF

10 Preparing 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. Connect in SQL*Plus as an administrative user who can create users, grant privileges, and create tablespaces. Remain connected as this administrative user for all subsequent steps.

  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 user-password 
       DEFAULT TABLESPACE streams_tbs
       QUOTA UNLIMITED ON streams_tbs;
    

    Note:

    Enter an appropriate password for the administrative user.

    See Also:

    Oracle Database 2 Day + 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. However, if DBA role is revoked, then the Oracle Streams administrator must have the privileges granted by GRANT_ADMIN_PRIVILEGE procedure in the DBMS_STREAMS_AUTH package to manage a Stream environment. Step 5 provides instructions for running this procedure.
  5. Optionally, run the GRANT_ADMIN_PRIVILEGE procedure in the DBMS_STREAMS_AUTH package. You might choose to run this procedure on the Oracle Streams administrator created in Step 3 if any of the following conditions are true:

    • The Oracle Streams administrator will run user-created subprograms that execute subprograms in Oracle-supplied packages associated with Oracle Streams. An example is a user-created stored procedure that executes a procedure in the DBMS_STREAMS_ADM package.

    • The Oracle Streams administrator will run user-created subprograms that query data dictionary views associated with Oracle Streams. An example is a user-created stored procedure that queries the DBA_APPLY_ERROR data dictionary view.

    • You plan to revoke DBA role from the Oracle Streams administrator when this user is not creating or altering capture processes, synchronous captures, or apply processes.

    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 Oracle Streams administrator the following privileges:

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

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

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

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

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

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

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.

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 10-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 10-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, you can use the default setting for this parameter. If this parameter is set to a value other than the default, then ensure that it includes the SEND value at the source database and the RECEIVE value at 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.

MEMORY_TARGET

Default: 0 (internally determined by Oracle Database)

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

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: 40 to operating system-dependent

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.

SGA_TARGET

Default: 0 (SGA autotuning is disabled)

Range: 64 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.

SHARED_POOL_SIZE

Default:

When MEMORY_MAX_TARGET, MEMORY_TARGET, or 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 MEMORY_MAX_TARGET, MEMORY_TARGET, or SGA_TARGET are set to zero (32-bit platforms): 32 MB, rounded up to the nearest granule size

When MEMORY_MAX_TARGET, MEMORY_TARGET, or SGA_TARGET are set to zero (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.

STREAMS_POOL_SIZE

Default: 0

Range:

Minimum: 0

Maximum: operating system-dependent

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: "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 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

Currently, Oracle Enterprise Manager provides limited configuration options for Oracle Streams. Specifically, Enterprise Manager provides the following options:

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 procedures begin with MAINTAIN_ and 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.