Introduction

Schema to schema replication is an easy exercise, as it is quite automated. The only difficulty will be setting up the conflict handler.

Convention and names

For this exercise we will replicate the user scott schema from DB dev1 (site 1) to (dev2) site2

        Site          Host    ORACLE SID    SCHEMA

       Site 1 linux1.domain.com         DEV1
     scott

       Site 2 linux2.domain.com         DEV2      scott

Action course

The following table summarize the action course to create a schema to schema capture.

Steps	SOURCE DB (site 1)	TARGET DB (site 2)	Specific multimaster
1	Create admin user	Create admin user
2	Add supplemental logging site_1 at db level	Add supplemental logging site_2 at db level
3	Create a dblink for the admin user from site_1 to site_2	Create a dblink for the admin user from site_2 to site_1
4	Create capture queue table	Create capture queue table
5	Create apply queue table	Create apply queue table
6	Create capture process	Create Apply process for the capture on site 1
7	Create Apply process for the capture on site 2	Create capture process
8	Create propagation process from site_1 to site_2	Create propagation process from site_2 to site_1
9	Prepare source instantiation	Instantiate site_2 with SCN of site_1	perform site 1 operation before site 2
10	instantiate site_1 with Scn of site_2	Prepare source instantiation	perform site 2 operation before site 1
11	Start capture process	Start apply process	The order of startup has no importance.
12	Start apply process	Start capture process
13	Add DML_HANDLER for resolution	Add DML_HANDLER for resolution	Add supplemental logging at table level for conflict resolutions on each site

Create admin user

-- Optional case Streams recommended tablespace does not exists. adapt ${ORA_DATA}
-- CREATE TABLESPACE streams LOGGING DATAFILE '$ORA_DATA/streams01.dbf' SIZE 100M REUSE AUTOEXTEND ON
-- EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO'

CREATE USER strmadmin IDENTIFIED BY strmadmin DEFAULT TABLESPACE streams TEMPORARY TABLESPACE temp;
GRANT connect, resource, dba, aq_administrator_role TO strmadmin;
GRANT select any table TO strmadmin;
GRANT CREATE JOB TO strmadmin;
GRANT select_catalog_role, select any dictionary TO strmadmin;
exec dbms_streams_auth.grant_admin_privilege('STRMADMIN');

Create a dblink for the admin user site 1

connect / as sysdba
CREATE DATABASE LINK dev2.domain.com CONNECT TO strmadmin IDENTIFIED BY strmadmin USING 'dev2.domain.com;

Create a dblink for the admin user site 2

connect / as sysdba
CREATE DATABASE LINK dev1.domain.com CONNECT TO strmadmin IDENTIFIED BY strmadmin USING 'dev1.domain.com ;

Add supplemental logging (site 1 & 2)

connect / as sysdba

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (primary key, unique, foreign key) COLUMNS;

Create capture queue table on site_1

exec DBMS_STREAMS_ADM.SET_UP_QUEUE( queue_table => 'cap_scott_1_qt', queue_name => 'cap_scott_1_q' );

Create capture queue table on site_2

exec DBMS_STREAMS_ADM.SET_UP_QUEUE( queue_table => 'cap_scott_2_qt', queue_name => 'cap_scott2_q' );

Create capture site 1

exec DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(    schema_name     => 'scott',
                                           streams_type    => 'capture',
                                           streams_name    => 'cap_scott_1',
                                           queue_name      => 'cap_scott_1_q',
                                           include_dml     => true,
                                           include_ddl     => true,
                                           inclusion_rule => true);

Create capture site 2

exec DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(    schema_name     => 'scott',
                                           streams_type    => 'capture',
                                           streams_name    => 'cap_scott_2',
                                           queue_name      => 'cap_scott_2_q',
                                           include_dml     => true,
                                           include_ddl     => true,
                                           inclusion_rule => true);

Create Apply queue table (site 1 )

exec DBMS_STREAMS_ADM.SET_UP_QUEUE( queue_table => 'app_scott_1_qt', queue_name => 'app_scott_1_q' );

Create Apply queue table (site 2 )

exec DBMS_STREAMS_ADM.SET_UP_QUEUE( queue_table => 'app_scott_2_qt', queue_name => 'app_scott_2_q' );

Create apply process on site 1

exec DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(    schema_name     => 'scott',
                                           streams_type    => 'apply',
                                           streams_name    => 'app_scott_1',
                                           queue_name      => 'app_scott_1_q',
                                           include_dml     => true,
                                           include_ddl     => true,
                                           source_database => 'dev2.domain.com',             /* note the name of the SID   */
                                           inclusion_rule => true);                         /* given with the domain name */

Create apply process on site 2

exec DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(    schema_name     => 'scott',
                                           streams_type    => 'apply',
                                           streams_name    => 'app_scott_2',
                                           queue_name      => 'app_scott_2_q',
                                           include_dml     => true,
                                           include_ddl     => true,
                                           source_database => 'dev1.domain.com',
                                           inclusion_rule => true);

Create propagation process from site 1 to site 2

exec DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES( schema_name            => 'scott',
                                                    streams_name           => 'prop_2_dev2',
                                                    source_queue_name      => 'cap_scott_1_q',
                                                    destination_queue_name => 'app_scott_2_q@linux2.domain.com',
                                                    include_dml            => TRUE,
                                                    include_ddl            => TRUE,
                                                    source_database        => 'dev1.domain.com',
                                                    inclusion_rule         => TRUE,
                                                    queue_to_queue         => TRUE);

Create propagation process from site 2 to site 1

exec DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES( schema_name            => 'scott',
                                                    streams_name           => 'prop_2_dev1',
                                                    source_queue_name      => 'cap_scott_2_q',
                                                    destination_queue_name => 'app_scott_1_q@linux1.domain.com',
                                                    include_dml            => TRUE,
                                                    include_ddl            => TRUE,
                                                    source_database        => 'dev2.domain.com',
                                                    inclusion_rule         => TRUE,
                                                    queue_to_queue         => TRUE);

Prepare source instantiation (site 1 & 2)

execute DBMS_CAPTURE_ADM.PREPARE_SCHEMA_INSTANTIATION( schema_name => 'scott',
supplemental_logging => 'keys');

instantiate site_1 with Scn of site_2

We instantiate the apply process using the current SCN of the capture site.

DECLARE
   iscn number ;
BEGIN
   select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER() into iscn from dual@dev2.domain.com;
   DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN(   source_schema_name     => 'scott',
                                                  source_database_name   => 'dev2.domain.com',
                                                  instantiation_scn      => iscn ,
                                                  recursive              => true);
END;
/

instantiate site_2 with Scn of site_1

We instantiate the apply process using the current SCN of the capture site.

DECLARE
   iscn number ;
BEGIN
   select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER() into iscn from dual@dev1.domain.com;
   DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN(   source_schema_name     => 'scott',
                                                  source_database_name   => 'dev1.domain.com',
                                                  instantiation_scn      => iscn ,
                                                  recursive              => true);
END;
/

Start capture (site 1 )

exec DBMS_CAPTURE_ADM.START_CAPTURE( capture_name => 'cap_scott_1');

Start capture (site 2)

exec DBMS_CAPTURE_ADM.START_CAPTURE( capture_name => 'cap_scott_1');

Start Apply (Site 1)

exec DBMS_APPLY_ADM.START_APPLY( apply_name => 'app_scott_1');

Start Apply (Site 2)

exec DBMS_APPLY_ADM.START_APPLY( apply_name => 'app_scott_2);