Oracle INIT.ORA, PFILE, and SPFILE
Version 18.3.0.1

General Information
Library Note Morgan's Library Page Header
For how many years have you been working with physical servers? Software Defined Servers can improve performance and security while lowering complexity and cost. With Software Defined Servers you define servers with an optimum configuration to your environment rather than being memory starved and cpu over-provisioned. Find out how you can improve your computing environment with TidalScale. your friends.
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='/home/oracle/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/app18/dbs/test_spfile.ora' FROM PFILE='/u01/app18/dbs/test_init.ora';
 
INIT.ORA
Some entries found in a collection of SPFILEs from different applications and clients #####################################################################
# Common Parameters
#####################################################################

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

*.db_domain=mlib
*.db_block_size=8192

*.db_writer_processes=4
#####################################################################
# Identification & Control Files
#####################################################################

*.db_name=orabase
*.db_unique_name='orabase1'
*.control_files='/u01/orabase18/oradata/orabase/control01.ctl',
                '/u01/orabase18/recovery_area/control02.ctl',
                '/home/oracle/control03.ctl'
control_file_record_keep_time=32

#####################################################################
# Version Specific
#####################################################################

compatible=18.0.0
enable_pluggable_database=TRUE

#####################################################################
# Platform Specific
#####################################################################

filesystemio_options=directio
#####################################################################
# Security, Audit and Resource Limit
#####################################################################

*.audit_file_dest=/u01/orabase18/admin/oradata/adump'
*.audit_trail=DB
*.resource_limit=TRUE
*.remote_login_passwordfile='EXCLUSIVE'

#####################################################################
# Recovery and Flashback
#####################################################################

*.db_flashback_retention_target=28800
*.db_recovery_file_dest='/u02/orabase18/recovery_area'
*.db_recovery_file_dest_size=12888M
#####################################################################
# NLS Settings
#####################################################################

*.nls_date_format = DD-MON-YYYY
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'

#####################################################################
# Archive & Redo Logs
#####################################################################

*.diagnostic_dest=/u01/orabase18
*.log_buffer=7232K
*.log_checkpoint_interval=0
*.log_archive_max_processes=4 
*.archive_lag_target=1800

#####################################################################
# Dump & Output Directories
#####################################################################

*.audit_file_dest='/u01/orabase18/admin/orabase/adump'
*.core_dump_dest='/u01/orabase18/diag/rdbms/orabase/orabase/cdump'
#####################################################################
# DB & Instance Limits
#####################################################################

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

*.open_cursors=4000
*.open_links=10
*.session_cached_cursors=60
*.session_max_open_files=12
*.sort_area_retained_size=1048576
*.sort_area_size=4194304

#####################################################################
# Buffer Pool
#####################################################################

*.db_block_buffers=0
*.db_block_checksum=TYPICAL
*.db_block_checking=FALSE

_db_block_lru_latches=2048
_db_block_hash_latches=65536

#####################################################################
# Security
#####################################################################
*.
sec_protocol_error_trace_action='LOG'
#####################################################################
# Shared Pool & Other "Pools"
# Sort, Hash Joins, Bitmap Indexes
#####################################################################

*.db_keep_cache_size=4G
*.db_recycle_cache_size=4G
*.java_pool_size=4G
*.large_pool_size=0
*.log_buffer=7232M

*.memoptimize_pool_size=4G
*.pga_aggregate_target=256G
*.result_cache_max_size=4G
*.sga_target=756G

*.shared_pool_size=0
*.shared_pool_reserved_size=15728640
*.streams_pool_size=0
*.memory_target=0

_shared_pool_reserved_min_alloc=4000

#####################################################################
# UNDO
#####################################################################
# use automatic undo

undo_management='auto'
# undo tablespace name
undo_tablespace=UNDOTBS1
# keep 12 hours (12*3600)
undo_retention=43200
#####################################################################
# Optimizer
#####################################################################
optimizer_mode='FIRST_ROWS'
#####################################################################
# Parallelism
#####################################################################

*.parallel_max_servers=96
*.parallel_min_servers=8
*.parallel_threads_per_cpu=1
*.parallel_execution_message_size=65535
*.recovery_parallelism=16

#####################################################################
# Shared Server
#####################################################################

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orabase18XDB)'
*.connection_brokers='((TYPE=DEDICATED)(BROKERS=1))'
*.connection_brokers='((TYPE=EMON)(BROKERS=1))'

#####################################################################
# Job Processing
#####################################################################

*.job_queue_processes=4000
*.aq_tm_processes=1
#####################################################################
# Miscellaneous
#####################################################################

*.background_core_dump=partial
*.db_file_multiblock_read_count=128
*.fast_start_parallel_rollback=LOW
*.plsql_warnings='ENABLE:ALL'
*.query_rewrite_integrity='TRUSTED'
*.query_rewrite_enabled='TRUE'

*.recyclebin='ON'
_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"


local_listener='LISTENER_ORABASE18'
global_names=TRUE
resource_manager_plan=''
 
Using an IFILE
Path to the init file to include in the init.ora

Up to three levels of nesting is 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 Undocumented Underscore 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:\orahome18\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 = "18.0.0.0.0"
standby_archive_dest = "c:\app\oracle\recovery_area\orabase"
db_recovery_file_dest = "c:\app\oracle\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:\orabase\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:\orabase"
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
Built-in Functions
Built-in Packages
Startup Parameters
Startup & Shutdown
What's New In 12cR2
What's New In 18cR3

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-2017 Daniel A. Morgan All Rights Reserved