============
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.
- 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
*.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'
NAME VALUE
---------------- -------------------------------------
db_name igt
SQL> show parameter spfile
NAME VALUE
---------------- -------------------------------------
spfile /software/oracle/122/dbs/spfileigt.ora
============
initigt.ora:
spfile='/software/oracle/admin/igt/pfile/spfileigt.ora'
==========
See configuration
==========
SQL> show parameters db_nameNAME 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)
