Solved

Instance_inIitalizatiion_File

Posted on 2011-09-03
6
245 Views
Last Modified: 2012-05-12
What is the difference between init.ora and spfile?

How do you know if a database is using init.ora or spfile or both?
0
Comment
Question by:sam15
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 36478766
both files serve the same purpose, that is defining system parameters


init.ora is a plain text file and can only be editted externally to the database.

spfile can be changed from within the database   with a command like this...
alter database set <some parameter>=<some value>  scope=spfile


look in v$parameter  if spfile is populated then you are using an spfile
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36478769
also,  if you try to execute an alter database command with scope=spfile  or scope=both  it will only work if you have an spfile.  If you don't you'll get an error
0
 

Author Comment

by:sam15
ID: 36480613
so let me clarify something.

A database must have an init.ora but the spfile is optional when you install oracle?

Both have same parameters and server same purpose? it is just one can be edited from O/S and the other using the SQL inside the database.

I think oracle database will read spfile first if it exists when you start the instance. If it does not exist, it will check the init.ora.
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 74

Expert Comment

by:sdstuber
ID: 36480894
you must have one or the other.  you can have both but you must have at least one, it doesn't matter which though


and yes they serve the same purpose,  
correct - you don't modify an spfile with a text editor though,  use alter system for that
0
 

Author Comment

by:sam15
ID: 36481707
if you have both file is oracle really reading both on startup?

I am not sure why oracle introudcued this spfile thing in 9i/10g. king of conufusing to have 2 files that serve same purpose.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36481777
only one will be the "first",  but an init.ora file can include an spfile

similarly  an spfile can include an ifile


they were introduced because it's more convenient and easy to remember to set your values from within the database

rather than issuing alter system  and then editting the init.ora,   you can instead issue alter system with scope=both and update both at the same time.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
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.
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…
Via a live example, show how to take different types of Oracle backups using RMAN.

751 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