ORA-00823 Specified value of sga_target greater than sga_max_size

While starting or stopping Oracle database if you are getting below error for pfile or spfile you can resolve these issues quickly using below steps.When an Oracle Instance is started, the characteristics of the Instance are established by parameters specified within the initialization parameter file. These initialization parameters are either stored in a PFILE or SPFILE. SPFILEs are available in Oracle 9i and above. All prior releases of Oracle are using PFILEs.

Error Seen while starting up(You might see the same for some other parameters)

SQL> conn sys as sysdba
Enter password:
Connected to an idle instance.
SQL> startup
ORA-00823: Specified value of sga_target greater than sga_max_size
SQL> shutdown immediate;
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory

To resolve these type of errors you need to create a pfile from the non working spfile or vice versa and edit the file to update the correct parameters according to the requirement and start it using the same file or create the file you want the DB to start with.

Below are the steps you can follow to fix the issue. Here we had to update the sga_target to some lower value than the sga_max_size to fix it. We have made the below mentioned value to “0” so that oracle will manage the sizes though you can set your according to your recommendations.

[oracle@techpastehome-db ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jun 24 03:55:16 2014

Copyright (c) 1982, 2013, Oracle. All rights reserved.

SQL> conn / as sysdba
Connected to an idle instance.
SQL> create pfile='/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/init1.ora' from spfile;

File created.

SQL> exit
Disconnected

[oracle@techpastehome-db dbs]$ vi /home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/init1.ora

– Match the parameters with the correct value according to recommendations provided by DBA.

– save the file.

Startup the DB using pfile to be sure to know all parameters added are good enough to let the DB start.

[oracle@techpastehome-db dbs]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jun 24 04:29:08 2014

Copyright (c) 1982, 2013, Oracle. All rights reserved.

SQL> conn /as sysdba;
Connected to an idle instance.

SQL> startup PFILE='/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/init1.ora';
ORACLE instance started.

Total System Global Area 1920831488 bytes
Fixed Size 2268112 bytes
Variable Size 964690992 bytes
Database Buffers 947912704 bytes
Redo Buffers 5959680 bytes
Database mounted.
Database opened.

Shutdown post successful start.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Now create a SPFILE from the new working PFILE

SQL> create spfile from pfile='/home/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/init1.ora';

File created.

SQL> startup;
ORACLE instance started.

Total System Global Area 1920831488 bytes
Fixed Size 2268112 bytes
Variable Size 964690992 bytes
Database Buffers 947912704 bytes
Redo Buffers 5959680 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Check the listener status if it’s already running leave it else start it using “lsnrctl start” command without quotes.

[oracle@techpastehome-db dbs]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 24-JUN-2014 04:39:14

Copyright (c) 1991, 2013, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 24-JUN-2014 02:40:45
Uptime 0 days 1 hr. 58 min. 29 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /home/oracle/app/oracle/diag/tnslsnr/techpastehome-db/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=techpastehome-db.com)(PORT=1521)))
Services Summary...
Service "tpdb11204" has 1 instance(s).
Instance "tpdb11204", status READY, has 1 handler(s) for this service...
Service "tpdb11204XDB" has 1 instance(s).
Instance "tpdb11204", status READY, has 1 handler(s) for this service...
The command completed successfully

Once the DB and the listener are started successfully you shall be able to connect the database without any issues.

In case of any ©Copyright or missing credits issue please check CopyRights page for faster resolutions.