Skip to Main Content
  • Questions
  • Realtime replication using Mviews on distributed database ?

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: September 10, 2015 - 5:29 pm UTC

Last updated: September 18, 2015 - 4:00 am UTC

Version: 11.1

Viewed 1000+ times

You Asked

I have created Mviews using dblinks.

These are simple Mviews - select * from table_name@dblink

Is it possible to do realtime replication using these Mviews on a distributed , physically separate databases ?

I read about Mview logs but looks like it doesnot support dblinks.

So Question is how to do realtime refresh of such mviews ?

On commit refresh is also not supported for Mivews with dblinks ?

I don't want to create my own insert, update delete triggers over dblink.

Any suggestions, workarounds ? How can Oracle best support this ?

and Connor said...

It really depends on what you define as "real time".

True "real time" means both databases would have a consistent view of the data at any point in time. That means when you commit at one site, you cannot do so until the other site will respond as well, aka, two phase commit in a true distribution transaction sense. (It also means that if one site is down, then both are down).

So I'm going to assume that you mean *near* "real time", ie, the remote database is (within a few seconds) in sync with the primary.

Your options there are basically:

- high frequency refresh intervals on your mviews (and obviously going hand in hand with that is that they all need to be FAST REFRESH capable)

- procedural replication, ie, your PLSQL calls on the primary will be replicated as PLSQL calls to the remote (which is facilitated using AQ under the covers)

- investing in a technology designed explicitly for this purpose, namely GoldenGate.

GoldenGate would be the best fit...but its extra cost, so you need to weigh that up. You could hand craft a similar solution using our Streams product, but thats more work and its fair to say that Streams is not as strategic to Oracle as our GoldenGate product.

Hope this helps.

Rating

  (3 ratings)

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

Comments

Near real time high freq fast refresh

A reader, September 11, 2015 - 7:02 pm UTC

Can you please elaborate on high freq fast refresh of mviews.

How does it work.

I only need to refresh remote mview only when data has changed in base table from which mview is created using db link with few mins delay is fine.

Can I do incremental fast refresh ?

Can you please give some examples on how to do this fast refresh.

Does it mean I blindly refresh mview say every 5 mins whether data changed or not ? Wouldn't that be an overkill ?

I tried some really crazy things like created a trigger on base table and called mview refresh proc over db link in this trigger but it did not refresh mview and the trigger did not even complain using autonomous transaction to overcome commit restriction of trigger.

Please provide some examples of fast refresh of mviews.
Connor McDonald
September 14, 2015 - 3:29 am UTC

Well...we got a whole book on that :-)

https://docs.oracle.com/database/121/REPLN/repmview.htm#REPLN351

If your materialised views are fast-refreshable, ie, their definition supports the use of appropriate materialised view *logs*, then refreshing every 5 mins will unlikely to be an issue, because only *deltas* are identified and applied.

That's what the *definition* of a fast refresh materialise view is...that it can be refreshed by applying deltas, rather than refreshing the entire thing.

Hope this helps.

Its distributed DB - dblinks- so no Fast refresh

A reader, September 15, 2015 - 1:24 pm UTC

Thanks for your feedback.

Its a distributed DB - dblinks- so no Fast refresh, so not possible to use mview logs.

So only option is Golden gate if not procedural logic.

Why is life ( Oracle ) so difficult :) Looks like such a simple requirement but no simple solution from Oracle.
All I want is a remote Mview near realtime refresh ?


Look like i will need to do full fresh every n minutes.
May be schedule proc to check data changed and then call mview refresh to avoid unnecessary refresh of the same data over and over again. Still this is too much. Not an elegant solution.

Connor McDonald
September 16, 2015 - 4:16 am UTC

Its a distributed DB - dblinks- so no Fast refresh, so not possible to use mview logs.


Hmmmm....

Database1
=========

SQL> create sequence seq cache 10000;

Sequence created.

SQL>
SQL> create table T as
  2  select seq.nextval s,
  3          d.*
  4  from dba_objects d, ( select 1 from dual connect by level <= 20 ) ;

Table created.

SQL>
SQL> alter table T add primary key ( s ) ;

Table altered.

SQL>
SQL> CREATE MATERIALIZED VIEW LOG ON t
  2    WITH PRIMARY KEY
  3  /

Materialized view log created.



Database2
=========

SQL> CREATE MATERIALIZED VIEW mv REFRESH FAST AS
  2  select * from T@database1;

Materialized view created.

SQL> set timing on
SQL> exec dbms_mview.refresh('mv','C');

PL/SQL procedure successfully completed.

Elapsed: 00:02:45.86
SQL> set timing on
SQL> exec dbms_mview.refresh('mv','F');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.10
SQL>



Fast refresh looks pretty fast to me :-)


Example was great

A reader, September 17, 2015 - 2:27 am UTC

Thanks for the example.
Most of the times we learn better from blogs and asktom site compared to the boring hard to comprehend oracle documentation which usually lacks good examples.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library