Solved

Instance_inIitalizatiion_File

Posted on 2011-09-03
6
249 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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
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.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

630 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