Forum Stats

  • 3,750,255 Users
  • 2,250,140 Discussions
  • 7,866,868 Comments

Discussions

SGA,PGA exceeds the memory and oracle is not working

Hussien Sharaf
Hussien Sharaf Member Posts: 17
edited Jan 5, 2016 8:48AM in General Database Discussions

Hi,

I have wrongly set the following:

ALTER system SET sga_max_size=1500M scope=spfile;

ALTER system SET SGA_TARGET=1400M scope=spfile;

ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 9000M scope=spfile;

My total memory is 16 GB, Win8

When I restarted the oracle , it couldn't startup and I m sure 100% that these memory sizes are the problem.

Now I can't login to the DB to fix these memory sizes.

How can I modify the SGA, PGA while the whole oracle is down? It is not only the DB instance that is nt working. It s the whole oracle.

Is there a way to modify the spfile from a text editor?

I d appreciate your fast replies.

Regards,

Hussien Sharaf

Message was edited by: 3008910

Tagged:
Hussien SharafChizzle PackAman....IT-HILLSJuanMVidar Eidissen

Best Answer

  • Chizzle Pack
    Chizzle Pack Member Posts: 22
    edited Dec 28, 2015 9:30PM Accepted Answer

    Vidar, excellent recommendation.  There are cases where editing the spfile directly can cause problems. If you happen to encounter that and the spfile is not usable, you can also create a new pfile from contents in the alert log, startup the instance using the new pfile, then make a copy of the pfile to the spfile.  here are the basic steps if the spfile is corrupt and you need to create a new one:

    1) find the alert log, copy the lines below the comment "System parameters with non-default values:" into a new file called init<sid>.ora and save the file into the default parameter file directory (dbs or database).

    2) make sure the bad spfile is not in the startup/parameter file directory, and startup the Oracle <SID> service & the instance should now be available.  if you're not able to connect as "/" try using sys/<pwd> as sysdba

    3) create a copy of the spfile from the init<sid>.ora file:   sql> create spfile from pfile;     -- you can specify the directory or directories, or leave it as the default.

    4) go back & validate the parameters as per our earlier discussion.

    Hussien, I hope this helps.

    CP

    Hussien Sharaf
«134

Answers

  • Chizzle Pack
    Chizzle Pack Member Posts: 22
    edited Dec 30, 2015 3:19PM

    here's a brief thread that may help:

    Unable to start Oracle (11g1) due to bad memory configuration (ORA-00837) - Database Administrators Stack Exchange

    do you have a PFILE as a backup of the previous SPFILE?  then you could startup the instance using the old PFILE.  this is one method...and you should be back to normal.

    then, CREATE SPFILE='/oradata/spfileORCL.ora' from PFILE='/oradata/initORCL.ora';


    also, for you PGA_AGGREGATE_TARGET setting, consider this as an option:

    by default the PGA_AGGREGATE_TARGET is 10MB or 20% of the SGA size, whichever is greater. (10g through 12c)

    Hussien SharafIT-HILLS
  • Hussien Sharaf
    Hussien Sharaf Member Posts: 17
    edited Dec 28, 2015 6:38PM

    Thanks for your quick reply,

    That thread says I should go to the sql prompt (SQLPLUS). But the sqlplus does nt allow me to connect even using the sysdba account.

  • Hussien Sharaf
    Hussien Sharaf Member Posts: 17
    edited Dec 28, 2015 6:42PM

    I m adding a scrn shot

    SQLPlus.png

  • Chizzle Pack
    Chizzle Pack Member Posts: 22
    edited Dec 28, 2015 6:57PM

    since your db is on Windows, confirm that the OracleTNSlistener & Oracle DB services are up & running (restart the services if necessary).

    IT-HILLS
  • Hussien Sharaf
    Hussien Sharaf Member Posts: 17
    edited Dec 28, 2015 7:06PM

    They are running, and I restarted them many times but I keep getting this error (attached) when I open the DB from the sqlDeveloper

    SQLPlus.png

    I also get this when I open the SQLpLUS:

    SQLPlus.png

  • Vidar Eidissen
    Vidar Eidissen Member Posts: 17
    edited Dec 28, 2015 7:18PM

    The spfile is binary, but still readable, so you can make a file-copy of it, naming it as a pfile and edit it over to text-format again, setting the proper values. Then you should be able to restart the database, as the pfile have preference over the spfile. From there you can create a new spfile.

    Hussien SharafAman....Hussien Sharaf
  • Hussien Sharaf
    Hussien Sharaf Member Posts: 17
    edited Dec 28, 2015 7:31PM

    Vidar,

    Thanks for ur reply.

    How can I "naming it as a pfile"? do u mean just rename it to "pfileite.ora" instead of "spfileite.ora"?

  • Vidar Eidissen
    Vidar Eidissen Member Posts: 17
    edited Dec 28, 2015 7:39PM

    Close. Copy your spfile to init<sid>.ora and edit it. Even notepad will do. And just to make sure, you might want to rename your spfile to something different. The restart the service.

    Chizzle PackAman....Hussien SharafHussien Sharaf
  • Chizzle Pack
    Chizzle Pack Member Posts: 22
    edited Dec 28, 2015 9:30PM Accepted Answer

    Vidar, excellent recommendation.  There are cases where editing the spfile directly can cause problems. If you happen to encounter that and the spfile is not usable, you can also create a new pfile from contents in the alert log, startup the instance using the new pfile, then make a copy of the pfile to the spfile.  here are the basic steps if the spfile is corrupt and you need to create a new one:

    1) find the alert log, copy the lines below the comment "System parameters with non-default values:" into a new file called init<sid>.ora and save the file into the default parameter file directory (dbs or database).

    2) make sure the bad spfile is not in the startup/parameter file directory, and startup the Oracle <SID> service & the instance should now be available.  if you're not able to connect as "/" try using sys/<pwd> as sysdba

    3) create a copy of the spfile from the init<sid>.ora file:   sql> create spfile from pfile;     -- you can specify the directory or directories, or leave it as the default.

    4) go back & validate the parameters as per our earlier discussion.

    Hussien, I hope this helps.

    CP

    Hussien Sharaf
  • Aman....
    Aman.... Member Posts: 22,913 Gold Crown
    edited Dec 28, 2015 11:04PM

    I don't have any Windows machine running Oracle db at the moment but I hope this should help. If you can't do anything as you mentioned that the database is completely hung, try to use the option PRELIM  with sqlplus and then shut down the database. After that, you can create the pfile from spfile. Please see below,

    [[email protected] ~]$ sqlplus / as sysdba
    
    SQL*Plus: Release 12.1.0.2.0 Production on Tue Dec 29 09:32:05 2015
    
    Copyright (c) 1982, 2014, Oracle.  All rights reserved.
    
    Connected to an idle instance.
    
    SQL> startup
    ORACLE instance started.
    
    Total System Global Area  578813952 bytes
    Fixed Size            2926952 bytes
    Variable Size          461375128 bytes
    Database Buffers      109051904 bytes
    Redo Buffers            5459968 bytes
    Database mounted.
    Database opened.
    SQL> exit
    Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
    With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
    [[email protected] ~]$ sqlplus -prelim "/as sysdba"
    
    SQL*Plus: Release 12.1.0.2.0 Production on Tue Dec 29 09:32:22 2015
    
    Copyright (c) 1982, 2014, Oracle.  All rights reserved.
    
    SQL> shut abort
    ORACLE instance shut down.
    
    SQL> create pfile='/tmp/pfile.ora' from spfile;
    
    File created.
    
    SQL> 
    

    You can of course edit the pfile afterwards and restart the database using the edited pfile.

    HTH

    Aman....

    Hussien Sharaf
This discussion has been closed.