Oracle init file, spfile, and pfile

============

General

============

The short names are init.ora, spfile.ora, pfile.ora, but the actual name are init<db_name>.ora, spfile<db_name>.ora, pfile<db_name>.ora

spfile.ora

AKA Server Parameter File

It is a binary file, that stores all the configuration/initialization parameters. 

It is used by oracle upon startup.

To start working with spfile:
- Create a text file init.ora
- using the CREATE SPFILE statement from init.ora - create the spfile.

pfile.ora

A text file, which can be generated from spfile

init.ora

A text file, normally. 

In older version - it was the actual configuration file. 

Nowadays, it can be reference to the spfile.

In STARTUP

By default, if you do not specify PFILE in your STARTUP command, Oracle will use a SPFILE from the default location. 

If you choose to use the traditional text initialization parameter file, you must specify the PFILE clause when issuing the STARTUP command.

In RAC

A single copy of the spfile can be used by all instances. 

Even though a single file is used to specify parameters, it has different format styles to support both the common values for all instances, as well as the specific values for an individual instance.

For Example:
*.OPEN_CURSORS=500 # For database-wide setting

RACDB1.OPEN_CURSORS=1000 # For RACDB1 instance

==========

init.ora

==========

Default location

Linux : $ORACLE_HOME/dbs/ 

Windows: %ORACLE_HOME%\database\

==========

spfile

==========

It is a binary file. and it can be edited with ALTER SYSTEM SET commands.

To override default location, it should be referenced from init.ora.

For example:
initigt.ora:
spfile='/software/oracle/admin/igt/pfile/spfileigt.ora'

==========

See configuration

==========

SQL> show parameters db_name
NAME             VALUE
---------------- -------------------------------------
db_name          igt

SQL> show parameter spfile
NAME             VALUE
---------------- -------------------------------------
spfile          /software/oracle/122/dbs/spfileigt.ora

============

List Parameters

============

List All Supported Parameters

SET LINESIZE 100

COL NAME FORMAT A40

COL VALUE FORMAT A40

SET LINESIZE 100

SELECT name, value

FROM V$PARAMETER

ORDER BY 1;

List All Modified Parameters

SET LINESIZE 100

COL NAME FORMAT A40

COL VALUE FORMAT A40

SELECT name, value

FROM V$PARAMETER

WHERE isdefault = 'FALSE'

ORDER BY 1;

List All Undocumented Underscore Parameters

SET PAGESIZE 1000

SET LINESIZE 140

COL KSPPINM FORMAT A40

COL KSPPDESC FORMAT A80

SELECT ksppinm, ksppdesc

FROM X$KSPPI

WHERE SUBSTR(ksppinm,1,1) = '_'

ORDER BY ksppinm;

List Obsolete Parameters

COL NAME FORMAT A40

SELECT *

FROM V$OBSOLETE_PARAMETER

ORDER BY 2;

============

General

============

Creating SPFILE

From init.ora

CREATE SPFILE FROM PFILE='/software/oracle/122/dbs/initigt.ora';

CREATE SPFILE '/software/oracle/122/dbs/spfileigt.ora' FROM PFILE='/software/oracle/122/dbs/initigt.ora';

From memory

CREATE SPFILE '/software/oracle/122/dbs/spfileigt.ora' FROM MEMORY;

Creating PFILE

CREATE PFILE '/software/oracle/122/dbs/pfileigt.ora' FROM SPFILE='/software/oracle/122/dbs/spfileigt.ora';

============

Changing Parameters

============

ALTER SESSION SET parameter_name = value;

ALTER SYSTEM  SET parameter_name = value [DEFERRED];

ALTER SYSTEM  SET parameter_name = value SCOPE = SPFILE;

ALTER SYSTEM  SET parameter_name = value SCOPE = MEMORY;

ALTER SYSTEM  SET parameter_name = value SCOPE = BOTH;

DEFERRED - Only for Dynamic parameters. The change affects only future sessions.

MEMORY -   Only for Dynamic parameters.

SPFILE -   Change only spfile. Need a restart for the change to take effect. This is the way to change static parameters.

============

Remove parameter from SPFILE

============

ALTER SYSTEM RESET OPEN_CURSORS SCOPE=SPFILE sid='*';

The parameter is removed from a server parameter file, causing the default value to take effect the next time you start an instance of the database.

============

Oracle 9i

============

. /etc/sh/orash/920env.sh

# Removing all oracle paths

export PATH=`echo $PATH | tr -s ":" "\n" | grep -v "/software/oracle/[0-9]*/bin" | tr "\n" ":"`

# Adding correct oracle path

export PATH=/software/oracle/920/bin:$PATH

export LD_LIBRARY_PATH=/software/oracle/920/lib:/usr/lib

export PROJECT_SID=gin

export DEFAULT_SID=gin

export ORACLE_SID=gin

export ORA_VER=920

export ORACLE_HOME=/software/oracle/920

export ORA_NLS33=/software/oracle/920/ocommon/nls/admin/data

export TNS_ADMIN=/software/oracle/111/network/admin

less /software/oracle/920/dbs/initgin.ora

SPFILE=/software/oracle/admin/gin/pfile/spfilegin.ora

sqlplus /nolog

connect / as sysdba

STARTUP;

============

Oracle 11

============

. /etc/sh/orash/111env.sh

# Removing all oracle paths

export PATH=`echo $PATH | tr -s ":" "\n" | grep -v "/software/oracle/[0-9]*/bin" | tr "\n" ":"`

# Adding only relevant oracle paths

export PATH=/software/oracle/111/bin:$PATH

export LD_LIBRARY_PATH=/software/oracle/111/lib:/usr/lib:/software/oracle/111/lib32

export PROJECT_SID=igt

export DEFAULT_SID=igt

export ORACLE_SID=igt

export ORA_VER=1110

export ORACLE_HOME=/software/oracle/111

export ORA_NLS33=/software/oracle/111/ocommon/nls/admin/data

export TNS_ADMIN=/software/oracle/111/network/admin

less /software/oracle/111/dbs/initigt.ora

SPFILE=/software/oracle/admin/igt/pfile/spfileigt.ora

sqlplus /nolog

connect / as sysdba

STARTUP;

sample init.ora 

db_name=DEFAULT

db_files = 80

db_file_multiblock_read_count = 8

db_block_buffers = 100     

shared_pool_size = 3500000

log_checkpoint_interval = 10000

processes = 50 

parallel_max_servers = 5 

log_buffer = 32768

max_dump_file_size = 10240

global_names = TRUE

control_files = (ora_control1, ora_control2)