MV on remote/master table fails to resume syncing (after master db host was temporarily unreachable)

connectivitydblinkmaterialized-vieworacle

I have a fast-refresh materialized view in a target db (18c) that syncs from a remote table/master db (19c):

--master db (19c)
create materialized view log on master.workorder with primary key;  

--target db (18c)
create materialized view target.wo_mv 
build immediate 
refresh fast 
start with sysdate 
next sysdate + (15/(60*60*24))
as (
select
    workorderid
    --other columns
from
    master.workorder@my_dblink         
);

My organization has ongoing issues where the master database's host becomes intermittently "unreachable". This happens daily and lasts for approximately 15 minutes. It's a serious issue that is being investigated and is beyond my control.

My DBA tells me that we get ORA-12514 & ORA-3150 errors in the target db when the master db is unreachable.

ORA-12514 TNS:listener does not currently know of service requested in connect descriptor

ORA-03150: end-of-file on communication channel for database link

Problem:

When the master db becomes unreachable, of course, the MV in the target db fails to sync. That makes sense.

However, what doesn't make sense to me is: the MV in the target db doesn't resume syncing once the master db comes back online.

  • Records fail to sync from the master MV logs to the target MV. The records just sit in the master MV log table and nothing happens.

Question:

As a novice, I would have expected the MV in the target db to automatically resume once the master db came back online. Everything else that uses the dblink between the two dbs works fine. Just not the MV sync.

Is there a reason why the MV in the target db wouldn't automatically resume when the master db comes back online?

Best Answer

If the controlling scheduled DBMS_JOB job fails, it may need to be manually reset/restarted. If you want more flexibility/resiliency with this, use Oracle Scheduler to control the refresh execution, rather than the default DBMS_JOB. Do this by leaving the start and next portions out of the MV definition:

create materialized view target.wo_mv 
build immediate 
refresh fast 
as (
select
    workorderid
    --other columns
from
    master.workorder@my_dblink         
);

Then build a Scheduler job with a call to DBMS_REFRESH to refresh your MV:

begin
   dbms_refresh.make(
     name                 => '15_MINUTE_REFRESH',
     list                 => '',
     next_date            => sysdate,
     interval             => '/*15:mins*/ sysdate + 15/(60*24)',
     implicit_destroy     => false,
     lax                  => false,
     job                  => 0,
     rollback_seg         => null,
     push_deferred_rpc    => true,
     refresh_after_errors => true,
     purge_option         => null,
     parallelism          => null,
     heap_size            => null);
end;
/

begin
   dbms_refresh.add(
     name => '15_MINUTE_REFRESH',
     list => 'WO_MV',
     lax  => true);
end;
/

commit;

Be sure to run all commands as the schema/user which owns the materialized view.

Also see here: https://oracle-base.com/articles/19c/dbms_job-jobs-converted-to-dbms_scheduler-jobs-19c#mview-refresh-groups

Related Question