The Invalid Object of Oracle Database
异常症状
在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)
[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