异常症状

在Oracle EBS系统中可以正常打开物料编辑页面,输入料号、描述等,选择物料模板,点击 保存按钮保存;此时切换到组织属性或组织分配页,再切换回物料属性页或者按料号查找该 物料,均无法找到该物料的信息;直接在数据库中查找该物料为空。

异常确认

登录到Oracle ERP系统,输入物料确实无法保存;最大化编辑页面,Oracle Forms左下角没 有保存数据的事务操作提示。

环境

  • Oracle RDBMS : 11.1.0.7.0
  • Oracle Applications : 12.1.1

异常解决

在ERP服务器上部署了一些定时任务,其中每天结束前(23:55)会定时检查数据库系统的警 告日志,若有错误,则会发送邮件通知。

2012-10-29 收到来自此ERP系统的警告日志邮件通知。

警告日志

alert_PROD.log.2012-10-28

Fri Oct 26 14:24:24 2012
Errors in file /u1/PROD/prodora/db/tech_st/11.1.0/admin/PROD_poprod/diag/rdbms/prod/
PROD/trace/PROD_ora_14573.trc  (incident=22169):
ORA-00600: internal error code, arguments: [qcsgpvc3], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u1/PROD/prodora/db/tech_st/11.1.0/admin/PROD_poprod/diag/rdbms/
prod/PROD/incident/incdir_22169/PROD_ora_14573_i22169.trc
Non critical error ORA-48913 caught while writing to trace file "/u1/PROD/prodora/db/tech_st/11.1.0/admin/PROD_poprod/diag/rdbms/
prod/PROD/incident/incdir_22169/PROD_ora_14573_i22169.trc"
Error message: ORA-48913: Writing into trace file failed, file size limit [10485760] reached
Writing to the above trace file is disabled for now on...

跟踪日志

PROD_ora_14573_i22169.trc

... ...
*** 2012-10-26 14:24:24.811
*** SESSION ID:(307.48840) 2012-10-26 14:24:24.811
*** CLIENT ID:() 2012-10-26 14:24:24.811
*** SERVICE NAME:(PROD) 2012-10-26 14:24:24.811
*** MODULE NAME:(PL/SQL Developer) 2012-10-26 14:24:24.811
*** ACTION NAME:(Main session) 2012-10-26 14:24:24.811

Dump continued from file: /u1/PROD/prodora/db/tech_st/11.1.0/admin/PROD_poprod/diag/rdbms/PROD_ora_14573.trc

PROD_ora_14573.trc

ORA-00600: internal error code, arguments: [qcsgpvc3], [], [], [], [], [], [], [], [], [], [], []
========= Dump for incident 22169 (ORA 600 [qcsgpvc3]) ========
----- Beginning of Customized Incident Dump(s) -----
QCSGPVC3: icodefs yet to be processed = 1
QCSGPVC3: ico = 0x7fa94803cad0
QCSGPVC3: ico->icocop = 0x7fa9478e3ab8
QCSGPVC3: ico->icocop->colkcc = (nil)
QCDDMP: -------------------------------------------------------
QCDDMP:  qcsgpvc3_CTX: [0x7fa94801ee10]
QCDDMP:  {
QCDDMP:    ->ctxqbc: [0x7fa94801d068]
QCDDMP:    {
QCDDMP:      ->qbcqtxt: N/A
QCDDMP:      ->qbcfro: [0x7fa94801d3f0]
QCDDMP:      {
QCDDMP:        ->frooid: [(nil)]
QCDDMP:        ->frotni: MTL_SYSTEM_ITEMS_INTERFACE
QCDDMP:        ->froaid: MTL_SYSTEM_ITEMS_INTERFACE
QCDDMP:        ->frotyp = 2
QCDDMP:        ->froflg = 0x4b
... ...

其中MTL_SYSTEM_ITEMS_INTERFACE表为物料接口表,用于临时保存物料信息。无法保存物 料,可能和此表异常相关。

在MOS上找到关于ORA-600[qcsgpvc3]的一篇文档,有可能是存储过程依赖的表结构发生了变 更,重新编译存储过程时会出现此错误。

根据此文档,在R12的测试环境上进行测试,过程如下:

SQL> create table xx_qcsgpvc( c1 varchar2(60),c2 number);
Table created.
SQL> create or replace package xx_qcsgpvc_p is
  2  procedure po(c1 varchar2, c2 number);
  3  end xx_qcsgpvc_p;
  4  /
Package created.

SQL> create or replace package body xx_qcsgpvc_p is
  2  procedure po(c1 varchar2,c2 number)
  3  is
  4  begin
  5  insert into xx_qcsgpvc(c1,c2) values (c1,c2);
  6  end po;
  7  end xx_qcsgpvc_p;
  8  /
Package body created.

SQL> alter table xx_qcsgpvc rename column c2 to i_problem;
Table altered.

SQL> alter package xx_qcsgpvc_p compile body;
Warning: Package Body altered with compilation errors.

警告日志

alert_VIS.ora

Mon Oct 29 09:15:01 2012
Errors in file
/u1/VIS/visora/db/tech_st/11.1.0/admin/VIS_demoerp/diag/rdbms/
vis/VIS/trace/VIS_ora_925.trc  (incident=24961):
ORA-00600: internal error code, arguments: [qcsgpvc3], [], [], [], [], [], [], [], [], [], [], []
Incident details in:
/u1/VIS/visora/db/tech_st/11.1.0/admin/VIS_demoerp/diag/rdbms/vis/
VIS/incident/incdir_24961/VIS_ora_925_i24961.trc

跟踪日志

VIS_ora_925_i24961.trc

... ...
*** 2012-10-29 09:15:01.536
*** SESSION ID:(278.26930) 2012-10-29 09:15:01.536
*** CLIENT ID:() 2012-10-29 09:15:01.536
*** SERVICE NAME:(SYS$USERS) 2012-10-29 09:15:01.536
*** MODULE NAME:(SQL*Plus) 2012-10-29 09:15:01.536
*** ACTION NAME:() 2012-10-29 09:15:01.536

Dump continued from file:
/u1/VIS/visora/db/tech_st/11.1.0/admin/VIS_demoerp/diag/rdbms/
vis/VIS/trace/VIS_ora_925.trc
ORA-00600: internal error code, arguments: [qcsgpvc3], [], [], [], [], [], [], [], [], [], [], []
========= Dump for incident 24961 (ORA 600 [qcsgpvc3]) ========
----- Beginning of Customized Incident Dump(s) -----
QCSGPVC3: icodefs yet to be processed = 1
QCSGPVC3: ico = 0x7fc230514c90
QCSGPVC3: ico->icocop = 0x7fc2305152b8
QCSGPVC3: ico->icocop->colkcc = (nil)
QCDDMP: -------------------------------------------------------
QCDDMP:  qcsgpvc3_CTX: [0x7fc2305106b8]
QCDDMP:  {
QCDDMP:    ->ctxqbc: [0x7fc230514438]
QCDDMP:    {
QCDDMP:      ->qbcqtxt: N/A
QCDDMP:      ->qbcfro: [0x7fc2305147c0]
QCDDMP:      {
QCDDMP:        ->frooid: [(nil)]
QCDDMP:        ->frotni: XX_QCSGPVC
QCDDMP:        ->froaid: XX_QCSGPVC
QCDDMP:        ->frotyp = 2
QCDDMP:        ->froflg = 0x43
... ...

根据以上日志,可能是系统对象做过更改,导致依赖于这些对象的存储过程失效而无法正常 运行。

查看物料相关表创建编译时间

SELECT owner,
       object_name,
       object_type,
       created,
       last_ddl_time,
       TIMESTAMP,
       status
  FROM dba_objects do
  WHERE do.object_name IN ('MTL_SYSTEM_ITEMS_INTERFACE', 'MTL_SYSTEM_ITEMS_B');

物料表和物料接口表近期均未更改和重编译。

紧接着查找系统无效对象

无效对象

SELECT owner,
       object_name,
       object_type,
       status
  FROM dba_objects
 WHERE status = 'INVALID'
 ORDER BY owner,
          object_type,
          object_name;
output:
APPS        OE_ITEMS_MV         MATERIALIZED VIEW       INVALID
APPS        XX_IMPORT_MATERIEL_PCK   PACKAGE BODY        INVALID
APPS        XX_ITEM_DEFAULT_SUBINVENTORY      TRIGGER INVALID

无效对象和依赖关系

SELECT object_name,
       object_type,
       referenced_owner,
       referenced_type,
       referenced_name
  FROM user_objects,
       user_dependencies
 WHERE object_name = NAME
   AND status != 'VALID'
 ORDER BY object_name,
          object_type,
          referenced_owner,
          referenced_type,
          referenced_name;

生成无效对象的编译脚本

SELECT decode(object_type,
              'PACKAGE BODY',
              'alter package ' || owner || '.' || object_name ||
              ' compile body;',
              'alter ' || object_type || ' ' || owner || '.' || object_name ||
              ' compile;')
  FROM dba_objects
 WHERE status = 'INVALID'
   AND object_type IN ('PACKAGE BODY',
                       'PACKAGE',
                       'FUNCTION',
                       'PROCEDURE',
                       'TRIGGER',
                       'VIEW')
 ORDER BY object_type,
          object_name;

编译后,还有无效触发器APPS.XX_ITEM_DEFAULT_SUBINVENTORY

再次尝试新建物料时,依然无法保存。

通过与客制化开发人员沟通,了解到最近在尝试开发物料导入功能,以减轻物料录入的负 担。此触发器在11i中使用过,但在R12中不再使用,这里暂且drop掉该触发器或者禁用该触 发器:

DROP TRIGGER apps.XX_ITEM_DEFAULT_SUBINVENTORY;

再次尝试新建物料时,可以正常保存。

根据ORA-600[qcsgpvc3]的错误提示以及近期有客制化功能开发可以总结,因对象结构发生 变更使得系统中存在一些无效对象从而导致物料无法保存。在本例中,引起物料无法保存的 应是依赖于物料基础表MTL_SYSTEMS_ITEM_B的触发器XX_ITEM_DEFAULT_SUBINVENTORY无效, 即当对物料基础表进行插入操作时该触发器无法运行,导致整个操作失败。在客制化开发过 程中,涉及到更改标准功能或者系统标准对象等,因Oracle系统的庞大和复杂性,无法确定 做出更改后所有功能都可以正常使用;但对数据库系统的日常维护来说,定期检查和编译无 效对象是必须的,否则某些功能将不能正常运行。

脚本

检查数据库系统警告日志,结合crontab,若有ora error message,则发送邮件通知

checkalert.sh

#!/bin/sh
#abstract:
#oracle database daily alert check and send mail notifications if ora error messages occur
#history:
#2012-08-14     [email protected]         first release
#variables
script_basepath=/u1/PROD/prodora/itsection/adm/sql
mail_date=$(date +%Y-%m-%d\ %H:%M:%S)
receipt=[email protected]
hostname=$(hostname)

#path
ORACLE_HOME=/u1/PROD/prodora/db/tech_st/11.1.0
export ORACLE_HOME
PATH=/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin:
$ORACLE_HOME/bin
export PATH
ORACLE_SID=PROD
export ORACLE_SID

#sql log file
script_name=alert
sqllog="${script_basepath}/${script_name}.log"

#check alert log
if [ "${script_name}" == "alert" ]; then
   alert_basepath=/u1/PROD/prodora/db/tech_st/11.1.0/admin/PROD_poprod/diag/rdbms/prod/PROD/trace
   alert_log=${alert_basepath}/alert_$ORACLE_SID.log
   if [ -f ${alert_log} ]; then
        current_date=$(date +%F)
        current_log=${alert_log}.${current_date}
        mv  ${alert_log} ${current_log}
        touch ${alert_log}
        grep -i ORA- ${current_log} > ${sqllog}
   else
        echo -e "Oracle Instance alert log file ${alert_log} does not exist!"
        touch ${alert_log}
   fi
fi

#mail
if [ ! -e ${sqllog} ]; then
   echo -e "sqllog file ${sqllog} does not exist!"
   exit 0
fi
if [ `cat ${sqllog} | wc -l` -gt 0 ]; then
   cat ${sqllog} | mail -s "${mail_date}:${hostname} dba daily check of ${script_name}" ${receipt}
fi

参考

  • Bug 7172752 - OERI[qcsgpvc3] recompiling a package body [ID 7172752.8]
  • Invalid Objects In Oracle Applications FAQ [ID 104457.1]


blog comments powered by Disqus

Published

29 October 2012

Categories

Tags

Github