Another customer finding (thanks again, Dennis!). After patching from 23.26.1 or 23.26.2 to Oracle AI Database 23.26.3 Release Update, the database with minimal SGA sizing did not start up anymore. It resulted in ORA-821. After analysing the situation and checking with the developers, it became clear that Oracle 23.26.3 may require a larger SGA than previous releases. Of course, only if you have a very small SGA defined.

Photo by John Tuesday on Unsplash
Where does this apply to?
This is a behavior change in Oracle AI Database 26ai, Release Update 23.26.3.0.0, and it is going to be documented soon as well. Since the change got introduced via a bug fix, it simply slipped through the cracks since it will only affect a very small number of database installations. For those of you who have SGAs larger than a few GB in size, stop reading here, and safe your time.
But those of you with smaller SGA settings may see that initialization parameters accepted by Oracle AI Database 26ai RU 23.26.2 for the SGA sizing may now fail to allow startup after moving to RU 23.26.3. This happens when its configured SGA_TARGET is below a certain minimum required by the target release.
What is the change in RU 23.26.3?
This change is associated with the fix for Bug 38250160 for a blockade of LSM. It first appears in RU 23.26.3. You will find that the presence of it requires a higher minimum for the default buffer cache, which can raise the minimum viable SGA.
Most of you have ASMM (Automatic Shared Memory Management) with SGA_TARGET enabled. In this case Oracle sizes components such as the default buffer cache, shared pool, large pool, and Streams pool within that target. A nonzero value for an automatically sized component parameter, such as DB_CACHE_SIZE or SHARED_POOL_SIZE, establishes a minimum for that component. For further information, please see Oracle’s SGA_TARGET reference and DB_CACHE_SIZE reference.
The behavior change you will see with a very small SGA is a default buffer cache allocation of approximately 128MB per CPU_COUNT. Let’s assume you have a CPU_COUNT of 16, this leads to a baseline alone of 2048MB. But, since there are other pools in addition as well in your SGA, and you may have defined minimum sizes by using non-zero values for various pools, the actual requirement may be higher and leads to a larger SGA needed.
Just in case you check and wonder that you haven’t set a CPU_COUNT value by yourself, be aware that Oracle then derives it automatically from the information it is able to see via the OS. See also the documentation of CPU_COUNT in the Reference Guide.
A quick example
To demonstrate the difference, let me use a simple example Dennis shared with me. We could reproduce this quickly in our environments as well.
Our environment has a CPU_COUNT=16.
Setting SGA_TARGET=64M (of course, this is too small) in 23.26.2:
SQL> startup pfile=/tmp/testcase/init_tst.ora nomount ORA-00821: Specified value of sga_target 64M is too small, needs to be at least 724M ORA-01078: failure in processing system parameters
Release Update 23.26.2 reports that it requires at least 724MB as SGA_TARGET.
Now, we are going to repeat this test with Release Update 23.26.3 instead:
SQL> startup pfile=/tmp/testcase/init_tst.ora nomount ORA-00821: Specified value of sga_target 64M is too small, needs to be at least 2516M ORA-01078: failure in processing system parameters
Now, we get an error but a much higher minimum SGA_TARGET setting is required: 2516MB. That’s a lot more.
In such cases, you need to raise the SGA_TARGET value. Multiply the CPU_COUNT with 128M, and then add the additional pool sizes you set in addition. Keep in mind that this is a startup threshold.
Of course, this can happen during:
- Patching to Oracle AI Database 23.26.3
- Upgrade from Oracle 19c or 21c to Oracle AI Database 23.26.3
- Database creation with a fixed template or script from Oracle AI Database 23.26.3 onwards
We will see whether we can add a check in AutoUpgrade. This hasn’t been decided yet.
Prechecking by yourself
You can precheck the settings beforehand. Accumulate the currently used/allocated SGA pools, then compare them to CPU_COUNT * 128MB and check whether you are smaller than the calculation. If this is the case, you will need to adjust your SGA_TARGET. Of course, this works only for patching and upgrades where you stay on the same host (unless all your hosts have the same CPU_COUNT spec).
You may try to run this query in your source database. Keep in mind that the calculation is based on the CPU_COUNT on this server. If you move somewhere else, the result may be different.
Please feel free to use this script for a check:
The output looks like:
UPGRADE_CHECK
--------------------------------------------------------------------------------
- Current SGA total size (SGA_TARGET): 956.0 MB
- Predefined SGA component sizes: 256.0 MB
- Component settings:
- SHARED_POOL_SIZE: 256.0 MB
- Target CPU_COUNT: 0
- Minimum SGA requirement for Oracle AI Database 26ai
RU 23.26.3 or newer: 256.0 MB
Based on this estimate, you can upgrade or patch to
Oracle AI Database 26ai RU 23.26.3 or newer without
increasing SGA_TARGET.
I added this to my scripts page as well.
In case you are moving to a different system with a different CPU_COUNT, use this version and input your target CPU_COUNT interactivcely:
Here, the output looks like (after adding a CPU_COUNT=48 interactively):
UPGRADE_CHECK
--------------------------------------------------------------------------------
- Current SGA total size (SGA_TARGET): 956.0 MB
- Predefined SGA component sizes: 256.0 MB
- Component settings:
- SHARED_POOL_SIZE: 256.0 MB
- Target CPU_COUNT: 48
- Minimum SGA requirement for Oracle AI Database 26ai
RU 23.26.3 or newer: 6,400.0 MB
You will need to increase SGA_TARGET by 5,444.0 MB
to a total of 6,400.0 MB.
Ensure SGA_MAX_SIZE can accommodate this value.
If you find better ways to measure and project the values, let me know.
Will this change affect Oracle Database 19c?
This fix is not eligible for inclusion in a standard Oracle Database 19c Release Update because it is classified as non-RAC-rolling for 19c. Oracle documents that RUs are RAC-rolling installable, so a non-RAC-rolling fix cannot be included in that RU delivery model. This applies to 19c RUs such as 19.34 and later RUs. Availability of a separate interim patch, if required, should be confirmed with Oracle Support for the specific platform and RU.
Will this affect Oracle Database 26ai Free Edition?
Loic, my dear colleague from France slacked me right after the blog post got published and asked whether this would affect “Free Edition”, too? Technically, yes since the fix is present in 23.26.3 in the code. But in reality, it won’t since the CPU_COUNT is capped at 2 – therefore, 256 MB are the minimum SGA_TARGET size then. The maximum SGA size is capped as well at 2 GB – therefore, you should not see any effect there.
Further Links and Information
–Mike
Thanks for this. We would have definitely run into this issue with our next patching cycle. we haven’t ever worried about this on our exadatas but x11 has significantly increased our cpu_count. Affecting parallel process spawning, sga, etc
You can constraint it by setting CPU_COUNT in the SPFILE if you need to.
Cheers,
Mike