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.
[[email protected] ~]$ sqlplus /nolog SQL*Plus: Release 220.127.116.11.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 [[email protected] 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.
[[email protected] dbs]$ sqlplus /nolog SQL*Plus: Release 18.104.22.168.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 22.214.171.124.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.
[[email protected] dbs]$ lsnrctl status LSNRCTL for Linux: Version 126.96.36.199.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 188.8.131.52.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.