Monday, August 31, 2009

ORA-27100: shared memory realm already exists

However if you merely see ORA-27100 error upon startup then proceed with this post.

Problem Description
When you try to startup your database instance even you issue startup nomount ORA-27100 is reported.

SQL> startup nomount
ORA-27100: shared memory realm already exists

Similarly, if you try to shutdown your oracle instance then ORA-27100 is reported.

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

Restarting the service also does not help any. And even in some cases, rebooting the server does not help as well.

Current Changes in the Server
We had sga_max_size to 600M and then we change it to 1G. After that whenever we restart oracle database above message rises.

Cause of the Problem
Some changes are made in sga_max_size or sga parameters. After that database is abnormally shut down whenever you issue startup, ORA-27100 is received as a result of changes made to parameters affecting the SGA size.

Solution of the Problem
In most cases, ORA-27100 can be resolved by simply restarting Windows. However if avaialibility is too important and you can't afford the time of starting windows then,
- Move/ Rename database spfile so that next time starting database service can invoke old pfile to startup.

- Start the database service. Make sure pfile exist with old sga parameter settings.

- Now the service will start the database using pfile , with old SGA settings.
and you have started your database successfully.

- Create a new spfile from pfile.


Copyright © 2008 Shaik Abdul Khaleel Technical blog. All rights reserved.