How To Create APS Related Snapshots And mLogs (Doc ID 415135.1)

Last updated on APRIL 19, 2023

Applies to:

Goal

APS Related Snapshots And MLOGs can become corrupted in many ways.

Table reorganization is a common way to corrupt the MLOG.

Example errors:

ORA-12008: error in materialized view refresh path
ORA-30036: unable to extend segment by 8 in undo tablespace 'APPS_UNDOTS1'
ORA-12032: cannot use rowid column from snapshot log on "ONT"."OE_ORDER_LINES_ALL"

ORA-12048: error encountered while refreshing materialized view "MRP"."MRP_FORECAST_DATES_SN"
ORA-12034: materialized view log on "MRP"."MRP_FORECAST_DATES" younger than last refresh.

etc.

The objective is to be able to run a FAST refresh successfully.  If a FAST refresh can be run then it is good to go.

However, if a FAST refresh just won't work then the simplest method to recover is to drop and recreate the problematic snapshot and MLOG.  It is just as quick as complete refresh and is the easiest way to reorg the object etc.

Normally, running Refresh Collection Snapshot in Complete mode is NOT required after applying Collection RUP patches. Normally a Fast refresh that is performed by the collection is all that is needed.

However, it is possible that some snapshots can be corrupted in many ways and may take increasingly involved steps to correct.

After applying a Collection RUP patch run Refresh Collection Snapshot in FAST mode.  The RUP patch will have set automatically set the profile  (on the Source instance) MSC:Source Setup Required to 'Y'

- IF all is well then return to normal Collection procedures.
- IF errors such as

ORA-12048: error encountered while refreshing materialized view "INV"."MTL_SUPPLY_SN"
ORA-12034: materialized view log on "INV"."MTL_SUPPLY" younger than last refresh

- THEN you will need to run Refresh Collection Snapshot in Complete mode for that particular snapshot.
- Then Rerun Refresh Collection Snapshot in FAST mode for that particular snapshot to verify it is fixed.
- If running Refresh Collection Snapshot in Complete mode does not clear the error then you will need to follow the drop and recreate the snapshot and mlog procedure in this note.
- IF the snapshot refreshes OK in FAST mode but has a performance problem
- THEN you will need to run Refresh Collection Snapshot in AUTOMATIC mode for that particular snapshot. 

- AUTOMATIC mode will truncate the related MLOG and then do a COMPLETE refresh.
- IF a performance issue persists after AUTOMATIC mode refresh then you may need to follow the drop and recreate the snapshot and mlog procedure in this note.

Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.