This chapter provides instructions for preparing a database or a distributed database environment to use Streams.
This chapter contains these topics:
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:
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.
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;
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 thanstrmadminpw
for the Streams administrator.Grant the Streams administrator DBA
role:
GRANT DBA TO strmadmin;
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 procedureUse 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:
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';
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.
Edit the generated script if necessary and save your changes.
Execute the script in SQL*Plus:
SET ECHO ON SPOOL grant_strms_privs.out @/usr/admin/grant_strms_privs.sql SPOOL OFF
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.
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.
Repeat all of the previous steps at each database in the environment that will use 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.
Table 10-1 Initialization Parameters Relevant to Streams
Parameter | Values | Description |
---|---|---|
Range: 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 Release 1, this parameter must be set to To use the new Streams features introduced in Oracle Database 10g Release 2, this parameter must be set to |
|
Range: 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 |
|
Modifiable?: Yes |
Specifies the number of This parameter must be set to at least |
|
Default: Range: Values:
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 ( To use downstream capture and copy the redo data to the downstream database using redo transport services, specify the |
|
Default: None Range: None Modifiable?: Yes |
Defines up to ten log archive destinations, where 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. |
|
Default: Range: One of the following:
Modifiable?: Yes |
Specifies the availability state of the corresponding destination. The parameter suffix ( To use downstream capture and copy the redo data to the downstream database using redo transport services, make sure the destination that corresponds to the |
|
Default: Range: 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 |
|
Default: Derived automatically Range: 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 can use multiple parallel execution servers. Set this initialization parameter to an appropriate value to ensure that there are enough parallel execution servers. |
|
Default: Range: 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. |
|
Range: 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. |
|
Default: Initial size of SGA at startup Range: 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 might need to increase the size of this parameter. |
|
Default: Range: Modifiable?: Yes |
Specifies the total size of all System Global Area (SGA) components. If this parameter is set to a nonzero value, then the size of the Streams pool is managed by Automatic Shared Memory Management. Oracle recommends enabling the autotuning of the various pools within the SGA by setting |
|
Default: If If If 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 |
|
Default: Range: Minimum: Maximum: operating system-dependent Modifiable?: Yes |
Specifies (in bytes) the size of the Streams pool. The Streams pool contains buffered queue messages. In addition, the Streams pool is used for internal communications during parallel capture and apply. If the This parameter is modifiable. If this parameter is reduced to zero when an 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:
For example, if parallelism is set to 3 for a capture process, then increase the Streams pool by 30 MB. If a database has two buffered queues, then increase the Streams pool by 20 MB or more. If parallelism is set to 5 for an apply process, then increase the Streams pool by 5 MB. You can use the See Also: "Streams Pool" |
|
Default: If If The default for Range: 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 |
|
Default: Range: 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 |
See Also:
Oracle Database Reference for more information about these initialization parameters
Oracle Data Guard Concepts and Administration for more information about the LOG_ARCHIVE_DEST_
n
parameter
"Streams Pool" for more information about the SGA_TARGET
and STREAMS_POOL_SIZE
parameters
Oracle Database Administrator's Guide for more information about the UNDO_RETENTION
parameter
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.
For non-Oracle databases, configure an Oracle 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 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