Oracle Streams - Adding a New Schema to Streams Configuration

Today I will be discussing about adding a new schema to existing streams replication configuration. In my current environment I have:

1. A source database: PROD.EXAMPLE.COM
    a. Having CHANGE CAPTURE process: CAPTURE_PROD
    b. An ANYDATA queue: STREAMS_QUEUE
    c. A PROPAGATION process: PROD_TO_RPL

2. A destination database: REPL.EXAMPLE.COM
    a. Having an ANYDATA queue: STREAMS_QUEUE
    b. An APPLY process: APPLY_RPL

To add a new schema, we must first stop replication processes running on source and destination:

Stop Streams Processes

1. Stop CHANGE CAPTURE on source

$ sqlplus streamadm/*****@PROD.EXAMPLE.COM
SQL> exec DBMS_CAPTURE_ADM.STOP_CAPTURE('CAPTURE_PROD');

2. Stop PROPAGATION on source

$ sqlplus streamadm/*****@PROD.EXAMPLE.COM
SQL> exec DBMS_PROPAGATION_ADM.STOP_PROPAGATION('PROD_TO_RPL');

3. Stop APPLY on destination

$ sqlplus streamadm/*****@REPL.EXAMPLE.COM
SQL> exec DBMS_APPLY_ADM.STOP_APPLY('APPLY_RPL');

Add a Schema and Setup Replication at Source and Destination Database

1. Create User and Assign Required Privileges/Roles etc

$  sqlplus streamadm/*****@PROD.EXAMPLE.COM
SQL> CREATE USER SCOTTY IDENTIFIED BY "welcome" DEFAULT TABLESPACE USERS; SQL> GRANT CONNECT, RESOURCE TO SCOTTY;

$ sqlplus streamadm/*****@REPL.EXAMPLE.COM
SQL> CREATE USER SCOTTY IDENTIFIED BY "welcome" DEFAULT TABLESPACE USERS; SQL> GRANT CONNECT, RESOURCE TO SCOTTY;

2. Prepare Schema for Instantiation at Source Database

$  sqlplus streamadm/*****@PROD.EXAMPLE.COM
SQL> BEGIN 
         DBMS_CAPTURE_ADM.PREPARE_SCHEMA_INSTANTIATION(
         schema_name          => 'SCOTTY',
         supplemental_logging => 'all');
     END;
     /

3. Create PROPAGATION Rule for new schema

$  sqlplus streamadm/*****@PROD.EXAMPLE.COM
SQL> 
BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES(
   schema_name              => 'SCOTTY',
   streams_name             => 'DBA_TO_RPL',
   source_queue_name        => 'streamadm.streams_queue',
   destination_queue_name   => 'streamadm.streams_queue@REPL.EXAMPLE.COM',
   include_dml              => TRUE,
   include_ddl              => TRUE,
   include_tagged_lcr       => FALSE,
   source_database          => 'PROD.EXAMPLE.COM',
   inclusion_rule           => TRUE,
   and_condition            => NULL,
   queue_to_queue           => TRUE);
END;
/

4. Create CHANGE CAPTURE Rule for new schema

$  sqlplus streamadm/*****@PROD.EXAMPLE.COM
SQL>BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
   schema_name          => 'SCOTTY',
   streams_type         => 'capture',
   streams_name         => 'CAPTURE_DBA',
   queue_name           => 'streamadm.streams_queue',
   include_dml          => TRUE,
   include_ddl          => TRUE,
   include_tagged_lcr   => FALSE,
   source_database      => 'PROD.EXAMPLE.COM',
   inclusion_rule       => TRUE,
   and_condition        => NULL);
END;
/

5. Instantiate Schema at Destination Database

$  sqlplus streamadm/*****@REPL.EXAMPLE.COM
SQL>DECLARE
  iscn  NUMBER;         -- Variable to hold instantiation SCN value
BEGIN
  iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
  DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN@REPL.EXAMPLE.COM(
    source_schema_name    => 'SCOTTY',
    source_database_name  => 'PROD.EXAMPLE.COM',
    instantiation_scn     => iscn,
    recursive             => TRUE);
END;
/

6. Create APPLY Rules for new schema at Destination Database

$  sqlplus streamadm/*****@REPL.EXAMPLE.COM
SQL>BEGIN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
   schema_name          => 'SCOTTY',
   streams_type         => 'apply',
   streams_name         => 'APPLY_RPL',
   queue_name           => 'streamadm.streams_queue',
   include_dml          => TRUE,
   include_ddl          => TRUE,
   include_tagged_lcr   => FALSE,
   source_database      => 'PROD.EXAMPLE.COM',
   inclusion_rule       => TRUE,
   and_condition        => NULL);
END;
/

Start Streams Processes

1. Start CHANGE CAPTURE on source

$ sqlplus streamadm/*****@PROD.EXAMPLE.COM
SQL> exec DBMS_CAPTURE_ADM.START_CAPTURE('CAPTURE_PROD');

2. Start PROPAGATION on source

$ sqlplus streamadm/*****@PROD.EXAMPLE.COM
SQL> exec DBMS_PROPAGATION_ADM.START_PROPAGATION('PROD_TO_RPL');

3. Start APPLY on destination

$ sqlplus streamadm/*****@REPL.EXAMPLE.COM
SQL> exec DBMS_APPLY_ADM.START_APPLY('APPLY_RPL');


All Done! Now Test Replication..