Easy Streams Cookbook Example

We have Call Centres all over the country. The Call Centre’s IVR (interactive voice response, is a phone technology that allows a computer to detect voice and touch tones using a normal phone call) is used for routing calls to the correct call centre. The subscriber data is stored in 1 location and I was asked to replicate that data to 2 databases in other parts of the country. The main reasons are to have the data closer to the IVR system and also to relieve the main production system from all these queries.

Decisions: Use one-directional replication. Capture the changes at the source. Do the instantiation (first copy of data) with DataPump.

Preperation Steps

Enable Supplemental Logging (at source)
Use columns in PK constraints for Supplemental log group

ALTER TABLE "SCHEMA_ABC"."TABLE_ABC" ADD SUPPLEMENTAL LOG GROUP ivr_rep (XXMSISDN) ALWAYS;

This step might wait on “enq: TX – contention” on a busy production system, so it better to do it with a Change Control after hours

To confirm Supplemental Log Groups:

COLUMN LOG_GROUP_NAME HEADING 'Log Group' FORMAT A20
COLUMN TABLE_NAME HEADING 'Table' FORMAT A15
COLUMN ALWAYS HEADING 'Conditional or|Unconditional' FORMAT A14
COLUMN LOG_GROUP_TYPE HEADING 'Type of Log Group' FORMAT A20
SELECT LOG_GROUP_NAME, TABLE_NAME, DECODE(ALWAYS,'ALWAYS', 'Unconditional','CONDITIONAL', 'Conditional') ALWAYS, LOG_GROUP_TYPE
FROM DBA_LOG_GROUPS;

Create default tablespace for the Streams Administrator Account to store the queue table. Do these steps on the source and destinations.

CREATE TABLESPACE streams_tbs DATAFILE '/data/oradata/lfivrdb/streams_tbs.dbf' SIZE 7168M;

CREATE USER strmadmin IDENTIFIED BY STRMXXX 
  PROFILE VODA_COMPLIANT
  DEFAULT TABLESPACE streams_tbs QUOTA UNLIMITED ON streams_tbs;
GRANT CONNECT, RESOURCE, DBA to STRMADMIN;
GRANT SELECT ANY DICTIONARY TO STRMADMIN;
GRANT SELECT_CATALOG_ROLE TO STRMADMIN;
EXEC DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE('STRMADMIN');

Set specific initialization parameters at the databases participating in the Streams setup

ALTER SYSTEM SET STREAMS_POOL_SIZE=200M SCOPE=BOTH;
ALTER SYSTEM SET AQ_TM_PROCESSES = 1 scope=both;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=10 scope=both;
ALTER SYSTEM SET “_job_queue_interval”=1 scope=spfile;

(This is not a dynamic parameter and the db will need to be bounced)

Create 2 database links on the source to the 2 destinations.

CONNECT STRMADMIN/STRMXXX
CREATE DATABASE LINK LFIVRDB.VODACOM.CO.ZA CONNECT TO STRMADMIN IDENTIFIED BY STRMXXX USING ‘LFIVRDB.VODACOM.CO.ZA’;
CREATE DATABASE LINK BEIVRDB.VODACOM.CO.ZA CONNECT TO STRMADMIN IDENTIFIED BY STRMXXX USING ‘BEIVRDB.VODACOM.CO.ZA’;

STEP 1
Create Streams Queues
One Capture on the source db and one Apply on each destination:

connect STRMADMIN/STRMXXX
BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => 'STREAMS_QUEUE_TABLE',
storage_clause => 'TABLESPACE STREAMS_TBS',
queue_name => ' STREAMS_CAPTURE_Q', 
queue_user => 'STRMADMIN');
END;
/

And on both dest sites:

connect STRMADMIN/STRMXXX
BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => 'STREAMS_QUEUE_TABLE',
storage_clause => 'TABLESPACE STREAMS_TBS',
queue_name => ' STREAMS_APPLY_Q', 
queue_user => 'STRMADMIN');
END;
/

STEP2
Create Capture Process on Source.
This step might wait on “enq: TX – contention” on a busy production system, so it better to do it with a Change Control after hours

connect STRMADMIN/STRMXXX
BEGIN
  DBMS_STREAMS_ADM.ADD_TABLE_RULES(
    table_name         => '"SCHEMA_ABC"."TABLE_ABC"',
    streams_type       => 'CAPTURE',
    streams_name       => '"STREAMS_CAPTURE"',
    queue_name         => '"STRMADMIN"."STREAMS_CAPTURE_Q"',
    include_dml        => true,
    include_ddl        => true,
    source_database    => 'DESTPRD.VODACOM.CO.ZA');
    END;
/

STEP3
Create 2 propagation process on the Source (one per destination)

connect STRMADMIN/STRMXXX
BEGIN
  DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
    table_name          => '"SCHEMA_ABC"."TABLE_ABC"',
    streams_name        => '"STREAMS_PROP_LF"',
    source_queue_name   => '"STRMADMIN"."STREAMS_CAPTURE_Q"',
    destination_queue_name  => '"STRMADMIN"."STREAMS_APPLY_Q"@LFIVRDB.VODACOM.CO.ZA',
    include_dml         => true,
    include_ddl         => true,
    source_database    => 'DESTPRD.VODACOM.CO.ZA',
    inclusion_rule      => true);
END;
/

And to the 2nd dest site:

connect STRMADMIN/STRMXXX
BEGIN
  DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
    table_name          => '"SCHEMA_ABC"."TABLE_ABC"',
    streams_name        => '"STREAMS_PROP_BE"',
    source_queue_name   => '"STRMADMIN"."STREAMS_CAPTURE_Q"',
    destination_queue_name  => '"STRMADMIN"."STREAMS_APPLY_Q"@BEIVRDB.VODACOM.CO.ZA',
    include_dml         => true,
    include_ddl         => true,
    source_database    => 'DESTPRD.VODACOM.CO.ZA',
    inclusion_rule      => true);
END;
/

STEP 4
Create 2 Apply processes. One on each destination.

connect STRMADMIN/STRMXXX
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name          => '"SCHEMA_ABC"."TABLE_ABC"',
streams_type => 'APPLY', 
streams_name => 'STREAMS_APPLY', 
queue_name => 'STRMADMIN.STREAMS_APPLY_Q',
include_dml => true, 
include_ddl => true, 
source_database    => 'DESTPRD.VODACOM.CO.ZA');
END;
/

And 2nd site

connect STRMADMIN/STRMXXX
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name          => '"SCHEMA_ABC"."TABLE_ABC"',
streams_type => 'APPLY', 
streams_name => 'STREAMS_APPLY', 
queue_name => 'STRMADMIN.STREAMS_APPLY_Q',
include_dml => true, 
include_ddl => true, 
source_database    => 'DESTPRD.VODACOM.CO.ZA');
END;
/

Instantiation. This is the initial copy of the table.

Set & Get SCN:

EXEC DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION(table_name =>  'SCHEMA_ABC.TABLE_ABC'); 

This step might wait on “enq: TX – contention” on a busy production system, so it better to do it with a Change Control after hours

To confirm the SCN number that you will need in a future step:

col scn for 9999999999999999
select * from dba_capture_prepared_tables 

Export table at source

expdp strmadmin/STRMXXX TABLES= SCHEMA_ABC.TABLE_ABC DUMPFILE=a%u.dmp parallel=8

compress and ftp dump files to the 2 destination servers

Import table at each destination
First create the schema owner of the table

create user SCHEMA_ABC identified by STRMXXX default tablespace streams_tbs;
grant connect, resource to SCHEMA_ABC
/

impdp strmadmin/STRMXXX TABLES=SCHEMA_ABC.TABLE_ABC DUMPFILE=a%u.dmp remap_tablespace=TBS_ABC:streams_tbs 

Manually instantiating the objects at each destination

connect STRMADMIN/STRMXXX
BEGIN
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(
source_object_name => 'SCHEMA_ABC.TABLE_ABC',
source_database_name => 'DESTPRD.VODACOM.CO.ZA',
instantiation_scn => &iscn);
END;
/
Enter value for iscn: 

STEP5
Finally start the Process:
On source

CONNECT strmadmin/STRMXXX
exec dbms_capture_adm.start_capture(capture_name => 'STREAMS_CAPTURE');

At 1st destination:

CONNECT strmadmin/STRMXXX
exec dbms_apply_adm.start_apply(apply_name => 'STREAMS_APPLY');

At 2nd destination:

CONNECT strmadmin/STRMXXX
exec dbms_apply_adm.start_apply(apply_name => 'STREAMS_APPLY');

Post Installation steps
On Source force Stream checkpoint (see Metalink Note:342342.1)

EXEC DBMS_CAPTURE_ADM.SET_PARAMETER('STREAMS_CAPTURE','_CHECKPOINT_FORCE','Y');

On each destination

EXEC DBMS_APPLY_ADM.SET_PARAMETER(apply_name => 'STREAMS_APPLY', parameter => 'WRITE_ALERT_LOG', value => 'Y' );
EXEC DBMS_APPLY_ADM.SET_PARAMETER(apply_name => 'STREAMS_APPLY',parameter => 'DISABLE_ON_ERROR',value => 'N' );