Solution to ORA-27100sharedmemoryrealmalreadyexists errors

Environment: win 2003 + oracle 10g

Win 2003 Server physical memory 4 GB

Oracle10g SGA 1504 M

Accident Background:

Adjust the SGA size because the OS memory is only 4 GB. We recommend that the SGA size not exceed 60%.

SQL> alter systemset sga_max_size = 2000 m scope = spfile;

Restarting Database Error ORA-27100 shared memory realm already exists

Cause analysis: the 32-bit oracle sga has a GB limit.

Solution:

Spfile is a binary file and cannot be edited directly. You can recreate the pfile file.

SQL> create pfile = 'd: \ oracle \ ora92 \ database \ pfileSID. ora 'from spfile = 'd: \ oracle \ ora92 \ database \ spfileSID. ora'

SQL> Shutdown immediate

PfileSID. ora, modify the following two parameters in the parameter file,

Reduce parameters.

*. Sga_max_size = 600000000
*. Sga_target = 600000000

Find manage/service/OracleServiceORCL to restart

SQL> Startup pfile = 'd: \ oracle \ ora92 \ database \ pfileSID. ora'

The ORACLE routine has been started.

Total System Global Area 603979776 bytes
Fixed Size 1250428 bytes
Variable Size 163580804 bytes
Database Buffers 432013312 bytes
Redo Buffers 7135232 bytes
The database has been loaded.
The database has been opened.

SQL> Create spfile = 'd: \ oracle \ ora92 \ database \ spfileSID. ora 'From pfile = 'd: \ oracle \ ora92 \ database \ pfileSID. ora'

SQL> startup force
SQL> show parameter spfile

SQL> show parameter sga;

Today, follow the online method. How to modify initialization parameters always reports an error. It took nearly one hour to restore the database. It was time to correct the database. Therefore, you must remember to back up the database after making similar changes.

System parameter modification operations are involved, and must be backed up,

Cp initsid. ora initsid. ora. bak

Cp spfilesid. ora spfilesid. ora. bak. In case of any problems, you must restore the service and shorten the downtime.

Configuration that cannot be modified repeatedly

S ga_max_size = 547483648

S ga_target_size = 547483648

Pag_aggregate_target = 149946368, which may be caused by PGA + shared pool + db cache + large pool> sga.

Correct Configuration:

Sga_max_size = 1524 M

Sga_target_size = 1524 M

Pag_aggregate_target = 50 M