N-Way Replication in Oracle 11g Streams: Part 1

Exclusive offer: get 50% off this eBook here
Oracle 11g Streams Implementer's Guide

Oracle 11g Streams Implementer's Guide — Save 50%

Design, implement, and maintain a distributed environment with Oracle Streams using this book and eBook

$35.99    $18.00
by Ann L. R. McKinnell Eric Yen | January 2010 | Oracle

This article series by Ann L.R. McKinnell and Eric Yen explains N-way replication using Oracle 11g Streams.

This article series covers the following:

  1. Planning for N-way replication
  2. Technique to avoid conflict
  3. The setup:
    • Configure replication from STRM1 to STRM2
    • Configure replication from STRM2 to STRM1
  4. Configure conflict resolution
  5. Expanding the example
  6. Rinse and repeat

N-way replication refers to a Streams environment where there are multiple sources. In this article, we will still use the STRM1 and STRM2 databases but with a little twist; making both databases the source. By making both STRM1 and STRM2 sources, we need to first consider a couple of unique situations and do a little more pre-planning, specifically for N-Way replication.

The concepts and techniques used to configure a 2-way replication can then be used to scale to N-way replication. We all need to crawl before we run, the better you crawl (understand) this article, the easier it will be to scale up to N-way replication. Pay close attention and learn the technique so that you can implement it well.

We need to repeat this—Streams is not Failover.

We need to repeat this—Streams is not Failover.

No, that is not a typo. The authors are passionate about Streams and want to see you successfully implement it. To successfully implement Streams, you need to know not to step into the trap of using it for Failover.

Both authors have done some work where Failover was the requirement. Streams is not a Failover solution. Failover is handled by Oracle Data Guard, NOT Oracle Streams. Streams is about distributing the data to multiple locations. On more than one occasion, Streams was used as a Failover technology because it can distribute data to multiple locations. Do not fall into the trap of using the wrong tool for the wrong job. Streams distributes (replicates) data. As such, there will always be some difference between the databases in a Streams environment. All replication technology has this problem. The only time where all of the databases are in sync is, when there is no activity and all replication has been applied to all target locations.

If you need Failover, then use the proper tool. Oracle Data Guard is for Failover. It has the necessary processes to guarantee a different level of failover from a primary site to a secondary site, whereas Streams is a Replication tool that distributes data. Just remember the following, when there is a discussion of Replication and Failover that comes up:

  • Streams distributes data, it is built for replication
  • Data Guard is built for Failover

Pre-planning for N-way replication

When we set up N-way replication, we must consider the possibility of a collision of data. Since we have multiple sources of data, it is possible for the exact same data to be inputted on any or all of the sources at the exact same time. When this happens, it is a conflict. This example is just one type of conflict that can happen in N-way replication environments. The types of conflict that can occur are as follows:

  • Update conflict: When transactions from different databases try to update the same row at nearly the same time.
  • Delete conflict: When one transaction deletes a row and the next transaction tries to update or delete the row. Transactions originate from different databases.
  • Unique conflict: When transactions from different databases violate a primary or unique constraint, the first transaction is accepted. The second transaction obtains the conflict.
  • Foreign key conflict : This happens when a transaction from a Source tries to insert a child record before the parent record exists.

The good news is that Oracle has provided built-in conflict resolution in Streams that solves the most common situations. The built-in solutions are as follows:

  • OVERWRITE
  • DISCARD
  • MAXIMUM
  • MINIMUM

We will provide an example of conflict resolution after we build our N-way replication. In our case, we will use MAXIMUM. As part of the pre-planning for N-way replication, we highly suggest creating a simple table such as the Setup Table.

Avoiding Conflict

As conflict requires additional pre-planning and configuration, one begins to wonder, "Are there techniques so that we can configure N-way replication without the possibility of conflict?" The simple answer to the question is "Yes". The not-so simple answer is that there is some configuration magic that needs to be done and the devil is in the details.

Limiting who and what can be updated is one method of avoiding conflict. Think of it this way— there is no conflict if we agree to who and what can update the specific data. User 1 can only update his specific data and no one else can do that. Similarly, user 2 can only update his specific data. So, user 1 and user 2 can never cause a conflict. Now this may be a little bit difficult depending on the application. This can be implemented with the use of offset sequences. One sequence produces only odd values, and another produces only even values. We could also use a combination of sequence and some unique characteristics of the database.

Oracle 11g Streams Implementer's Guide Design, implement, and maintain a distributed environment with Oracle Streams using this book and eBook
Published: December 2009
eBook Price: $35.99
Book Price: $59.99
See more
Select your format and quantity:

The Setup

Here is the big picture for this article. We will create a new user and tablespace to isolate all the examples used in this article. That user's schema will contain one table replicated between STRM1 and STRM2 database. Both STRM1 and STRM2 are sources. STRM1 is the source for STRM2 and vice-versa. The Streams Administrator will configure the Streams environment. Conflict resolution will handle the situation where the exact same data inputted on one or more sources at the same time. The conflict resolution will use the built-in MAXIMUM confliction resolution handler.

The set-up table is given as follows:

Description

Value

New User

LEARNING

Table

EMPLOYEE

New Tablespace

LEARNING

Replication Type

N-Way

Conflict Resolution

MAXIMUM

Streams Administrator

STRM_ADMIN on both STRM1 & STRM2

Databases

STRM1 & STRM2

 

N-Way Replication in Oracle 11g Streams: Part 1

 

This article is heavy on the PL/SQL and does not use DB Control or Grid Control to set up Streams. As such, this is a deeper dive into what actually is going on under the covers. There is an extensive use of comments in the provided code and images to help you understand and visualize what Streams is doing. The set-up here is to make sure you learn, understand, and are able to implement N-way replication by providing a working example that is extendable.

All code in this article ran against a Beta of Oracle 11g R2 on both Windows and Linux 32 bit. The authors have made efforts to make the code reusable with slight modification.

Preliminary Setup

For this article, the preliminary setup involves:

On both STRM1 and STRM2:

  1. Create Tablespace for User "Learning"
  2. Create User "Learning"
  3. Create Table "Employees" and ADD SUPPLEMENTAL LOG
  4. Create Trigger on "Employees" table to record when data was inserted or updated
  5. Load data for table "Employees"
  6. If not already done
    1. Create strm_admin using a DBA account.
      • Tablespace for Streams Administrator
      • Create Streams Administrator reuse from Chapter 4, Single-Source Configuration.
        1. Clear out previous configuration
        2. Drop and recreate Streams Administrator
    2. Check TNSNAMES.ORA
      • The tnsnames.ora file on the STRM1 host should have an STRM2 entry
      • The tnsnames.ora file on the STRM2 host should have an STRM1 entry
    3. Make sure that the parameter global_names is set to TRUE on both databases.
      select name,
      value from v$parameter
      where name = 'global_names';
    4. Global names
      select * from global_name;
    5. DBLINKS
      • Create Private DB link as STRM_ADMIN
  7. Grant permissions to Streams Administrator for the table "Employees"
    1. Check TNSNAMES.ORA  
          
      • The tnsnames.ora file on the STRM1 host should have an STRM2 entry
      •   

      • The tnsnames.ora file on the STRM2 host should have an STRM1 entry
      •   

    2. Make sure that the parameter global_names is set to TRUE on both databases.
      select name,
      value from v$parameter
      where name = 'global_names';
    3. Global names
      select * from global_name;
    4. DBLINKS
      • Create Private DB link as STRM_ADMIN
  8. Scripts_5_1_PSU.sql provides the setup for the above. The script does not include the steps:

Carefully review Scripts_5_1_PSU.sql and modify what you deem appropriate. Then use an account with DBA privileges to run Scripts_5_1_PSU.sql script. At the minimum, you will need to modify the creation of the tablespaces.

At this point, both STRM1 and STRM2 are set up according to the setup table with the exception of the implementation of conflict resolution. Now, we will configure Streams first on STRM1 then on STRM2. Check scripts will also be run to confirm that the configuration of Streams is going well.

In a Streams environment, you connect to more than one database at a time while performing Streams administration. To make things easier, we highly suggest changing your glogin.sql script located in your $ORACLE_HOME/SQLPLUS/admin. The following code will change the prompt from the default SQL> to the USER@GLOBAL_NAME>. In our case we will see:

STRM_ADMIN@STRM1.US.APGTECH.COM>
STRM_ADMIN@STRM2.US.APGTECH.COM>

when we are logged into STRM1 and STRM2 as STRM_ADMIN respectively.

When logged into the database as DBA, the login prompt will reflect

DBA1@STRM1.US.APGTECH.COM>
DBA1@STRM1.US.APGTECH.COM>

showing DBA1 being logged into STRM1 and STRM2 respectively.

-- start: change the sql prompt
-- to reflect user@global_name
-- Code for modifying glogin.sql
-- Append to the end of the glogin.sql
set termout off
col gname new_value prompt_gname
select global_name gname from global_name;
set sqlprompt "&&_USER'@'prompt_gname> "
set termout on
-- end: change the sql prompt

STRM_ADMIN is used to run all of the scripts to set up for Streams. If necessary, a DBA account may be used. The login prompt will reflect the logged-in user and at what database. STRM_ADMIN does have DBA role and privileges, but we suggest opening a separate session and using a different user for simplicity. The code for the next two sections is in the files Scripts_5_1_STRM1_STRM2.sql and Scripts_5_1_STRM2_STRM1.sql.

Oracle 11g Streams Implementer's Guide Design, implement, and maintain a distributed environment with Oracle Streams using this book and eBook
Published: December 2009
eBook Price: $35.99
Book Price: $59.99
See more
Select your format and quantity:

Streaming STRM1 to STRM2

If you are reusing the Streams please run the following to clear out all previous configurations. This will destroy the previous configuration! If you are starting with a new STRM_ADMIN account with no previous configuration, skip this step.

STRM_ADMIN@STRM1.US.APGTECH.COM>
EXEC DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION();

N-Way Replication in Oracle 11g Streams: Part 1

Let us step back and plan the set up of Streams between STRM1 and STRM2.

On STRM1, log in as STRM_ADMIN

-- ADD THE QUEUE: A good queue name is STREAMS_CAPTURE_Q

-- ADD THE CAPTURE RULE

-- ADD THE PROPAGATION RULE

-- INSTANTIATE TABLE ACROSS DBLINK

-- DBLINK TO DESTINATION is STRM2.US.APGTECH.COM

-- SOURCE is STRM1.US.APGTECH.COM

On STRM2 log in as STRM_ADMIN

-- ADD THE QUEUE: A good queue name is STREAMS_APPLY_Q

-- ADD THE APPLY RULE

Start everything up and test the Stream on STRM1

Then, check to see if the record is Stream'ed to STRM2.

We take the additional step of performing a commit after each running of a procedure. Although not technically needed, we use it as a precaution.

-- On STRM1, log in as STRM_ADMIN

-- ADD THE QUEUE: A good queue name is STREAMS_CAPTURE_Q

-- STRM_ADMIN@STRM1.US.APGTECH.COM>
BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => '"STREAMS_CAPTURE_QT"',
queue_name => '"STREAMS_CAPTURE_Q"',
queue_user => '"STRM_ADMIN"');
END;
/
commit;
-- ADD THE CAPTURE RULE
-- STRM_ADMIN@STRM1.US.APGTECH.COM>
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
Chapter 5
[ 161 ]
table_name => '"LEARNING.EMPLOYEES"',
streams_type => 'capture',
streams_name => '"STREAMS_CAPTURE"',
queue_name => '"STRM_ADMIN"."STREAMS_CAPTURE_Q"',
include_dml => true,
include_ddl => true,
include_tagged_lcr => false,
inclusion_rule => true);
END;
/
commit;
-- ADD THE PROPAGATION RULE
-- STRM_ADMIN@STRM1.US.APGTECH.COM>
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
table_name => '"LEARNING.EMPLOYEES"',
streams_name => '"STREAMS_PROPAGATION"',
source_queue_name =>
'"STRM_ADMIN"."STREAMS_CAPTURE_Q"',
destination_queue_name =>
'"STRM_ADMIN"."STREAMS_APPLY_Q"@STRM2.US.APGTECH.COM',
include_dml => true,
include_ddl => true,
source_database => 'STRM1.US.APGTECH.COM',
inclusion_rule => true);
END;
/
COMMIT;
-- INSTANTIATE TABLE ACROSS DBLINK
-- STRM_ADMIN@STRM1.US.APGTECH.COM>
DECLARE
-- Variable to hold instantiation System Change Number
iscn NUMBER;
BEGIN
iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@STRM2.US.APGTECH.COM(
source_object_name => 'LEARNING.EMPLOYEES',
source_database_name => 'STRM1.US.APGTECH.COM',
instantiation_scn => iscn);
END;
/
COMMIT;

-- On STRM2 log in as STRM_ADMIN

-- ADD THE QUEUE a good queue name is STREAMS_APPLY_Q

-- STRM_ADMIN@STRM2.US.APGTECH.COM>
BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => '"STREAMS_APPLY_QT"',
queue_name => '"STREAMS_APPLY_Q"',
queue_user => '"STRM_ADMIN"');
END;
/
COMMIT;
-- ADD THE APPLY RULE
-- STRM_ADMIN@STRM2.US.APGTECH.COM>
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => '"LEARNING.EMPLOYEES"',
streams_type => 'apply',
streams_name => '"STREAMS_APPLY"',
queue_name => '"STRM_ADMIN"."STREAMS_APPLY_Q"',
include_dml => true,
include_ddl => true,
include_tagged_lcr => false,
inclusion_rule => true);
END;
/
commit;

Start everything up and test.

-- STRM_ADMIN@STRM2.US.APGTECH.COM>
BEGIN
DBMS_APPLY_ADM.SET_PARAMETER(
apply_name => 'STREAMS_APPLY',
parameter => 'disable_on_error',
value => 'n');
END;
/
COMMIT;
-- STRM_ADMIN@STRM2.US.APGTECH.COM>
DECLARE
v_started number;

BEGIN
SELECT DECODE(status, 'ENABLED', 1, 0) INTO v_started
FROM DBA_APPLY where apply_name = 'STREAMS_APPLY';
if (v_started = 0) then
DBMS_APPLY_ADM.START_APPLY(apply_name => '"STREAMS_APPLY"');
end if;
END;
/
COMMIT;
-- STRM_ADMIN@STRM1.US.APGTECH.COM>
DECLARE
v_started number;
BEGIN
SELECT DECODE(status, 'ENABLED', 1, 0) INTO v_started
FROM DBA_CAPTURE where CAPTURE_NAME = 'STREAMS_CAPTURE';
if (v_started = 0) then
DBMS_CAPTURE_ADM.START_CAPTURE(capture_name =>
'"STREAMS_CAPTURE"');
end if;
END;
/

Then on STRM1,

-- STRM_ADMIN@STRM1.US.APGTECH.COM>
ACCEPT fname PROMPT 'Enter Your First Name:'
ACCEPT lname PROMPT 'Enter Your Last Name:'
Insert into LEARNING.EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME,
TIME) Values (5, '&fname', '&lname', NULL);
dbms_lock.sleep(10); --give it time to replicate

On the first record we have found that the Streams take a while to "warm up". That is why we used dbms_lock above. Once Streams is up and running it runs and runs and runs.

Then on STRM2, search for the record.

-- STRM_ADMIN@STRM2.US.APGTECH.COM>
Select * from LEARNING.EMPLOYEES;

If everything is working, now is a good time for a break before moving on to the next section. In the next section, we will set up the reverse STRM2 to STRM1.

>> Continue Reading N-Way Replication in Oracle 11g Streams: Part 2

 

About the Author :


Ann L. R. McKinnell

Ann McKinnell is currently a Senior Principal Consultant with APG Technologies, LLC and has been an OCP since Oracle 7.3.4. She came to APG with over 8 years as a senior technical member of Oracle Global Support, specializing in Replication and Distributed System technologies. She was a recognized global technical expert for Oracle Distributed Systems; earning the internal nickname "The Replication Goddess". Ann has trained Oracle Support and Consulting personnel from many countries in Advanced Replication and Distributed System Internals and Problem Solving techniques. She has authored and co-authored many of the Oracle Advanced Replication notes found on Oracle Metalink, and was a technical reviewer for the Oracle University Pilot 9i Steams course material, as well as various Oracle Replication and Database Administration user manuals. Ann continues to specialize in practical implementation strategies and the development of distributed Oracle database systems, database architecture, and software and database design and integration.

Eric Yen

Eric Yen began working with Oracle Databases at version 7.3.4. Over the following 14 years, he obtained his Oracle DBA Certification starting with version 8, maintaining it up to the current release, and he has also earned the (ISC)2 CISSP certification. Eric began working with Oracle Streams with Oracle 9i Streams beta. As a Senior Principal Consultant with APG Technologies, LLC, Eric's work includes designing and implementing Streams solutions for Government clients using the more recent versions of Streams in Oracle 10 and Oracle 11. During his little spare time, you can find Eric exercising and tinkering around with Oracle products.

Books From Packt

Oracle Application Express 3.2 – The Essentials and More
Oracle Application Express 3.2 – The Essentials and More

Oracle Coherence 3.5
Oracle Coherence 3.5

Oracle SQL Developer 2.1
Oracle SQL Developer 2.1

Middleware Management with Oracle Enterprise Manager Grid Control 10g R5
Middleware Management with Oracle Enterprise Manager Grid Control 10g R5

Getting Started With Oracle SOA Suite 11g R1 – A Hands-On Tutorial
Getting Started With Oracle SOA Suite 11g R1 – A Hands-On Tutorial

Oracle User Productivity Kit 3.5
Oracle User Productivity Kit 3.5

Oracle Warehouse Builder 11g: Getting Started
Oracle Warehouse Builder 11g: Getting Started

Oracle Application Express Forms Converter
Oracle Application Express Forms Converter

No votes yet

Post new comment

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
B
N
3
c
B
u
Enter the code without spaces and pay attention to upper/lower case.
Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software