0

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.

1

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.

  • I was able to update the /tmp/initXE.ora file, but when I try to recreate back the spfile I got SQL> create spfile='/u01/app/oracle/product/11.2.0/xe/dbs/spfileXE.ora' from pfile='/tmp/initXE.ora'; create spfile='/u01/app/oracle/product/11.2.0/xe/dbs/spfileXE.ora' from pfile='/tmp/initXE.ora' * ERROR at line 1: ORA-32002: cannot create SPFILE already being used by the instance – danidemi Dec 21 '15 at 15:05
  • @danidemi Did you start the instance somehow? Seems it was already running. – Balazs Papp Dec 22 '15 at 11:56
  • Let me elaborate. As I described in the question, halving the amount of RAM in the virtual machine triggered the Oracle problem and made it unable to start. In this situation I was not able to execute create pfile you suggested because sqlplus complained about not being connected. So I went back to 4Gb, I reconfigured Oracle and it restarted successfully. I was then able to connect from sqlplus and execute the create pfile command, but the create spfile failed because obviously I was connected to the very same instance I was trying to modify. – danidemi Dec 22 '15 at 14:31
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.