Re: Materalized View Refresh after upgrade - Oracle 11g to 12c
Date: Tue, 22 Sep 2020 17:58:09 +0100
Message-ID: <CAGtsp8nDoMv68Uc=famG5TwW1MfsJjxZ3pmQpLjbwgGTpqLMQg_at_mail.gmail.com>
I'm wondering if someone has mis-interpreted the requirement to refresh all the existing MVs so that all the mv logs are empty before the upgrade. A recent posting on the Developer Forum reported the following from the pre-upgrade (12.1 SE to 18c) assessment:
Oracle recommends that all materialized views (MV's) are refreshed before
upgrading the database because this will clear the MV logs and the
sumdelta$ table, and make the UPGRADE process faster. If you choose to
not refresh some MVs, the change data for those MV's will be carried
through the UPGRADE process. After UPGRADE, you can refresh the MV's and
MV incremental refresh should work in normal cases. It's possible that your system will have different requirements because it's lower versions involved, but it doesn't sound as if there's anything in there to do with "old references not being valid". The report does, however,, contain the following:
If there are any stale
MVs depending on changes in sys.sumdelta$, do not truncate it, because
doing so will cause wrong results after refresh. This, perhaps, is where the "old references not being valid" is supposed to be relevant.
Regards
Jonathan Lewis
On Tue, 22 Sep 2020 at 17:43, Gokul Kumar Gopal <gokulkumar.gopal_at_gmail.com> wrote:
> Hello,
>
> We are currently migrating from 11.2.0.4 to 12.2.
>
> There are about 150 materialized view logs on the basetables.
>
> Several consumers refresh the MVs using these MV logs (on demand fast
> refresh).
>
> We are now told that due to migration, the MVs will have to be refreshed
> in FULL, because all the old references are no longer valid.
>
> I have not found anything in the documentation to this effect.
>
> I would like to confirm with the experts in the forum if this is the case.
>
> Rgds,
> Gokul
>
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Sep 22 2020 - 18:58:09 CEST