Explanation: Oracle Application Express requires the system global area (SGA) and program global area (PGA) to be at least 300 MB.

Databases typically use automatic memory management, where the memory can be controlled by the server parameter TARGET_MEMORY. If our database does not use automatic memory management, then we have to manually configure memory parameters.

This post describe the resolution steps for the ORA-00838, which we got when we changed the TARGET_MEMORY to 300M by using the ALTER SYSTEM SET MEMORY_TARGET=’300M’ SCOPE=spfile; command.

Error: The error that I encounter is as below:

ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least 3072M
ORA-01078: failure in processing system parameters

How we started to get the error? I was trying to install the Oracle APEX 20.2. And for that I have set the TARGET_MEMORY to 300M by using the below script.

SQL> ALTER SYSTEM SET MEMORY_TARGET='300M' SCOPE=spfile;

After that when in tried to STARTUP the database I started getting the below error.

ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least 3072M
ORA-01078: failure in processing system parameters

Now, neither we are able to stop the database nor correct the MEMEORY_TARGET. We end up with the below error when we executed STARTUP command.

ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0

Solution: For solution of the above error we will follow the below steps.

Step 1: As we know spfile is not an human readable or editable file, so we will create pfile from spfile. So that we able to change oracle system parameter. ( i.e. MEMORY_TARGET )

SQL> create pfile='C:/Users/TEMP/pfile.ora' from spfile;
SQL> File created.

Step 2: After the pfile file is created, change MEMORY_TARGET parameter value in the pfile by adding the below line at the end of the pfile.

*.memory_target=3072M

Step 3: Start the Oracle database with the newly updated pfile as follows.

sys/ as sysdba


SQL*Plus: Release 12.1.0.1.0 Production on Mon Jun 9 11:24:56 2014

Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.

Step 4: STARTUP the pfile.

SQL> startup pfile=C:/Users/TEMP/pfile.ora

And we will get the below output

ORACLE instance started.
Total System Global Area 3206836224 bytes
Fixed Size 2293496 bytes
Variable Size 1879048456 bytes
Database Buffers 1308622848 bytes
Redo Buffers 16871424 bytes
Database mounted.
Database opened.

Step 5: Create spfile from the newly created pfile, because we are using spfile for our database.

SQL> create spfile from pfile='C:/Users/TEMP/pfile.ora';
SQL> File created.

Step 6:  Bounce the database after creating spfile to reflect changes.

SQL> startup force;

And we will get the below output:

ORACLE instance started.

Total System Global Area 3206836224 bytes
Fixed Size 2293496 bytes
Variable Size 1879048456 bytes
Database Buffers 1308622848 bytes
Redo Buffers 16871424 bytes
Database mounted.
Database opened.

Step 7: Check for the MEMORY_TARGET value using the below command.

SQL> show parameter memory_target;

And we will get the output as shown below:

NAME             TYPE           VALUE
-------------------------------------------------
memory_target    big integer    3G

Hence, the solution to the ORA-00838 error.

If you like the post please comment, share, and do join me on Facebook. Please subscribe to my YouTube Channel for video tutorials.

Thanks & Regards,
Susanto Paul

 1,890 total views,  6 views today