ORA-24330 OCI INTERNAL ERROR

Solution: alter system set client_result_cache_size=0 scope=spfile;




Effect of client_result_cache_size On Client Applications Or OCI Based Applications [ID 1300727.1]


Applies to:

Oracle Server - Enterprise Edition - Version: 11.2.0.2 and later   [Release: 11.2 and later ]
Information in this document applies to any platform.
Symptoms

- Receiving error 'ORA-24330: OCI INTERNAL ERROR' while running back end Procedures from SQL*Plus and also from the Application connected to the Database - Oracle 11g 11.2.0.2.

- Unable to log in through Application.
Changes

- Database was upgraded from Oracle 11g Release 2 - 11.2.0.1 to 11.2.0.2. The client_result_cache_size was set to 1GB.

- Parameter client_result_cache_size is not on by default and the recommended value is 0.


Cause

client_result_cache_size is supposed to reset once the client process terminates but in cases like transactions with millions of rows the cache gets full very soon and does not reset itself while the client process is still on and fails during that time with errors like 'ORA-24330: OCI INTERNAL ERROR'.

If client_result_cache_size is set to a non zero value, technically it would mean that you have turned on the setting client_result_cache_size and it does have a limit active until that client session that started is active.  For example; if the value is set to 1GB and the process that started client process is still actively processing million of rows then it will for sure get 'ORA-24330: OCI INTERNAL ERROR' when it reaches 1GB cache which is the set hard limit value.

So for higher volume of transactions the recommended value for client_result_cache_size is zero to avoid all such issues.

BUG 8480915 - ORA-24330: OCI INTERNAL ERROR IN ODP.NET STRESS WHEN CLIENT RESULT CACHE ENABLED

Base BUG 8418934 - KPOQGINV: CLIENT CACHING GOT DISABLED AFTER BADREGID ERROR
Solution

'client_result_cache_size' is an internal integer parameter and to change settings, please do:

1. Setting client_result_cache_size = 0 in the initialization (init.ora) parameter files followed by instance restart is the most reliable way.

client_result_cache_size=0 need to be set at the init.ora and bouncing the DB should effectively set that value.

If client_result_cache_size is set to 0 and it would mean that NO client_result_cache_size is on and nothing will be in cache.

(OR)

Client caching can be totally removed by commenting out the parameter setting client_result_cache_size from init.ora (or) by totally removing that entry.

Once the DB comes back on, you can check the value from SQL*Plus by doing:

SQL> show parameter CLIENT_RESULT_CACHE_SIZE;

2. Alternatively;

If spfile is used;

i. connect / as sysdba to database
ii. alter system set client_result_cache_size=64000 scope=SPFILE; -- value set to 64KB, please note that this is just a sample value.
iii. shutdown the database
iv. startup the database

Once the DB comes back on, you can check the value from SQL*Plus by doing:

SQL> show parameter CLIENT_RESULT_CACHE_SIZE;

'client_result_cache_size' is stored on the Client side.  Once that limit have reached to the maximum then all other subsequent connections will fail.  That is why it is recommended to set to 0 for high volume of transactions in which case there will be no caching.  Even if there's a limit it gets reset only when the Database is bounced.
References