Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1137
  • Last Modified:

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.
0
iainmulcahy
Asked:
iainmulcahy
  • 8
  • 4
  • 3
  • +1
1 Solution
 
sathyagiriCommented:
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
 
schwertnerCommented:
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
 
actonwangCommented:
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
actonwangCommented:
>>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
 
iainmulcahyAuthor Commented:
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
 
iainmulcahyAuthor Commented:
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
 
schwertnerCommented:
SELECT * FROM v$parameter;
as user SYS!
Never edit SPFILE!
0
 
iainmulcahyAuthor Commented:
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
 
schwertnerCommented:
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
 
iainmulcahyAuthor Commented:
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
 
iainmulcahyAuthor Commented:
Ok, solved that. Working on your check list.
0
 
iainmulcahyAuthor Commented:
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
 
iainmulcahyAuthor Commented:
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
 
iainmulcahyAuthor Commented:
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
 
schwertnerCommented:
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
 
actonwangCommented:
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

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

  • 8
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now