How To Identify the Database Links of a Failed Refresh Job

From time to time, we get DBMS_JOB failures for failed refreshes that happen due to broken database links.

The error in the alert.log is ORA-12012: error on auto execute of job {number}, followed by one of ORA-12547: TNS:lost contact, ORA-12170: TNS:Connect timeout occurred, ORA-12541: TNS:no listener, or any other of the network connection failures.

I usually have a quick look into DBA_JOBS to find out the materialized view (MV) name and then peak into the DBA_MVIEWS.MASTER_LINK column.

However, I have a client with refreshes scheduled using MV refresh groups, and it took me a while to recall the view name, asking around and RTFM’ing. Since this situation comes up regularly, I wrapped up a quick script that parses job content, takes in account MV refresh groups, and outputs the database link(s) involved. I think it could be useful to few others so here it goes:

with rv as
(select replace(replace(replace(what,
           ′dbms_refresh.refresh(′′″′, ′′), ′″.″′, ′.′), ′″′′);′) v
           from dba_jobs
           where what like ′dbms_refresh.refresh%′
             and job=&job_no
),
rv2 as
(select substr(v,1,instr(v,′.′)-1) o, substr(v,instr(v,′.′)+1) v
                from rv
)
select distinct master_link
   from dba_mviews
   where (owner,mview_name) in (select o, v from rv2)
union all
select distinct master_link
   from dba_mviews
   where (owner,mview_name) in
      (select owner, name
          from dba_refresh_children
          where (rowner,rname) in (select o, v from rv2)
      );

A few minutes later, I saw in our tracking system a reference (thanks to Marc Billette) to some documentation with few useful scripts for MV troubleshooting. The new result is much nicer:

SELECT DISTINCT mv.master_link
  FROM dba_rgroup rg, dba_refresh_children rc, dba_mviews mv
 WHERE rg.job = &job_no
   AND rc.rowner = rg.owner
   AND rc.rname = rg.name
   AND rc.type = 'SNAPSHOT'
   AND mv.mview_name = rc.name
   AND mv.owner = rc.owner;

Unfortunately, it seems that someone was messing around with the refresh jobs, and some of them were not associated with any refresh group in DBA_RGROUP. Now I have the task of cleaning up the refresh jobs mess, but that’s another story.

I decided I should leave the first query just in case any readers are in the same situation, even though it’s not very clear SQL compared to the second version. In addition, the first query works for both single MV refreshes and for refresh groups.