最近 一客户申诉 其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

可忽略。

来自 “ ITPUB博客 ” 链接 http://blog.itpub.net/29357786/viewspace-2639665/ 如需转载 请注明出处 否则将追究法律责任。

转载于:http://blog.itpub.net/29357786/viewspace-2639665/