Oracle INIT.ORA, PFILE, and SPFILE
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
Purpose Back in the days of the dinosaurs, like myself, we ran databases based on parameters hard coded into the init.ora file and were forced to do a shutdown and restart every time we changed a parameter and wanted that change to reflect in the database. These days any sane DBA runs their database from a compiled version called the Server Parameter (SP) file which allows a substantial percentage of the parameters to be altered, real-time using the ALTER SYSTEM SCOPE=<BOTH | SPFILE> syntax.
Dependencies
CDB_HIST_PARAMETER GV$PARAMETER_VALID_VALUES INT$DBA_HIST_PARAMETER_NAME
DBA_HIST_PARAMETER GV$SPPARAMETER NLS_DATABASE_PARAMETERS
DBA_HIST_PARAMETER_NAME GV$SYSTEM_PARAMETER NLS_INSTANCE_PARAMETERS
GV$PARAMETER GV$SYSTEM_PARAMETER2 NLS_SESSION_PARAMETERS
GV$PARAMETER2 INT$DBA_HIST_PARAMETER  
Security Model To recompile an SPFILE requires SYSDBA or SYSOPER privileges
 
Init.ora
Is an IFILE being used SQL> show parameter ifile
Create PFILE From SPFILE CREATE pfile=<pfile_name> FROM spfile=<spfile_name>;
CREATE PFILE='/home/oracle/initorabase.ora' FROM SPFILE='SPFILEORABASE.ORA';
Create PFILE From Memory CREATE pfile=<pfile_name> FROM MEMORY
CREATE PFILE='/home/oracle/initorabase.ora' FROM MEMORY;
 
SPFile
Is an SPFILE being used? SQL> show parameter spfile
Create SPFILE From PFILE CREATE spfile=<spfile_name> FROM pfile=<pfile_name>;
CREATE SPFILE FROM PFILE='initorabase.ora';
Recompile SPFILE CREATE spfile FROM pfile=<initSID.ora>;
SQL> conn / as sysdba
SQL> SHUTDOWN IMMEDIATE;
SQL> CREATE spfile FROM pfile='initorabase.ora';
SQL> STARTUP;
SQL> show parameter spfile

CREATE SPFILE='/u01/oracle/dbs/test_spfile.ora' FROM PFILE='/u01/oracle/dbs/test_init.ora';
 
INIT.ORA
This is a typical production init file used to create an SPFILE #####################################################################
# Common Parameters
#####################################################################

ifile = ?/dbs/orabase-dg.ora
#####################################################################
# Structural Parms
#####################################################################

*.db_domain=mlib
*.db_block_size=8192
*.db_writer_processes=8
#####################################################################
# Identification & Control Files
#####################################################################

*.db_name=orabase
*.control_files='/app/oracle/product/oradata/orabase/control01.ctl',
                '/app/oracle/product/fast_recovery_area/control02.ctl',
                '/home/oracle/control03.ctl'
#####################################################################
# Version Specific
#####################################################################

compatible=12.1.0.2.0
#####################################################################
# Platform Specific
#####################################################################

disk_asynch_io=FALSE
filesystemio_options=directio
use_indirect_data_buffers=TRUE
#####################################################################
# Security, Audit and Resource Limit
#####################################################################

*.audit_trail=DB
*.resource_limit=TRUE
*.remote_login_passwordfile='EXCLUSIVE'
#####################################################################
# Recovery and Flashback
#####################################################################

*.db_flashback_retention_target=2880
*.db_recovery_file_dest='app/oracle/product/fast_recovery_area'
*.db_recovery_file_dest_size=4294967296
#####################################################################
# NLS Settings
#####################################################################

nls_date_format = DD-MON-YYYY
#####################################################################
# Archive & Redo Logs
#####################################################################

*.diagnostic_dest=/app/oracle/product
#log_archive_dest=/app/oracle/product/fast_recovery_area/arch
log_buffer=4194304 
log_checkpoint_interval=1050624
log_archive_max_processes=4 
#_log_simultaneous_copies=16
archive_lag_target=1800
#####################################################################
# Dump & Output Directories
#####################################################################

*.audit_file_dest='/app/oracle/product/admin/orabase/adump'
*.core_dump_dest='/app/oracle/product/admin/orabase/cdump'
#####################################################################
# DB & Instance Limits
#####################################################################

db_files=512
sessions=4000
*.processes=1500
transactions=200
#####################################################################
# Process & Session Specific
#####################################################################

*.open_cursors=4000
open_links=10
session_cached_cursors=40
session_max_open_files=30
sort_area_retained_size=1048576
*.sort_area_size=4194304
#####################################################################
# Buffer Pool
#####################################################################

db_block_buffers=300000
db_block_checksum=true
db_block_checking=true

_db_block_lru_latches=2048
_db_block_hash_latches=65536
#####################################################################
# Shared Pool & Other "Pools"
# Sort, Hash Joins, Bitmap Indexes
#####################################################################

*.java_pool_size=0
*.large_pool_size=0
*.pga_aggregate_target=0
*.shared_pool_size=0
*.shared_pool_reserved_size=15728640
*.streams_pool_size=0
*.memory_target=857735168

_shared_pool_reserved_min_alloc=4000
#####################################################################
# UNDO
#####################################################################
# use automatic undo

undo_management='auto'
# which tablespace
undo_tablespace=UNDOTBS1
# keep 8 hours (8*3600)
undo_retention=28000
_undo_autotune=false
#####################################################################
# Parallelism
#####################################################################

parallel_max_servers=32
parallel_min_servers=0
parallel_threads_per_cpu=8
parallel_execution_message_size=65535
recovery_parallelism=16
#####################################################################
# Shared Server
#####################################################################

dispatchers="(PROTOCOL=TCP) (SERVICE=orabaseXDB)"
#max_dispatchers=40
#max_shared_servers=150
#mts_service=orabase
#shared_servers=8
#####################################################################
# Job Processing
#####################################################################

job_queue_processes=8
aq_tm_processes=1
#####################################################################
# Miscellaneous
#####################################################################

background_core_dump=partial
db_file_multiblock_read_count=16
fast_start_parallel_rollback=FALSE
*.optimizer_index_caching=80
*.optimizer_index_cost_adj=10
*.query_rewrite_integrity='TRUSTED'
*.recyclebin='OFF'

_disable_selftune_checkpointing=true
#####################################################################
# Undocumented Parameters & Temporary Fixes
#####################################################################

_b_tree_bitmap_plans=false
_shared_pool_reserved_min_alloc=4000
_small_table_threshold=2560
_optim_peek_user_binds=false
#####################################################################
# Events
#####################################################################

#event="600 trace name library_cache level 10"
#trace PMON actions
#event="10500 trace name context forever"
#event="10196 trace name context forever"
#event="10246 trace name context forever"
#event="4031 trace name errorstack level 10"
#event="10511 trace name context forever, level 2"
#event="32333 trace name context forever, level 8"
 
Using an IFILE
Just a path to the init file to include in the init.ora

Up to three levels of nesting allowed.
ifile=$ORACLE_HOME/dbs/orabase_dg.ora
 
List Parameters
List All Supported Parameters col name format a35
col value format a35

SELECT name, value
FROM gv$parameter
ORDER BY 1;
List All Modified Parameters col name format a35
col value format a35

SELECT name, value
FROM gv$parameter
WHERE isdefault = 'FALSE'
ORDER BY 1;
List All Unsupported Parameters set pagesize 45
set linesize 121
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 a42

SELECT *
FROM gv$obsolete_parameter
ORDER BY 2;
 
Online Init Parameter Modification
Alter Parameter Until Restart ALTER SYSTEM SET <parameter_name> = <value>
COMMENT=<comment_text>
SCOPE=<MEMORY | SPFILE | BOTH>;
col name format a30
col value format a30

SELECT name, value
FROM gv$parameter
WHERE name = 'optimizer_index_cost_adj';

ALTER SYSTEM SET optimizer_index_cost_adj = 12
COMMENT='Temporary change on Nov 29'
SCOPE=MEMORY;

SELECT name, value
FROM gv$parameter
WHERE name = 'optimizer_index_cost_adj';
Alter SPFILE Only col name format a30
col value format a30

SELECT name, value
FROM gv$parameter
WHERE name = 'open_cursors';

ALTER SYSTEM SET open_cursors=300
COMMENT='Change To Take Effect After Jan 1 Reboot'
SCOPE=SPFILE;

SELECT name, value
FROM gv$parameter
WHERE name = 'open_cursors';
Alter Parameter Immediately and SPFILE for Restart col name format a30
col value format a30

SELECT name, value
FROM gv$parameter
WHERE name = 'query_rewrite_integrity';

ALTER SYSTEM SET query_rewrite_integrity = 'ENFORCED'
COMMENT='Permanent Change To System Configuration'
SCOPE=BOTH;

SELECT name, value
FROM gv$parameter
WHERE name = ' query_rewrite_integrity';
Alter SPFILE in a RAC environment ALTER SYSTEM SET <parameter_name> = <value>
COMMENT=<comment_text>
SCOPE=<MEMORY | SPFILE | BOTH>
SID=<sid_name | *>;
col name format a30
col value format a30

SELECT name, value
FROM gv$parameter
WHERE name = 'optimizer_index_cost_adj';

ALTER SYSTEM SET optimizer_index_cost_adj = 10
COMMENT='Permanent Change To System Configuration'
SCOPE=BOTH
SID=*;

SELECT name, value
FROM gv$parameter
WHERE name = 'optimizer_index_cost_adj';
Drop a parameter from an SPFILE ALTER SYSTEM RESET <parameter_name>
COMMENT=<comment_text>
SCOPE=<MEMORY | SPFILE | BOTH>
SID=<sid_name | *>;
SELECT name, value
FROM gv$spparameter
ORDER BY 1;

ALTER SYSTEM RESET timed_statistics
SCOPE=SPFILE
SID='*';

SELECT name, value
FROM gv$spparameter
ORDER BY 1;

ALTER SYSTEM SET timed_statistics=TRUE
SCOPE=SPFILE
SID='*';

SELECT name, value
FROM gv$spparameter
ORDER BY 1;
View Parameter Alterations desc dba_capture_parameters

SELECT *
FROM dba_capture_parameters;

col name format a30
col value format a30

SELECT name, value
FROM gv$parameter
WHERE name = 'optimizer_index_cost_adj';

ALTER SYSTEM SET optimizer_index_cost_adj = 12
COMMENT='Temporary change on Aug 14 2006' SCOPE=MEMORY;

SELECT name, value
FROM gv$parameter
WHERE name = 'optimizer_index_cost_adj';
 
Startup Specifying an Init.ora
Startup STARTUP PFILE = /app/oracle/product/12.1.0/dbhome_1/dbs/initorabase.ora
 
Startup If The SPFILE Contains Invalid Parameters
Damaged SPFILE Startup SQL> shutdown immediate 
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup ORA-00400: invalid release value booger for parameter compatible
SQL>

-- create new init.ora
$ cat newpfile.ora
spfile='/apps/oracle/product/12.1.0.2/dbhome_1/dbs/spfileogo.ora'
compatible=12.1.0.2

SQL> startup pfile=newpfile.ora
ORACLE instance started.

ORACLE instance started.

Total System Global Area 2550136832 bytes
Fixed Size                  3048872 bytes
Variable Size             671091288 bytes
Database Buffers         1862270976 bytes
Redo Buffers               13725696 bytes
Database mounted.
Database opened.
SQL> alter system set compatible='12.1.0.2' scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 2550136832 bytes
Fixed Size                  3048872 bytes
Variable Size             671091288 bytes
Database Buffers         1862270976 bytes
Redo Buffers               13725696 bytes
Database mounted.
Database opened.
SQL>
 
Enable Tracing
Example: Do not add this to your pfile on your own # Uncomment the following line if you wish to enable the
# Oracle Trace product to trace server activity. This enables
# scheduling of server # collections from the Oracle Enterprise
# Manager Console.

# Also, if the oracle_trace_collection_name parameter is non-null,
# every session will write to the named collection, as well as
# enabling you to schedule future collections from the console.

# oracle_trace_enable = true
# oracle_trace_collection_name = ""

# define directories to store trace and alert files

#event = "10061 trace name context forever, level 10"
 
Find Deprecated Parameters
This demonstration shows how to identify deprecated parameters specified in the spfile. -- during startup

SQL> startup
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.

Total System Global Area 2550136832 bytes
Fixed Size                  3048872 bytes
Variable Size             671091288 bytes
Database Buffers         1862270976 bytes
Redo Buffers               13725696 bytes
Database mounted.
Database opened.
SQL>

-- look in alert log at /diag/rdmbs/orabase/orabase/trace for the error

Using parameter settings in server-side spfile c:\app\oracle\product\12.1.0\dbhome_1\database\spfileorabase.ora
System parameters with non-default values:
processes = 150
memory_target = 816M
control_files = "c:\app\oracle\product\oradata\orabase\control01.ctl"
control_files = "c:\app\oracle\product\oradata\orabase\control02.ctl"
control_files = "c:\app\oracle\product\oradata\orabase\control03.ctl"
db_block_size = 8192
compatible = "12.1.0.2.0"
standby_archive_dest = "c:\app\oracle\fast_recovery_area\orabase"
db_recovery_file_dest = "c:\app\oracle\fast_recovery_area"
db_recovery_file_dest_size= 2G
db_flashback_retention_target= 2880
undo_tablespace = "UNDOTBS1"
undo_retention = 3600
remote_login_passwordfile='EXCLUSIVE'
db_domain = "MORGANSLIBRARY.ORG"
global_names = FALSE
dispatchers = "(PROTOCOL=TCP) (SERVICE=orabaseXDB)"
cursor_sharing = "SIMILAR"
audit_file_dest = "c:\app\oracle\product\admin\orabase\adump"
audit_trail = "DB"
sort_area_size = 1024000
db_name = "orabase"
open_cursors = 300
optimizer_index_cost_adj = 90
optimizer_index_caching = 33
query_rewrite_integrity = "TRUSTED"
diagnostic_dest = "c:\app\oracle\product\12.1.0\dbhome_1"
Deprecated system parameters with specified values:
standby_archive_dest
End of deprecated system parameter listing


-- create a pfile from the spfile
create pfile from spfile;

-- edit the pfile to remove the deprecated parameter(s)

-- create a new spfile and restart Oracle

create spfile from pfile;

startup;

Related Topics
Startup Parameters
Startup & Shutdown

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved