8
Preparing a Streams Environment

This chapter provides instructions for preparing a database or a distributed database environment to use Streams and for configuring a Streams environment.

This chapter contains these topics:

• Configuring a Streams Administrator
• Setting Initialization Parameters Relevant to Streams
• Preparing a Database to Run a Streams Capture Process
• Configuring Network Connectivity and Database Links

Configuring a Streams Administrator

To manage a 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 a Streams administrator, and the Streams administrator should not use the SYSTEM tablespace as its default tablespace.

Complete the following steps to configure a Streams administrator at each database in the environment that will use 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 Streams administrator or use an existing tablespace. For example, the following statement creates a new tablespace for the 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 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 strmadminpw
      DEFAULT TABLESPACE streams_tbs
      QUOTA UNLIMITED ON streams_tbs;
   

   Note: To ensure security, use a password other than strmadminpw for the Streams administrator.

4. Grant the Streams administrator CONNECT and RESOURCE role so that this administrator can connect to the database and manage different types of database objects in the administrator's own schema. Also, grant the Streams administrator DBA role.

   GRANT CONNECT, RESOURCE, DBA TO strmadmin;
   
   

5. Optionally, run the GRANT_ADMIN_PRIVILEGE procedure in the DBMS_STREAMS_AUTH package. You may choose to run this procedure on the Streams administrator created in Step3 if any of the following conditions are true:
   • The Streams administrator will run user-created subprograms that execute subprograms in Oracle supplied packages associated with Streams. An example is a user-created stored procedure that executes a procedure in the DBMS_STREAMS_ADM package.
   • The Streams administrator will run user-created subprograms that query data dictionary views associated with Streams. An example is a user-created stored procedure that queries the DBA_APPLY_ERROR data dictionary view.

   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 may run the GRANT_ADMIN_PRIVILEGE procedure to grant such privileges to the Streams administrator, or you may grant them directly.

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

   See Also: 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 Streams administrator the following privileges:
   • SELECT_CATALOG_ROLE if you want to grant the user privileges to query non-Streams data dictionary views
   • SELECT ANY DICTIONARY privilege if you plan to use the Streams tool in the Oracle Enterprise Manager Console
   • 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 a Streams apply process. These procedures may 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 rule-based transformation for a rule used by a Streams capture process, propagation, apply process, or messaging client. For a capture process, 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 Streams administrator must create a supplemental log group for a table in another schema, then the Streams administrator must have the necessary privileges to alter the table.
   • If the Streams administrator does not own the queue used by a Streams capture process, propagation, apply process, or messaging client, and is not specified as the queue user for the queue when the queue is created, then the Streams administrator must be configured as a secure queue user of the queue if you want the Streams administrator to be able to enqueue events into or dequeue events from the queue. The Streams administrator may 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 Streams administrator may need to access
7. Repeat all of the previous steps at each database in the environment that will use Streams.

   See Also: "Monitoring Streams Administrators and Other Streams Users"

Setting Initialization Parameters Relevant to Streams

Table 8-1 lists initialization parameters that are important for the operation, reliability, and performance of a Streams environment. Set these parameters appropriately for your Streams environment. This table specifies whether each parameter is modifiable. A modifiable initialization parameter can be modified using the ALTER SESSION or ALTER SYSTEM statement while an instance is running.

Table 8-1 Initialization Parameters Relevant to Streams  

Parameter	Values	Description
COMPATIBLE	Default: 9.2.0 Range: 9.2.0 to Current Release Number 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 Streams features introduced in Oracle Database 10g, this parameter must be set to 10.1.0 or higher. To use downstream capture, this parameter must be set to 10.1.0 or higher at both the source database and the downstream database.
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 Streams to share information between databases, set this parameter to true at each database that is participating in your Streams environment.
JOB_QUEUE_PROCESSES	Default: 0 Range: 0 to 1000 Modifiable?: Yes	Specifies the number of Jn job queue processes for each instance (J000 ... J999). Job queue processes handle requests created by DBMS_JOB. This parameter must be set to at least 2 at each database that is propagating events in your Streams environment, and should be set to the same value as the maximum number of jobs that can run simultaneously plus two.
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 log files to the downstream database using log transport services, at least one log archive destination must be at the site running the downstream capture process. See Also: Oracle Data Guard Concepts and Administration
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 log files to the downstream database using log transport services, make sure the destination that corresponds to the LOG_ARCHIVE_DEST_n destination for the downstream database is set to enable.
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 a Streams environment, make sure this parameter is set to the default value of 4 or higher.
PARALLEL_MAX_SERVERS	Default: Derived from the values of the following parameters: CPU_COUNT PARALLEL_ADAPTIVE_MULTI_USER PARALLEL_AUTOMATIC_TUNING Range: 0 to 3599 Modifiable?: Yes	Specifies the maximum number of parallel execution processes and parallel recovery processes for an instance. As demand increases, Oracle will increase the number of processes from the number created at instance startup up to this value. In a Streams environment, each capture process and apply process may use multiple parallel execution servers. Set this initialization parameter to an appropriate value to ensure that there are enough parallel execution servers.
PROCESSES	Default: Derived from PARALLEL_MAX_SERVERS Range: 6 to operating system dependent limit Modifiable?: No	Specifies the maximum number of operating system user processes that can simultaneously connect to Oracle. Make sure the value of this parameter allows for all background processes, such as locks, job queue processes, and parallel execution processes. In Streams, capture processes and apply processes use background processes and parallel execution processes, and propagation jobs use job queue processes.
REMOTE_ARCHIVE_ENABLE	Default: true Range: true or false Modifiable?: No	Enables or disables the sending of redo archival to remote destinations and the receipt of remotely archived redo. To use downstream capture and copy the redo log files to the downstream database using log transport services, this parameter must be set to true at both the source database and the downstream database.
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 may 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 limit Modifiable?: No	Specifies the maximum size of SGA for the lifetime of a database instance. To run multiple capture processes on a single database, you may need to increase the size of this parameter.
SHARED_POOL_SIZE	Default: 32-bit platforms: 32 MB, rounded up to the nearest granule size 64-bit platforms: 84 MB, rounded up to the nearest granule size Range: Minimum: the granule size Maximum: 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 STREAMS_POOL_SIZE initialization parameter is set to zero, then Streams may use up to 10% of the shared pool.
STREAMS_POOL_SIZE	Default: 0 Range: Minimum: 0 Maximum: operating system-dependent Modifiable?: Yes	Specifies (in bytes) the size of the Streams pool. The Streams pool contains captured events. In addition, the Streams pool is used for internal communications during parallel capture and apply. If the size of the Streams pool is greater than zero, then any SGA memory used by Streams is allocated from the Streams pool. If the Streams pool size is set to zero, then SGA memory used by Streams is allocated from the shared pool and may use up to 10% of the shared pool. This parameter is modifiable. However, if this parameter is set to zero when an instance starts, then increasing it beyond zero has no effect on the current instance because it is using the shared pool for Streams allocations. Also, if this parameter is set to a value greater than zero when an instance starts and is then reduced to zero when the instance is running, then Streams processes and jobs will not run. You should increase the size of the Streams pool for each of the following factors: 10 MB for each capture process parallelism 1 MB for each apply process parallelism 10 MB or more for each queue staging captured events For example, if parallelism is set to 3 for a capture process, then increase the Streams pool by 30 MB. If parallelism is set to 5 for an apply process, then increase the Streams pool by 5 MB.
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 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, make sure 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. Make sure the undo tablespace has enough space to accommodate the UNDO_RETENTION setting. See Also: Oracle Database Administrator's Guide for more information about the retention period and the undo tablespace

Preparing a Database to Run a Streams Capture Process

Any source database that generates redo log information that will be captured by a capture process must be running in ARCHIVELOG mode. In addition, make sure the initialization parameters are set properly on any database that will run a capture process.

Configuring Network Connectivity and Database Links

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

• For Oracle databases, configure your network and Oracle Net so that the databases can communicate with each other.

  See Also: Oracle Net Services Administrator's Guide

• For non-Oracle databases, configure an Oracle gateway for communication between the Oracle database and the non-Oracle database.

  See Also: Oracle Database Heterogeneous Connectivity Administrator's Guide

• If you plan to propagate events from a source queue at a database to a destination queue at another database, then create a private database link between the database containing the source queue and the database containing the destination queue. Each database link should use a CONNECT TO clause for the user propagating events between databases.

  For example, to create a database link to a database named dbs2.net connecting as a Streams administrator named strmadmin, run the following statement:

  CREATE DATABASE LINK dbs2.net CONNECT TO strmadmin IDENTIFIED BY strmadminpw 
     USING 'dbs2.net';
  

  See Also: Oracle Database Administrator's Guide for more information about creating database links