Showing posts with label MV. Show all posts
Showing posts with label MV. Show all posts
Friday, October 14, 2016
12c: Updates to Fast-Refresh Materialized Views with joins are lost after DB upgrade
An interesting article on My Oracle Support, "Updates Lost to Fast-Refresh Materialized Views with Joins After Upgrade to 12c" from document ID 2186836.1, tells us that an upgrade from 12.1.0.1 to 12.1.0.2 can negatively impact your fast-refresh materialized views with joins! It seems that the system "covers up" the problem by having the last update date updated while the data does not get updated!!
Friday, December 6, 2013
R12: Materialized Views
You will probably find this best as a "oh yeah" kind of reminder, but I learned a lesson during our R12 upgrade when we went live: treat MVs just like you treat your ETLs and not only test them prior, but validate after as well.
Obviously we did test, but sometimes in a huge project not all changes get propagated as they should so we have MVs without column modifications (especially for RA_CUSTOMERS). Additionally, we ran into this in our alert log:
ORA-12012: error on auto execute of job 93
ORA-12008: error in materialized view refresh path
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2563
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2776
ORA-06512: at "SYS.DBMS_IREFRESH", line 685
ORA-06512: at "SYS.DBMS_REFRESH", line 195
Why did we encounter this? We didn't validate that the MV was in a VALID state before asking the system to run it as per the normal run schedule. This leads me to think of yet another alert to tell us when we have a MV go INVALID just in case it happens again and we aren't watching the alert log like hawks.
Obviously we did test, but sometimes in a huge project not all changes get propagated as they should so we have MVs without column modifications (especially for RA_CUSTOMERS). Additionally, we ran into this in our alert log:
ORA-12012: error on auto execute of job 93
ORA-12008: error in materialized view refresh path
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2563
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2776
ORA-06512: at "SYS.DBMS_IREFRESH", line 685
ORA-06512: at "SYS.DBMS_REFRESH", line 195
Why did we encounter this? We didn't validate that the MV was in a VALID state before asking the system to run it as per the normal run schedule. This leads me to think of yet another alert to tell us when we have a MV go INVALID just in case it happens again and we aren't watching the alert log like hawks.
Subscribe to:
Posts (Atom)