You Asked

Hi,

I cleaned up a little mess with a deprecated/obsolete parameter in the spfile. The database didn't start and I had to create a pfile from spfile, remove the parameter and recreate the spfile.
Everything is fine now. Almost :-)

Now many parameters are marked as non-default. In v$parameter I see 124 parameters that have value = default_value AND isdefault = 'FALSE', many of it are _underscore parameters.

I think I also know why this happened: the created pfile contains a lot of parameters that I never touched and with creating an spfile from that pfile the database marks them as non-default.

So, is there a chance to create a clean pfile that contains only the relevant (modified by me) parameters? I thought that the isdefault column in v$parameter is enough for me to keep in mind which parameters I set and which not.

Regards,

Chris

and Connor said...

I've been bitten by that before as well. The concept of "not" default tends to vary here. There is

not default - it was specified explictly in the parameter file, and
not default - we derived it from something else and thus is was not the default for this platform.

For example, I ran this on my database:

SQL> create pfile = 'c:\tmp\init.ora' from spfile;

File created.

SQL> create pfile = 'c:\tmp\init2.ora' from memory;

File created.

and since the database is running off the spfile, you'd expect that what is in memory is the same what came from the spfile. But if I look at the two files - there's a LOT of difference

--
-- init.ora
--
db19.__data_transfer_cache_size=0
db19.__db_cache_size=3238002688
db19.__inmemory_ext_roarea=0
db19.__inmemory_ext_rwarea=0
db19.__java_pool_size=0
db19.__large_pool_size=117440512
db19.__oracle_base='C:\oracle'#ORACLE_BASE set from environment
db19.__pga_aggregate_target=1258291200
db19.__sga_target=4294967296
db19.__shared_io_pool_size=134217728
db19.__shared_pool_size=788529152
db19.__streams_pool_size=0
db19.__unified_pga_pool_size=0
*.audit_file_dest='C:\oracle\admin\db19\adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='x:\oracle\oradata\DB19\control01.ctl','x:\oracle\oradata\DB19\control02.ctl'
*.db_block_size=8192
*.db_name='db19'
*.diagnostic_dest='C:\oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=db19XDB)'
*.enable_pluggable_database=true
*.local_listener='LISTENER_DB19'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=1258291200
*.processes=960
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=4294967296
*.undo_tablespace='UNDOTBS1'

--
-- init2.ora
--
# Oracle init.ora parameter file generated by instance db19 on 06/11/2019 11:25:57
__data_transfer_cache_size=0
__db_cache_size=3088M
__inmemory_ext_roarea=0
__inmemory_ext_rwarea=0
__java_pool_size=0
__large_pool_size=112M
__oracle_base='C:\oracle' # ORACLE_BASE set from environment
__pga_aggregate_target=1200M
__reload_lsnr='0' # lreg reload listener
__sga_target=4G
__shared_io_pool_size=128M
__shared_pool_size=752M
__streams_pool_size=0
__unified_pga_pool_size=0
_always_anti_join='CHOOSE'
_always_semi_join='CHOOSE'
_b_tree_bitmap_plans=TRUE
_bloom_serial_filter='ON'
_complex_view_merging=TRUE
_compression_compatibility='19.0.0'
_diag_adr_trace_dest='C:\oracle\diag\rdbms\db19\db19\trace'
_ds_xt_split_count=1
_eliminate_common_subexpr=TRUE
_fast_full_scan_enabled=TRUE
_generalized_pruning_enabled=TRUE
_gs_anti_semi_join_allowed=TRUE
_hang_resolution_scope='OFF' # _hang_resolution_scope updated by kjznhm
_improved_outerjoin_card=TRUE
_improved_row_length_enabled=TRUE
_index_join_enabled=TRUE
_key_vector_create_pushdown_threshold=20000
_ksb_restart_policy_times='0'
_ksb_restart_policy_times='60'
_ksb_restart_policy_times='120'
_ksb_restart_policy_times='240' # internal update to set default
_left_nested_loops_random=TRUE
_mv_access_compute_fresh_data='ON'
_new_initial_join_orders=TRUE
_new_sort_cost_estimate=TRUE
_nlj_batching_enabled=1
_odci_index_pmo_rebuild=FALSE # domain index pmo rebuild
_optim_enhance_nnull_detection=TRUE
_optim_peek_user_binds=TRUE
_optimizer_ads_use_partial_results=TRUE
_optimizer_better_inlist_costing='ALL'
_optimizer_cbqt_or_expansion='ON'
_optimizer_cluster_by_rowid_control=129
_optimizer_control_shard_qry_processing=65528
_optimizer_cost_based_transformation='LINEAR'
_optimizer_cost_model='CHOOSE'
_optimizer_extended_cursor_sharing='UDO'
_optimizer_extended_cursor_sharing_rel='SIMPLE'
_optimizer_extended_stats_usage_control=192
_optimizer_join_order_control=3
_optimizer_max_permutations=2000
_optimizer_mode_force=TRUE
_optimizer_native_full_outer_join='FORCE'
_optimizer_or_expansion='DEPTH'
_optimizer_proc_rate_level='BASIC'
_optimizer_system_stats_usage=TRUE
_optimizer_try_st_before_jppd=TRUE
_optimizer_use_cbqt_star_transformation=TRUE
_or_expand_nvl_predicate=TRUE
_ordered_nested_loop=TRUE
_parallel_broadcast_enabled=TRUE
_pga_max_size=240M
_pivot_implementation_method='CHOOSE'
_pred_move_around=TRUE
_push_join_predicate=TRUE
_push_join_union_view=TRUE
_push_join_union_view2=TRUE
_px_dist_agg_partial_rollup_pushdown='ADAPTIVE'
_px_groupby_pushdown='FORCE'
_px_partial_rollup_pushdown='ADAPTIVE'
_px_shared_hash_join=FALSE
_px_wif_dfo_declumping='CHOOSE'
_resource_manager_plan=''
_smm_max_size=122880
_smm_max_size_static=122880
_smm_min_size=1024
_smm_px_max_size=614400
_smm_px_max_size_static=614400
_sql_model_unfold_forloops='RUN_TIME'
_sqltune_category_parsed='DEFAULT' # parsed sqltune_category
_subquery_pruning_mv_enabled=FALSE
_table_scan_cost_plus_one=TRUE
_union_rewrite_for_gs='YES_GSET_MVS'
_unnest_subquery=TRUE
_use_column_stats_for_function=TRUE
_xt_sampling_scan_granules='ON'
audit_file_dest='C:\ORACLE\ADMIN\DB19\ADUMP'
audit_trail='DB'
compatible='19.0.0'
connection_brokers='((TYPE=DEDICATED)(BROKERS=1))'
connection_brokers='((TYPE=EMON)(BROKERS=1))' # connection_brokers default value
control_files='X:\ORACLE\ORADATA\DB19\CONTROL01.CTL'
control_files='X:\ORACLE\ORADATA\DB19\CONTROL02.CTL'
core_dump_dest='C:\oracle\diag\rdbms\db19\db19\cdump'
cpu_count=12
cpu_min_count='12'
db_block_size=8192
db_name='db19'
diagnostic_dest='C:\ORACLE'
dispatchers='(PROTOCOL=TCP) (SERVICE=db19XDB)'
enable_pluggable_database=TRUE
job_queue_processes=240 # job queue processes default tuning
local_listener='LISTENER_DB19'
log_buffer=7000K # log buffer update
nls_language='AMERICAN'
nls_territory='AMERICA'
open_cursors=300
optimizer_mode='ALL_ROWS'
pga_aggregate_target=1200M
plsql_warnings='DISABLE:ALL' # PL/SQL warnings at init.ora
processes=960
query_rewrite_enabled='TRUE'
remote_login_passwordfile='EXCLUSIVE'
resource_manager_plan=''
result_cache_max_size=20992K
sga_target=4G
undo_tablespace='UNDOTBS1'

Your options here are probably either

a) manually go through and remove all the stuff you *know* you never specified (underscore ones are obvious candidates here), or

b) if you have alert.log history, then scroll back through that to a time when your parameters were fine. You'll see something like this:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0.
Windows NT Version V6.2  
ORACLE_HOME = C:\oracle\product\19
Node name           : GTX
CPU                 : 12 - type 86646 physical cores
Process Affinity    : 0x0000000000000000
Memory (Avail/Total): Ph:17740M/32574M, Ph+PgF:29375M/56126M 
Using parameter settings in server-side spfile C:\ORACLE\PRODUCT\19\DATABASE\SPFILEDB19.ORA
System parameters with non-default values:
  processes                = 960
  nls_language             = "AMERICAN"
  nls_territory            = "AMERICA"
  sga_target               = 4G
  control_files            = "X:\ORACLE\ORADATA\DB19\CONTROL01.CTL"
  control_files            = "X:\ORACLE\ORADATA\DB19\CONTROL02.CTL"
  db_block_size            = 8192
  compatible               = "19.0.0"
  undo_tablespace          = "UNDOTBS1"
  remote_login_passwordfile= "EXCLUSIVE"
  dispatchers              = "(PROTOCOL=TCP) (SERVICE=db19XDB)"
  local_listener           = "LISTENER_DB19"
  audit_file_dest          = "C:\ORACLE\ADMIN\DB19\ADUMP"
  audit_trail              = "DB"
  db_name                  = "db19"
  open_cursors             = 300
  pga_aggregate_target     = 1200M
  diagnostic_dest          = "C:\ORACLE"
  enable_pluggable_database= TRUE

which is a list of the parameter we found in the spfile. Failing that, check out DBA_HIST_PARAMETER

Rating

(1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks for clarification

Christian, June 12, 2019 - 7:09 am UTC

Hi Connor,

thanks for your answer. I thought about that - but I don't trust all my ideas. :-)

Regards,

Chris

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Thanks for clarification

More to Explore

Administration

after repairing spfile (create pfile from spfile and create spfile from pfile) many parameters have isdefault='FALSE' in v$parameter

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Thanks for clarification

More to Explore

Administration