Solved

Instance_inIitalizatiion_File

Posted on 2011-09-03
6
232 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
  • 4
  • 2
6 Comments
 
LVL 73

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 73

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
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 73

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 73

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

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

708 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

17 Experts available now in Live!

Get 1:1 Help Now