Preliminary setup

Before you start to creates captures and apply processes, you need to setup a Streams administrator and this administrator needs to be into a separated tablespaces. Usually the tablespace is called 'Streams' and the user administrator is 'STRMADMIN'.

The best reason to have a dedicated tablespace for streams is that the exceptions queues, specially on the apply site may grow indefinitely. If you disable a trigger on a target table on the apply site, despite that your replication setup is fine, all rows will go to the exception queues. There is no reason for the capture site to stop sending rows, since everything in respect of the streams is correct.  So to avoid impacting the rest of the DB, the user strmadmin and all its queue tables and exceptions queues are usually setup in a separated tablespace.

for instance this will do:

The role of user STRMADMIN

Streams uses a technical schema,  which names by default is strmadmin most of the time. When dealing with MQseries, the names becomes 'mquser'. This user is the owner of the capture, apply and propagations processes as well as the queue and queue tables. 

This user, from now on let's call it definitively 'STRMADMIN' needs some grants, among them is the grant DBA.  The administrator needs also the 'grant_admin_privilege'. 
Here is the minimum that the Streams administrator needs:

init.ora parameters

The following init.ora must be set (Metalink note: 418755.1)

Parameter	Value	Comments
Compatible	minimum value must be set to 10.2.0
GLOBAL_NAMES	TRUE
JOB_QUEUE_PROCESSES	>= 4
PARALLEL_MAX_SERVERS	>= 2
SHARED_POOL_SIZE	Streams uses 10% of shared pool size with a minimum of 10 mb per capture process
OPEN_LINKS	>= 4
SGA_TARGET	> 0	The size of the Streams pool is managed by Automatic Shared Memory Management when this parameter is set
STREAMS_POOL_SIZE	>= 200m
UNDO_RETENTION	>=3600
_job_queue_interval	1
aq_tm_processes	1	Scan rate interval (seconds) of job queue

Additional parameters:

There are some additional parameters to set. There are extensive documentation on metalink about each of them :

ALTER SYSTEM SET aq_tm_processes=1 scope=spfile ; -- note 335516.1 alter system set "_spin_count"=5000 scope=spfile ; ALTER TABLE SYS.STREAMS$_APPLY_PROGRESS INITRANS 16 PCTFREE 10;

 

Allowing duplicate rows

Prior to 10Gr2, operation on tables without PK with LCR that affected duplicate row generated Error message by the apply. You can now allow operation on duplicate rows by setting a specific parameter. In
Oracle Database 10g Release 2, the new allow_duplicate_rows apply process parameter can be set to true to allow an apply process to apply a row LCR that changes more than one row.

exec DBMS_APPLY_ADM.SET_PARAMETER( apply_name    => 'apply_stream',                                     parameter     =>'allow_duplicate_rows',                                     value         => 'y');

The GRANT_ADMIN_PRIVILEGE

Executing this sole command generates and execute the scripts below.

You may obtain the script below by running this procedure

CREATE DIRECTORY user_grant_dir AS '/tmp'; 

BEGIN DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE( grantee          => 'strmadmin', 
                                               grant_privileges => false, 
                                               file_name        => 'grant_strms_privs.sql', 
                                               directory_name   => 'user_grant_dir'); END; /

[linux1:oracle] > cat /tmp/grant_strms_privs.sql

-- rule privileges section
grant execute on dbms_rule_adm to STRMADMIN
/
BEGIN
dbms_rule_adm.grant_system_privilege(
privilege => dbms_rule_adm.CREATE_EVALUATION_CONTEXT_OBJ,
grantee => 'STRMADMIN',
grant_option => true);
END;
/
BEGIN
dbms_rule_adm.grant_system_privilege(
privilege => dbms_rule_adm.CREATE_RULE_SET_OBJ,
grantee => 'STRMADMIN',
grant_option => true);
END;
/
BEGIN
dbms_rule_adm.grant_system_privilege(
privilege => dbms_rule_adm.CREATE_RULE_OBJ,
grantee => 'STRMADMIN',
grant_option => true);
END;
/
BEGIN
dbms_rule_adm.grant_system_privilege(
privilege => dbms_rule_adm.CREATE_ANY_RULE_SET,
grantee => 'STRMADMIN',
grant_option => true);
END;
/
BEGIN
dbms_rule_adm.grant_system_privilege(
privilege => dbms_rule_adm.ALTER_ANY_RULE_SET,
grantee => 'STRMADMIN',
grant_option => true);
END;
/
BEGIN
dbms_rule_adm.grant_system_privilege(
privilege => dbms_rule_adm.EXECUTE_ANY_RULE_SET,
grantee => 'STRMADMIN',
grant_option => true);
END;
/
BEGIN
dbms_rule_adm.grant_system_privilege(
privilege => dbms_rule_adm.CREATE_ANY_RULE,
grantee => 'STRMADMIN',
grant_option => true);
END;
/
BEGIN
dbms_rule_adm.grant_system_privilege(
privilege => dbms_rule_adm.ALTER_ANY_RULE,
grantee => 'STRMADMIN',
grant_option => true);
END;
/
BEGIN
dbms_rule_adm.grant_system_privilege(
privilege => dbms_rule_adm.EXECUTE_ANY_RULE,
grantee => 'STRMADMIN',
grant_option => true);
END;
/
-- queue privileges section
grant execute on dbms_aq to STRMADMIN
/
grant execute on dbms_aqadm to STRMADMIN
/
BEGIN
dbms_aqadm.grant_system_privilege(
'ENQUEUE_ANY',
'STRMADMIN',
admin_option => TRUE);
END;
/
BEGIN
dbms_aqadm.grant_system_privilege(
'DEQUEUE_ANY',
'STRMADMIN',
admin_option => TRUE);
END;
/
BEGIN
dbms_aqadm.grant_system_privilege(
'MANAGE_ANY',
'STRMADMIN',
TRUE);
END;
/
grant select on dba_queue_tables to STRMADMIN
/
grant select on dba_queues to STRMADMIN
/
grant select on dba_queue_schedules to STRMADMIN
/
grant select on sys.v_$aq to STRMADMIN
/
grant select on sys.gv_$aq to STRMADMIN
/
grant select on sys.aq$_propagation_status to STRMADMIN
/
grant execute on sys.dbms_aqin to STRMADMIN
/
grant select on sys.aq$internet_users to STRMADMIN
/
grant execute on sys.dbms_transform to STRMADMIN
/
grant execute on sys.dbms_aqelm to STRMADMIN
/
grant select on dba_aq_agents to STRMADMIN
/
grant select on dba_aq_agent_privs to STRMADMIN



/
grant select on dba_queue_subscribers to STRMADMIN
/
-- streams packages section
grant execute on dbms_capture_adm to STRMADMIN
/
grant execute on dbms_propagation_adm to STRMADMIN
/
grant execute on dbms_apply_adm to STRMADMIN
/
grant execute on dbms_streams_adm to STRMADMIN
/
grant execute on dbms_streams_messaging to STRMADMIN



/
-- streams views section
grant select on dba_streams_global_rules to STRMADMIN
/
grant select on dba_streams_schema_rules to STRMADMIN
/
grant select on dba_streams_table_rules to STRMADMIN
/
grant select on dba_streams_transform_function to STRMADMIN
/
grant select on dba_streams_administrator to STRMADMIN
/
grant select on dba_streams_message_rules to STRMADMIN
/
grant select on dba_streams_message_consumers to STRMADMIN
/
grant select on dba_apply to STRMADMIN
/
grant select on dba_apply_parameters to STRMADMIN
/
grant select on dba_apply_instantiated_objects to STRMADMIN
/
grant select on dba_apply_instantiated_schemas to STRMADMIN
/
grant select on dba_apply_instantiated_global to STRMADMIN
/
grant select on dba_apply_key_columns to STRMADMIN
/
grant select on dba_apply_conflict_columns to STRMADMIN
/
grant select on dba_apply_dml_handlers to STRMADMIN
/
grant select on dba_apply_progress to STRMADMIN
/
grant select on dba_apply_error to STRMADMIN
/
grant select on dba_apply_enqueue to STRMADMIN
/
grant select on dba_apply_execute to STRMADMIN
/
grant select on sys.gv_$streams_apply_coordinator to STRMADMIN
/
grant select on sys.v_$streams_apply_coordinator to STRMADMIN
/
grant select on sys.gv_$streams_apply_server to STRMADMIN
/
grant select on sys.v_$streams_apply_server to STRMADMIN
/
grant select on sys.gv_$streams_apply_reader to STRMADMIN
/
grant select on sys.v_$streams_apply_reader to STRMADMIN
/
grant select on dba_capture to STRMADMIN
/
grant select on dba_capture_parameters to STRMADMIN
/
grant select on dba_capture_prepared_database to STRMADMIN
/
grant select on dba_capture_prepared_schemas to STRMADMIN
/
grant select on dba_capture_prepared_tables to STRMADMIN
/
grant select on dba_capture_extra_attributes to STRMADMIN
/
grant select on dba_registered_archived_log to STRMADMIN
/
grant select on sys.gv_$streams_capture to STRMADMIN
/
grant select on sys.v_$streams_capture to STRMADMIN
/
grant select on dba_rule_sets to STRMADMIN
/
grant select on dba_rulesets to STRMADMIN
/
grant select on dba_rules to STRMADMIN
/
grant select on dba_rule_set_rules to STRMADMIN
/
grant select on dba_evaluation_contexts to STRMADMIN
/
grant select on dba_evaluation_context_tables to STRMADMIN
/
grant select on dba_evaluation_context_vars to STRMADMIN
/
grant select on dba_queue_publishers to STRMADMIN
/
grant select on sys.gv_$buffered_queues to STRMADMIN
/
grant select on sys.v_$buffered_queues to STRMADMIN
/
grant select on sys.gv_$buffered_subscribers to STRMADMIN
/
grant select on sys.v_$buffered_subscribers to STRMADMIN
/
grant select on sys.gv_$buffered_publishers to STRMADMIN
/
grant select on sys.v_$buffered_publishers to STRMADMIN
/
grant select on dba_propagation to STRMADMIN



/
-- streams views section
grant select on dba_streams_unsupported to STRMADMIN
/
-- streams views section
grant select on dba_streams_newly_supported to STRMADMIN
/
grant select on dba_streams_rules to STRMADMIN
/
grant select on dba_apply_table_columns to STRMADMIN
/
grant select on dba_streams_add_column to STRMADMIN
/
grant select on dba_streams_delete_column to STRMADMIN
/
grant select on dba_streams_rename_column to STRMADMIN
/
grant select on dba_streams_rename_schema to STRMADMIN
/
grant select on dba_streams_rename_table to STRMADMIN
/
grant select on dba_streams_transformations to STRMADMIN
/
grant select on dba_apply_spill_txn to STRMADMIN
/
grant select on sys.gv_$streams_transaction to STRMADMIN
/
grant select on sys.v_$streams_transaction to STRMADMIN
/
-- miscellaneous privileges section
grant restricted session to STRMADMIN
/
grant execute on dbms_flashback to STRMADMIN
/
BEGIN
dbms_streams_auth.grant_remote_admin_access('STRMADMIN');
END;
/