Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Memory leak with Oracle DB on Windows Server

UW (Germany)Mar 7 2024

We observe a memory leak on a Windows 2019 Server with an ORACLE database (Standard Edition Version 19.21).

The Server has 8GB Memory and about half of it is reserved for the database. Approximately every third week we have to restart the server as there is no memory left over. If we wait too long the database crashes with ORA-27102. Direct after starting the server Windows task manager shows 2974MB used memory for "Oracle RDBMS Kernel Executable". The memory usage of PGA and SGA I checked as follows:

with a as 
( select sum(current_size) byte
    from v$memory_dynamic_components 
   where component not like '%Target%'
    union all
  select sum(pga_alloc_mem) byte 
  from v$process
)
select round(sum(byte)/1024/1024) MB from a;

        MB
----------
      2842

So the sum of SGA and PGA is about a 150MB lower than the memory consumption, shown by Windows. Two and a half weeks later the Windows task manager shows 4997.5MB used memory for "Oracle RDBMS Kernel Executable". But the combined size of PGA and SGA has only changed a few MB:

with a as 
( select sum(current_size) byte
    from v$memory_dynamic_components 
   where component not like '%Target%'
    union all
  select sum(pga_alloc_mem) byte 
  from v$process
)
select round(sum(byte)/1024/1024) MB from a;

        MB
----------
      2891

I found DOC ID 2765825.1 19c "ODBC Driver Cause Memory Leak When Connecting to 19c DB". Even if I think that this it not the reason for our problem and as the bug described there was fixed in Oracle version 19.14 I changed CLIENT_STATISTICS_LEVEL to OFF, but this did not solve the problem.

More detailed statistics for SGA and PGA are:

SGA (this query shows the same values directly after restart of the server and 2½ weeks later)

select 
   component, 
   current_size, 
   min_size, 
   max_size
from v$memory_dynamic_components;

COMPONENT                                                       CURRENT_SIZE   MIN_SIZE   MAX_SIZE
---------------------------------------------------------------- ------------ ---------- ----------
shared pool                                                       1509949440 1509949440 1509949440
large pool                                                          16777216   16777216   16777216
java pool                                                          150994944  150994944  150994944
streams pool                                                        33554432   33554432   33554432
unified pga pool                                                           0          0          0
SGA Target                                                        2550136832 2550136832 2550136832
memoptimize buffer cache                                                   0          0          0
DEFAULT buffer cache                                               687865856  687865856  687865856
KEEP buffer cache                                                          0          0          0
RECYCLE buffer cache                                                       0          0          0
DEFAULT 2K buffer cache                                                    0          0          0
DEFAULT 4K buffer cache                                                    0          0          0
DEFAULT 8K buffer cache                                                    0          0          0
DEFAULT 16K buffer cache                                                   0          0          0
DEFAULT 32K buffer cache                                                   0          0          0
Shared IO Pool                                                     134217728  134217728  134217728
Data Transfer Cache                                                        0          0          0
In-Memory Area                                                             0          0          0
In Memory RW Extension Area                                                0          0          0
In Memory RO Extension Area                                                0          0          0
PGA Target                                                         855638016  855638016  855638016
ASM Buffer Cache                                                           0          0          0

PGA after start of the server:

select con_id, 
       sum(pga_used_mem) pga_used_mem,
       sum(pga_alloc_mem) pga_alloc_mem
from v$process
group by con_id;

    CON_ID PGA_USED_MEM PGA_ALLOC_MEM
---------- ------------ -------------
         1     23012676      32781068
         3     23704830      29710054
         0    231887494     384337174

PGA after 2½ weeks:

    CON_ID PGA_USED_MEM PGA_ALLOC_MEM
---------- ------------ -------------
         1      2963370       3246234
         3     44360896      50388936
         0    284980456     447909272

We have some more Oracle Databases 19.21 SE running on a Windows server but the other databases don't have this problem. Any suggestions what the reason might be or what else I should check to find the source of the problem?

Comments

Hi.,

May i know which version of database you are using, Is it 19C, if 19C DB then, you are importing into the CDB or Any of PDB Database?

Based on your export command you have issued., I have given 2 scenario to perform as shown below.

expdp user/pass schemas=ctm920 directory=BACKUP_DIRDC logfile=backup.log filesize=1G

If you are using import on Container Database (CDB)

- Check the OS Directory is created & you have linked this OS Directory to DB directory.
Ex: $ mkdir -p /u01/dumpfile/
- Place dumpfile into /u01/dumpfile location.
- SQL> create or replace directory dumpfile as '/u01/dumpfile/';
- SQL> grant read, write on directory dumpfile to [import user];

impdp user/pass dumpfile=expdat.dmp logfile=imp_log.log directory=BACKUP_DIRDC TABLE_EXISTS_ACTION=REPLACE

If you are using import on Pluggable Database (PDB)

- Check the OS Directory is created & you have linked this OS Directory to DB directory.
Ex: $ mkdir -p /u01/dumpfile/
- Place dumpfile into /u01/dumpfile location.
- SQL> create or replace directory dumpfile as '/u01/dumpfile/';
- SQL> grant read, write on directory dumpfile to [import user];

impdp user/pass@(PDB NAME) dumpfile=expdat.dmp logfile=imp_log.log directory=BACKUP_DIRDC TABLE_EXISTS_ACTION=REPLACE

Keep us update.

Warm Regards,
Mir Sayeed Hassan
Oracle ACE Pro
Oracle Certified OCP | OCI

Solomon Yakobson 5 days ago

impdp parameter TABLE_EXISTS_ACTION=REPLACE applies to tables only. Errors you are getting indicate FUNCTION:"ctm920 "."CURRTIME", PROCEDURE:"ctm920 "."PURGE_CMR_RUNINF_1" and FUNCTION:"ctm920 "."DDIFF" already exist in target database. You need to decide do you want to keep there existing objects and for that you need to add EXCLUDE parameters to import or import them and for that you need to drop these objects before import.

SY.

1 - 2

Post Details

Added on Mar 7 2024
5 comments
1,103 views