How to Configure Oracle Streams in Oracle environment

In this post i am going to discuss steps to configure Oracle Streams in Oracle environment. We wouldn't be discussing about streams in details but steps only. If you need more details please refer the oracle documentation.

1. Streams Configuration Setup – Source System

• Add following parameters to source and destination instances
  log_archive_format='ORCLPRODA_%t_%s_%r.arc'
  log_archive_dest_1='LOCATION=/ocfsd106/ora/arch/ORCLPRODA'
  STREAMS_POOL_SIZE =500M
  _job_queue_interval=1
• Create tablespace to hold stream data Source
  CREATE TABLESPACE STREAMS_DATA DATAFILE '/ocfsd105/ora/ORCLPRODdata/ocfsd106/streamd01.dbf' SIZE 1000 M REUSE AUTOEXTEND OFF EXTENT MANAGEMENT LOCAL SEGMENT
  SPACE MANAGEMENT AUTO;  
• Create/Setup Stream User to hold stream data
  
  CREATE USER strmadmin
  IDENTIFIED BY strmadmin
  DEFAULT TABLESPACE STREAMS_DATA
  TEMPORARY TABLESPACE temp
  QUOTA UNLIMITED ON STREAMS_DATA;
  
  GRANT CONNECT, RESOURCE, DBA TO strmadmin;
  GRANT SELECT ANY DICTIONARY TO STRMADMIN;
  GRANT AQ_ADMINISTRATOR_ROLE to STRMADMIN;
  GRANT SELECT ANY DICTIONARY TO STRMADMIN;
  GRANT EXECUTE ON DBMS_AQ TO STRMADMIN;
  GRANT EXECUTE ON DBMS_AQADM TO STRMADMIN;
  GRANT EXECUTE ON DBMS_FLASHBACK TO STRMADMIN;
  GRANT EXECUTE ON DBMS_STREAMS_ADM TO STRMADMIN;
  GRANT EXECUTE ON DBMS_CAPTURE_ADM TO STRMADMIN;
  GRANT EXECUTE ON DBMS_APPLY_ADM TO STRMADMIN;
  GRANT EXECUTE ON DBMS_RULE_ADM TO STRMADMIN;
  GRANT EXECUTE ON DBMS_PROPAGATION_ADM TO STRMADMIN;
  GRANT SELECT_CATALOG_ROLE TO strmadmin;
  
  BEGIN
  DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(
  privilege => 'ENQUEUE_ANY',
  grantee => 'STRMADMIN',
  admin_option => FALSE);
  END;
  /
  
  BEGIN
  DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(
  privilege => 'DEQUEUE_ANY',
  grantee => 'STRMADMIN',
  admin_option => FALSE);
  END;
  /
  BEGIN
  DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(
  privilege => 'MANAGE_ANY',
  grantee => 'STRMADMIN',
  admin_option => TRUE);
  END;
  /
  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;
  /
  BEGIN
  DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
  privilege => DBMS_RULE_ADM.EXECUTE_ANY_EVALUATION_CONTEXT,
  grantee => 'STRMADMIN',
  grant_option => TRUE);
  END;
  /
  BEGIN
  DBMS_RULE_ADM.GRANT_OBJECT_PRIVILEGE(
  privilege => DBMS_RULE_ADM.EXECUTE_ON_EVALUATION_CONTEXT,
  object_name => 'SYS.STREAMS$_EVALUATION_CONTEXT',
  grantee => 'STRMADMIN',
  grant_option => FALSE );
  END;
  
  /
• Create database Link from Source to Target
  update global_name set global_name='ORCLPROD.WORLD';
  
  CREATE DATABASE LINK "ORCLREP.WORLD" CONNECT TO STRMADMIN IDENTIFIED BY STRMADMIN USING 'ORCLREP';
• Create Streams queue for each schema
  
  sqlplus strmadmin/strmadmin
  
  BEGIN
  DBMS_STREAMS_ADM.SET_UP_QUEUE(
  queue_name => 'STRMADMIN.STREAMS_SQUEUE',
  queue_table =>' STRMADMIN.STREAMS_SQUEUE_TABLE',
  queue_user => 'STRMADMIN');
  END;
  
  It's a good idea to create sepereta queue for each schema you are planning to replicate.
  
  
• Create capture process and Rules for the tables at the source database
  BEGIN
  DBMS_STREAMS_ADM.ADD_TABLE_RULES (
  table_name => 'APPLSYS.FND_TERRITORIES_TL',
  streams_type => 'CAPTURE',
  streams_name => 'STRMADMIN_CAPTURE_APPLSYS',    queue_name => 'STRMADMIN.STREAMS_APPSQUEUE',  include_dml => true,
  include_ddl => true,
  source_database => 'ORCLPROD');
  END;
  /
  
  Add propagation rules for the table at the source database. This step will also create a propagation job to the destination database.
  
  Example:
  
  BEGIN
  DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
  table_name => 'APPLSYS.FND_TERRITORIES_TL',
  streams_name => 'STRMADMIN_PROPAGATE',
  source_queue_name => 'STRMADMIN.STREAMS_SQUEUE',
  destination_queue_name => 'STRMADMIN.STREAMS_TQUEUE@ORCLREP',
  include_dml => true,
  include_ddl => true,
  source_database => 'ORCLPROD');
  END;
  

2. Streams Configuration Setup – Target System

• Create tablespace to hold stream data
  
  CREATE TABLESPACE STREAMS_DATA DATAFILE '/ocfsr101/ora/ORCLREPdata/streamd01.dbf' SIZE 1000 M REUSE AUTOEXTEND OFF EXTENT MANAGEMENT LOCAL SEGMENT
  SPACE MANAGEMENT AUTO;
  
• Create/Setup Stream User to hold stream data
  
  CREATE USER strmadmin
  IDENTIFIED BY strmadmin
  DEFAULT TABLESPACE STREAMS_DATA
  TEMPORARY TABLESPACE temp
  QUOTA UNLIMITED ON STREAMS_DATA;
  
  GRANT CONNECT, RESOURCE, DBA TO strmadmin;
  GRANT SELECT ANY DICTIONARY TO STRMADMIN;
  GRANT AQ_ADMINISTRATOR_ROLE to STRMADMIN;
  GRANT SELECT ANY DICTIONARY TO STRMADMIN;
  GRANT EXECUTE ON DBMS_AQ TO STRMADMIN;
  GRANT EXECUTE ON DBMS_AQADM TO STRMADMIN;
  GRANT EXECUTE ON DBMS_FLASHBACK TO STRMADMIN;
  GRANT EXECUTE ON DBMS_STREAMS_ADM TO STRMADMIN;
  GRANT EXECUTE ON DBMS_CAPTURE_ADM TO STRMADMIN;
  GRANT EXECUTE ON DBMS_APPLY_ADM TO STRMADMIN;
  GRANT EXECUTE ON DBMS_RULE_ADM TO STRMADMIN;
  GRANT EXECUTE ON DBMS_PROPAGATION_ADM TO STRMADMIN;
  GRANT SELECT_CATALOG_ROLE TO strmadmin;
  
  BEGIN
  DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(
  privilege => 'ENQUEUE_ANY',
  grantee => 'STRMADMIN',
  admin_option => FALSE);
  END;
  /
  
  BEGIN
  DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(
  privilege => 'DEQUEUE_ANY',
  grantee => 'STRMADMIN',
  admin_option => FALSE);
  END;
  /
  BEGIN
  DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(
  privilege => 'MANAGE_ANY',
  grantee => 'STRMADMIN',
  admin_option => TRUE);
  END;
  /
  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;
  /
  BEGIN
  DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
  privilege => DBMS_RULE_ADM.EXECUTE_ANY_EVALUATION_CONTEXT,
  grantee => 'STRMADMIN',
  grant_option => TRUE);
  END;
  /
  BEGIN
  DBMS_RULE_ADM.GRANT_OBJECT_PRIVILEGE(
  privilege => DBMS_RULE_ADM.EXECUTE_ON_EVALUATION_CONTEXT,
  object_name => 'SYS.STREAMS$_EVALUATION_CONTEXT',
  grantee => 'STRMADMIN',
  grant_option => FALSE );
  END;
  
  /
• Create Tablespaces and Users at Destination database
  
  CREATE TABLESPACE ONTD DATAFILE '/ocfsr101/ora/ORCLREPdata/ontd01.dbf' SIZE 8000 M AUTOEXTEND OFF EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
  
  CREATE USER ONT
  IDENTIFIED BY ONT
  DEFAULT TABLESPACE ONTD
  TEMPORARY TABLESPACE temp
  QUOTA UNLIMITED ON ONTD;
  
  GRANT CONNECT, RESOURCE TO ONT;
  
  alter user ONT QUOTA UNLIMITED ON ONTX;
• Export tables from source database to target database
  
  Source
  
  exp ont/ont tables=(OE_ORDER_LINES_ALL) file=('ONT_ORCLPROD01.dmp') log=ONT_ORCLPROD.log FILESIZE=4G DIRECT=YES
  
  Target
  
  imp ont/ont file=('ONT_ORCLPROD01.dmp') log=ONT_ORCLREP.log FULL=Y
• Create database Link from Target to Source
  update global_name set global_name='ORCLREP.WORLD';
  
  CREATE DATABASE LINK "ORCLPROD.WORLD"
  CONNECT TO STRMADMIN
  IDENTIFIED BY STRMADMIN
  USING 'ORCLPROD';
• Create Streams queue for each schema
  
  sqlplus strmadmin/strmadmin
  
  BEGIN
  DBMS_STREAMS_ADM.SET_UP_QUEUE(
  queue_name => 'STRMADMIN.STREAMS_TQUEUE',
  queue_table =>' STRMADMIN.STREAMS_TQUEUE_TABLE',
  queue_user => 'STRMADMIN');
  END;
• Create Apply process and Rules for the tables at the destination database
  
  Example:
  
  BEGIN
  DBMS_STREAMS_ADM.ADD_TABLE_RULES(
  table_name => 'APPLSYS.FND_TERRITORIES_TL',
  streams_type => 'APPLY',
  streams_name => 'STRMADMIN_APPLY',
  queue_name => 'STRMADMIN.STREAMS_TQUEUE',
  include_dml => true,
  include_ddl => true,
  source_database => 'ORCLPROD');
  END;
  /
  
  
• Grant Full permission on all objects to stream admin user
  
  Example:
  
  grant all on APPLSYS.FND_TERRITORIES_TL to STRMADMIN;
• Activate stream admin user to apply all DML and DDL changes
  
  The user specified in the APPLY_USER parameter must have the necessary privileges to perform DML and DDL changes on the apply objects.
  
  BEGIN
  DBMS_APPLY_ADM.ALTER_APPLY(
  apply_name => 'STRMADMIN_APPLY',
  apply_user => 'STRMADMIN');
  END;

3. Instantiate Table level Replication

• Find the SCN number in the source
  
  set serveroutput on
  DECLARE
  iscn NUMBER;
  BEGIN
  iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
  DBMS_OUTPUT.PUT_LINE (iscn);
  END;
  /
  
  DECLARE
  source_scn NUMBER;
  BEGIN
  source_scn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
  DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(
  source_object_name => 'APPLSYS.FND_TERRITORIES_TL',
  source_database_name => 'ORCLPROD',
  instantiation_scn => source_scn);
  END;
  /
  
  Instantiate the objects in the target. The SET_TABLE_INSTANTIATION_SCN procedure controls which LCRs for a table are to be applied by the apply process. If the commit SCN of an LCR from the source database is less than or equal to this instantiation SCN, then the apply process discards the LCR else, the apply process applies the LCR.
  
  BEGIN
  DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(
  source_object_name => 'APPLSYS.FND_TERRITORIES_TL',
  source_database_name => 'ORCLPROD',
  instantiation_scn => 8221381383942);
  END;
  DECLARE source_scn NUMBER; BEGIN
  source_scn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER(); DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@ORCLREP( source_object_name => 'APPLSYS.FND_TERRITORIES_TL', source_database_name => 'ORCLPROD, instantiation_scn => source_scn); END;/
  
  
• Set performance tuning parameters in source and target database
  begin
  dbms_capture_adm.set_parameter('STRMADMIN_CAPTURE','_checkpoint_frequency','400');
  end;
  
  begin
  dbms_capture_adm.set_parameter(capture_name=>'STRMADMIN_CAPTURE',checkpoint_retention_time=>2); end; Begin dbms_capture_adm.set_parameter('CAPTURE_NAME','_CHECKPOINT_FORCE','Y'); END;
  
  begin
  dbms_aqadm.alter_propagation_schedule('STRMADMIN.STREAMS_SQUEUE','ORCLREP',latency=>1);
  end;
• Start the capture process in the source
  
  BEGIN
  DBMS_CAPTURE_ADM.START_CAPTURE(capture_name => 'STRMADMIN_CAPTURE');
  END;
  /
• Start the apply process in the target
  
  BEGIN
  DBMS_APPLY_ADM.START_APPLY(apply_name => 'STRMADMIN_APPLY');
  END;
  /
  
• Set parameters for Apply Process at target site
  
  begin
  dbms_apply_adm.set_parameter('STRMADMIN_APPLY','disable_on_error','n');
  end;
  
  Begin
  dbms_apply_adm.set_parameter('STRMADMIN_APPLY','_TXN_BUFFER_SIZE',2);
  end;
  Begin
  dbms_apply_adm.set_parameter('STRMADMIN_APPLY','_DYNAMIC_STMTS','Y');
  end;
• Monitor the apply process in the target
  
  SELECT (HWM_TIME-HWM_MESSAGE_CREATE_TIME)*86400 "Latency in Seconds",
  TO_CHAR(HWM_MESSAGE_CREATE_TIME,'HH24:MI:SS MM/DD/YY')
  "Event Creation",
  TO_CHAR(HWM_TIME,'HH24:MI:SS MM/DD/YY') "Apply Time",
  HWM_MESSAGE_NUMBER "Applied Message Number"
  FROM V$STREAMS_APPLY_COORDINATOR
  WHERE APPLY_NAME = 'STRMADMIN_APPLY';
  
  SELECT
  (APPLY_TIME-APPLIED_MESSAGE_CREATE_TIME)*86400 "Latency in Seconds",
  TO_CHAR(APPLIED_MESSAGE_CREATE_TIME,'HH24:MI:SS MM/DD/YY')
  "Event Creation",
  TO_CHAR(APPLY_TIME,'HH24:MI:SS MM/DD/YY') "Apply Time",
  APPLIED_MESSAGE_NUMBER "Applied Message Number"
  FROM DBA_APPLY_PROGRESS
  WHERE APPLY_NAME like 'STRMADMIN_APPLY';
  
  SELECT DECODE(ap.APPLY_CAPTURED,
  'YES','Captured LCRS',
  'NO','User-enqueued messages','UNKNOWN') APPLY_CAPTURED,
  SUBSTR(s.PROGRAM,INSTR(S.PROGRAM,'(')+1,4) PROCESS_NAME,
  r.STATE,
  r.TOTAL_ASSIGNED,
  r.TOTAL_MESSAGES_APPLIED
  FROM V$STREAMS_APPLY_SERVER R, V$SESSION S, DBA_APPLY AP
  WHERE r.APPLY_NAME like 'STRMADMIN_APPLY' AND
  r.SID = s.SID AND
  r.SERIAL# = s.SERIAL# AND
  r.APPLY_NAME = ap.APPLY_NAME
  ORDER BY r.SERVER_ID;
  
  SELECT q.owner, q.name, t.queue_table, t.owner_instance
  FROM DBA_QUEUES q, DBA_QUEUE_TABLES t
  WHERE t.object_type = 'SYS.ANYDATA' AND
  q.queue_table = t.queue_table AND
  q.owner = t.owner;
  
• Flow Control Query COLUMN queue_schema FORMAT A12 COLUMN queue_name FORMAT A20 SELECT queue_schema, queue_name, unbrowsed_msgs, overspilled_msgs, memory_usage, publisher_stateFROM V$BUFFERED_PUBLISHERS;
  
  SELECT
  subscriber_name, cnum_msgs, total_dequeued_msg, total_spilled_msg
  FROM V$BUFFERED_SUBSCRIBERS;
• Dynamic Streams views V$STREAMS_CAPTURE GV$STREAMS_CAPTURE V$STREAMS_APPLY_COORDINATOR GV$STREAMS_APPLY_COORDINATOR V$STREAMS_APPLY_READER GV$STREAMS_APPLY_READER V$STREAMS_APPLY_SERVER GV$STREAMS_APPLY_SERVER V$STREAMS_POOL_ADVICE GV$STREAMS_POOL_ADVICE V$STREAMS_TRANSACTION GV$STREAMS_TRANSACTION V$BUFFERED_PUBLISHERS GV$BUFFERED_PUBLISHERS V$BUFFERED_QUEUES GV$BUFFERED_QUEUES V$BUFFERED_SUBSCRIBERS GV$BUFFERED_SUBSCRIBERS V$PROPAGATION_RECEIVER GV$PROPAGATION_RECEIVER V$PROPAGATION_SENDER GV$PROPAGATION_SENDER V$RULE GV$RULE V$RULE_SET GV$RULE_SET V$RULE_SET_AGGREGATE_STATS GV$RULE_SET_AGGREGATE_STATS
• Static Streams Views Capture Views DBA_CAPTURE DBA_CAPTURE_EXTRA_ATTRIBUTES DBA_CAPTURE_PARAMETERS DBA_CAPTURE_PREPARED_DATABASE DBA_CAPTURE_PREPARED_SCHEMAS DBA_CAPTURE_PREPARED_TABLES
• Apply Views DBA_APPLY DBA_APPLY_CONFLICT_COLUMNS DBA_APPLY_DML_HANDLERS DBA_APPLY_ENQUEUE DBA_APPLY_ERROR DBA_APPLY_EXECUTE DBA_APPLY_INSTANTIATED_GLOBAL DBA_APPLY_INSTANTIATED_OBJECTS DBA_APPLY_INSTANTIATED_SCHEMAS DBA_APPLY_KEY_COLUMNS DBA_APPLY_OBJECT_DEPENDENCIES DBA_APPLY_PARAMETERS DBA_APPLY_PROGRESS DBA_APPLY_SPILL_TXN DBA_APPLY_TABLE_COLUMNS DBA_APPLY_VALUE_DEPENDENCIES
• Propagation & Queue Views Streams Views DBA_PROPAGATION DBA_QUEUE_SCHEDULES DBA_QUEUE_SUBSCRIBERS DBA_QUEUE_TABLES DBA_QUEUES
• Streams views DBA_REGISTERED_ARCHIVED_LOG DBA_RECOVERABLE_SCRIPT DBA_RECOVERABLE_SCRIPT_BLOCKS DBA_RECOVERABLE_SCRIPT_ERRORS DBA_RECOVERABLE_SCRIPT_PARAMS DBA_STREAMS_ADD_COLUMN DBA_STREAMS_ADMINISTRATOR DBA_STREAMS_DELETE_COLUMN DBA_STREAMS_GLOBAL_RULES DBA_STREAMS_MESSAGE_CONSUMERS DBA_STREAMS_MESSAGE_RULES DBA_STREAMS_NEWLY_SUPPORTED DBA_STREAMS_RENAME_COLUMN DBA_STREAMS_RENAME_SCHEMA DBA_STREAMS_RENAME_TABLE DBA_STREAMS_RULES DBA_STREAMS_SCHEMA_RULES DBA_STREAMS_TABLE_RULES DBA_STREAMS_TRANSFORM_FUNCTION DBA_STREAMS_TRANSFORMATIONS DBA_STREAMS_UNSUPPORTED DBA_RULE_SET_RULES DBA_RULE_SETS DBA_RULES DBA_HIST_BUFFERED_QUEUES DBA_HIST_BUFFERED_SUBSCRIBERS DBA_HIST_RULE_SET DBA_HIST_STREAMS_APPLY_SUM DBA_HIST_STREAMS_CAPTURE DBA_HIST_STREAMS_POOL_ADVICE
  
• Apply process parameter _HASH_TABLE_SIZE: set this relatively high (such as 10000000), to minimize “wait dependency” bottlenecks.
• Apply process commit frequency
• Propagate
  • Set the propagate parameter LATENCY to 0
  • Set the propagate parameter QUEUE_TO_QUEUE to TRUE in case of RAC(10.2 only).
• Capture Process
  
  streams$_capture_process: lists all defined capture processes
  dba_capture: basic status, error info
  v$streams_capture: detailed status info
  dba_capture_parameters: configuration information
  
• Propagate Process
  
  streams$_propagation_process: Lists all defined propagate processes
  dba_propagation: Basic status, error info
  v$propagation_sender: Detailed status
  v$propagation_receiver: Detailed status
  
• Apply Process
  
  streams$_apply_process: Lists all defined apply processes
  dba_apply: Basic status, error info
  v$streams_apply_reader: Status of the apply reader
  v$streams_apply_server: Status of apply server(s)
  v$streams_apply_coordinator: Overall status, latency info
  dba_apply_parameters: Configuration information
  
• “Miscellaneous” Tables and Views
  
  v$buffered_queues: View that displays the current and cumulative number of messages enqueued and spilled, for each buffered queue.
  sys.streams$_apply_spill_msgs_part: Table that the apply process uses, to “spill” messages from large transactions to disk.
  system.logmnr_restart_ckpt$: Table that holds capture process “checkpoint” information.
  
  

In the Next post i will discuss Downstream Oracle Streams Configuration.