0

I have an oracle database (DB1) which, via a db-link to DB2, refreshes materialized views every midnight using a scheduled JOB.

The refresh of the MVs occurs ussing a procedure which runs over every table name in a schema and refreshed each table, one by one.

Recently, there have been electricity issues where DB2 is stationed. If DB2 is offline whilst DB1 starts its job the materialized views on DB1 become empty. Why does the refresh not simply fail if there is no connection? How can I stop this from happening?

Thanks in advance

AYR
  • 1,139
  • 3
  • 14
  • 24

1 Answers1

1

Are you sure that the refresh process isn't erroring out (in which case the error would be written to the alert log)?

Are you doing complete refreshes? Or incremental refreshes? Are you doing atomic refreshes? Or non-atomic refreshes? My guess is that you are doing a complete, non-atomic refresh (which, behind the scenes, means that you are doing a TRUNCATE and direct-path INSERT) where the TRUNCATE succeeds but the direct-path INSERT fails. You could do a complete, atomic refresh instead which would be doing a DELETE and INSERT. This will, however, be slower than a non-atomic refresh when the remote database is available and it will generate more REDO. Or, potentially, you could do an incremental refresh instead but that would, at a minimum, require that materialized view logs are created on the remote database.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • ATOMIC_REFRESH => FALSE. I cannot make any changes to the remote database (DB2) only to DB1. – AYR Oct 20 '13 at 08:04
  • @AYR - OK. Are you willing to incur the cost of a slower refresh in order to do an atomic refresh? – Justin Cave Oct 20 '13 at 22:25
  • Yes. It can take as long as it wants as long as it doesn't refresh when it shouldn't. Do I simple change it to true and that's it? – AYR Oct 23 '13 at 11:59
  • 1
    @AYR - The materialized view will still refresh even if `atomic_refresh` is set to true. The refresh will still fail. The failure will still be written to the alert.log. The difference is that the destination will be able to rollback the effects of the refresh since it was atomic. And yes, if you are calling `dbms_mview.refresh` with `atomic_refresh => false`, you would simply change that to `true` in order to get the atomic refresh behavior. – Justin Cave Oct 23 '13 at 13:55