| 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; |
