CODEWITHSUNDEEP
databaseoracle-databaseoracle10goracle11gdatabase-replication
John Russell
John Russell

Reputation: 2247

Oracle: Should I use replication?

I am working on an ASP.NET web application that's deployed as two separate sites. Each site is backed by it's own instance of the same (same schema) ORACLE database (10g), but each database contains "separate" data. One site contains only "secure data" and the other site contains only "open data". Only a few user can see the secure site and the secure data while the open site is available to most of the company.

The secure site users now want to be able to see both secure and open data from the secure site. In addition, they want to be able to modify the open data, and the changes to open data from the secure site need to be propagated to the open site and conflicts need to be handled accordingly.

I am hoping for a database-only solution since we have reports tied to stored procedures that will now need to show secure and open data for secure users.

We can assume the data is marked accordingly as secure (Y/N).

The current path we're on seems less than desirable. It involves modifying the secure database to include a database link to the open database. We're also adding extra views to the secure database to "union" secure and open data via database link. At the same time, we're overhauling the original stored procedures on the secure site to handle CRUD operations, e.g.

if (someRecord.IsSecure = 'Y')
update secure data;
else
update open data;

Before we proceed further, I wanted to reach out and see if there was a better way to solve this problem such as through replication.

I've been looking at multi-master replication. Does this seem like the right path? Has anyone faced this type of scenario?

Upvotes: 0

Views: 598

Answers (1)

DazzaL
DazzaL

Reputation: 21973

On the project I'm working on we use Oracle Streams to support multi-master replication (we operate 3 sites globally in New York, London and Singapore all requiring the ability to read and update the same data ).

Streams supports conditional replication which we make use of for regulatory requirements (e.g in Singapore, private banking client data cannot leave the country).

with Streams, you could, for example replicate either a whole schema (DML and/or DDL) or just a list of tables. Within a transaction you can control streams so that individual row operations on a replicated object don't replicate ..eg:

if (is_secure)
then
  dbms_streams.set_tag('01'); -- set a non null tag to prevent replication.
end if;

insert into your_table ()..values ();

if (is_secure)
then
  dbms_streams.set_tag(''); -- set a the null tag back 
end if;

insert into your_table ()..values ();

for a scenario where is_secure is true, only the second rule would replicate (you can set your replication rules to not replicate if a non-null streams tag is present). So in your code you'd just set this tag if the data was secure. that way streams would not apply this transaction to the remote site.

for multi master replication you should consider up-front the possibility of "collisions" i.e this where two+ sites modify the same data in parallel. you should handle this (you can set up apply handlers for complex scenarios) to resolve the collision without causing an apply error. for example this would be a poor design for a replicated object:

create table foo
(
  id number primary key,
  data
);
create sequence foo_seq start with 1 increment by 1;

if that is created on both sites, and someone issues in parallel on site 1:

insert into foo (id, data) values (foo_seq.nextval, 'a');

and site 2:

insert into foo (id, data) values (foo_seq.nextval, 'b');

both sites try to insert "1" into the primary key. this would fail on replication and you'd end up with apply errors to sort out.

you could set the sequence differently on each env for example start with 100 increment by 100 on one env and start with 101 increment by 100 on the other ext which would eliminate this issue, or / and add a location_id column that is set to 1 or 2 etc for each site.

Also with updates you need to be ale to handle two+ sites updating the same row at the same time (as there's always a bit of latency between sites..the more latency the more change of getting into a scenario that needs collision handling).

Streams is a bit complex to set up and understand so you need to read around the subject quite a bit. It's very mature and still supported, but in terms of enhancements, it's wont get much from now on, as Oracle focuses on Goldengate (GG is a licensed product and so would incur extra costs whereas Streams is part of the enterprise licence).

Upvotes: 1

Related Questions