Solved

SPFile/PFile help

Posted on 2006-07-04
16
1,103 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
>>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
Comment Utility
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
Comment Utility
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
Comment Utility
SELECT * FROM v$parameter;
as user SYS!
Never edit SPFILE!
0
 

Author Comment

by:iainmulcahy
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Ok, solved that. Working on your check list.
0
 

Author Comment

by:iainmulcahy
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

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.

Join & Write a Comment

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

743 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

15 Experts available now in Live!

Get 1:1 Help Now