10 Preparing a Streams Environment

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: For security purposes, use a password other than strmadminpw for the Streams administrator.

   
   

4. Grant the Streams administrator DBA role:

   GRANT DBA TO strmadmin;
   
   

5. Optionally, run the GRANT_ADMIN_PRIVILEGE procedure in the DBMS_STREAMS_AUTH package. You might 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 can run the GRANT_ADMIN_PRIVILEGE procedure to grant such privileges to the 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 a 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 Streams administrator the following privileges:

   • 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 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 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 messages into or dequeue messages from the queue. The 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 Streams administrator might 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 10-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 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.

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 Database 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 messages 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 messages 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';