Oracle Error: the sga requires more space than was allocated for it

Question

(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.

Answer 1

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>'

Answer 2

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.