ORACLE dblink遠端DB表truncat導致本地proc執行報錯ORA-12012&ORA-06550&PLS-00907

    最近，一客戶申訴，其oracle資料庫一業務每天定時執行的儲存過程都會報錯：ORA-12012&ORA-06550&PLS-00907，但是，報錯後再次執行就正常；該儲存過程原先設定的執行時間是早上5:30，首次執行報錯5:32、5：34再次執行正常，後來把儲存過程執行時間修改為5:40，首次執行還是報錯，5:44執行儲存過程不報錯。下面是問題的分析過程，供

大家參考。

    一、明確問題

   客戶反饋，近期每天5:40定時執行的儲存過程p_rpt_kpi_prem_agency資料庫告警日誌總是有報錯，報錯資訊如下：

Sun Mar 24 05:42:03 2019 

Errors in file /uhome/app/oracle/diag/rdbms/q006r03s/Q006R03S/trace/Q006R03S_j000_30628.trc: 

ORA-12012: ?? 341 

ORA-06550: 

PLS-00907: ? PRPT.P_RPT_KPI_PREM_AGENCY () 

......

Tue Mar 26 05:42:04 2019 

Errors in file /uhome/app/oracle/diag/rdbms/q006r03s/Q006R03S/trace/Q006R03S_j000_129704.trc: 

ORA-12012: ?? 341 

ORA-06550: 

PLS-00907: ? PRPT.P_RPT_KPI_PREM_AGENCY () 

......

Wed Mar 27 05:42:01 2019 

Errors in file /uhome/app/oracle/diag/rdbms/q006r03s/Q006R03S/trace/Q006R03S_j000_125276.trc: 

ORA-12012: ?? 341 

ORA-06550: 

PLS-00907: ? PRPT.P_RPT_KPI_PREM_AGENCY ()

    跟蹤檔案Q006R03S_j000_125276.trc的內容如下：

[oracle@G0ora06 trace]$ more /uhome/app/oracle/diag/rdbms/q006r03s/Q006R03S/trace/Q006R03S_j000_119531.trc 

Trace file /uhome/app/oracle/diag/rdbms/q006r03s/Q006R03S/trace/Q006R03S_j000_119531.trc 

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 

With the Partitioning, OLAP, Data Mining and Real Application Testing options 

ORACLE_HOME = /uhome/app/oracle/product/11.2.0/db_1 

System name: Linux 

Node name: G0ora06 

Release: 2.6.32-696.el6.x86_64 

Version: #1 SMP Tue Feb 21 00:53:17 EST 2017 

Machine: x86_64 

Instance name: Q006R03S 

Redo thread mounted by this instance: 1 

Oracle process number: 172 

Unix process pid: 119531, image: oracle@G0ora06 (J000) 

*** 2019-03-28 05:42:03.241 

*** SESSION ID:(1589.61291) 2019-03-28 05:42:03.241 

*** CLIENT ID:() 2019-03-28 05:42:03.241 

*** SERVICE NAME:(SYS$USERS) 2019-03-28 05:42:03.241 

*** MODULE NAME:() 2019-03-28 05:42:03.241 

*** ACTION NAME:() 2019-03-28 05:42:03.241 

ORA-12012: ?? 341 

ORA-06550: 

PLS-00907: ? PRPT.P_RPT_KPI_PREM_AGENCY () 

[oracle@G0ora06 trace]$ 

2、分析材料收集

    問題資料庫告警日誌，dba_hist_active_sess_history部分資料，儲存過程涉及物件的部分歷史資訊。

3、問題分析

    從問題資料庫儲存過程p_rpt_kpi_prem_agency相關的告警及跟蹤檔案資訊看，報錯集中在：ORA-12012&ORA-06550&PLS-00907

    檢視問題資料庫伺服器系統日誌messages，沒有發現與資料庫相關明顯的報錯提示：

    檢視問題資料庫問題時間段的負載，發現問題時間段資料庫負載很低，只有67左右，排除資料庫高負載導致

    檢視問題時間段資料庫內部的等待事件，發現問題時間段前後有dblink讀取資料。

    觀察問題儲存過程，發現確實有dblink相關的程式碼：

SELECT trunc(p_start_date) report_day, 
t.department_code, 
SUM(t.auto_num_renewable_mtd) auto_num_renewable_mtd, 
SUM(CASE 
WHEN t.channel_type2_renewed = '17' THEN 
t.auto_num_renewed_mtd 
ELSE 
0 
END) auto_num_renewed_mtd, 
0 auto_num_renewable_ytd, 
0 auto_num_renewed_ytd 
FROM edw_opr.rpt_t_auto_renew_ratio_mtd@misdw t 
WHERE t.report_month = t.renewable_month 
AND t.report_month = to_char(p_start_date, 'yyyymm') 
AND t.channel_type2_renewable = '17' 
GROUP BY t.department_code 
......
SELECT trunc(p_start_date) report_day, 
t.department_code, 
0 auto_num_renewable_mtd, 
0 auto_num_renewed_mtd, 
SUM(t.auto_num_renewable_ytd) auto_num_renewable_ytd, 
SUM(CASE 
WHEN t.channel_type2_renewed = '17' THEN 
t.auto_num_renewed_ytd 
ELSE 
0 
END) auto_num_renewed_ytd 
FROM edw_opr.rpt_t_auto_renew_ratio_ytd@misdw t 
WHERE t.report_month = to_char(p_start_date, 'yyyymm') 
AND t.channel_type2_renewable = '17' 
GROUP BY t.department_code

  登陸dblink misdw資料庫檢視dblink遠端資料庫告警日誌，問題時間段內沒有明顯的資料庫報錯；

  檢視dblink misdw資料庫負載，發現dblink遠端資料庫負載很低，排查dblink遠端資料庫高負載引起job執行報錯：

     本地檢視dblink select語句相關表的last_ddl_time時間，排除表RPT_INTF_KPI_RENEW定義發生變更的影響：

  檢視dblink misdw遠端資料庫相關的表物件的lat_ddl_time，發現 表 edw_opr.rpt_t_auto_renew_ratio_mtd、

edw_opr.rpt_t_auto_renew_ratio_ytd 的 lat_ddl_time剛好在問題儲存過 程 p_rpt_kpi_prem_agency執行開始時間5:40

之前。

  由此可以斷定：由於dblink遠端資料庫表發生truncate、drop、alter等ddl定義導致本地儲存過程執行報錯：

ORA-12012& ORA-06550& PLS-00907。但是，經過溝通misdw儲存過程定時對錶進行truncate，表的結構並未發生改變，

因此，儲存過程dblink遠端庫表發生DDL變更，儲存過程執行時因相關dblink 物件misdw 失效重新編譯拋錯， dblink

編譯透過後主儲存過程 p_rpt_intf_kpi_prem_agency繼續正常執行，未影響主儲存過程 p_rpt_intf_kpi_prem_agency

執行結果。

4、分析總結

  由於dblink遠端資料庫表 edw_opr.rpt_t_auto_renew_ratio_mtd、 edw_opr.rpt_t_auto_renew_ratio_ytd 發生

truncate的 ddl定義變更導致本地儲存過程執行報錯： ORA-12012& ORA-06550& PLS-00907。但是，misdw儲存過程只定

時對錶進行truncate， 表的結構並未發生改變， 因此， 儲存過程因相關dblink物件misdw失效導致報錯， dblink misdw

編譯透過後主儲存過程 p_rpt_kpi_prem_agency繼續正常執行， 未影響主儲存過程 p_rpt_kpi_prem_agency 執行結果。

5、建議

  a、在 101.99.11.22相關作業對標 edw_opr.rpt_t_auto_renew_ratio_mtd、 edw_opr.rpt_t_auto_renew_ratio_ytd 後，

及時編譯 本地儲存過程 p_rpt_kpi_prem_agency；

  b、因儲存過程相關物件在儲存過程執行時會重新編譯，不影響程式正常執行，報錯 ORA-12012& ORA-06550& PLS-00907

可忽略。