I’m setting up a local development environment in a virtual machine in which I installed Oracle XE among other software.
When I initially set up the virtual machine, the RAM was set to 4Gb. In this condition, I installed and run Oracle XE smoothly, without problem.
Then 4Gb of RAM proved to be a little to much for the laptops of my teammates, so I stopped the virtual machine, decreased its memory to 2Gb and restarted it.
Since then Oracle XE starts, as far as I can tell from the output of the service, but it refuse incoming connections because it seems MEMORY_TARGET is set too high.
Now, since I’m currently locked out from the database, how can disable the MEMORY_TARGET at all, or at least decrease the amount of space it tries to allocate ? I know I should be able to do it altering some system tables, but I’m not longer able to connect.
Here’s some info you can need to give a clearer picture:
cat /u01/app/oracle/diag/rdbms/xe/XE/trace/alert_XE.log WARNING: You are trying to use the MEMORY_TARGET feature. This feature requires the /dev/shm file system to be mounted for at least 1073741824 bytes /dev/shm is either not mounted or is mounted with available space less than this size. Please fix this so that MEMORY_TARGET can work as expected. Current available is 1049767936 and used is 73728 bytes. Ensure that the mount point is /dev/shm for this directory. memory_target needs larger /dev/shm
This is instead the status of the service…
$ sudo service oracle-xe status LSNRCTL for Linux: Version 184.108.40.206.0 - Production on 21-DEC-2015 12:04:39 Copyright (c) 1991, 2011, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 220.127.116.11.0 - Production Start Date 21-DEC-2015 11:31:33 Uptime 0 days 0 hr. 33 min. 5 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Default Service XE Listener Parameter File /u01/app/oracle/product/11.2.0/xe/network/admin/listener.ora Listener Log File /u01/app/oracle/product/11.2.0/xe/log/diag/tnslsnr/infocertcop/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_XE))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=infocertcop.localdomain)(PORT=1521))) Services Summary... Service "PLSExtProc" has 1 instance(s). Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully
I know I should be able to fix the issue restarting the virtual machine with 4Gb of RAM, change the MEMORY_TARGET configuration and then decrease again the amount of RAM, but I would really prefer to discover how to do it in another way, if possible.
Find the name of the spfile on the filesystem, it should be something like this:
You should not edit the spfile directly as it contains binary data, but you can create a pure, text-based parameterfile from it:
sqlplus /nolog create pfile='/tmp/initXE.ora' from spfile='/u01/app/oracle/product/11.2.0/xe/dbs/spfileXE.ora'; exit
/tmp/initXE.ora, modify the values you want, then recreate the spfile from it:
sqlplus /nolog create spfile='/u01/app/oracle/product/11.2.0/xe/dbs/spfileXE.ora' from pfile='/tmp/initXE.ora'; exit
Once it’s done, try restarting the database.
Create a pfile initDB.ora which contains these 3 lines, this will override memory_target value from spfile:
spfile='/u01/app/oracle/product/11.2.0/xe/dbs/spfileXE.ora' memory_target=xG memory_max_target=xG
Connect to DB using sqlplus.
sql> shutdown immediate sql> startup pfile='/path/initDB.ora' sql> alter system set memory_target=xG scope=spfile sid='*'; sql> alter system set memory_max_target=xG scope=spfile sid='*'; sql> shutdown immediate sql> startup