ORA-27100 after altering the SGA_MAX_SIZE

[Carlos Correia]

Hi all,

After issuing the following command
ALTER SYSTEM SET sga_max_size='2097152000'scope=SPFILE
and restarting the server.

Oracle does not seem to start up correctly. I then attempt

E:\oracle\product\10.1.0>sqlplus /nolog

SQL*Plus: Release 10.1.0.2.0 - Production on Wed Jul 20 09:36:57 2005

Copyright (c) 1982, 2004, Oracle. All rights reserved.

SQL> connect / as sysdba
Connected to an idle instance.
SQL> startup
ORA-27100: shared memory realm already exists
SQL> startup force
ORA-27100: shared memory realm already exists
SQL> shutdown immediate
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
SQL> shutdown abort
ORACLE instance shut down.
SQL> exit
Disconnected

E:\oracle\product\10.1.0>sqlplus /nolog

SQL*Plus: Release 10.1.0.2.0 - Production on Wed Jul 20 09:38:05 2005

Copyright (c) 1982, 2004, Oracle. All rights reserved.

SQL> connect / as sysdba
Connected to an idle instance.
SQL> startup force
ORA-27100: shared memory realm already exists

------
SERVER DETAILS
RDBMS Version: 10.1.0.2
Operating System and Version: Windows 2003 SP1
Error Number (if applicable): ORA-27100

---

How do I go about fixing this problem.

Thanks in advance for your help.

[DA Morgan]

Post by Carlos Correia
Hi all,
After issuing the following command
ALTER SYSTEM SET sga_max_size='2097152000'scope=SPFILE
and restarting the server.
Oracle does not seem to start up correctly. I then attempt
E:\oracle\product\10.1.0>sqlplus /nolog
SQL*Plus: Release 10.1.0.2.0 - Production on Wed Jul 20 09:36:57 2005
Copyright (c) 1982, 2004, Oracle. All rights reserved.
SQL> connect / as sysdba
Connected to an idle instance.
SQL> startup
ORA-27100: shared memory realm already exists
SQL> startup force
ORA-27100: shared memory realm already exists
SQL> shutdown immediate
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
SQL> shutdown abort
ORACLE instance shut down.
SQL> exit
Disconnected
E:\oracle\product\10.1.0>sqlplus /nolog
SQL*Plus: Release 10.1.0.2.0 - Production on Wed Jul 20 09:38:05 2005
Copyright (c) 1982, 2004, Oracle. All rights reserved.
SQL> connect / as sysdba
Connected to an idle instance.
SQL> startup force
ORA-27100: shared memory realm already exists
------
SERVER DETAILS
RDBMS Version: 10.1.0.2
Operating System and Version: Windows 2003 SP1
Error Number (if applicable): ORA-27100
---
How do I go about fixing this problem.
Thanks in advance for your help.

Did you start the Oracle service in the Control Panel.

Verify it is running.

[Carlos Correia]

Yeah everything had started ... managed to create spfile from pfile and
it go sorted.

[Babaloo]

On 20 Jul 2005 02:03:57 -0700, "Carlos Correia"

Post by Carlos Correia
Copyright (c) 1982, 2004, Oracle. All rights reserved.

Detailed solution to identical problem follows:

I know the original poster mentioned create spfile from pfile, but
here's how I got it to work, in a little more detail. Many thanks to
Carlos for the reply to himself which gave me the hint!

The problem, in my case, seemed to be that the Windows 2003 server
couldn't create the shared memory of the right size, and left the
remnants of the failed creation out there. This is using 10g 10.1.0.2.

This is compounded by the fact that the SPFILE I was using was in the
default location, (on windows: ORACLEHOME\database\SPFILE<SID>.ORA)
It was picking up the info in this SPFILE instead of the PFILE I was
specifying to oradim, which caused problems, to say the least!

So, to fix this problem, I did this:
1) Stop the service, in my case using:
C:\> oradim -shutdown -sid <SID> -shuttype srvc,inst
This frees up the shared memory handle that was left dangling.

2) Rename and backup the default installed PFILE (on windows
ORACLEHOME\database\init<SID>.ora)

3) Copy the PFILE you want to use to start the instance to the default
location (see above).

4) Restart JUST THE SERVICE, NOT THE INSTANCE!!! Very important!
ORACLEHOME\database\> oradim -startup -sid <SID> -starttype srvc
-pfile init<SID>.ora

5) sqlplus '/ as sysdba'

6) SQL> create spfile from pfile;

7) SQL> startup;

8) SQL> exit;

9) Verify that the oracle process is consuming memory, not running at
about 18 MB. The virtual memory used by the oracle process (viewable
in task manager) should be about the sum of the PGA and SGA in your
PFILE. It might take a few moments to get there.

10) Start the EM service (OracleDBConsole<SID> in the services list)

11) Check the results by seeing if the EM shows your database. It
should be good, or at least mine was. It can take a seeming eternity
to get past the "agent is down" message. Just wait.

If this doesn't work, I don't know what else to tell you. This worked
for me... I am writing this without the server as reference, so I
can't swear that I didn't make a typo.

Note that create spfile from pfile='path' didn't work for some reason,
although I might have just been tired and made a mistake.

Good luck.

Bill (***@S.P.A.M.A.L.O.T.istation.N.O.T.com)
remove dotted caps for mail.