Skip to Main Content
  • Questions
  • Strangler Pattern For Linked Oracle DB - how could it be done?

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Tom.

Asked: November 14, 2017 - 2:46 pm UTC

Last updated: November 15, 2017 - 4:57 pm UTC

Version: 10.2.0.4.0

Viewed 1000+ times

You Asked

Hi,

I'm not a big expert with Oracle databases so please forgive my fumbling description.

We're working with a system where there are two databases which are connected using a database link. A -> queries and RPCs -> B

There is a need to introduce a new system, let's call it C, that provides a similar service to database B but they both need to run in parallel, until B is eventually superseded.

In the past, in other situations using different types of system we have used the strangler pattern. It's a technique for migrating systems gradually whereby a custom "router" is placed in between A and B. Initially it simply accepts requests from A and forwards them to B. Then in the next step you introduce a bit of logic to divert some queries or calls to system C and respond as though the new B. You ramp up the amount of queries an d calls serviced by C until you can turn B off.

Does this make sense?

I'm wondering if this kind of pattern is possible with database links? Could we introduce a small database between A and B, call it "Router", it presents the same interface as B and initially only passes on queries and calls. Then could we add a little routing logic to divert some queries and calls to a new database, C or perhaps call a REST API instead?

I'm guessing that one doesn't simply intercept an Oracle DB link with anything other than an Oracle database.

and Chris said...

Well, you could do something like:

- Create your "router" database
- For all the objects accessed in B, create synonyms/views in the router DB pointing to those in B over the DB link, e.g.

create or replace view vw_b as select * from vb_b@dblink_to_b;


- Update the DB link in A to point to B
- Gradually change the views/synonyms in the router DB to point to C, such as:

create or replace view vw_b as select * from vb_b@dblink_to_c;


- Switch out the router DB when you're done

Of course, this introduces an extra layer of network hops between A and the final database. Which may slow things down. Particularly if you'll have queries accessing tables from B and C at the same time.

You could mitigate this using materialized views to stage data into the router DB. Of course, this makes switching objects from B -> C more complicated.

So another option to consider:

Does C actually need to be a separate database? Or can it be a schema within B?

You can stick with the view/synonym approach I described above. But instead of changing the DB link, you're just updating the schema.

This avoids the extra network roundtrip and keeps all the data in one database. Which can help if you want to join tables in the old and new systems.

But of course, that brings the bigger question of why you're trying move B -> C at all ;)

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Very Helpful - Thanks

Tom Duckering, November 15, 2017 - 4:08 pm UTC

Thanks Chris - a very useful answer with helpful reminder of the additional network hops and performance impact.

The desire to eventually ditch B is that simply putting new stuff in it is not architecturally desirable. One reason is that it's geographically tied to other things.

To answer your "bigger question" it's business related. The new system C is a very big thing offering what is effect a more cost effective and modern implementation of what B does. It's really more than a DB - but a DB which is the integration point for something much bigger.

Sorry to be vague but NDA stuff.
Chris Saxon
November 15, 2017 - 4:57 pm UTC

"it's more than a DB, it's the answer to all our business's problems!" ;)

Anyway, the extra network may not be an issue. But obviously this all depends on your setup. Do some prototyping and tests before you commit to adding the router DB!

Another option is you make a schema on B the "router". Then when you're mostly migrated switch this to a schema on C.

Good luck!

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database