Disable MEMORY_TARGET after being locked out of database?

Posted on

Question :

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

Answer :

Find the name of the spfile on the filesystem, it should be something like this:

/u01/app/oracle/product/11.2.0/xe/dbs/spfileXE.ora

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

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

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

Leave a Reply

Your email address will not be published. Required fields are marked *