6 min read

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

-- [email protected]>
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
-- [email protected]>
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
-- [email protected]>
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
-- [email protected]>
DECLARE
iscn NUMBER; -- Variable to hold instantiation SCN value
BEGIN
iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
[email protected](
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
-- [email protected]>
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
-- [email protected]>
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.

-- [email protected]>
BEGIN
DBMS_APPLY_ADM.SET_PARAMETER(
apply_name => 'STREAMS_APPLY',
parameter => 'disable_on_error',
value => 'n');
END;
/
COMMIT;
-- [email protected]>
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;
-- [email protected]>
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:

-- [email protected]>
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.

-- [email protected]>
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.

Packt

LEAVE A REPLY

Please enter your comment!
Please enter your name here