After increase in the value of SGA_MAX_SIZE from 1500M to 2G to accommodate future database growth requirement without a restart of the database instance the following issue has been encountered:
ORA-27100: shared memory realm already exists is reported during a manual startup of the database .
ORA-27100: shared memory realm already exists
SQL> shutdown immediate
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Restarting the Oracle service, the same error is encountered.
Rebooting the database server machine does not help as well to free some memory for oracle (because that also restarts the service).
The actual root cause is in fact the limitation of physical memory.
When the service is not restarted but the instance is stopped and restarted after increasing sga_max_size, the next errors can be observed:
ORA-27102: out of memory
OSD-00022: additional error information
O/S-Error: (OS 8) Not enough storage is available to process this command.
To be able to start the instance/database again, please use the next steps:
C:\> sqlplus / as sysdba
Connected to an idle instance.
SQL> create pfile from spfile;
— Edit the pfile to correct the too large sga_max_size value
— to a value fitting the available physical memory
SQL> create spfile from pfile;
— Restart the service
— This uses the corrected spfile to automatically start the instance
— (with ORA_<SID>_AUTOSTART=TRUE in the Windows Registry)