Oracle | INIT.ORA

Oracle INIT.ORA, PFILE, and SPFILE Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

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='test21db_iad25g' *.control_files='+DATA', '+REC0' control_file_record_keep_time=32 ##################################################################### # Version Specific ##################################################################### *.compatible=21.0.0 *.enable_pluggable_database=TRUE ##################################################################### # Platform Specific ##################################################################### *.filesystemio_options=setall ##################################################################### # Security, Audit and Resource Limit ##################################################################### *.audit_file_dest='/u01/app/oracle/admin/test21db_iad25g/adump' *.audit_trail=DB *.resource_limit=TRUE *.remote_login_passwordfile='EXCLUSIVE' ##################################################################### # Recovery and Flashback ##################################################################### *.db_flashback_retention_target=28800 *.db_recovery_file_dest='+RECO' *.db_recovery_file_dest_size=255G ##################################################################### # NLS Settings ##################################################################### *.nls_date_format = DD-MON-YYYY HH24:MI:SS *.nls_language='AMERICAN' *.nls_territory='AMERICA' ##################################################################### # Archive & Redo Logs ##################################################################### *.diagnostic_dest=/u01/app/oracle *.log_buffer=16M *.log_checkpoint_interval=0 *.log_archive_max_processes=4 *.archive_lag_target=0 ##################################################################### # Dump & Output Directories ##################################################################### *.audit_file_dest='/u01/app/oracle/admin/test21db_iad25g/adump' *.core_dump_dest='/u01/app/oracle/diag/rdbms/test21db_iad25g /test21db/cdump' ##################################################################### # DB & Instance Limits ##################################################################### *.db_files=1024 *.sessions=4000 *.processes=1500 *.transactions=600 ##################################################################### # Process & Session Specific ##################################################################### *.open_cursors=4000 *.open_links=4 *.session_cached_cursors=100 *.session_max_open_files=12 *.sort_area_retained_size=0 *.sort_area_size=4194304 ##################################################################### # Buffer Pool ##################################################################### *.db_block_buffers=0 *.db_block_checksum=FULL *.db_block_checking=FULL _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=test21dbXDB)' *.connection_brokers='((TYPE=DEDICATED)(BROKERS=1))', '((TYPE=EMON)(BROKERS=1))' ##################################################################### # Job Processing ##################################################################### *.job_queue_processes=40 *.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_TEST21DB' global_names=TRUE resource_manager_plan='SCHEDULER[0x5296]:DEFAULT_MAINTENANCE_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> SID='<* | instance_number>' 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'

      SID='*'

      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' SID='*' 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' SID='*' 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 Jun 24 2021' 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> startup ORA-00400: invalid release value booger for parameter compatible -- 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. 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='21.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.

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