CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle Init.Ora & SPFile
Version 11.1
 
Note: To recompile an SPFILE requires SYSDBA or SYSOPER privileges
Data Dictionary Views
gv$parameter gv$parameter2 gv$spparameter
 
Init.ora
Which IFILE being used SQL> show parameter ifile
Create PFILE From SPFILE CREATE pfile=<pfile_name> FROM spfile=<spfile_name>;
CREATE PFILE='c: emp\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: emp\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 parms
#####################################################################
ifile = ?/dbs/orabase-dg.ora
#####################################################################
# Structural Parms
#####################################################################
db_domain = psoug
db_block_size = 8192
db_writer_processes = 8
#####################################################################
# Identification & Control Files
#####################################################################
db_name = orabase
control_files = (/app/oracle/product/orabase/control.ctl
/app/oracle/product/databases/orabase/control.ctl)
#####################################################################
# Version Specific
#####################################################################
compatible = 10.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
#####################################################################
# NLS Settings
#####################################################################
nls_date_format = DD-MON-YYYY
#####################################################################
# Archive & Redo Logs
#####################################################################
#log_archive_dest = /app/oracle/product/flash_recovery_area/arch
log_buffer                = 4194304 
log_checkpoint_interval   = 1050624
log_archive_max_processes = 4 
#_log_simultaneous_copies = 48
archive_lag_target        = 1800
#####################################################################
# Dump & Output Directories
#####################################################################
audit_file_dest      = /app/oracle/product/admin/orabase/adump
background_dump_dest = /app/oracle/product/admin/orabase/bdump
core_dump_dest       = /app/oracle/product/admin/orabase/cdump
user_dump_dest       = /app/oracle/product/admin/orabase/udump
utl_file_dir         = /app/oracle/product/admin/orabase/output
#####################################################################
# 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

#buffer_pool_keep    = (buffers:120000, lru_latches:150)
#buffer_pool_recycle = (buffers:55296, lru_latches:48)
#####################################################################
# Shared Pool & Other "Pools"
#
Sort, Hash Joins, Bitmap Indexes
#####################################################################
java_pool_size = 0
large_pool_size = 500M
pga_aggregate_target = 1024M
shared_pool_size = 750M
shared_pool_reserved_size = 96M
streams_pool_size = 0

_shared_pool_reserved_min_alloc = 4000
#####################################################################
# UNDO
#####################################################################
# use automatic undo
undo_management = 'auto'
# which tablespace
undo_tablespace = 'undo_t1'
# 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

#parallel_automatic_tuning      = true (deprecated in 10g)
#####################################################################
# 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
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"
# tracing 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

U p 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 .1.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.1.0.0.0"
standby_archive_dest = "C:\oracle\product/flash_recovery_area/orabase"
db_recovery_file_dest = "c:\oracle\product lash_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 = "PSOUG.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;
 
Related Topics
Startup Parameters
Startup & Shutdown
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [65 users online]    © 2010 psoug.org