ORA-27100: shared memory realm already exists

by Snehashish Ghosh

Scenario


 After increase in the value of SGA_MAX_SIZE from 1500M to 2G to accommodate future database growth requirement without a restart of the database instance the following issue has been encountered:

ORA-27100: shared memory realm already exists is reported during a manual startup of the database .
SQL> startup

ORA-27100: shared memory realm already exists

SQL> shutdown immediate
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
SQL>

Restarting the Oracle service, the same error is encountered.
Rebooting the database server machine does not help as well to free some memory for oracle (because that also restarts the service).

The actual root cause is in fact the limitation of physical memory.
When the service is not restarted but the instance is stopped and restarted after increasing sga_max_size, the next errors can be observed:

SQL> startup
ORA-27102: out of memory
OSD-00022: additional error information
O/S-Error: (OS 8) Not enough storage is available to process this command.
SQL>
 
Solution

To be able to start the instance/database again, please use the next steps:

C:\> sqlplus / as sysdba
...
Connected to an idle instance.

SQL> create pfile from spfile;

—  Edit the pfile to correct the too large sga_max_size value
—  to a value fitting the available physical memory

SQL> create spfile from pfile;

—  Restart the service 
—  This uses the corrected spfile to automatically start the instance 
—  (with ORA_<SID>_AUTOSTART=TRUE in the Windows Registry)

You can also perform the following:
 
1. stop the windows Oracle Service
2. copy the pfile from most recent backup which should have been done before making any change in Production database.
3. Paste the pfile into the <ORACLE_HOME>\database directory.This pfile contains old value for SGA_MX_SIZE parameter.
4. rename the old spfile to some other meaningful name like spfileSID.ora_old_date
5. start oracle from windows service, which now uses pfile as spfile is not available.
6. In the command prompt connect as sysdba,create spfile from pfile.
7. shutdown and start database. increase the sga_max_size to permitted value,restart database again.
 
Conclusion

The Oracle database server instance first looks for spfile in the default directory,
if spfile is not available Oracle instance searches for default pfile in the default directory.Using the knowledge of default parameter file lookup sequence by Oracle Instance memory related parameter issues like ORA-27100: shared memory realm already exists can be solved. 
 
 
 
 
 
 
 

Leave a reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">

*