Oracle数据库数据恢复、性能优化

找回密码
注册
搜索
热搜: 活动 交友 discuz
发新帖

158

积分

1

好友

8

主题
1#
发表于 2012-6-28 14:33:12 | 查看: 6699| 回复: 3
环境:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
ORACLE_HOME = /oracle/app/oracle/product/10.2/db
System name:    AIX
Node name:      RESDB1
Release:        1
Version:        6
Machine:        00CD21164C00
Instance name: nrms1



今天在alert里面发现这个错误,ORA-07445: exception encountered: core dump [opiptp+0040] [SIGSEGV] [Address not mapped to object] [0x11113A360]我metlink检查了一下,发现这篇文章描述如下:

Connection Is Lost On Insert or Select Connect by With ORA-7445 [opiptp()+50] [ID 436199.1]
修改时间 06-APR-2012     类型 PROBLEM     状态 PUBLISHED

In this Document
[size=-1]  Symptoms
  Cause
  Solution
  References



Applies to:Oracle Server - Enterprise Edition - Version: 10.2.0.2 to 10.2.0.5 - Release: 10.2 to 10.2
Information in this document applies to any platform.
***Checked for relevance on 06-APR-2012***SymptomsOn 10.2.0.3 in Production:
When attempting to insert append to global temporary table the following error occurs.

connection is lost with ORA-03113, logged for the client

The following error logged at the alert log file ORA-07445: Exception aufgetreten: CORE Dump [opiptp()+50] [SIGSEGV] [Address not mapped to object] [0xB718CABC] [] []

The stack trace is similar to:
opiptp opiptc opiexe opipls opiodr rpidrus

Users cannot insert append or select from  table.
CauseThe issue is caused by the following Bug 5968363 : ORA-7445 [_OPIPTP+50] FROM A COMPLEX QUERY WITH A CONNECT BY, WITH, FUNCTION CALL .

The Bug is not backport able , This issue is fixed in 11.1.0.7

SolutionPossible workarunds are:

1. set the parameter "_optimizer_connect_by_cost_based" to false , at the database level :
SQL> alter system set "_optimizer_connect_by_cost_based" = false scope=both ;


OR

2.  set the parameter "_optimizer_connect_by_cost_based" to false , at the session level :

SQL> alter session set "_optimizer_connect_by_cost_based" = false;


OR

3. Add  /*+ NO_CONNECT_BY_COST_BASED */ hint to the query, instead of setting the parameter


The effect of this parameter , is it just disables the optimizer to use cost-based transformation for connect by queries only.




----but 这个里面的参数和我的不一样:文章是opiptp()+50。我的是:[opiptp+0040]



问题:我找的这篇文章对不对啊?

我看了trace文件 nrms1_ora_30147244.rar (352.08 KB, 下载次数: 1139) ,是一个查询引起的,我发来是想看看刘大是怎么分析的,学习一下练习读懂trace文件。请刘大在写分析过程的时候多写一点,谢谢。
2#
发表于 2012-6-29 15:00:30
10.2.0.4.0 - 64bit +   AIX

ksedmp: internal or fatal error
ORA-07445: exception encountered: core dump [opiptp+0040] [SIGSEGV] [Address not mapped to object] [0x11113A360] [] []
Current SQL statement for this session:
SELECT NAME FROM (SELECT * FROM RM.AREA CONNECT BY PRIOR PARENT_AREA = AREA_ID START WITH AREA_ID = :B1 ) WHERE AREA_LEVEL = :B2
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
700000cdecb1460       592  package body RM.PG_NM_INTERFACE_FUN
700000cb6d7e330      1054  package body RM.PG_NM_IMPORT_RSC
700000c203ebf28         3  anonymous block
----- Call Stack Trace -----




stack call rpidrv= > rpiswu2= > rpidru=> skgmstack=> rpidrus=> opiodr=>opipls=>opiexe=>opiptc=>opiptp=> 报错


SQL语句:

SELECT NAME
  FROM (SELECT *
          FROM RM.AREA
        CONNECT BY PRIOR PARENT_AREA = AREA_ID START WITH AREA_ID = :B1)
WHERE AREA_LEVEL = :B2


ODM FINDING:

Connection Is Lost On Insert or Select Connect by With ORA-7445 [opiptp()+50]





Answer:


1. 找的没错

2. 我是使用  stack call opiptc + opiptp 在MOS上搜索的

回复 只看该作者 道具 举报

3#
发表于 2012-7-11 09:50:20
hi
刘大
  我想问下
为什么stack 从stack call rpidrv 这个开始呢?
谢谢

回复 只看该作者 道具 举报

4#
发表于 2012-7-13 15:03:42
这个库好熟悉啊!!!!

回复 只看该作者 道具 举报

您需要登录后才可以回帖 登录 | 注册

QQ|手机版|Archiver|Oracle数据库数据恢复、性能优化

GMT+8, 2024-12-26 01:45 , Processed in 0.078941 second(s), 24 queries .

Powered by Discuz! X2.5

© 2001-2012 Comsenz Inc.

回顶部
TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569