Thursday, February 14, 2019

How to solve ORA-02017: integer value required when trying to adjust parameters



I was trying to rectify an issue where the alert log of the database kept throwing errors like this:
ORA-01013: user requested cancel of current operation
ORA-00600: internal error code, arguments: [KGL-heap-size-exceeded], [0x114890D28], [6], [1263979496], [], [], [], [], [], [], [], []

Reading Doc ID 330239.1 "Memory Notification: Library Cache Object loaded into SGA / ORA-600 [KGL-heap-size-exceeded]", I realized that these errors are not causing any failure, as they are simply warnings that Oracle implemented to alert DBAs about potential large occupants of database memory.

There are two hidden parameters which control these messages:
set lines 200
col name format a40
col description format a80
col KSPPSTVL format a20
 select
 nam.ksppinm NAME,
 nam.ksppdesc DESCRIPTION,
 val.KSPPSTVL
 from
 x$ksppi nam,
 x$ksppsv val
 where nam.indx = val.indx and nam.ksppinm like '%kgl_large_heap_%_threshold%';

NAME                                 DESCRIPTION                                                        KSPPSTVL
-----------------------------------  ------------------------------------------------------------------ --------------
_kgl_large_heap_warning_threshold    maximum heap size before KGL writes warnings to the alert log      524288000
_kgl_large_heap_assert_threshold     maximum heap size before KGL raises an internal error              524288000

These are the default values, 50M. Not very much. Instead, I want a warning to be written to the alert log at 1500M, and an ORA-00600 error to be thrown at 2G.

The first instruction works just fine:
alter system set "_kgl_large_heap_warning_threshold"=1572864000 scope=spfile;

System altered.

The secoond fails with:
alter system set "_kgl_large_heap_assert_threshold"=2147483648 scope=spfile;

ERROR at line 1:
ORA-02017: integer value required

Why?

The answer was found in the document Doc ID 2049098.1 Setting Parameter SORT_AREA_SIZE Throws error ORA-2017: "Integer Value Required", which states

"The parameter SORT_AREA_SIZE has a 2G limit"

and points out that instead of setting the value to 2147483648 bytes, it should be lowered to a value below 2G.

So the solution was to reduce the value by one single byte, from

alter system set "_kgl_large_heap_assert_threshold"=2147483648 scope=spfile;
to
alter system set "_kgl_large_heap_assert_threshold"=2147483647 scope=spfile;

and Oracle happily accepted the new value.


No comments:

Post a Comment