Why Does Force Refresh Produce Huge Archived Logs

In one day, I found archived logs grew very fast, they were generated almost 10 times comparing to normal business days, and could make the archive destination full. As we know, once the destination of archived logs is full, your database will stop.

In the first place, I thought it was data migration. The data migration can generate a lot of redos. But, I knew there was no schedule for data migration recently. So, I checked the performance of the server and found J000 was very busy at that moment. What job will generate a lot of redo logs? I guess it could be snapshots caused the problems.

Some developers choose Force Refresh instead of Fast Refresh for bypassing the possibilities of failures resulted from Fast Refresh, which means, if the MV is unable to go Fast Refresh, it will perform a Complete Refresh without explicit errors. This could be a blindside of space management, especially when the master table was extremely large. So, the consequence is that you will see a lot of redo logs are generated.

To ease or resolve the problem, you can think of several ways:

1. Increase the interval of MVs:
   You can make the snapshot doing a complete refresh less often and ease your space problem, but the root cause is still there.
2. Set refresh type as Fast Refresh instead of Force Refresh:
   If the master table is very large, you should choose the appropriate option. But before changing the refresh type, you'd better ensure the respective MV logs existent, otherwise, the operations will fail.
3. Set the MV with NOLOGGING mode:
   If you insist to remain the refresh type. You can alter the MV as NOLOGGING, but the drawback is the MV is not recoverable from now on. Please note that, the option has no effect if the database is playing the primary role under data guard.