iainmulcahy
asked on
SPFile/PFile help
Hi,
Quite new to Oracle, so please bare with me.
I need to alter my shared pool in the configuration for the DB instance, but I am receiving the ORA-04033 Error (insufficent memory if though there isn't). I see that this problem is common and to fix it I need to alter my SPFile, but I cannot find mine to do so. I ran 'show parameter spfile' but it just returned 'String'. Does this mean I have none?
It seems though that I have a Pfile called init.ora in my Oracle/admin/<instance name>'/pfile/ dir. Do I alter this instead?
Please help as I am not sure what to do next.
Also, any suggestions as to what the Shared Pool value should be? What decides its value?
Many thanks.
Quite new to Oracle, so please bare with me.
I need to alter my shared pool in the configuration for the DB instance, but I am receiving the ORA-04033 Error (insufficent memory if though there isn't). I see that this problem is common and to fix it I need to alter my SPFile, but I cannot find mine to do so. I ran 'show parameter spfile' but it just returned 'String'. Does this mean I have none?
It seems though that I have a Pfile called init.ora in my Oracle/admin/<instance name>'/pfile/ dir. Do I alter this instead?
Please help as I am not sure what to do next.
Also, any suggestions as to what the Shared Pool value should be? What decides its value?
Many thanks.
To change SPFILE parameter:
ALTER SYSTEM SET shared_pool_size=250m scope=both
Before this make a spare copy of the SPFILE as PFILE
CREATE PFILE='c:\init.ora' FROM spfile;
make sure that you use the right parameter
SELECT * FROM v$parameter;
ALTER SYSTEM SET shared_pool_size=250m scope=both
Before this make a spare copy of the SPFILE as PFILE
CREATE PFILE='c:\init.ora' FROM spfile;
make sure that you use the right parameter
SELECT * FROM v$parameter;
Depending on which platform you are using, you will find spfile or pfile either:
<ORACLE_HOME>/database <--- windows or
<ORACLE_HOME>/dbs <-- unix
you will find those files as in formats of spfile<SID>.ora , init<SID>.ora.
>>Oracle/admin/<instance name>'/pfile/
Usually Oracle will not use this unless you start your instance as:
startup pfile='<file in that directory>';
<ORACLE_HOME>/database <--- windows or
<ORACLE_HOME>/dbs <-- unix
you will find those files as in formats of spfile<SID>.ora , init<SID>.ora.
>>Oracle/admin/<instance name>'/pfile/
Usually Oracle will not use this unless you start your instance as:
startup pfile='<file in that directory>';
>>but I am receiving the ORA-04033 Error (insufficent memory if though there isn't).
if you are using spfile (it should most of time), then what you need to do is:
(1) enlarge your sga_max_size
alter system set sga_max_size=<size> scope=spfile;
(2) restart the server.
(3) enlarge your shared_pool_size
alter system set shared_pool_size=<size>;
Acton
if you are using spfile (it should most of time), then what you need to do is:
(1) enlarge your sga_max_size
alter system set sga_max_size=<size> scope=spfile;
(2) restart the server.
(3) enlarge your shared_pool_size
alter system set shared_pool_size=<size>;
Acton
ASKER
Thanks Actionwang, Found my SPFile, but when I issue the Alter system command I get the
ORA-32001: write to SPFILE requested but no SPFILE specified at startup.
ORA-32001: write to SPFILE requested but no SPFILE specified at startup.
ASKER
My SPFILE does exist in the Database folder (using Windows) by the way. I opened it and it has all the values. However if I run 'show parameter spfile', it returns an empty value. Isn't it being recognised? Do I have to create a new one? I tired but i get 'insufficent privileges'.
SELECT * FROM v$parameter;
as user SYS!
Never edit SPFILE!
as user SYS!
Never edit SPFILE!
ASKER
Thanks schwertner. Can you ellaborate on that? Do I alter using v$parameter? Can you give me an example of editing my sga_max_size without using the SPFile?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
THanks man.
Ok, tried create pfile='c:\init.ora' from spfile but I am getting 'ORA-01031: insufficient privileges', even logged in under system. How can I change this
Ok, tried create pfile='c:\init.ora' from spfile but I am getting 'ORA-01031: insufficient privileges', even logged in under system. How can I change this
ASKER
Ok, solved that. Working on your check list.
ASKER
Hi,
everything worked great. I altered the shared pool size, but on 'startup' I get the following errors:
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
everything worked great. I altered the shared pool size, but on 'startup' I get the following errors:
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
ASKER
Ok that's solved.
schwertner, I did everything on your list but it doesn't seem to be loading the data from the SPfile in the database dir. None of my changes are been loaded!!! Is it even using that file?
Also, what file can I save the initialization parameters too?
schwertner, I did everything on your list but it doesn't seem to be loading the data from the SPfile in the database dir. None of my changes are been loaded!!! Is it even using that file?
Also, what file can I save the initialization parameters too?
ASKER
I tried altering the PFILE in Oracle/admin/<instance name>/pfile/ called init.ora and this loaded my changes fine. So I guess I'm done. No need for all the above at all.
Should I create an SPFile from this??
Should I create an SPFile from this??
Yes, you should create SPFILE (using the same name as the existing one).
Shutdown the DB.
Save the old SPFILE.
Replace it with the new one.
Startup the instance.
Shutdown the DB.
Save the old SPFILE.
Replace it with the new one.
Startup the instance.
sorry for late reply,
iainmulcahy:
seems sch had your problem.
I still recommend that you follow my instructions. Better to do from spfile.
acton
iainmulcahy:
seems sch had your problem.
I still recommend that you follow my instructions. Better to do from spfile.
acton
Also regarding the Shared Pool value 200MB should be a good size.
Refer to this link for a script that will help you in estimating your shared pool size.
http://www.ss64.com/orasyntax/sharedpool_sql.txt