ORA-00600 troubleshooting and analysis examples

ORA-00600 troubleshooting and analysis examples

Yesterday to deal with a ORA-00600 of errors, which also experienced various twists and turns, really look at the flowers in the fog, after seeing through a lot of problems are found to have a reason. At first, the Development said an error was reported when a job was running. The Oracle database version was 11.2.0.2.0.

When the problem is submitted to me, the customer has checked some information. However, there is still no conclusion.
For this problem, I started to check the database logs as usual.
A ora-00600 error occurred during that time period.
Wed Jun 10 13:47:17 2015
Errors in file/opt/app/oracle/dbccbspr1/diag/rdbms/PRODB/trace/prodb_p070_assist.trc (incident = 2124332 ):
ORA-00600: internal error code, arguments: [srsnext_3], [], [], [], [], [], [], [], [], [], []
Incident details in:/opt/app/oracle/dbccbspr1/diag/rdbms/PRODB/incident/incdir_2124332/prodb_p070_1__i2124332.trc
Wed Jun 10 13:48:04 2015
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.

I encountered several strange ora errors before and after this ora-00600 error. I sorted the error logs by time.

Thread 1 advanced to log sequence 82829 (LGWR switch)
Current log #1 seq #82829 mem #0:/dbccbPR1/oracle/PRODB/redolog_A1/redo/redo01A. log
Current log #1 seq #82829 mem #1:/dbccbPR1/oracle/PRODB/redolog_B1/redo/redo01B. log
Archived Log entry 82894 added for thread 1 sequence 82828 ID 0xb8c6d509 dest 1:
Wed Jun 10 07:10:17 2015
ORA-00060: Deadlock detected. More info in file/opt/app/oracle/dbccbspr1/diag/rdbms/PRODB/trace/PRODB_ora_18508.trc.
Wed Jun 10 07:10:21 2015
ORA-00060: Deadlock detected. More info in file/opt/app/oracle/dbccbspr1/diag/rdbms/PRODB/trace/PRODB_ora_18593.trc.
Wed Jun 10 07:12:14 2015
ORA-00060: Deadlock detected. More info in file/opt/app/oracle/dbccbspr1/diag/rdbms/PRODB/trace/PRODB_ora_16505.trc.
Wed Jun 10 07:26:54 2015
ORA-00060: Deadlock detected. More info in file/opt/app/oracle/dbccbspr1/diag/rdbms/PRODB/trace/PRODB_ora_18558.trc.
Wed Jun 10 07:52:47 2015
Thread 1 advanced to log sequence 82830 (LGWR switch)
Current log #2 seq #82830 mem #0:/dbccbPR1/oracle/PRODB/redolog_A2/redo/redo02A. log
Current log #2 seq #82830 mem #1:/dbccbPR1/oracle/PRODB/redolog_B2/redo/redo02B. log

Wed Jun 10 12:09:40 2015
Exception [type: SIGSEGV, Address not mapped to object] [ADDR: 0x210] [PC: 0x193F4DF, kxfpqrclb () + 71] [flags: 0x0, count: 1]
Errors in file/opt/app/oracle/dbccbspr1/diag/rdbms/PRODB/trace/PRODB_p135_15651.trc (incident = 2116204 ):
ORA-07445: exception encountered: core dump [kxfpqrclb () + 71] [SIGSEGV] [ADDR: 0x210] [PC: 0x193F4DF] [Address not mapped to object] []
ORA-10382: parallel query server interrupt (reset)
Incident details in:/opt/app/oracle/dbccbspr1/diag/rdbms/PRODB/incident/incdir_220.204/prodb_p135_15651_i21_204.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Wed Jun 10 12:09:41 2015
Dumping diagnostic data in directory = [cdmp_20150610120941], requested by (instance = 1, osid = 15651 (P135), summary = [incident = 2116204].
Wed Jun 10 12:09:44 2015
Sweep [inc] [2116204]: completed
Sweep [inc2] [2116204]: completed
Wed Jun 10 12:09:59 2015

Wed Jun 10 13:46:27 2015
Archived Log entry 82967 added for thread 1 sequence 82901 ID 0xb8c6d509 dest 1:
Wed Jun 10 13:47:17 2015
Errors in file/opt/app/oracle/dbccbspr1/diag/rdbms/PRODB/trace/prodb_p070_assist.trc (incident = 2124332 ):
ORA-00600: internal error code, arguments: [srsnext_3], [], [], [], [], [], [], [], [], [], []
Incident details in:/opt/app/oracle/dbccbspr1/diag/rdbms/PRODB/incident/incdir_2124332/prodb_p070_1__i2124332.trc
Wed Jun 10 13:48:04 2015

Archived Log entry 82993 added for thread 1 sequence 82927 ID 0xb8c6d509 dest 1:
Wed Jun 10 14:38:53 2015
Errors in file/opt/app/oracle/dbccbspr1/diag/rdbms/PRODB/trace/PRODB_ora_15548.trc (incident = 2110436 ):
ORA-04030: out of process memory when trying to allocate 16328 bytes (koh-kghu sessi, pmucalm coll)
Incident details in:/opt/app/oracle/dbccbspr1/diag/rdbms/PRODB/incident/incdir_211_36/prodb_ora_15548_i211_36.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.

To be clearer, I associate the corresponding ORA error with the database load.
The deadlock error may be caused by an application deadlock. After a simple view of the trace log and basic confirmation, the error is handed over to the developer for analysis.
From the subsequent errors, it seems that there is no direct relationship with the subsequent problems. Let's put down the deaklock error for the moment. Therefore, it is not marked in the figure.
The other three are marked in red. We can see that there were several ORA errors in sequence during the time points when the load began to increase.

The first error.
ORA-07445: exception encountered: core dump [kxfpqrclb () + 71] [SIGSEGV] [ADDR: 0x210] [PC: 0x193F4DF] [Address not mapped to object] []
ORA-10382: parallel query server interrupt (reset)
It can be seen from the log information that it seems to be related to parallelism.
For this error. I found a similar article in metalink.
ORA-07445: [kxfpqrclb () + 72] [SIGSEGV] And ORA-10382 (Doc ID 1987833.1)
After reading solution, I was a little disappointed and needed to upgrade to version 12.2.

SOLUTION

Unpublished Bug 16682786: HIT ORA-7445 [KXFPQRCLB + 1691] WHEN RUN SHARED CURSOR TEST

The base bug is fixed in 12.2 release.

Check patch downling link for availability of patch, if patch does not exists then create an SR with oracle support with output of "opatch lsinventory-details" to get a patch ..
It is not clear for the moment. Continue to skip the next error.
Second Error
ORA-00600: internal error code, arguments: [srsnext_3], [], [], [], [], [], [], [], [], [], []
For this problem, metalink does have a related article Query Fails with ORA-00600: Internal Error Code, Arguments: [srsnext_3] (Doc ID 1589589.1)
However, the solution provided by this question makes me helpless. Because there is indeed no Upgrade Plan within a period of time.

CAUSE

Bug 11852469: TS11.2.0.3V3-TRC-SRSNEXT.

Rediscovery information:

If the srsnext_3 internal error is raised and the query involves statistical functions or other aggregates that are treated as distinct aggregates then you may be encountering this problem.

SOLUTION

Apply patch 11852469 if it exists for your version/platform

Or

Apply patchset 11.2.0.3 where the fix is already DED

The third error.
Archived Log entry 82993 added for thread 1 sequence 82927 ID 0xb8c6d509 dest 1:
Wed Jun 10 14:38:53 2015
Errors in file/opt/app/oracle/dbccbspr1/diag/rdbms/PRODB/trace/PRODB_ora_15548.trc (incident = 2110436 ):
ORA-04030: out of process memory when trying to allocate 16328 bytes (koh-kghu sessi, pmucalm coll)
Incident details in:/opt/app/oracle/dbccbspr1/diag/rdbms/PRODB/incident/incdir_211_36/prodb_ora_15548_i211_36.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
 
 
For this error, metalink has a related article. Diagnose and resolve ORA-4030 errors (Doc ID 1548826.1)

According to metalink, this error means that the Oracle Server process cannot allocate more memory from the operating system. The memory is composed of PGA (Program Global Area) and its content depends on the server configuration. For dedicated server processes, the memory includes a stack and the UGA (User Global Area) used to store User session data, cursor information, and sorting areas ). In multi-threaded configuration (Shared Server), UGA is allocated in SGA (System Global Area), so in this configuration UGA is not the cause of ORA-4030 errors.
Therefore, the ORA-4030 indicates that the process requires more memory (stack UGA or PGA) to execute its tasks.
It seems that the cache configuration is faulty.
First sell a customs, in fact, this is not the case, and the ora-00600 of the error, if not repeated, serious impact, is not directly to consider patching, may be done through some other methods. Some analysis methods will be used later.

For more details, please continue to read the highlights on the next page:

1 2 Next Page