Oracle Streams XStreams?

Oracle Streams is a highly flexible feature of Oracle Database 10g that enables information sharing between databases. A primary capability of Oracle Streams is the ability to replicate information from one database to another.

There are, however, advanced datatypes available for use in the database that are not currently supported. Due to its high degree of flexibility, Oracle Streams provides the capability to replicate changes to some of these unsupported datatypes between databases with the use of Extended Datatype Support (EDS). In particular, the EDS package generates workaround scripts to support the replication of tables with the following datatypes:

• Object column with simple object types
• Object column with nested object types
• Varray
• Spatial type SDO_GEOMETRY
• XMLType 

This article is intended to assist Replication DBAs in setting up and configuring Oracle Streams Replication to replicate changes made to tables having unsupported data types. The article outlines the steps to set up replication for a table with an OBJECT DATA TYPE column from one SCHEMA to another SCHEMA in the same database using Extended Data Support (EDS). 

SOLUTION

Requirements

Applicable from release 10.2.0.1 to 11.1.0.7

Configuring

As a prerequisite, ensure the relevant streams parameters are configured in the source and target instances as detailed in the relevant notes for your release:

Note 418755.1 Primary Note for Streams Recommended Configuration

The Streams Administrator (STRMADMIN) has been created as per Note 786528.1 How to create STRMADMIN user and grant privileges.

Also that the EDS packages have been installed under the Streams Adminsitrator Schema following the instructions in the following note:

Note 556742.1  Extended Datatype Support (EDS) for Streams

Instructions

To run this script either set your environment so the values below are the same as yours or replace them in the script with values appropriate to your environment :

STRM102M.REGION.EXAMPLE.COM = Global Database name of the Database

STRMADMIN = Streams Administrator with password <PASSWORD>

The code assumes that the STRMADMIN already exists in the database with all required privileges and that the capture process STRMADMIN_CAPTURE and the apply process LOCAL_APPLY have been created under the its schema .

Sample Code

Note:
If you are viewing this document online, then you can copy the text from the "BEGINNING OF SCRIPT" line after this note to the next "END OF SCRIPT" line into a text editor and then edit the text to create a script for your environment. Run the script with SQL*Plus on a computer that can connect to all of the databases in the environment.

/*
Create source and target SCHEMAS
*/

conn sys/&sys_pwd_source@strm102m.region.example.com as sysdba

create user usr1 identified by <PASSWORD>;

grant connect, resource to usr1;

create user usr2 identified by usr2;

grant connect, resosurce to usr2; 

/* Create TYPE and TABLE on the USR1 and USR2 SCHEMAS*/

conn usr1/<PASSWORD>@strm102m.region.example.com

create type address_typ as object (
   street varchar2(200),
   city varchar2(200),
   state char(2),
   zip varchar2(20));
/

create table customer (
   custid varchar2(10) primary key,
   name varchar2(50),
   address address_typ );

conn usr2/usr2@strm102m.region.example.com

create type address_typ as object ( 
   street varchar2(200), 
   city varchar2(200), 
   state char(2), 
   zip varchar2(20)); 
/ 

create table customer ( 
   custid varchar2(10) primary key, 
   name varchar2(50), 
   address address_typ );

/************************* BEGINNING OF SCRIPT ******************************
Run SET ECHO ON and specify the spool file for the script. Check the spool file for errors after you run this script.
*/

SET ECHO ON
SPOOL eds.out

/* Generate the EDS configuration scripts for the APPLY process */
 

conn strmadmin/<PASSWORD>@strm102m.region.example.com

create Directory TEST_DIR as '/home/oracle/';

/* Stop the apply process */

begin
   dbms_apply_adm.stop_apply('LOCAL_APPLY');
end;
/

begin
   extended_datatype_support.set_up_destination_schemas(
   schema_names => 'USR2',
   apply_user => 'STRMADMIN',
   apply_name => 'LOCAL_APPLY',
   source_database => 'STRM102M.REGION.EXAMPLE.COM',
   destination_queue_name => 'STRMADMIN.STREAMS_QUEUE',
   perform_actions => FALSE,
   script_directory_object => 'TEST_DIR',
   script_name_prefix => 'eds');
end;
/

/* Run the scripts generated under the TEST_DIR directory to configure the apply rules*/

conn strmadmin/<PASSWORD>@strm102m.region.example.com
 

/* Generate the EDS configuration scripts for the CAPTURE process
    As both capture and apply are going to be running in the same database, the parameter  
    ADD_PROPAGATION_RULES is set to FALSE.  Even though no propgation rules will
    be generated, it is necessary to specify the name of the propagation and a destination queue.
    The propagation and the destination queue don't need exist*/

conn strmadmin/<PASSWORD>@strm1102m.region.example.com

/* Stop the capture process */

begin
   dbms_capture_adm.stop_capture('STRMADMIN_CAPTURE');
end;
/

begin
   extended_datatype_support.set_up_source_schemas(
      schema_names => 'USR1',
      capture_name => 'STRMADMIN_CAPTURE',
      propagation_name => 'NO_PROP',
      source_database => 'STRM102M.REGION.EXAMPLE.COM',
      source_queue_name => 'STRMADMIN.STREAMS_QUEUE',
      destination_queue_name=>'STRMADMIN.DUMMY_QUEUE@nowhere',
      perform_actions => FALSE,
      add_capture_rules => TRUE,
      add_propagation_rules => FALSE,
      script_directory_object => 'TEST_DIR',
      script_name_prefix => 'cap');
end;
/

/* Run the scripts generated under the TEST_DIR directory to configure the capture rules  */

conn strmadmin/<PASSWORD>@strm102m.region.example.com
 

/* Identify the capture rule created by the EDS scripts for the L$CUSTOMER shadow table and add a declarative transformation to rename the SCHEMA for USR1 to USR2 at capture time */
/* As the STRMADMIN has been defined as APPLY user, make sure it has access privileges on the objects created in the USR2 schema.  Also make sure alter session privileges have been granted to him.  */

/*  Set the isntantiation SCN for the replicated table*/
 

set serveroutput on
DECLARE
   iscn NUMBER; -- Variable to hold instantiation SCN value
BEGIN
   iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
   DBMS_OUTPUT.PUT_LINE ('Instantiation SCN is: ' || iscn);
   DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(
        source_object_name => 'USR1.L$CUSTOMER',
        source_database_name => 'STRM102M.REGION.EXAMPLE.COM',
        instantiation_scn => iscn);
END;
/

/* Start the apply and capture processes */ 

begin 
dbms_apply_adm.start_apply('LOCAL_APPLY'); 
end; 
/ 

begin 
dbms_capture_adm.start_capture('STRMADMIN_CAPTURE'); 
end; 
/ 

 /* Set a declarative transformation for the capture rule for table L$CUSTOMER (Shadow
     Table) to rename the schema from USR1 to USR2 */

begin
   DBMS_STREAMS_ADM.RENAME_SCHEMA(
   rule_name => 'STRMADMIN.L$CUSTOMER25',
   from_schema_name => 'USR1',
   to_schema_name => 'USR2',
   step_number => 0,
   operation => 'ADD');
end;
/ /*************************** END OF SCRIPT ******************************/ /* Test Repliation */


conn usr1/<PASSWORD>@strm102m.region.example.com

insert into customer values(1,'Roberts',
                                        address_typ('998 N. Mills Av','Orlando', 'FL','32828'));
commit

This article will help in configuring stream replication between 2 tables within same schema and database using RENAME_TABLE transformation procedure.

 Please ensure the following are setup as prerequisites.

 Ensure the streams parameters are configured in the source and target instances as detailed in
  Note 418755.1 Master Note for Streams Recommended Configuration

  For additional Supplemental logging requirements please check the Streams manual documentation.

  Below script will demonstrate replicating data from table A to table B in TEST schema in database DB1.

SOLUTION

/* STEP 1.- Create the Streams Administrator.

Refer to the Oracle Streams Replication Administrator's Guide for more information about configuring an Oracle Streams administrator.

create user <stream admin user>identified by <Password>;

ALTER USER STRMADMIN DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON USERS;
GRANT CONNECT, RESOURCE, AQ_ADMINISTRATOR_ROLE,DBA to STRMADMIN;
execute DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE('STRMADMIN');

/* STEP 2:- Connected as the Streams Administrator, create the streams queue.

conn <stream admin user>/<passwd>
BEGIN
  DBMS_STREAMS_ADM.SET_UP_QUEUE(
   queue_name => 'STREAMS_QUEUE',
   queue_table =>'STREAMS_QUEUE_TABLE',
   queue_user => 'STRMADMIN');
   END;
   /

/* STEP 3:- Create sample user TEST and create two tables A and B .

 create user test identified by test;
 grant connect,resource,dba to test;

 conn test/test
 create table a(id number(5),name varchar2(10));
 create table b(id number(5),name varchar2(10));

/* STEP 4:-  Connected as the Streams Administrator,instantiate table TEST.A .

conn <stream admin user>/<passwd>

 REM Connect to source.world instance
 SET serveroutput ON
 DECLARE
   -- Declare variable to hold instantiation SCN
     iscn  NUMBER;
  BEGIN
     iscn:=DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
    DBMS_OUTPUT.PUT_LINE ('Instantiation SCN is: ' || iscn);
  END;
  /
                                           
 BEGIN
    DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN (
      source_object_name    => 'TEST.A',
   source_database_name  => 'DB1',
     instantiation_scn     => &iscn);
 END;
  /
Enter value for iscn: Enter value obtained from above query.

/* STEP 5:-  Connected as the Streams Administrator,Add capture rule for table TEST.A

SQL> BEGIN
  DBMS_STREAMS_ADM.ADD_TABLE_RULES(
   table_name     => 'TEST.A',
   streams_type    => 'CAPTURE',
    streams_name    => 'STRMADMIN_CAPTURE',
     queue_name      => 'STRMADMIN.STREAMS_QUEUE',
     include_dml     => true,
    include_ddl     => true,
     source_database => 'DB1');
  END;
  /

/* STEP 6:-  Connected as the Streams Administrator,Add Applye rule for table TEST.A and use RENAME_TABLE declarative transformation function to transform name from TEST.A to TEST.B

 var dml_rule varchar2(30);
var ddl_rule varchar2(30);
 BEGIN
   DBMS_STREAMS_ADM.ADD_TABLE_RULES(
   table_name => 'TEST.A',
   streams_type => 'APPLY',
  streams_name => 'STRMADMIN_APPLY',
   queue_name => 'STRMADMIN.STREAMS_QUEUE',
   source_database => 'DB1',
   dml_rule_name =>:dml_rule,
   ddl_rule_name =>:ddl_rule);
  DBMS_STREAMS_ADM.RENAME_TABLE(
  rule_name => :dml_rule,
  from_table_name => 'TEST.A',
  to_table_name => 'TEST.B',
 step_number => 0,
  operation => 'ADD');
 END;
  /

/* STEP 7:-  Strat the Apply process and capture process.


 BEGIN
  DBMS_APPLY_ADM.START_APPLY(
  apply_name => 'STRMADMIN_APPLY');
   END;
  /

 BEGIN
  DBMS_CAPTURE_ADM.START_CAPTURE(
  capture_name => 'STRMADMIN_CAPTURE');
  END;
  /
 

 

Check the output:

SQL> insert into test.a values(1,'ABC');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test.b;

           ID NAME
-------------- ----------
            1 ABC