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.
