ORA-27100: Shared Memory realm already exists Solution

This Unit adds a server with 8 GB memory. After installing the Oracle 9i Database, I want to reset the SGA. During the test, we found that when the maximum size of SGA is set too large, the spfile file of the database will be damaged and the database cannot be started, after searching for information on the Internet, we found that there is a limitation of 1.7g for the SGA Of the 32-bit Oracle machine. If you accidentally set the sga_max_size value to more than 1.7 GB, the ORA-27100 shared memory realm already exists error is reported during Oracle Database startup.

There are two solutions ):

 

First:

The spfile file (in the/Oracle/ora92/database directory) is a binary file and cannot be edited directly. You can recreate the pfile file (in the drive letter: /Oracle/admin/SID/pfile directory ).

SQL> shutdown immediate;

Pfile files can be directly written. You can change the value of sga_max_size to the appropriate size, connect to the database as sysdba, and then start pfile

SQL> startup pfile = 'd:/Oracle/ora92/database/pfilesid. ora ';

Delete the original spile file and recreate the spile file through the corrected pfile file.

SQL> Create spfile = 'd:/Oracle/ora92/database/spfilesid. ora'

From pfile = 'd:/Oracle/ora92/database/pfilesid. ora ';

SQL> startup;

Summary: When the instance cannot be started because of a SGA change, in this case, it cannot be changed through the spfile (binary file), you can change it by creating a pfile file.

Second:

This is a Windows Service andOracleIf the sga_max_size problem occurs during Oracle Windows Startup, the instance will not be started successfully, but there will still be an error of Instance Storage. Neither shutdown immediate nor shutdown abort can be closed. Because Oracle automatically starts an instance when the Windows service starts by default, the instance is automatically started with the default spfile every time the service is started, leading to memory errors.
 
After knowing the cause of the problem, the solution is simple. There are two solutions:
1. Shut down the service, delete the spfile file of the database, and use the new correct pfile to start it and generate the new spfile;
2. Set the database to not automatically start the instance when the service starts, start the instance with the correct pfile, and then generate a new spfile.
 

There are two ways to set the database to not automatically start an instance when the service is started:
1. directly modify the database object item ora_mydb_autostart in the Registry HKEY_LOCAL_MACHINE/software/Oracle/home0 to false.
2. Use the Oracle Installation Tool administration assistant for Windows NT to modify the database startup and shutdown options.
"Oracle Managed Objects" -- "Database" -- "Database Name" -- Right-click "enable and disable options" -- remove the check box of "startup routine at Service Startup. (You can also right-click here to stop the database service)

 

1. Later, in the pfile file of the database
*. Sga_max_size = 1224736768 (1168 m)
*. Sga_target = 1224736768
The above SGA parameter settings are small

2. Shut down the "oracle" Process in the "service" and use pfile to start the database when restarting. It can be started successfully.
(The system is different, but in many cases it is not the same. Oracle in Windows feels a lot of problems, but prefer oracle in UNIX and Linux systems)

 

 

Reprinted from: http://blog.sina.com.cn/s/blog_5fdd008d0100dfvn.html