Re: ORA-27100 after altering the SGA_MAX_SIZE

On 20 Jul 2005 02:03:57 -0700, "Carlos Correia" <cepcorreia_at_hotmail.com> wrote:

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

1. Start the EM service (OracleDBConsole<SID> in the services list)
2. 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 (billl_at_S.P.A.M.A.L.O.T.istation.N.O.T.com) remove dotted caps for mail. Received on Fri Oct 28 2005 - 13:18:12 CDT