ORA-00700: soft internal error, arguments

I have come across ORA-00700 error recently and found below error in the logs. This

ORA-00700: soft internal error, arguments: [dbgrfafr_1], [60], [60], [1], [0x7FB5DD11BC70], [], [], [], [], [], [], []
Incident details in: /oracle/diag/rdbms/orcl/ORCL/incident/incdir_75765/ORCL_ora_8274_i75765.trc

Incident details in the trace will have more detailed error. In my case below connections are coming from foglight monitoring tool . After research found that this error can occur due failing statement called from monitoring tools like TOAD,foglight..etc

Dump file /oracle/diag/rdbms/orcl/ORCL/incident/incdir_75765/ORCL_ora_8274_i75765.trc
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0
Build label: RDBMS_19.3.0.0.0DBRU_LINUX.X64_190417
ORACLE_HOME: /oracle/product/19.0.0.0/dbhome_1
System name: Linux
Node name: server1
Release: 3.10.0-1062.12.1.el7.x86_64
Version: #1 SMP Thu Dec 12 06:44:49 EST 2019
Machine: x86_64
Instance name: ORCL
Redo thread mounted by this instance: 1
Oracle process number: 130
Unix process pid: 29807, image: oracle@server1

*** 2020-05-20T00:45:56.870884-07:00
*** SESSION ID:(204.25936) 2020-05-20T00:45:56.870895-07:00
*** CLIENT ID:() 2020-05-20T00:45:56.870900-07:00
*** SERVICE NAME:(ORCL) 2020-05-20T00:45:56.870904-07:00
*** MODULE NAME:(Foglight for Oracle <5.9.5.20>) 2020-05-20T00:45:56.870909-07:00
*** ACTION NAME:(isrgorcl-db-ORCL-DBO_Instance_General) 2020-05-20T00:45:56.870914-07:00
*** CLIENT DRIVER:(jdbcthin : 12.2.0.1.0) 2020-05-20T00:45:56.870919-07:00

[TOC00000]
Jump to table of contents
Dump continued from file: /oracle/diag/rdbms/orcl/ORCL/trace/ORCL_ora_29807.trc
[TOC00001]
ORA-00700: soft internal error, arguments: [dbgrfafr_1], [60], [60], [0], [0x7FFC2A3E6488], [], [], [], [], [], [], []

[TOC00001-END]
[TOC00002]
========= Dump for incident 201097 (ORA 700 [dbgrfafr_1]) ========

*** 2020-05-20T00:45:56.871792-07:00
dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0)
[TOC00003]
----- Current SQL Statement for this session (sql_id=gxpn94tgbwz4d) -----
select /*+ RULE */ version "db_version",
decode(log_mode,'NOARCHIVELOG','NO','YES') "is_archived",
decode(a.asm_files,0,'NO','YES') "is_asm",
case when dg.dataguard > 0 THEN 'YES'
when controlfile_type = 'STANDBY' THEN 'YES'
else 'NO'
end as "is_dataguard",
database_role,
decode(r.rman_conf,0,'NO','YES') "is_rman",
flashback_on "is_flashback",
instance_name,
instance_number,
open_mode,
decode(c.cell_count,0,'NO','YES') "is_exadata",
decode(al.al_count,0,'NO','YES') "is_alertlog",
'NO' "is_rds",
decode(cdb,'YES','YES','NO') is_pluggable
from sys.v$instance, sys.v$database,
(select count(*) asm_files from v$datafile where name like '+%' and rownum<2) a,
(select count(*) dataguard from sys.v$archive_dest where target = 'STANDBY' and rownum<2) dg,
(select count(*) rman_conf from sys.v$RMAN_STATUS where rownum<2) r,
(select count(*) cell_count from sys.v$cell where rownum<2) c,
(select count(*) al_count from sys.x$DBGALERTEXT where rownum<2) al
[TOC00003-END]

Also few other users encountered HIGH CPU consumption issue. This can happen when $DBGALERTEXT is populated from the XML alert log file situated in the ADR location. Note that this is an undocumented fixed table and when XML alert log is very large it takes time to access and also it can cause ORA-700 error.

You can query on X$DBGALERTEXT table to see high CPU taking a long time to complete.

SELECT count(*)
FROM X$DBGALERTEXT
WHERE to_date(to_char(originating_timestamp, 'dd-mon-yyyy hh24:mi'), 'dd-mon-yyyy hh24:mi') > to_date(to_char(systimestamp - .00694, 'dd-mon-yyyy hh24:mi'), 'dd-mon-yyyy hh24:mi') /* last 10 minutes */
AND (message_text = 'ORA-00600'
OR message_text LIKE 'útal%'
OR message_text LIKE '%error%'
OR message_text LIKE '%ORA-%'
OR message_text LIKE '%terminating the instance%');

It’s a good practice to purge the table regularly and it can be purged using ADRCI utility.

--First make sure you check the count
SQL> select count(*) from X$DBGALERTEXT;

--Connect to ADRCI utility

$adrci> show home
ADR Homes:
diag/rdbms/orcl/ORCL
diag/tnslsnr/server1/listener_orcl
$adrci> SET HOMEPATH /oracle/diag/rdbms/orcl/ORCL

--Run below command to purse logs older than 1 day.
adrci> purge -age 1440 -type alert
adrci> exit

If you still see the error from Monitoring tools like TOAD, FOGLIGHT contact the vendor. If the issue is not from monitoring tools then contact Oracle

Refer  Metalink note 2056666.1  for more info.

Regards

Satishbabu G, Oracle ACE

http://oracleracepxert.com

ORA-00600, ORA-07445 and ORA-00020 errors and related bugs in 12c (12.2.0.1)

I have come across below error recently in 12c (12.2.0.1). It first started with ORA-00600 followed by ORA-07445 and ORA-00020. The ORA-00600, ORA-07445 looks like real culprit and which lead to ORA-00020 error.

ORA-00600: internal error code, arguments: [17126], [0x0B0D29528], [], [], [], [], [], [], [], [], [], []
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2020-01-13T23:20:30.493308-08:00
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x32000000170] [PC:0x10F9D3FD, kghfnd_in_free_lists()+717] [flags: 0x0, count: 1]
Errors in file /home/oracle/diag/rdbms/ORADB/ORADB/trace/ORADB_ora_62437.trc (incident=41538):
ORA-07445: exception encountered: core dump [kghfnd_in_free_lists()+717] [SIGSEGV] [ADDR:0x32000000170] [PC:0x10F9D3FD] [Address not mapped to object] []
Incident details in: /home/oracle/diag/rdbms/ORADB/ORADB/incident/incdir_41538/ORADB_ora_62437_i41538.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2020-01-13T23:20:31.182120-08:00
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
2020-01-13T23:20:31.182223-08:00
opidcl aborting process unknown ospid (48392) as a result of ORA-600
……

2020-01-14T01:29:37.222083-08:00
ORA-00020: maximum number of processes (500) exceeded
ORA-20 errors will not be written to the alert log for
the next minute. Please look at trace files to see all
the ORA-20 errors.
After investigation and troubleshooting I found below bugs

1. There is a unpublished bug related to Unified memory KGH related crashes/errors. After discussing with support come to know that particular fix was found on an internal note to already be included in Oracle 12.2.0.1

Bug 30053838 - LNX-20-ATP: HIT ORA-7445 [KGHFND_IN_FREE_LISTS] AND ORA-600 [KSM_PGA_UM_EXT_FREE:INVALID_EXT_MAGIC], INST CRASH

2. There is a published bug 24596874 and the fix fix for 24596874 is first included in 20.1.0 . Also in term patches may be available for earlier versions.

For bug 24596874 , users notice Memory corruption when CDB environment is setup with star schema on one PDB. Redaction policies enabled on tables/view of the schema and query workload executed with 50 concurrent sessions. Multiple ORA 600 [kghfrh:ds] were seen in the alert log.

So both of the above bugs are ruled out as unpublished bug already part of 12.2.0.1 and published bug is related to Star schema on PDB.

After further investigation with Oracle support found another unpublished Bug 30448845 : DATABASE HANG WITH SIGNATURE: 'PMON TIMER'<='PRIOR SPAWNER CLEAN UP'<='LATCH FREE'<='RELIABLE MESSAGE'

As per Oracle support, this is fixed in 18.1 but the fix is not easy to backports. Oracle suggest to update parameter _pmon_slaves_arr_size=0 as workaround in Oracle 12.2

Users cannot update internal oracle parameters directly in SPFILE, please follow the steps mentioned in the below blog post
https://www.oracleracexpert.com/2020/01/ora-00800-soft-external-error-arguments.html

Note that every environment, errors, load, use cases are different, I would highly recommend users to open a ticket with Oracle support and get confirmation before making any changes to your databases.
Regards
Satishbabu Gunukula, Oracle ACE
http://oracleracexpert.com 

Java SQL Exception “No more data to read from socket” in Oracle

Application using JDBC 10.1.0.5 fails with below error after upgrading your database from Oracle 9i to 10g.

JDBC Version 10.1.0.5 to 11.1.0.7 are affected with this issue.

java.sql.SQLException: OALL8 is in an inconsistent state.
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:138)
at oracle.jdbc.driver.T4C8Oall.init(T4C8Oall.java:325)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:170)
at oracle.jdbc.driver.T4CPreparedStatement.execute_for_rows(T4CPreparedStatement.java:633)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1161)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3001) Caused by: java.sql.SQLException: No more data to read from socket at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:138)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:175)
at oracle.jdbc.driver.DatabaseError.check_error(DatabaseError.java:898)
at oracle.jdbc.driver.T4CMAREngine.unmarshalUB1(T4CMAREngine.java:994)
at oracle.jdbc.driver.T4CMAREngine.unmarshalSB1(T4CMAREngine.java:951)
at oracle.jdbc.driver.T4C7Ocommoncall.receive(T4C7Ocommoncall.java:100)
at oracle.jdbc.driver.T4CConnection.do_rollback(T4CConnection.java:478)
at oracle.jdbc.driver.PhysicalConnection.rollback(PhysicalConnection.java:1263)

Cause:- The error is due to the published Bug 5851267.

This issue is caused by constraints used by the optimizer getting attempted to be used at run time when they should not. This issue is documented in Note:463899.1

You should see the following error in the trace file during the execution of the transaction

ORA-07445: Exception aufgetreten: CORE Dump [delrefi()+34] [SIGSEGV] [Address not mapped to object] [0x8] [] []

Solution: This issue has been fixed in 11.1.0.7.

If you are running in 10g then you can apply the patch for Bug:5851267
Or

you can use the following workaround to resolve the issue.

Set database parameter "_optimizer_join_elimination_enabled" to false. By doing this you can disable the elimination transformation.

SQL> alter session set “_optimizer_join_elimination_enabled”=false;

For more information refer below metalink notes
BUG:5851267 - ORA-07445 DURING EXECUTION OF PACKAGE WHERE DELETE OCCURS
NOTE:463899.1 - ORA-7445 [Delrefi] Where Delete Occurs

Regards
Satishbabu Gunukula, Oracle ACE
http://www.oracleracexpert.com

ORA-07445 exception encountered, ORA-04030 out of processes memrory

I have received below error on one of the 10gR2 database.

ORA-07445: exception encountered: core dump [kohfrem()+145] [SIGSEGV] [Address not mapped to object] [0x2B69FFFFFFF8] [] []
 
ORA-04030: out of process memory when trying to allocate 16408 bytes (koh-kghu sessi,pmucpkl kolctx)

If user or application running many jobs then the processes running the jobs may use an increasing amount of memory over time and the job queue process may fail with ORA-4030 errors.

In that case you might be hitting a bug 7715994 and refer below Metalink note
Bug 7715994 Memory leak / ORA-4030 when running many jobs
The bug has been fixed in 11.2.0.1. After spending many hours I found below workaround
Work around: When running large numbers of jobs, restart the session occasionally.

If user or application using UTL_FILE package then you see a constant growth of memory & the heapdumps suggest "koh-kghu sessi" heap is growing as a part of allocating chunks marked with "pmucalm coll", then you might be hitting the bug 7197637.

Refer below metalink note
Bug 7197637: MEMORY LEAK LEADING TO ORA-4030 (KOH-KGHU SESSI,PMUCALM COLL) USING UTL_FILE

The affected Oracle versions with this bug are 10.2.0.4 and 11.1.0.6 and it is fixed in 11gR2.
Workaround: Do not open too many files.

NOTE that users might expose Bug 9928290 due to regression exposed in the bug fix 7197637 and it is advised to apply both patches to fix the issue. But patch 9928290 is available from version 10.2.0.4.4

If your database running in <= 10.2.0.4.4 then you need to choose one of the below options.

1. Upgrade the database to 10.2.0.5 (using patch 8202632) and apply patches 9928290, 7197637
or
2. Upgrade the database to 10.2.0.4.4 PSU (using patch 9352164) and apply patches 9928290, 7197637

There are many bugs caused by this error. Check below metalink note for known bugs and possible actions in various versions

OERR: ORA 4030 "out of process memory when trying to allocate %s bytes (%s,%s)"

Please note that there are different errors across the various versions. I would recommend checking the Look-up Tool to find the Actual Cause and Possible solutions and contact Oracle Support.

ORA-600/ORA-7445/ORA-700 Error Look-up Tool [Article ID 153788.1]
Master Note for Diagnosing ORA-7445 and Related Core Dumps [Article ID 1092855.1]

Regards,
Satishbabu Gunukula, Oracle ACE
http://www.oracleracexpert.com

Unable to start RAC instance after applying a patch

Many users experienced “ORA-07445” error after applying any patch on 10.2.0.2 version and unable to start the RAC database instances on NODE2 or remote NODE’S (if more than 2 nodes). This also leads to dumps or internal errors on the remote nodes.

ORA-07445: exception encountered: core dump [kkxsyn()+584] [SIGSEGV]

The issues is a known bug 5128575 and the effected version are >=10.2.0.2

Check for possible Error messages in ASM/Database alert.log and trace files on NODE2 or Remote nodes. If you find the below SYMPTOMS then you are hitting a BUG 5128575.

ASM alert.log errors:
Errors in file /oracle/v10202/admin/+ASM/udump/+asm2_ora_30841.trc:
ORA-07445: exception encountered: core dump [kkxsyn()+584] [SIGSEGV] [Address not mapped to object] [0x000000168] [] []

Trace file errors:
Exception signal: 11 (SIGSEGV), code: 1 (Address not mapped to object), addr: 0x168, PC: [0x3f06748, kkxsyn()+584]
ksedmp: internal or fatal error
ORA-07445: exception encountered: core dump [kkxsyn()+584] [SIGSEGV] [Address not mapped to object] [0x000000168] [] []
No current SQL statement being executed.

Database errors:
Startup database fails with below errors
SQL> startup database
ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file '+DATA/testdb/spfiletestdb.ora'
ORA-17503: ksfdopn:2 Failed to open file +DATA/testdb/spfiletestdb.ora
ORA-03113: end-of-file on communication channel

Cause:
Installing the 10.2.0.2 Patch Set in RAC on any Unix platform does not correctly update the libknlopt.a file on all remote nodes. The local node where the installer is run does update libknlopt.a file and remote nodes do not get the updated file.

Workaround:
Step 1: Shutdown ASM and all Database instances
Step 2: Manually copy $ORACLE_HOME/rdbms/lib/libknlopt.a from the local node to NODE2 or all remote nodes (if more than 2 nodes)
$ scp libknlopt.a oracle@testrac2:$ORACLE_HOME/rdbms/lib/libknlopt.a
Step 3: Re-link Oracle on all remote nodes.
3. cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk ioracle

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com

ORA-07445 Error

The ORA-07445 is very common error in many versions, but there are different reasons for it.

Here are some reasons for this error:-
1. Software Bugs
2. High RAM Usage.
3. OS Limits
4. Shared pool corruptions due to I/o slaves error (Refer Metalink note:1404681)
5. SGA corruption (Refer Metalink note: 5202899/5736850)
6. Library cache lock by query coordinator (Refer Metalink note: 3271112)
7. Hardware Errors
8.Oracle Block Corruptions
9. Program Errors

Oracle Metalink provided a Look up tool to search ORA-00600/ORA-07445 errors, look for workaround or bugs.

Error: ORA-07445: exception encountered: core dump [%s] [%s] [%s] [%s] [%s] [%s]"
Cause: An OS exception occurred which should result in the creation of a core file. This is an internal error.
Action: Contact your customer support representative.

The ORA-07445 has a multitude of causes and contacting Oracle technical support on Metalink is always required.

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com/