Setting up Bidirectional Streams SCHEMA Replication

Details below are for configuring a schema level replication using API DBMS_STREAMS_ADM.MAINTAIN_SCHEMAS.

1. Verify Source & Target System for Streams Configuration

CONNECT system/manager@ {STREAMS/STRMREPL}
SET SQLPROMPT "STREAMS> "
set pages 0;
set linesize 2000;
SELECT * FROM global_name; 
SHOW PARAMETER service_names
SHOW PARAMETER global_names

-- Verify database parameters
col name HEADING 'Parameter|Name' format a30
col value HEADING 'Parameter|Value' format a15
col description HEADING 'Description' format a60 word
select name,value,description from v$parameter where name in
 ('aq_tm_processes', 'archive_lag_target', 'job_queue_processes','_job_queue_interval',
 'shared_pool_size', 'sga_max_size', 'sga_target','streams_pool_size',
 'global_names', 'compatible','log_parallelism', 'logmnr_max_persistent_sessions',
 'parallel_max_servers', 'processes', 'sessions'
 );

-- Verify ARCHIVELOG mode and archive log settings
ARCHIVE LOG LIST
SHOW PARAMETER log_archive_format
SHOW PARAMETER log_archive_dest_1
SHOW PARAMETER log_archive_dest_state_1

-- Verify Streams administrator
SELECT username, account_status, default_tablespace
 FROM DBA_USERS
 WHERE username = 'STRMADMIN';
SELECT *
 FROM DBA_STREAMS_ADMINISTRATOR;

-- Verify database link as Streams administrator
CONNECT strmadmin/strmadmin@STREAMS
SELECT db_link
 FROM DBA_DB_LINKS;

-- Verify directory objects
SELECT directory_name, directory_path
 FROM   dba_directories;

2. Setting Up SCOTT Schema Replication

STREAMS>>conn scott/tiger
Connected.
STREAMS>>select * from tab;
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
DEPT                           TABLE
EMP                            TABLE
BONUS                          TABLE
SALGRADE                       TABLE
SALES                          TABLE
COSTS                          TABLE
TIMES                          TABLE
CHANNELS                       TABLE
PROMOTIONS                     TABLE
COUNTRIES                      TABLE
PRODUCTS                       TABLE
CATEGORIES_TAB                 TABLE
PRODUCT_REF_LIST_NESTEDTAB     TABLE
SUBCATEGORY_REF_LIST_NESTEDTAB TABLE
CUSTOMERS                      TABLE
EMP_REPL                       TABLE
16 rows selected.

STRMREPL>>conn scott/tiger
Connected.
STRMREPL>>select * from tab;
no rows selected

There are no tables existing at destination schema : SCOTT@STRMREPL

set serveroutput on;
set term on;
set lines 200;
BEGIN
 DBMS_STREAMS_ADM.MAINTAIN_SCHEMAS(
 schema_names => 'SCOTT',
 source_directory_object => 'STREAMS_DIR',
 destination_directory_object => 'STRMREPL_DIR',
 source_database => 'STREAMS',
 destination_database => 'STRMREPL',
 perform_actions => TRUE,
 dump_file_name => 'scott_exp.dmp',
 capture_name => 'SCOTT_CAP',
 capture_queue_table => 'SCOTT_SCHEMA_CAPTURE_QT',
 capture_queue_name => 'SCOTT_SCHEMA_CAPTURE_Q',
 propagation_name => 'SCOTT_SCHEMA_PROPAGATION',
 apply_name => 'SCOTT_SCHEMA_APPLY',
 apply_queue_table => 'strmadmin.SCOTT_SCHEMA_APPLY_QT',
 apply_queue_name => 'strmadmin.SCOTT_SCHEMA_APPLY_Q',
 bi_directional=> TRUE,
 include_ddl => FALSE,
 instantiation=>DBMS_STREAMS_ADM.INSTANTIATION_SCHEMA);
 END;
 /
job finished
PL/SQL procedure successfully completed.

This can take a long time depending on schema to be replciated.
To monitor progress have alook at filesizes of expdp operation "scott_exp.dmp" at STREAMS_DIR & STRMREPL_DIR

select from dba_recoverable_script_errors to see there were no errors
STREAMS>> select error_message,block_num from dba_recoverable_script_errors

STRMREPL>>conn scott/tiger
Connected.
STRMREPL>>select * from tab;
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
DEPT                           TABLE
EMP                            TABLE
BONUS                          TABLE
SALGRADE                       TABLE
SALES                          TABLE
COSTS                          TABLE
TIMES                          TABLE
CHANNELS                       TABLE
PROMOTIONS                     TABLE
COUNTRIES                      TABLE
PRODUCTS                       TABLE
CATEGORIES_TAB                 TABLE
PRODUCT_REF_LIST_NESTEDTAB     TABLE
SUBCATEGORY_REF_LIST_NESTEDTAB TABLE
CUSTOMERS                      TABLE
EMP_REPL                       TABLE
16 rows selected.

3) Let’s verify DML changes executed in STREAMS are propagated to STRMREPL database

STREAMS>>delete from scott.promotions;
2 rows deleted.
STREAMS>>commit;
Commit complete.
STREAMS>>select count(*) from scott.promotions;
 COUNT(*)
----------
 0

At replicated Site 
STRMREPL>>select count(*) from scott.promotions;
 COUNT(*)
----------
 0

Note the DDL changes are not replicated as I set "include_ddl => FALSE" at "MAINTAIN_SCHEMAS"

STREAMS>>drop table scott.emp;
Table dropped.
STREAMS>>desc scott.emp;
ERROR:
ORA-04043: object scott.emp does not exist

STRMREPL>>desc scott.emp;
 Name                                                                                                    Null?    Type
 ----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
 EMPNO                                                                                                   NOT NULL NUMBER(4)
 ENAME                                                                                                    VARCHAR2(10)
 JOB                                                                                                      VARCHAR2(9)
 MGR                                                                                                      NUMBER(4)
 HIREDATE                                                                                                 DATE
 SAL                                                                                                      NUMBER(7,2)
 COMM                                                                                                     NUMBER(7,2)
 DEPTNO                                                                                                   NUMBER(2)

Issues Noticed

STREAMS>>select error_message,block_num from dba_recoverable_script_errors;

ERROR_MESSAGE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 BLOCK_NUM
----------
ORA-24035: AQ agent  is not a subscriber for queue STRMADMIN.SCOTT_SCHEMA_CAPTURE_Q

I fixed it by deleting rows from following 2 tables.

SQL> conn / as sysdba
Connected.
STREAMS>> delete from dba_recoverable_script_errors;
STREAMS>> delete from dba_recoverable_script;
STREAMS>> commit;

If needed you can delete all streams config details except db_links and parameters using API below

A) Disable propagation schedule
STREAMS>> exec dbms_aqadm.disable_propagation_schedule(queue_name => 'SCOTT_SCHEMA_CAPTURE_Q',destination => 'STRMREPL',destination_queue => 'strmadmin.SCOTT_SCHEMA_APPLY_Q');

B) Drop propagation
STREAMS>> exec dbms_propagation_adm.drop_propagation(propagation_name => 'SCOTT_SCHEMA_PROPAGATION',drop_unused_rule_sets => true);
PL/SQL procedure successfully completed.

C) Remove Streams
STREAMS>> exec  dbms_streams_adm.remove_streams_configuration;
PL/SQL procedure successfully completed.

D) Drop Queues for capture and apply
– drop capture queue
STREAMS>>exec  DBMS_STREAMS_ADM.REMOVE_QUEUE('SCOTT_SCHEMA_CAPTURE_Q',TRUE);
PL/SQL procedure successfully completed.

– drop apply queue
STREAMS>>Exec DBMS_STREAMS_ADM.REMOVE_QUEUE('strmadmin.SCOTT_SCHEMA_APPLY_Q',TRUE);
PL/SQL procedure successfully completed.

Finally delete any STREAMs errors reported
STREAMS>> delete from dba_recoverable_script_errors;
STREAMS>> delete from dba_recoverable_script;
STREAMS>> commit;