10 Preparing and Configuring an Oracle Streams 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:

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

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.

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:

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

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

• 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 an Oracle Streams administrator named strmadmin, run the following statement:

  CREATE DATABASE LINK dbs2.net CONNECT TO strmadmin 
     IDENTIFIED BY user-password 
     USING 'dbs2.net';
  

Configuring Oracle Streams

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

• The Oracle Streams Global, Schema, Table and Subset Replication Wizard can configure a single-source replication environment that replicates changes to the entire source database, certain schemas in the source database, certain tables in the source database, or subsets of tables in the source database. A capture process captures changes to the replicated database objects at the source database.

• The Oracle Streams Tablespace Replication Wizard can configure a single-source replication environment that replicates changes to one or more self-contained tablespaces. A capture process captures changes to the replicated database objects at the source database.

• The Messaging option opens the Messaging subpage. Using this subpage, you can configure the queues, queue tables, and propagations in a messaging environment. Users and applications can enqueue messages, propagate messages to subscribing queues, notify user applications that messages are ready for consumption, and dequeue messages at the destination.

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:

• Oracle Database 2 Day + Data Replication and Integration Guide for examples that configure Oracle Streams messaging environments and replication environments

• Oracle Streams Replication Administrator's Guide for information about Oracle Streams replication and for examples that configure Oracle Streams replication environments

• Oracle Streams Advanced Queuing User's Guide for information about configuring an Oracle Streams messaging environment

• Oracle Database PL/SQL Packages and Types Reference for more information about the procedures

• Chapter 32, "Single-Database Capture and Apply Example"