ORA-00821: Specified value of sga_target 1184M is too small, needs to be at least 1344M about oci-oracle-xe HOT 19 CLOSED

@gvenzl yes awesome it worked! Many thanks :)

from oci-oracle-xe.

Hey @rsvoboda, this is the fix:

If the CPU count is >=16 the script increased the SGA_TARGET parameter to 1.5GB before it tries to startup the database.
This only happens during the database configuration step, i.e. on the first container startup.
If a volume has been used and mapped, this is not executed, assuming that there is an already working and from the user configured database there.

from oci-oracle-xe.

Closing issue as the fix has been verified.

from oci-oracle-xe.

Fix implemented and push onto the registry.

from oci-oracle-xe.

Thanks, good to know that option exists. I don't want to use more, but I also can't specify how many I want to use since I'm just using Quarkus Devservices. I'll open a ticket on Quarkus to discuss this

from oci-oracle-xe.

Teammates have been reporting it's working fine for them, so I'm wondering what's special about my machine. One point to make is my workstation has 48 cores (it's a Threadripper 3960X) - I'm assuming the DB scales some parameters dynamicaly based on the host?

So I've tried to run it with restricted resources all the way down to a single core:

podman run --memory-swappiness=0 --memory="1g" --cpus="1.0" --rm=true --name=HibernateTestingOracle -p 1521:1521 -e ORACLE_PASSWORD=hibernate_orm_test gvenzl/oracle-xe:21.3.0-slim

Unfortunately it's still failing - I wonder if the ergonomics of the RDBMS are reading the effective number of cores in a fitting way for containers; this could be hard to fix but I could use a suggestion for a workaround :)

from oci-oracle-xe.

Hey @Sanne, yes, you are spot on! It's the number of cores that cause this "memory too low" error. The database keeps track of various performance metrics of the CPUs and also scales a couple of other performance-related parameters according to them, unfortunately not being aware that it's running inside a container. However, I think I can probably script around that at container startup.

from oci-oracle-xe.

Hey @Sanne, can you try again with gvenzl/oracle-xe:21.3.0-slim, please?

from oci-oracle-xe.

@gvenzl can you point us to the change you did?

from oci-oracle-xe.

Awesome, that's great! @rsvoboda, was just a quick and dirty test last night. Will wrap things up over the weekend and point you to it when it's done.

from oci-oracle-xe.

While that might have solved the issue on Threadripper 3960x, it is still an issue on 3970x with 32 cores / 64 threads:

ORA-00821: Specified value of sga_target 1504M is too small, needs to be at least 1696M

from oci-oracle-xe.

Seems like a smarter solution is in order that adjusts accordingly to the number of threads.

But luckily now we know that 48 threads require 1344MB while 64 threads 1696MB, suggesting that each individual thread increases the memory requirement by about 22MB (352MB delta for 16 additional threads).

from oci-oracle-xe.

I am encountering the same issue when trying to run tests using oracle in a gitlab runner that lives on a 128-core host:

CONTAINER: starting up...
CONTAINER: first database startup, initializing...
CONTAINER: uncompressing database data files, please wait...
CONTAINER: done uncompressing database data files, duration: 16 seconds.
CONTAINER: machine has high CPU count: 128
/opt/oracle/container-entrypoint.sh: line 156: ((: 128 = 32 : attempted assignment to non-variable (error token is "= 32 ")
CONTAINER: increasing SGA_TARGET to 1800MB.
File created.
File created.
CONTAINER: done increasing SGA_TARGET.
CONTAINER: starting up Oracle Database...
LSNRCTL for Linux: Version 21.0.0.0.0 - Production on 04-APR-2022 07:47:42
Copyright (c) 1991, 2021, Oracle.  All rights reserved.
Starting /opt/oracle/product/21c/dbhomeXE/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 21.0.0.0.0 - Production
System parameter file is /opt/oracle/homes/OraDBHome21cXE/network/admin/listener.ora
Log messages written to /opt/oracle/diag/tnslsnr/089976faa82b/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_XE)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 21.0.0.0.0 - Production
Start Date                04-APR-2022 07:47:42
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           XE
Listener Parameter File   /opt/oracle/homes/OraDBHome21cXE/network/admin/listener.ora
Listener Log File         /opt/oracle/diag/tnslsnr/089976faa82b/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_XE)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
The listener supports no services
The command completed successfully
ORA-00821: Specified value of sga_target 1808M is too small, needs to be at least 2976M
ORA-01078: failure in processing system parameters

from oci-oracle-xe.

@gvenzl sorry to bother you again, but do you think this issue should be reopened, or should I open a separate issue?

from oci-oracle-xe.

Wouldn't docker run --cpus=2 option do the trick?
https://docs.docker.com/config/containers/resource_constraints/#configure-the-default-cfs-scheduler
Unless you really want to use more.

from oci-oracle-xe.

Hey @Felk,

I'm afraid that at this point we are out of options because Oracle XE only allows for up to 2GB RAM.
So even if we were to try to increase the memory to 2.9GB, we would just receive another error.

Hopefully, this can be addressed via the CPU setting.

from oci-oracle-xe.

Sadly that did not appear to work, see quarkusio/quarkus#24939 (comment)

(note this time our gitlab host apparently was downgraded to 96 cores, but that also already wants 2320M

from oci-oracle-xe.

Looks like the oracle property CPU_COUNT might work instead. Is that something that would need to be added here, possibly in the install script?

from oci-oracle-xe.

I am encountering the same issue in a Kubernetes version v1.25.0 that lives on a 32-core host:

ORA-00821: Specified value of sga_target 1536M is too small, needs to be at least 2112M
ORA-01078: failure in processing system parameters

Everything works fine before. But now idk what to do. Can you help, plz?

bash-4.4$ cat spfileXE.ora | grep cpu_count
*.cpu_count=2

SQL> show parameter cpu

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cpu_count                            integer     2
cpu_min_count                        string      2
parallel_threads_per_cpu             integer     1

from oci-oracle-xe.