(Oracle SQL 12c running on Windows 10)

My installation has previously been working fine. I was trying to set up one of my tables to use the In-memory option available in 12c. I was following this guide: https://blogs.oracle.com/In-Memory/entry/getting_started_with_oracle_database I wanted to allocate 4Gigs of system RAM to be used as in-memory table storage space for a table I would specify later. To do this I ran these commands using the guide as a reference:

This one to allocate 4G of RAM space:

ALTER SYSTEM SET inmemory_size = 4G scope=spfile;

And this one, which is what caused the problem:

ALTER SYSTEM SET sga_target = 75G scope=spfile;

The guide said "By default, the installer set the SGA_TARGET to 71G, " which is where I came up with 71G + 4G = 75G.

Now of course I get the error when trying to connect to the DB:

ORA-27101: shared memory realm does not exist

the sga requires more space than was allocated for it

Since I can't connect to the database because it won't start, how would I fix the invalid setting? Is there some sort of "undo"?

Edit: No, there is no backup of the spfile or pfile that I can use to generate the spfile. I have manually changed the sga_target to small values that should be fine but still get the error no matter what. I also tried creating a new, additional database. When I did that, the other database started working for some reason. It just looks like the new one though and is missing new tables I had previously created.

  • Do you have a backup of your spfile? Or an alternate pfile that you can use to start your database? If not, you should be able to manually edit the spfile while the database is shut down. You'd never want to have an sga_target that was greater than the amount of RAM on your server-- a 75 GB SGA would only make sense if your server had at least 128 GB of RAM available. Aug 5, 2015 at 21:15
  • @Justin: I think manually editing the spfile is not the supported way. It should be converted in a pfile. This pfile could be edited and converted back.
    – miracle173
    Feb 11, 2016 at 4:41
  • @miracle173 - Ideally, yes. I don't know off the top of my head whether you can convert a spfile to a pfile if you can't start the database. Feb 11, 2016 at 5:25
  • @JustinCave: You need sqlplus but no database
    – miracle173
    Feb 11, 2016 at 6:41

2 Answers 2

  1. Before you start make a copy of the files you change (the spfile)
  2. Check where your spfile is located, on Linux this is $ORACLE_HOME/dbs/spfile${ORACLE_SID].ora. If this does not exist check the content of $ORACLE_HOME/dbs/init${ORACLE_SID].ora. There the spfile parameter may be set which defines the path of the spfile. For Windows look in the %ORACLE_HOME%\database directory instead of.
  3. Open sqlplus and create a pfile from the spfile create pfile='<auxiliarypfile>' from spfile='<spfilepath>'. Do not overwite an existing pfile but write the file in an arbitrary directory.
  4. Edit the pfile <auxiliarypfile> created in step 3 with a text editor like notepad.
  5. Open sqlplus and overwrite the spfile by a new created spfile create spfile='<spfilepath>' from pfile='<auxiliarypfile>'

Try shutting down the database and open a text editor to create a pfile. Only two parameters are required for a pfile - db_name and data_block_size.

Then start up the database in no mount mode using the pfile.

startup pfile=/u01/oracle/dbs/initorcl.ora

You should then be able to alter the sga settings.

  • Why do you startup this instance? That does not make sense.
    – miracle173
    Feb 11, 2016 at 4:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.