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: Read the rest of this entry . . .
