Beware of Failed Refreshes, Troubleshoot Broken Links!!

Author: Mark Alex

Most of the times we face the scenario where some database links are broken and DBMS_JOB refreshes are failed. In such situation we get ORA-12012 error in the alert log. This error is followed by a number of other errors such as ORA-12547 error , ORA-12170 and ORA-12541 error.

ORA-12012: error on auto execute of job number
ORA-12547: TNS:lost contact
ORA-12170: TNS:Connect timeout occurred
ORA-12541: TNS:no listener, or any other of the network connection failures.

In order to troubleshoot this problem we need to find out the materialized view name from DBA_JOBS and then peak into the MASTER_LINK column of DBA_MVIEWS.

Scheduling refreshes by using materialized refresh groups is a common task and those who follow this approach have to regularly encounter failed refresh jobs due to broken database links.

An efficient approach to solve this problem is to parse down the job content, consider materialized view refresh groups and output the database links involved in this problem. The solution works for single materialized view refreshes and for refresh groups both.

WITH MYREFRESHJOB AS
(
   SELECT REPLACE(REPLACE(REPLACE(MYVALUE, DBMS_REFRESH.REFRESH(''?', ''), '?.?', '.'), '?'');') MY_VAR
   FROM DBA_JOBS
   WHERE MYVALUE LIKE 'DBMS_REFRESH.REFRESH%' AND JOB=&JOB_NO
),

MYREFRESHJOB2 AS
(
   SELECT SUBSTR(MY_VAR,1,INSTR(MY_VAR,'.')-1) MY_VAR2, SUBSTR(MY_VAR,INSTR(MY_VAR,'.')+1) MY_VAR
   FROM MYREFRESHJOB
)

SELECT DISTINCT MASTER_LINK
FROM DBA_MVIEWS
WHERE
(
   MYOWNER,MY_MV_NAME
)
IN
(
   SELECT MY_VAR2, MY_VAR
   FROM MYREFRESHJOB2
)
UNION ALL
   SELECT DISTINCT MASTER_LINK
   FROM DBA_MVIEWS
   WHERE (MYOWNER,MY_MV_NAME) IN
   (
      SELECT MYOWNER, MYNAME
      FROM DBA_REFRESH_CHILDREN
      WHERE (RMYOWNER,RMYNAME)
      IN
      (
         SELECT MY_VAR2, MY_VAR FROM MYREFRESHJOB2
      )
   );

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   The Power of Oracle 11g Sequence Enhancements!
   Web Applications: Oracle-SQL Database Security
   Exciting Oracle 11g features you should not miss to know!!
   Adding Custom Messages to Oracle Alert Log!!