Oracle INIT.ORA, PFILE, and SPFILE
Version 11.2.0.3
 
General Information
Note: To recompile an SPFILE requires SYSDBA or SYSOPER privileges
Data Dictionary Views
gv$parameter gv$parameter2 gv$spparameter
 
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='c:\temp\initorabase.ora' FROM SPFILE='SPFILEORABASE.ORA';
 
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';
Create PFILE From Memory CREATE pfile_name <path_and_pfile_name> FROM MEMORY;
CREATE PFILE='c:\temp\spfileorabase.ora' FROM MEMORY;
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=11.2.0.0.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 0
set linesize 100
col ksppinm format a40
col ksppdec format a40

SELECT ksppinm, ksppdesc
FROM x$ksppi
WHERE SUBSTR(ksppinm,1,1) = '_'
ORDER BY ksppinm;
List Obsolete Parameters SELECT *
FROM gv$obsolete_parameter
ORDER BY 1;
 
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 = /u01/oracle/product/ora10/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/9.2.0.1/dbs/spfileogo.ora'
compatible=9.2.0.1

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

Total System Global Area 302747688 bytes
Fixed Size                  450600 bytes
Variable Size            167772160 bytes
Database Buffers         134217728 bytes
Redo Buffers                307200 bytes
Database mounted.
Database opened.
SQL> alter system set compatible='9.2.0.1' scope=spfile;

System altered.

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

Total System Global Area 302747688 bytes
Fixed Size                  450600 bytes
Variable Size            167772160 bytes
Database Buffers         134217728 bytes
Redo Buffers                307200 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 535662592 bytes
Fixed Size 1334380 bytes
Variable Size 260047764 bytes
Database Buffers 268435456 bytes
Redo Buffers 5844992 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:\oracle\product\11.2.0\db_1\database\spfileorabase.ora
System parameters with non-default values:
processes = 150
memory_target = 816M
control_files = "c:\oracle\product\oradata\orabase\CONTROL01.CTL"
control_files = "c:\oracle\product\oradata\orabase\CONTROL02.CTL"
control_files = "c:\oracle\product\oradata\orabase\CONTROL03.CTL"
db_block_size = 8192
compatible = "11.2.0.0.0"
standby_archive_dest = "C:\oracle\product/fast_recovery_area/orabase"
db_recovery_file_dest = "c:\oracle\product\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:\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:\ORACLE\PRODUCT"
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;
 
 
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