need help getting server started on unix - shared pool memory issue 1

[focusdev]

I'm a newb to both oracle and linux, but I just installed oracle 10g on linux last night and everything seemed to be working fine until I tried to bring the oracle db back up today.

After entering the following command (which I assume starts oracle)
STARTUP OPEN PFILE=$ORACLE_HOME/dbs/init.ora
I get the following error
ORA-00371: not enough shared pool memory, should be atleast 62198988 bytes

The following settings were added to /etc/sysctl.conf as per the instructions.

kernel.sem = 250 32000 100 128
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
fs.file-max = 65536

 

[SantaMufasa]

Focus,

Oracle typically runs with over 200 parameter settings.

The following settings were added to /etc/sysctl.conf as per the instructions.

kernel.sem = 250 32000 100 128
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
fs.file-max = 65536

None of the 200+ Oracle parameter settings have their values set from the above Unix o/s settings.


You can confirm the current values of your Oracle instance's parameter settings by querying the V$PARAMETER data dictionary view. Following is a script, that I call "parms.sql", which I created to facilitate querying either specific, or all, Oracle parameter settings:

col p heading "Parameter" format a41
col v heading "Value" format a50 word wrap
set verify off
accept parm prompt "Enter a (partial) parameter name to display (or <enter> for all): "
select name p, value v from v$parameter
where name like lower('%&parm%')
order by name

Since the script contains an "ACCEPT...PROMPT" command, you must run the code as a script (not with a copy-and-paste). Here is a sample invocation of "parms.sql" that shows my settings (on a Unix Oracle instance) for the current value of its SHARED_POOL_SIZE:

SQL> @parms
Enter a (partial) parameter name to display (or <enter> for all): shared_pool_size

Parameter            Value
-------------------- ---------------
shared_pool_size     250000000

Notice that in this case, this instance runs at 250M bytes.

If you do not explicitly set a parameter's value for your Oracle instance, then Oracle assigns a default value to the parameter. You can confirm your Oracle instance's current SHARED_POOL_SIZE parameter setting by running the above script.

Since you cannot modify the Oracle instance's SHARED_POOL_SIZE parameter with the Unix settings, above, you can set Oracle parameter values via:

1) the "traditional" method: modifying parameters in the Oracle "init<SID>.ora" file (usually located in Unix in your $ORACLE_HOME/dbs path), or,

2) the spfile (which values reside in the database), or,

3) both of the above.

I'll explain how to modify your instance's SHARED_POOL_SIZE via editing the instance's init<SID>.ora file:

1) locate and edit (via "vi", for example) your Oracle instance's init<SID>.ora file. (Note: if your instance's name is "XYZ", the the parameter file would have the name, "initXYZ.ora", and reside, most likely, in your $ORACLE_HOME/dbs path.

2) Locate (or create) in that file the parameter labelled, "SHARED_POOL_SIZE". Cause the entry to read, "SHARED_POOL_SIZE=<some value>", where <some value> is at least the 62MB that the error message advises, but preferrably a larger number, similar to the 200+MB value appearing in my query, above.

3) Save the file.

4) Bounce your Oracle instance.

If you have additional questions, please post.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[focusdev]

Thanks Mufasa,

I tried to find the initorcl.ora file for the pfile setting but could not find it. I did find the memory setting in the initdm.ora file and tried that but it stated that the database name was invalid or missing.

Just on a hunch, I tried just the startup command by itself and server did come up, but I could not utilize isqlplus. I will need to learn how to start that service another day.

 

[KenCunningham]

I assume you are trying to run isqlplus as user oracle? If so, check that the full path to isqlplus is included in the oracle user's PATH variable - use echo $PATH to see what it currently is.

I want to be good, is that not enough?

 

[focusdev]

Ken,

Correct me if i'm wrong, but isqlplus is an application that is run through an http listener on port 5560 and would have nothing to do with how I logged in. If I were to open up port 5560 on the firewall, I should be able to use isqlplus from any computer on the network without actually signing into linux. This being the case, the PATH variable would have no effect.

Now if you are thinking about sqlplus (the terminal interface for sqlplus) that I do have access to.

 

[KenCunningham]

Hi - you're probably right, I've never used isqlplus, hence my ignorance!!

I want to be good, is that not enough?

 

[focusdev]

Just to keep the thread complete - I did find how to start isqlplus.

The following path is what oracle defaulted to durring installation, but the command is

/u01/app/oracle/product/10.1.0/db_1/bin/isqlplusctl start