The setup

Here is the big picture for this chapter. We will create a new user and tablespace to isolate all the examples used in this chapter. 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

The setup

This chapter 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.

Note

All code in this chapter 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

Please refer to Chapter 3, Prepare the Rafts and Secure Your Gear: The pre-work before configuring Oracle 11g Streams, for a full explanation on how to configure the database for Streams. For this chapter, the preliminary setup involves:

On both STRM1 and STRM2:

  • Create Tablespace for User "Learning"
  • Create User "Learning"
  • Create Table "Employees" and ADD SUPPLEMENTAL LOG
  • Create Trigger on "Employees" table to record when data was inserted or updated
  • Load data for table "Employees"
  • If not already done
    • Create strm_admin using a DBA account.
      • Tablespace for Streams Administrator
      • Create Streams Administrator reuse from Chapter 4, Single-Source Configuration.
        • Clear out previous configuration
        • Drop and recreate Streams Administrator
    • 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
    • Make sure that the parameter global_names is set to TRUE on both databases.
      select name, value from v$parameter where name = 'global_names';
    • Global names
      select * from global_name;
    • DBLINKS
      • Create Private DB link as STRM_ADMIN
  • Grant permissions to Streams Administrator for the table "Employees"

Scripts_5_1_PSU.sql provides the setup for the above. The script does not include the steps:

  • 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
  • Make sure that the parameter global_names is set to TRUE on both databases.
    select name, value from v$parameter where name = 'global_names';
  • Global names
    select * from global_name;
  • DBLINKS
    • Create Private DB Link as STRM_ADMIN

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:

[email protected]>

[email protected]>

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

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

[email protected]>

[email protected]>

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.

Streaming STRM1 to STRM2

If you are reusing the Streams set up from Chapter 4, Single-Source Configuration, 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. 

[email protected]>
EXEC DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION();
Streaming STRM1 to STRM2

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.

Note

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 

-- [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"@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
-- [email protected]>
DECLARE
-- Variable to hold instantiation System Change Number
iscn NUMBER;
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 STRM2 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 STRM1,

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

Note

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.

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

Streaming STRM2 to STRM1

Streaming STRM2 to STRM1

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

Note

On the first record we have found 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 STRM1, search for the record.

-- [email protected]>
Select * from LEARNING.EMPLOYEES;

We now have N-way replication.

Streaming STRM2 to STRM1

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!

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset