Pages

Tuesday, March 29, 2016

ORA-600 When running SELECT SQL via DB_LINK on a local Instance

==========================
Scenario
==========================
Oracle version is 11.1.0.7
In oracle alert log, there are ORA-600 messages, and coredump is occurring:

From alert.log
*** 2016-03-14 01:00:11.316
*** SESSION ID:(873.36749) 2016-03-14 01:00:11.316
*** CLIENT ID:() 2016-03-14 01:00:11.316
*** SERVICE NAME:(igt) 2016-03-14 01:00:11.316
*** MODULE NAME:(oracle@my_server (TNS V1-V3)) 2016-03-14 01:00:11.316
*** ACTION NAME:() 2016-03-14 01:00:11.316

Dump of change vector:
TYP:0 CLS: 1 AFN:21 DBA:0x0542c56b OBJ:468668 SCN:0x0000.f027f373 SEQ:  5 OP:10.2 ENC:0

*** 2016-03-14 01:00:11.316
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x7FFFCA3EF125] [PC:0x7B12B32, kdvlin()+52]
Incident 75851 created, dump file: /software/oracle/diag/rdbms/igt/igt/incident/incdir_75851/igt_ora_22056_i75851.trc
ORA-07445: exception encountered: core dump [kdvlin()+52] [SIGSEGV] [ADDR:0x7FFFCA3EF125] [PC:0x7B12B32] [Address not mapped to object] []

Cleaning up copy latch 0
Copy latch cleanup completed
Incident 75852 created, dump file: /software/oracle/diag/rdbms/igt/igt/incident/incdir_75852/igt_ora_22056_i75852.trc
ORA-00600: internal error code, arguments: [4153], [16], [], [], [], [], [], [], [], [], [], []
ORA-00607: Internal error occurred while making a change to a data block
ORA-00602: internal programming exception
ORA-07445: exception encountered: core dump [

*** 2016-03-14 01:00:14.912
----- Error Stack Dump -----
ORA-00600: internal error code, arguments: [4153], [16], [], [], [], [], [], [], [], [], [], []
ORA-00607: Internal error occurred while making a change to a data block
ORA-00602: internal programming exception
ORA-07445: exception encountered: core dump [kdvlin()+52] [SIGSEGV] [ADDR:0x7FFFCA3EF125] [PC:0x7B12B32] [Address not mapped to object] []
----- Current SQL Statement for this session (sql_id=fbbvh8j9v9fhx) -----


SELECT /*+ OPAQUE_TRANSFORM */ "SCENARIO_ID","IMSI","MSISDN","EVENT_TYPE_ID","COUNTRY_ID","NETWORK_ID","VLR_ID","CAMPAIGN_ID","MESSAGE_TYPE","MESSAGE_ID","TS_LAST_MODIFIED","DAY","MESSAGE_TEXT","VISIT_ID","CAMEL_SERVICE_KEY","CORRELATION_ID" FROM "ONLINE_EVENTS" WHERE "TS_LAST_MODIFIED">=:1 AND "DAY"=:2 AND "TS_LAST_MODIFIED"<=:3

Why the code is failing on a simple SELECT part?
==========================
Investigation

==========================
What is weird in this error, is the hint /*+ OPAQUE_TRANSFORM */ .
This hint does not exists in the code. Where did it come from ?


Per Oracle documentation:

What is OPAQUE_TRANSFORM Hint and how to Control it (Doc ID 780503.1)
APPLIES TO: 
Oracle Database - Enterprise Edition - Version 10.2.0.3 and later

What is OPAQUE_TRANSFORM usage :
The OPAQUE_TRANSFORM hint is to help with the transformation of datatype when certain type of operations are done within the database.  
For example object types .
It is also used for a insert-as-remote-select operation on a remote database.
 This hint should not interfere with the query optimizer plan.

-Note that if local site is 11g and remote is  11g server, this opens 2 sessions 

   on the remote database and OPAQUE_TRANSFORM hint gives DX LOCK deadlock. 

==========================
Resolution

==========================
The SELECT statement in question is querying "ONLINE_EVENTS" table, inserting results into a local table.

After checking in USER_OBJECTS and in USER_SYNONYMS, it appears that ONLINE_EVENTS is in fact a synonym to a table in a remote schema, connected by DB LINK.

Normally that would be OK, but in this specific installation, both schemas were installed on the same instance.

So USER_A is querying table USER_B.ONLINE_EVENTS

Since both schemas are on the same instance, the DB LINK usage is in fact redundant.

After replacing the code to work with direct select, i.e. SELECT * FROM USER_B.ONLINE_EVENTS rather than via DB_LINK, the ORA-600 no longer appeared.

It seems that this is a bug in Oracle 11.1.0.7.

No comments:

Post a Comment