ORA-600[15267]解决过程

最终的解决办法为：

alter system set  optimizer_secure_view_merging=FALSE  scope=both sid="*";

_______________________________

Abstract
ORA-00600: INTERNAL ERROR CODE, ARGUMENTS: [15267], [], [], [], [], [], [], [] 

01-JUN-09 01:19:49 GMT
DATA COLLECTED
===============
LOG FILE
---------------
Filename = alert_orcl2.log
See ...
Sat May 30 14:04:12 2009
Errors in file /oracle/admin/orcl/bdump/orcl2_mmon_20724.trc:
ORA-00600: internal error code, arguments: [15267], [], [], [], [], [], [], []

FILE VERSIONS
----------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
ORACLE_HOME = /oracle/product/10.2/database
System name: HP-UX
Node name: ywxt02
Release: B.11.23
Version: U
Machine: ia64
Instance name: orcl2
Redo thread mounted by this instance: 2
Oracle process number: 16
Unix process pid: 20724, image: oracle@ywxt02 (MMON)

==>Customer's version is 10.2.0.3.0
==>Customer's platform. is HP-UX ia64

TRACE FILE
---------------
Filename = orcl2_mmon_20724.trc
See ...
*** 2009-05-30 14:04:12.683
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [15267], [], [], [], [], [], [], []

Current SQL
----------------
SELECT INSTANCE_NAME,
HOST_NAME,
NVL(GVI_STARTUP_TIME, SYSTIMESTAMP) - INTERVAL '1' SECOND AS SHUTDOWN_TIME
FROM
(SELECT RRI.INSTANCE_NAME AS INSTANCE_NAME,
RRI.HOST_NAME AS HOST_NAME,
FROM_TZ(RRI.STARTUP_TIME, '+00:00') AS RRI_STARTUP_TIME,
DBMS_HA_ALERTS_PRVT.INSTANCE_STARTUP_TIMESTAMP_TZ(GVI.STARTUP_TIME) AS GVI_STARTUP_TIME
FROM
RECENT_RESOURCE_INCARNATIONS$ RRI
LEFT OUTER JOIN GV$INSTANCE GVI
ON GVI.INSTANCE_NAME = RRI.RESOURCE_NAME WHERE RRI.RESOURCE_TYPE = 'INSTANC
E'
AND :B2 = RRI.DB_UNIQUE_NAME
AND :B1 = RRI.DB_DOMAIN)
WHERE GVI_STARTUP_TIME IS NULL
OR GVI_STARTUP_TIME > RRI_STARTUP_TIME
GROUP BY INSTANCE_NAME, HOST_NAME, GVI_STARTUP_TIME

Call Stack Trace
--------------------
ksedst eCheck piodr 0 run exec bs

BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Due to this issue,the directory of cdump quickly filled with core file and the directory of bdump quickly filled w
ith mmon trace file

ISSUE VERIFICATION
===================
Verified the issue in the log file as noted below:

LOG FILE
-----------------------
Filename = alert_orcl2.log
See the following error:
Sat May 30 14:04:12 2009
Errors in file /oracle/admin/orcl/bdump/orcl2_mmon_20724.trc:
ORA-00600: internal error code, arguments: [15267], [], [], [], [], [], [], []

01-JUN-09 02:29:45 GMT
RESEARCH
=========
(Note: This is INTERNAL ONLY research. No action should be taken by the customer on this information.
This is research only, and may NOT be applicable to your specific situation.)

WEBIV SEARCH
---------------------
Using keyword: ORA 00600 15267
Find
1.
Article-ID: Note 466341.1
Title: ORA-00600 [15267] When Running A Query With A view Which Is
Inside Another view Or An Inline view
Symptoms
o Running a query if a view is inside another view or an inline view, you can get error
ORA-00600: [15267].
Stack trace is similar to:
kgeriv kgeasi kkdlobo kkqtsChkSameOwners
-- OR --
ksedmp kgeriv kgeasi kkdlobo vopSecChkSameOwners

Applies to:
Oracle Server - Enterprise Edition - Version: 10.2 This problem can occur on any platfor
m.

This is internal Bug 5655388
Abstract: TT11.1EVENT10049: ORA-600 INTERNAL ERROR [15267]

During secure view merging, if a view is inside another view, we try to
pin the outer view object, even if it is an inline view. This may cause
an ora-600 [15267] with kkqtsChkSameOwners or vopSecChkSameOwners in the stack.

A possible workaround is to set the parameter
optimizer_secure_view_merging = FALSE

2.
Bug 5655388
Abstract: TT11.1EVENT10049: ORA-600 INTERNAL ERROR [15267]
This issue is fixed in: 11.1

WEBIV SEARCH
---------------------
Using keyword: optimizer_secure_view_merging
Find
Article-ID: Note 567135.1
Topic: INIT.ORA Parameters (introduced in 10g)

In 10g / 11g the parameter can be TRUE or FALSE with the following
behaviour:
FALSE This is similar to behaviour in earlier releases such as 9.2.
Oracle allows views and PLSQL functions in a query to be
merged / moved around during query optimization in order to
try and get the lowest cost execution plan even if the
views / functions have different owners.

TRUE This is the default value.
Oracle does not use view merging or predicate move-around/pushing
in some cases where the query has user-defined functions and
views (regular, inline, unnested,etc) if the views / functions
are owned by someone other than the person running the query.

If OPTIMIZER_SECURE_VIEW_MERGING = FALSE then individual users can be
given permission to merge other users views etc.. by granting them the
MERGE ANY VIEW privilege.

01-JUN-09 03:33:37 GMT
.
CAUSE DETERMINATION
====================
This is caused by internal Bug 5655388
Abstract: TT11.1EVENT10049: ORA-600 INTERNAL ERROR [15267]

During secure view merging, if a view is inside another view, we try to
pin the outer view object, even if it is an inline view. This may cause
an ora-600 [15267] with kkqtsChkSameOwners or vopSecChkSameOwners in the stack.

CAUSE JUSTIFICATION
====================
The following justifies how the issue is related to this specific customer:
1 This customer is running a query with a view GV$INSTANCE inside an inline view, and get error
ORA-00600: [15267].

2 Customer has a call stack trace
ksedmp

3 This customer's above symptom matches the description in Note 466341.1 and Bug
5655388
Symptoms
Running a query if a view is inside another view or an inline view, you can get error
ORA-00600: [15267].
Stack trace is similar to:
ksedmp kgeriv kgeasi kkdlobo vopSecChkSameOwners

3. Bug 5655388 affects versions 10.2
this customer is on 10.2.0.3.0, so could be affected by this bug.

4. This problem can occur on any platform, so this customer matches the platform. req
uirement.

This is explained in the following Bug and Bug Description Note:
Bug 5655388 Abstract: TT11.1EVENT10049: ORA-600 INTERNAL ERROR [15267]
Note 466341.1 ORA-00600 [15267] When Running A Query With A view Which Is Inside Another v
iew Or An Inline view

.
PROPOSED SOLUTION(S)
======================
1. Upgrade to 11.1
-Or-
2. Use workaround, set the parameter optimizer_secure_view_merging = FALSE

PROPOSED SOLUTION JUSTIFICATION(S)
====================================
Bug 5655388 is fixed on 11.1,and there is no one-off patch available for bug5655388 for this customer's platform.
But according to Note 466341.1, set the parameter optimizer_secure_view_merging = FALSE should be a workaround.
According to Note 567135.1, if this parameter is set to FALSE, its behavior. is "similar t
o behaviour in earlier releases such as 9.2." This indicates there will not be
'secure view merging' feature which is introduced in 10g. So it can avoid this e
rror.

UPDATE
=======

SOLUTION / ACTION PLAN
=======================
To implement the solution, please execute the following steps:
1 Log in sqlplus as sysdba
$>sqlplus "/ as sysdba"
2 set optimizer_secure_view_merging=FALSE;
SQL> alter system set optimizer_secure_view_merging=FALSE;
3 Shutdown and restart database, check the optimizer_secure_view_merging has been set correctly
SQL> show parameter optimizer_secure_view_merging

Thank you
   

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

转载于:http://blog.itpub.net/16426127/viewspace-605628/