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
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.
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?