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.
No comments:
Post a Comment