Home > DBA Scripts, Oracle, Oracle 10g, Oracle 9i, SQL > Oracle – Pfile and Spfile

Oracle – Pfile and Spfile

The default location for a spfile or pfile is $ORACLE_HOME/dbs however if you are unsure as to where your spfile is located you can issue the following from SQLPLUS:

SQL> SHOW PARAMETER spfile;
NAME     TYPE       VALUE
--------     -------       ---------
spfile       string       /app/oracle/product/10.2.0.4server/db_1/dbs/spfileictst3f.ora

Create pfile from spfile

If you wish to backup your spfile, you can create a pfile from the spfile which will save the current parameter configuration.

SQL> create pfile='<pfile location>' from spfile;

or

SQL> CREATE PFILE='<pfile location>' FROM SPFILE = '<spfile location>';

Create spfile from pfile

If you want to then revert back to a saved pfile, you can overwrite your current spfile with the following:

SQL> CREATE SPFILE FROM PFILE = '<pfile location>';

or

SQL> CREATE SPFILE='<spfile location>' FROM PFILE='<pfile location>';

Check spfile parameters

To check all the spfile parameters you can issue the following:

SQL> SHOW PARAMETER;

Check pfile parameters

You can either check the pfile parameters, after starting a database up with a pfile, using the above method. Or you can go to the pfile location and either cat or view the file. To change the pfile parameters you can directly edit the pfile using vi from the command line.

Change spfile parameters

Some parameters you can save to memory, for use just within the current session; some you can save within the spfile; and some must be set at both. Parameters saved within the spfile can only be initiated following a database restart.

To save a new parameter within the spfile you issue the following within SQLPLUS:

SQL> ALTER SYSTEM SET <parameter name>='<value>' SCOPE=[SPFILE/MEMORY/BOTH];

Startup database with pfile or spfile

If you want to startup a database using either a different spfile or using a pfile you first need to shut it down. Then you should issue the following:

SQL> CONNECT sys/password AS SYSDBA
SQL> startup pfile='<pfile location>';

or

SQL> CONNECT sys/password AS SYSDBA
SQL> startup spfile='<spfile location>';

Advertisements
  1. GURUVAREDDY BOLLA
    July 13, 2011 at 12:35 PM

    SQL> CREATE PFILE=’C:\ORACLEXE\APP\ORACLE\ADMIN\XE\PFILE’
    2 FROM SPFILE=’C:\ORACLEXE\APP\ORACLE\PRODUCT\10.2.0\SERVER\DATABAS\SPFILE’;
    CREATE PFILE=’C:\ORACLEXE\APP\ORACLE\ADMIN\XE\PFILE’
    *
    ERROR at line 1:
    ORA-27041: unable to open file
    OSD-04002: unable to open file
    O/S-Error: (OS 3) The system cannot find the path specified.
    I AM GETTING THIS ERROR..

    • July 13, 2011 at 12:43 PM

      That error means that you are referencing a file that does not exist.
      I suggest checking the location of C:\ORACLEXE\APP\ORACLE\PRODUCT\10.2.0\SERVER\DATABAS\SPFILE
      If that file does not exist, then you will get the ORA-27041 error.
      It looks like you might have a typo of DATABAS instead of DATABASE.

  2. May 20, 2013 at 10:41 AM

    While in town, be sure that you have planned at the very least trying,
    and we had no trouble pairing them with our setup. Along with kilometer
    customers kilometer connected with shoreline along with a rental car will
    allow you to rent Mercedes at cheap affordable price rates and get an Essex Wedding
    paphos car hire.

  3. June 28, 2013 at 11:20 AM

    Just want to say your article is as astounding. The clarity in your post
    is just great and i could assume you’re an expert on this subject. Well with your permission allow me to grab your feed to keep updated with forthcoming post. Thanks a million and please keep up the gratifying work.

  4. pinterest
    July 27, 2013 at 8:15 PM

    I got this web page from my pal who informed me about this web page and at the moment this time I am
    visiting this site and reading very informative posts at this time.

  5. August 14, 2013 at 3:02 AM

    Nice blog here! Also your site loads up fast! What web host are you using?
    Can I get your affiliate link to your host? I wish my web
    site loaded up as fast as yours lol

  6. September 19, 2013 at 12:23 AM

    Fastidious answer back in return of this difficulty
    with firm arguments and tellling the whole thing concerning
    that.

  7. December 2, 2013 at 3:45 PM

    It iis perfect time to make some plans for the future and it is
    time to be happy. I have learn this putt up and
    if I may just I wish to recmmend you some attention-grabbing issues or tips.
    Perhaps you could write next articles regarding
    this article. I want to read even more things about it!

  8. January 12, 2014 at 5:49 PM

    Hi friends, good post and good arguments commented at this place, I am genuinely enjoying by these.

  9. May 10, 2014 at 12:25 AM

    I’m wanting to download some videos from various different sites like youtube, Google videos, and a few other sites for my entertainment at home. I’ve downloaded an extension from Media Converter for Firefox, but I don’t see it anywhere. Can someone recommend an extension to allow me to download online videos?.

  10. May 12, 2014 at 1:11 PM

    Where or what would I have to do? And what’s the cost?.

  11. May 14, 2014 at 4:41 AM

    I’m planning to write a literature blog, and I want to get as much readers as possible, even those who don’t like books much, to be able to encourage them to read. Suggestions? No bashing please!.

  12. May 16, 2014 at 6:31 PM

    I know anything you write is technically copyrighted, but I’m not sure how you could prove that it was your original material..

  13. May 27, 2014 at 9:06 AM

    Hi there. I was just wondering why its such a big thing these days to have a ridiculous amount of ram in a computer? People tell me that the more Ram you have in your computer the faster your computer will be. I always thought that it was the processor that controls the speed of a computer and not the Ram.. . I had 512MB of Ram in my laptop and I have now changed it to 2Gigs of Ram and I’ve seen no difference in performance what so ever. . Could somebody please explain to me what does Ram does exactly?. Thanks in advance.

  14. June 4, 2014 at 6:47 AM

    I want to do a live webcast, but I can’t figure out how to do it on the Blogger platform. Can anyone give me some direction?. . Thanks!.

  15. June 16, 2014 at 9:53 PM

    How do I get “undefined” on my blog posts? There are 3 undefined words right below the title of my blog post. How do I get rid of it?.

  16. June 24, 2014 at 12:10 PM

    I would like to change my blogger archive frequency from 1 month to longer, like 3 months. How do I do this?.

  17. June 24, 2014 at 12:50 PM

    Is it possible to make a spoiler button in blogger blog posts ? I don’t know javascript and know very basic html only. I’ve tried copying snippets of javascript into my posts to try but most of them doesn’t work. Some only works in the create post window but after publishing it doesn’t work anymore. Most buttons just does nothing after being clicked. Any solutions ?.

  18. June 24, 2014 at 6:12 PM

    Just curious if there are any blogging websites where I can start a blog that aren’t as well known.. I am already aware of sites like blogger, wordpress, livejournal, xanga, vox, etc. . . Are there any up and coming blogging websites possibly?.

  19. mcdqqvr@gmail.com
    June 30, 2014 at 11:33 AM

    I’ve asked my older sister countless times to format my computer because I’m experiencing lag in the game I play. Btw, this computer has been formatted 4-5 times. But whenever I ask my sister to format my computer, she says no because she says that if you format a computer too much, the computer itself gets slower than it already was. Is this true? Or is that an excuse to get out of formatting my computer because it takes away her 2-3 hours?.

  20. July 1, 2014 at 9:43 PM

    I’m into photography and would like to start selling my work and attracting more clients. My question is how would I go about starting a website where people can buy my photos or contact me about taking photos? I’m looking for a way to maximize my advertising and would really like to start a website. Any advice would be greatly appreciated..

  21. July 4, 2014 at 12:02 AM

    Hi all – I have a straight-up HTML website that I’d like to transition to Joomla. If possible, I’d like to install Joomla into a subfolder and create and test the site there, while keeping the existing site functioning until I’m ready to move it over. Is this possible?. . Thanks!.

  22. July 4, 2014 at 1:17 PM

    I’ve been looking for blogs where blogger covers penis enlargement or at least delves into the topic of penis enlargement with an independent and serious voice. What are the leading blogs of this type?.

  23. July 5, 2014 at 6:03 AM

    I’m going to start a serious blog that will eventually hold a great deal of content. I am a graphic designer, and my husband does my web programming, so this is for reals (haha, yes I just said that). That being said, I’ve already started collecting my content and writing entries, but just how much content should a brand new blog have when you “unveil” it to the world (ideally)?. . Thanks :).

  24. July 5, 2014 at 7:03 PM

    Write more, thats all I have to say. Literally, it seems as
    though you relied on the video to make your point.
    You obviously know what youre talking about, why waste
    your intelligence on just posting videos to your site when you could be giving us something
    enlightening to read?

  25. July 6, 2014 at 7:56 AM

    If I want to use a font that says it’s for personal use only, would it be ok to use on blogger? Also, I don’t know if getting Google Adsense would make a difference..

  26. mcdqqvr@gmail.com
    July 7, 2014 at 12:15 AM

    I may have found something that is copyright infringement on associated content… What exactly happens if it’s reported?. Does associated content nail down on the writer? Or is the content simply removed?. Is the writer charged with criminal offenses?. Thanks..

  27. July 9, 2014 at 11:29 PM

    Just want to share something. I need to submit my blogs to blog sites with so many people reading blogs. That’s what I need most..

  28. July 10, 2014 at 6:01 PM

    How do I get “undefined” on my blog posts? There are 3 undefined words right below the title of my blog post. How do I get rid of it?.

  29. July 14, 2014 at 8:46 PM

    Wow! That’s an interesting slant.

  30. July 16, 2014 at 7:00 PM

    I have a domain that is redirecting it views to my main site. I want to build a wordpress site on that domain but i cant access my admin for wordpress because it just redirects me to my main site. I don’t want to take my domain off redirect until the wordpress site is done. So what do i do?. It is redirected thru my registrar. How would i login with my IP?.

  31. July 18, 2014 at 8:10 AM

    I want to add a registration form to Joomla.. . I need to be able to edit the fields of the form. . The form needs to register people as users in Joomla, and trigger an HTML welcome email, as well as a notification to an admin about the registration.. . I’ve tried community builder, but you can’t disable things like username, which is irrelevant to me, and the welcome emails won’t do HTML.. . Any suggestions?.

  32. July 19, 2014 at 5:43 AM

    I use firefox and some nights at 12:00 exactly or 12:30 exactly, firefox stops working and does not load a page. The internet connection is perfectly fine though because it says excellent. I am connected to a home router and the signal strength is always excellent. I do not know why this happens at exactly 12:00 or 12:30 on my desktop time but it’s a pain. I’ve tried “ping”ing and everything looks fine..

  33. July 20, 2014 at 5:09 AM

    Whenever I go on my computer after a few minutes (I’d say about 5) it just restarts for some reason. I’ve tried to restore my computer but can’t because it will restart before it finishes. How can i stop the restarting or reatore my computer when this is happening Someone please help.

  34. rfgxcmmm@gmail.com
    July 20, 2014 at 10:14 AM

    I like Facebook, but I hate that their notes section isn’t as appealing as MySpace’s blogs. I’ve recently transferred a blog from MySpace to Facebook using the “share” icon located beneath each blog.. . However, I do not like the way it appears on my Facebook page. Are there any better ways to import my blogs?.

  35. July 26, 2014 at 3:54 AM

    Either double major with journalism and creative writing, or a “good” school in an urban area where I could major in journalism and minor in creative writing? I’m only a sophmore in high school but I know I love to write and I don’t know what I’d enjoy more as an actual career, journalism or creative writing. What would be the best college for someone like me?.

  36. August 2, 2014 at 10:07 AM

    I really love to write and I’m pretty good at it. But whenever I try to write a story or something I start off good but always end up throwing it away–I can never seem to finish it. . . Sometimes I have a good idea that I really like but it’s just hard for me to write a story about and keep going. I like creative writing, but now I’ve almost given up because I can’t even write a short story. HELP!!! What should I do?. Any good creative writing websites to help me get started? Books?. I’m DESPERATE!!.

  37. August 5, 2014 at 3:41 AM

    What’s a good wordpress blog theme for an icanhascheeseburer or failblog clone?

  38. August 7, 2014 at 5:12 PM

    certainly like your web-site but you need to test the spelling on several of your posts.
    Several of them are rife with spelling problems and I to find it very troublesome
    to tell the reality however I’ll certainly come back again.

  39. August 11, 2014 at 9:39 PM

    I’m interested in fashion and I want to start a blog but I have no idea where to start or how to get people interested in my blog. Any ideas welcome..

  40. August 28, 2014 at 9:49 AM

    My family members every time say that I am wasting my time here at web, however I know I am getting experience everyday by reading such nice articles or reviews.

  41. September 15, 2014 at 3:47 AM

    Good respond in return of this issue with firm arguments and describing the
    whole thing concerning that.

  42. January 9, 2015 at 3:04 PM

    This post presents clear idea in support of the new people of blogging, that in fact how to do blogging and site-building.

  43. Anonymous
    January 4, 2016 at 5:55 AM

    VERY GOOD TIPS

  44. Anonymous
    February 24, 2017 at 10:17 AM

    v

    • Anonymous
      February 24, 2017 at 10:18 AM

      very good

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: