Oracle initialization parameter file

Every Oracle instance makes use of a set of initialization parameters that dictate database limits such as

1. the number of users,
2. specify the names and locations of key files and directories,
3. and optimize performance by setting the size of key resources such as memory.

Before we jump into the details of Oracle database creation, it’s important to familiarize ourself with the important Oracle initialization parameters and how Oracle uses them.

TYPES OF INITIALIZATION PARAMETER FILE

Oracle uses a parameter file to store the initialization parameters and their settings for an instance. We can use either of the following two types of parameter files:

1. Server parameter file (SPFILE): Dynamic persistent server parameter file SPFILE, commonly referred as spfile.ora A binary file that contains the initialization parameters
2. Initialization parameter file (pfile): Static parameter file, PFILE, commonly referred as init.ora  A text file that contains a list of all initialization parameters.

Contents Of Parameter File

·         A list of instance parameters

·         The name of the database the instance is associated with

·         Allocation for memory structures of the SGA

·         What to do with filled online redo log files.  (Archive Destination)

·         The names and locations of control files.

·         Information about undo tablespace.

 Text initialization parameter file:  PFILE à   init.ora  (sid) means database name

• Text File
• Modified with an operating system editor
• Modification made manually
• Whenever we need to change the value of the parameter file we must shutdown the database.
• The pfile is read only during instance startup. (Changes take effect on next startup)

For oracle 10g (R2)   à  default location E:\oracle\product\10.2.0\Db_1\database\initorcl.ora

Server parameter file:  SPFILE : à spfile.ora  à   (sid)  means database name

- Binary File

- Maintained by the oracle server

- Always resides on the server side having read and write by database server

- Ability to make changes persistent across SHUTDOWN and STARTUP

- Can self-tune parameter values

- Can have RMAN support for backup the initialization parameter

For oracle 9i à  default location C:\oracle\ora92\database\spfileorcl.ora

For oracle 10g  à  default location E:\oracle\product\10.2.0\Db_1\dbs\spfileorcl.ora

perform  following query to check your database was started with a PFILE or SPFILE

SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "File Type"

       FROM sys.v_$parameter WHERE name = 'spfile';