Solved

SPFile/PFile help

Posted on 2006-07-04
16
1,105 Views
Last Modified: 2012-05-05
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.
0
Comment
Question by:iainmulcahy
  • 8
  • 4
  • 3
  • +1
16 Comments
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17037051
Your PFILE should be under $ORACLE_HOME/database folder. It would have the name in the following format init<SID>.ora

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
0
 
LVL 47

Expert Comment

by:schwertner
ID: 17037128
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;
0
 
LVL 19

Expert Comment

by:actonwang
ID: 17037618
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>';

0
 
LVL 19

Expert Comment

by:actonwang
ID: 17037639
>>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
   
0
 

Author Comment

by:iainmulcahy
ID: 17040871
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.

0
 

Author Comment

by:iainmulcahy
ID: 17040906
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'.
0
 
LVL 47

Expert Comment

by:schwertner
ID: 17041343
SELECT * FROM v$parameter;
as user SYS!
Never edit SPFILE!
0
 

Author Comment

by:iainmulcahy
ID: 17041399
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?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 47

Accepted Solution

by:
schwertner earned 350 total points
ID: 17041477
No, you can not alter using v$parameter. Only showing.

To test sga_max_size create a pfile. PFILE can be edited with text editors:

SQL>create pfile='c:\init.ora' from spfile;

After that edit the created pfile.

Now shutdown the db and open it using PFILE:

SQL>shutdown immediate
SQL>startup pfile=c:\init.ora

If it works fine create a SPFILE (show the name in ...\rdbms ...)

SQL>create spfile=c:\spfile...ora' from pfile='c:\init.ora'

Now shutdown the instance, backup the old SPFILE, replace it with the new
and bounce the instance:

SQL>startup
0
 

Author Comment

by:iainmulcahy
ID: 17041641
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
0
 

Author Comment

by:iainmulcahy
ID: 17041791
Ok, solved that. Working on your check list.
0
 

Author Comment

by:iainmulcahy
ID: 17042228
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
0
 

Author Comment

by:iainmulcahy
ID: 17042609
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?

0
 

Author Comment

by:iainmulcahy
ID: 17043087
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??
0
 
LVL 47

Expert Comment

by:schwertner
ID: 17043199
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.
0
 
LVL 19

Expert Comment

by:actonwang
ID: 17043629
sorry for late reply,

iainmulcahy:

       seems sch had your problem.
       I still recommend that you follow my instructions. Better to do from spfile.

acton
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now