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

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

£22.99    £11.50
by Ann L. R. McKinnell | February 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

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

Streaming STRM2 to STRM1

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

Now the plan for setting up Streams for STRM2. It is the mirror image of what we have done above, except for the test part.

On STRM2, 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 STRM1.US.APGTECH.COM

-- SOURCE is STRM2.US.APGTECH.COM

On STRM1 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 STRM2. Then check to see if the record is STREAM'ed to STRM1.

-- On STRM2 log in as STRM_ADMIN

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

-- STRM_ADMIN@STRM2.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@STRM2.US.APGTECH.COM>
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
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@STRM2.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"@STRM1.US.APGTECH.COM',
include_dml => true,
include_ddl => true,
source_database => 'STRM2.US.APGTECH.COM',
inclusion_rule => true);
END;
/
COMMIT;

Because the table was instantiated from STRM1 already, you can skip this step.

-- INSTANTIATE TABLE ACROSS DBLINK
-- STRM_ADMIN@STRM2.US.APGTECH.COM>
DECLARE
iscn NUMBER; -- Variable to hold instantiation SCN value
BEGIN
iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@STRM1.US.APGTECH.COM(
source_object_name => 'LEARNING.EMPLOYEES',
source_database_name => 'STRM1.US.APGTECH.COM',
instantiation_scn => iscn);
END;
/
COMMIT;

-- On STRM1, log in as STRM_ADMIN.

-- ADD THE QUEUE, a good queue name is STREAMS_APPLY_Q
-- STRM_ADMIN@STRM1.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@STRM1.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@STRM1.US.APGTECH.COM>
BEGIN
DBMS_APPLY_ADM.SET_PARAMETER(
apply_name => 'STREAMS_APPLY',
parameter => 'disable_on_error',
value => 'n');
END;
/
COMMIT;
-- STRM_ADMIN@STRM1.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@STRM2.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 STRM2:

-- STRM_ADMIN@STRM2.US.APGTECH.COM>
ACCEPT fname PROMPT 'Enter Your Mom's First Name:'
ACCEPT lname PROMPT 'Enter Your Mom's 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

Then on STRM1, search for the record.

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

We now have N-way replication.

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

But wait, what about conflict resolution?
Good catch; all of this was just to set up N-way replication. In this case, it is a 2-way replication. It will work the majority of the time; that is until there is conflict. Conflict resolution needs to be set up and in this example the supplied/built-in conflict resolution handler MAXIMUM will be used. Now, let us cause some CONFLICT!
Then we will be good people and create the conflict resolution and ask for world peace while we are at it!

Conflict resolution

Conflict between User 1 and User 2 has happened. Unbeknown to both of them, they have both inserted the exact same row of data to the same table, at roughly the same time. User 1's insert is to the STRM1 database. User 2's insert is to the STRM2 database.

Normally the transaction that arrives second will raise an error. It is most likely that the error will be some sort of primary key violation and that the transaction will fail. We do not want that to happen. We want the transaction that arrives last to "win" and be committed to the database.

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

At this point, you may be wondering "How do I choose which conflict resolution to use?" Well, you do not get to choose, the Business Community that you support will determine the rules most of the time. They will tell you how they want conflict resolution handled. Your responsibility is to know what can be solved with built-in conflict resolutions and when you will need to create custom conflict resolution.

Going back to User 1 and User 2. In this particular case, User 2's insert arrives later than User 1's insert. Now the conflict resolution is added using the DBMS_APPLY_ADM package, specifically the procedure DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_ HANDLER which instructs the APPLY process on how to handle the conflict.

Scripts_5_1_CR.sql shows the conflict resolution used to resolve the conflict between User 1 and User 2. Since it is part of the APPLY process, this script is run by the Streams Administrator. In our case, that would be STRM_ADMIN. This type of conflict can occur on either STRM1 or STRM2 database, so the script will be run on both databases. The numbers to the left are there for reference reasons. They are not in the provided code.

-- Scripts_5_1_CR.sql
1. DECLARE
2. cols DBMS_UTILITY.NAME_ARRAY;
3. BEGIN
4. cols(0) := 'employee_id';
5. cols(1) := 'first_name';
6. cols(2) := 'last_name';
7. cols(3) := 'time';
8. DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER(
9. object_name => 'learning.employees',
10. method_name => 'MAXIMUM',
11. resolution_column => 'time',
12. column_list => cols);
13. END;
14. /
15. Commit;

So what do these 15 magical lines do to resolve conflict?
Let us break it down piece by piece logically first, and look at the specific syntax of the code. Oracle needs to know where to look when a conflict happens. In our example, that is the learning.employees table. Furthermore, Oracle needs more than just the table name. It needs to know what columns are involved. Line 9 informs Oracle of the table. Lines 1 -7 relate to the columns. Line 8 is the actual procedure name.

What Oracle is supposed to do when this conflict happens, is answered by Line 10. Line 10 instructs Oracle to take the MAXIMUM of the resolution_column and use that to resolve the conflict. Since our resolution column is time, the last transaction to arrive is the "winner" and is applied.

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: £22.99
Book Price: £36.99
See more
Select your format and quantity:

Extending the example

This article has covered the concepts needed to implement N-way replication. The provided code made it tangible. Now, what does it take to go to the next level? Start with the confl icts. In this case we used the built-in MAXIMUM conflict resolution handler. In your case, start with the business units that you are supporting and learn the business use cases. Then drive down to the tables involved in the transactions from the uses cases. Setting up conflict resolution is implemented at the table level. But you need to think at the transaction level. The example in this article is for a table. Taking this skeleton code you can move up to schema level by using the related schema-level procedure. For example, we used the following in this article:

Table level:

DBMS_STREAMS_ADM.ADD_TABLE_RULES
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN
DBMS_STREAMS_ADM.ADD_TABLE_RULES

by changing it to Schema level:

DBMS_STREAMS_ADM.ADD_SCHEMA_RULES
DBMS_STREAMS_ADM.ADD_SCHEMA_PROPAGATION_RULES
DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN
DBMS_STREAMS_ADM.ADD_SCHEMA_RULES

By making minor syntax changes, you can quickly start Streaming at the schema level.

Rinse and repeat

You may have noticed that setting up Streams for N-way replication can be confusing. We suggest that you establish the conflict resolution solution with the business units first. Then document it with something as simple as the setup table. Remember to plan thinking about the transaction and then implement conflict resolution at table level. Your implementation documentation should be both visual and in text. During the actual implementation, start with one host and complete and test before moving to the next host.

Summary

Building N-way replication is about making sure it is exactly what you need. Misusing N-way replication as a Failover technology is a trap that you need to avoid at all cost. Remember, replication is about distributing data while Failover (technology) is about disaster recovery.
Planning for N-way replication starts with conflict resolution. Working with your Business Units is a must, and setting and managing expectations needs to be done before any discussion of implementation. The use of Use Case scenarios driven down to transactions, then to tables involved in those transactions is one method to promote discussions with the Business Units.

Document your solution prior to implementation. Use the documentation as your implementation plan. Implementing N-way replication is easy when you are organized and have the steps defined and in order ahead of time. As part of the documentation/implementation plan, we recommend the use of a simple table, such as the Setup Table, and deciding ahead of time the order of implementation. This answers the question of "Where do we start and what next?" So, identify which host to start with and the order of implementation is important to avoid confusion during implementation.

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: £22.99
Book Price: £36.99
See more
Select your format and quantity:

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

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