Re: Fast Refresh Materialized View on Prebuilt Table
Date: 06 Oct 2014 17:26:18 -0000
Message-ID: <705426149.621154.1412616377425.JavaMail.yahoo_at_jws10663.mail.bf1.yahoo.com>
Thanks Kim - that looks interesting. I'll have to give a try. In the meantime I've got the following working :- 1. Create trigger on base table to write primary key of any DML to a log table, dml_log (a sort of DIY materialized view log in addition to the real MV Log).2. Copy table to remove site and create materialized view. Set up fast refresh schedule.3. Run a "fake" update using my dml_log table as a driver (UPDATE base_table SET any_old_column=any_old_column WHERE pk IN (SELECT pk FROM dml_log)). This does not update any data but causes the relevant rows to be re-added to the MV Log. The next fast refresh picks these up and inserts/updates them as needed at the target.4. Drop trigger and dml_log Charlotte
On Monday, October 6, 2014 3:39 PM, Kim Berg Hansen <kibeha_at_gmail.com> wrote:
I haven't tried this, but perhaps there might be some hints you can use in the section of the replication manual called "Performing an Offline Instantiation of a Materialized View Site Using Export/Import" : http://docs.oracle.com/cd/B28359_01/server.111/b28327/rarmanmv.htm#i30332
Looks rather complex, I'm afraid...
But you said your issue was that the mview log on the source is deleted when you create your mview on the target?
Perhaps experiment with something like:
- create mview on target without prebuilt table but with BUILD DEFERRED and a next_date far in the future
- get the metadata on the source database with "select * from dba_registered_mviews"
- drop the mview on the target
- register mview manually on the source with DBMS_MVIEW.REGISTER_MVIEW using the metadata found in 2)
- copy the table
- create the mview using PREBUILT TABLE Maybe if you are lucky it will not clear the mview log on the source since the mview was registered in the source BEFORE the create mview is executed.I am not certain it will work, but there is a chance that registering the mview on the source just possibly can make the source consider that the mview is from before the copy and therefore still needs the mview log entries from the intervening hours.Worth a try, maybe?
Regards
Kim Berg Hansen
http://dspsd.blogspot.comkibeha_at_gmail.com_at_kibeha
On Mon, Oct 6, 2014 at 4:00 PM, Charlotte Hammond <charlottejanehammond_at_yahoo.com> wrote:
Hi Kim,
Unfortunately the table is too big and the network too slow/flaky for a complete refresh. It's ok for the relatively small daily updates through a fast refresh, but a complete refresh requires shipping the table data on physical media.
Charlotte
On Monday, October 6, 2014 11:57 AM, Kim Berg Hansen <kibeha_at_gmail.com> wrote:
Hi Charlotte
Is there a specific reason you wish to copy the table from source to target manually and then create the mview using prebuilt?
We use mview replication quite a bit, and I've always just created the fast refreshable mviews with "build immediate" and that way let the mview do the initial copying of the table.That way the source mview log keeps the changes during the mview build and then the first fast refresh afterwards will get those changes.
Regards
Kim Berg Hansen
http://dspsd.blogspot.comkibeha_at_gmail.com_at_kibeha
On Mon, Oct 6, 2014 at 12:25 PM, Charlotte Hammond <dmarc-noreply_at_freelists.org> wrote:
Hi All,
I'm creating a large fast-refreshable materialized view using a prebuilt table.
The problem I have is that by the time I have copied the table from the source system to the target system several hours have passed and there are now numerous changes on the target. As soon as I issue the CREATE MATERIALIZED VIEW command the log is deleted and the fast refresh starts on a table which is by now out of date.
I'm looking for a good way to sync up the changes made during the copy period. I've got some ideas using triggers (and possibly a small outage on the source) but it's seems messy. I'm wondering if I'm missing some clean / built-in functionality to resolve this?
The database is 11.2.0.4 SE
Thanks in advance for any ideas!Charlotte
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Oct 06 2014 - 19:26:18 CEST