reading PDBs spfile parameters

Last week I was asked by a customer if changes in a PDBs spfile parameter can be monitored easily. 

My first thought was to check if AWR on CDB$ROOT level does this for me already - unfortunately it doesn't. 

So the next idea was to sample PDB_SPFILE$ on my own. 
Unfortunately this table is not documented by Oracle and my colleague Miguel Anjo found records there are not deleted when they are reset on PDB level. 

To identify such behavior, I like to have a look in all the nice files in $ORACLE_HOME/rdbms/admin.
In e1201000.sql I luckily found this code. Even e1201000.sql is used in case of downgrade, it's quite helpful.

Rem *************************************************************************
Rem BEGIN BUG 23300354 - remove HWM and flags column values in pdb_spfile$
Rem *************************************************************************
update pdb_spfile$ set
db_uniq_name=(select sys_context('userenv', 'DB_UNIQUE_NAME') from dual)
where db_uniq_name='*';
Rem Set HWM value to 0
update pdb_spfile$ set spare1=0;
Rem Delete deleted rows and reset flags to 0
delete from pdb_spfile$ where bitand(nvl(spare2,0),1) = 1;
update pdb_spfile$ set spare2=0;
commit;

bla

Another approach is to check what#s Oracle doing at UNPLUGGING a PDB: 

select value$ from pdb_spfile$ where name=:1 and pdb_uid=:2                   and bitand(nvl(spare2,0),1)=0

I tried the probably most obvious method: 

CDB$ROOT> select * from containers(v$spparameter);

But this only show results for CDB$OOT (CON_ID => 1). 

Even a test with x$kspspfile shows only values for CON_ID => 1. 

This is quite sad, especially as x$kspspfile IS used when I query v$spparameter from my PDB1 (which has CON_ID=3 in this test), but there seems to be some additional magic in x$kspspfile which I can not mitigate on SQL layer.

possible changes in Oracle 20

As this post is written in July 2019, it's highly speculative writing about changes in Oracle version 20.
But at least it's based on some information available from Oracle.

A new autoupgrade.jar version is available at AutoUpgrade Tool (Doc ID 2485457.1). In this Note, there is also a BUGS_20190715.txt which contains (among others):

AUPG-955 Disable NONCDB validation when upgrading a DB to 20c
AUPG-975 upgrade from 12.1.0.2 to 20 is not supported

Based on that I assume, noncdb will not be officially supported anymore, but maybe it's not supported in the autoupgrade.jar, but manual steps are still allowed & supported - we will se.
Also a direct upgrade from 12.1 to 20 is out of scope of this tool.

But there is more information available.
in the autoupgrade.jar in directory resources/preupgrade/config/ there is a file parameters20.properties. It contains information about deprecated/obsolete/removed parameters.

The changes in comparison to 19 I found:

from deprecated to removed:
unified_audit_sga_queue_size

from deprecated to obsolete:
cluster_database_instances

new parameters and their status:

PARAMETER	STATUS
_arch_sim_mode	removed
_gc_async_receive	removed
_hcs_disable_sp_jback_opt	removed
_autonomous_performance_features_index	removed
_cgs_os_level_connection_reqno	deprecated
_autonomous_performance_features_stats	removed
_px_monitor_load	obsolete
_hcs_disable_level_prune_vis_lvs	removed
_hcs_disable_level_prune_gby	removed
_asm_enable_parity_datafile_creation	removed
_part_access_version_by_number	obsolete
_tablespace_encryption_default_algorithm	removed
_hcs_disable_cell_qry_meas_prune_opt	removed
_unified_audit_flush_threshold	removed
_optimizer_stats_on_conventional_dml_config	removed
_reset_maxcap_history	removed
_ldr_io_size2	obsolete
_hang_long_wait_time_threshold	removed
_read_mostly_instance	deprecated
_cgs_os_level_connection_pthreadno	removed
_max_cr_rollbacks	deprecated
_hcs_enable_multi_parent_gen	removed
_disable_thread_snapshot	removed
_hcs_disable_tgt_depths_opt	removed
_enable_imc_mira	removed
_log_archive_avoid_memcpy	removed
_gcs_min_slaves	deprecated
_hcs_enable_dynamic_cache	removed
_unified_audit_flush_interval	removed
_hcs_disable_hord_in_oby_prune	removed
_ta_lns_wait_for_arch_log	removed
_hcs_disable_jback_opt_for_hord_in_oby	removed
_show_mgd_recovery_state	removed
_hcs_disable_pushed_preds_in_gen_sql	removed
_hcs_disable_cell_qry_tmpls	removed
_test_param_5	deprecated
_autonomous_performance_features	removed
_session_percent_threshold	removed
_ash_compression_enable	deprecated
_kebm_max_parallel_intensive_autotasks	removed

let's see if this will change in next iterations.

The biggest value of such sources (for me) is the reliability. It's not something a vendor wants the customers to know/act on (like a "what changed" document would be); it's the information required by tools, so no political filters are there.

SecureFile LOB - spfile parameters

Here is a list of all the spfile parameters I found (and guess are) related to SecureFile LOBs.

This Post will be updated with additional information whenever I collect them.

Parameter	Default value	Description
_dbfs_modify_implicit_fetch	TRUE 12.1.0.2	DBFS Link allows implicit fetch on modify - only on SecureFiles
_enable_securefile_flashback_opt	FALSE 12.1.0.2	Enable securefile flashback optimization
_kdli_STOP_bsz	0 12.1.0.2	undocumented parameter for internal use only
_kdli_STOP_dba	0 12.1.0.2	undocumented parameter for internal use only
_kdli_STOP_fsz	0 12.1.0.2	undocumented parameter for internal use only
_kdli_STOP_nio	0 12.1.0.2	undocumented parameter for internal use only
_kdli_STOP_tsn	0 12.1.0.2	undocumented parameter for internal use only
_kdli_allow_corrupt	TRUE 12.1.0.2	allow corrupt filesystem_logging data blocks during read/write
_kdli_buffer_inject	TRUE 12.1.0.2	use buffer injection for CACHE [NO]LOGGING lobs
_kdli_cache_inode	TRUE 12.1.0.2	cache inode state across calls
_kdli_cache_read_threshold	0 12.1.0.2	minimum lob size for cache->nocache read (0 disables heuristic)
_kdli_cache_size	8 12.1.0.2	maximum #entries in inode cache
_kdli_cache_verify	FALSE 12.1.0.2	verify cached inode via deserialization
_kdli_cache_write_threshold	0 12.1.0.2	minimum lob size for cache->nocache write (0 disables heuristic)
_kdli_cacheable_length	0 12.1.0.2	minimum lob length for inode cacheability
_kdli_checkpoint_flush	FALSE 12.1.0.2	do not invalidate cache buffers after write
_kdli_dbc	none 12.1.0.2	override db_block_checking setting for securefiles
_kdli_delay_flushes	TRUE 12.1.0.2	delay flushing cache writes to direct-write lobs
_kdli_descn_adj	FALSE 12.1.0.2	coalesce extents with deallocation scn adjustment
_kdli_flush_cache_reads	FALSE 12.1.0.2	flush cache-reads data blocks after load
_kdli_flush_injections	TRUE 12.1.0.2	flush injected buffers of CACHE NOLOGGING lobs before commit
_kdli_force_cr	TRUE 12.1.0.2	force CR when reading data blocks of direct-write lobs
_kdli_force_cr_meta	TRUE 12.1.0.2	force CR when reading metadata blocks of direct-write lobs
_kdli_force_storage	none 12.1.0.2	force storage settings for all lobs
_kdli_full_readahead_threshold	0 12.1.0.2	maximum lob size for full readahead
_kdli_inject_assert	0 12.1.0.2	inject asserts into the inode
_kdli_inject_batch	0 12.1.0.2	buffer injection batch size [1, KCBNEWMAX]
_kdli_inject_crash	0 12.1.0.2	inject crashes into the inode
_kdli_inline_xfm	TRUE 12.1.0.2	allow inline transformed lobs
_kdli_inode_preference	data 12.1.0.2	inline inode evolution preference (data, headless, lhb)
_kdli_inplace_overwrite	0 12.1.0.2	maximum inplace overwrite size (> chunksize)
_kdli_itree_entries	0 12.1.0.2	#entries in lhb/itree blocks (for testing only)
_kdli_memory_protect	FALSE 12.1.0.2	trace accesses to inode memory outside kdli API functions
_kdli_mts_so	TRUE 12.1.0.2	use state objects in shared server for asyncIO pipelines
_kdli_oneblk	FALSE 12.1.0.2	allocate chunks as single blocks
_kdli_preallocation_mode	length 12.1.0.2	preallocation mode for lob growth
_kdli_preallocation_pct	0 12.1.0.2	percentage preallocation [0 .. inf) for lob growth
_kdli_ralc_length	10485760 12.1.0.2	lob length threshold to trigger rounded allocations
_kdli_ralc_rounding	1048576 12.1.0.2	rounding granularity for rounded allocations
_kdli_rci_lobmap_entries	255 12.1.0.2	#entries in RCI lobmap before migration to lhb
_kdli_readahead_limit	0 12.1.0.2	shared/cached IO readahead limit
_kdli_readahead_strategy	contig 12.1.0.2	shared/cached IO readahead strategy
_kdli_recent_scn	FALSE 12.1.0.2	use recent (not dependent) scns for block format/allocation
_kdli_reshape	FALSE 12.1.0.2	reshape an inode to inline or headless on length truncation
_kdli_safe_callbacks	TRUE 12.1.0.2	invoke inode read/write callbacks safely
_kdli_sio_async	TRUE 12.1.0.2	asynchronous shared IO
_kdli_sio_backoff	FALSE 12.1.0.2	use exponential backoff when attempting SIOP allocations
_kdli_sio_bps	0 12.1.0.2	maximum blocks per IO slot
_kdli_sio_dop	2 12.1.0.2	degree-of-parallelism in the SIO keep pool
_kdli_sio_fbwrite_pct	35 12.1.0.2	percentage of buffer used for direct writes in flashback-db
_kdli_sio_fgio	TRUE 12.1.0.2	reap asynchronous IO in the foreground
_kdli_sio_fileopen	none 12.1.0.2	shared IO fileopen mode: datasync vs nodatasync vs async
_kdli_sio_flush	FALSE 12.1.0.2	enable shared IO pool operations
_kdli_sio_free	TRUE 12.1.0.2	free IO buffers when not in active use
_kdli_sio_min_read	0 12.1.0.2	shared IO pool read threshold
_kdli_sio_min_write	0 12.1.0.2	shared IO pool write threshold
_kdli_sio_nbufs	8 12.1.0.2	maximum #IO buffers to allocate per session
_kdli_sio_niods	8 12.1.0.2	maximum #IO descriptors to allocate per session
_kdli_sio_on	TRUE 12.1.0.2	enable shared IO pool operations
_kdli_sio_pga	FALSE 12.1.0.2	use PGA allocations for direct IO
_kdli_sio_pga_top	FALSE 12.1.0.2	PGA allocations come from toplevel PGA heap
_kdli_sio_strategy	extent 12.1.0.2	shared IO strategy: block vs. extent
_kdli_sio_write_pct	100 12.1.0.2	percentage of buffer used for direct writes
_kdli_small_cache_limit	32 12.1.0.2	size limit of small inode cache
_kdli_sort_dbas	FALSE 12.1.0.2	sort dbas during chunkification
_kdli_space_cache_limit	2048 12.1.0.2	maximum #blocks in per-segment space cache
_kdli_space_cache_segments	16 12.1.0.2	#segments in space cache
_kdli_squeeze	TRUE 12.1.0.2	compact lobmap extents with contiguous dbas
_kdli_timer_dmp	FALSE 12.1.0.2	dump inode timers on session termination
_kdli_timer_trc	FALSE 12.1.0.2	trace inode timers to uts/tracefile
_kdli_trace	0 12.1.0.2	inode trace level
_kdli_vll_direct	TRUE 12.1.0.2	use skip-navigation and direct-positioning in vll-domain
_kdlxp_dedup_hash_algo	SHA1 12.1.0.2	secure hash algorithm for deduplication - only on SecureFiles
_kdlxp_lobcmpadp	FALSE 12.1.0.2	enable adaptive compression - only on SecureFiles
_kdlxp_lobcmplevel	2 12.1.0.2	Default securefile compression
_kdlxp_lobcmprciver	1 12.1.0.2	Default securefile compression map version
_kdlxp_lobcompress	FALSE 12.1.0.2	enable lob compression - only on SecureFiles
_kdlxp_lobdeduplicate	FALSE 12.1.0.2	enable lob deduplication - only on SecureFiles
_kdlxp_lobdedupvalidate	TRUE 12.1.0.2	enable deduplicate validate - only on SecureFiles
_kdlxp_lobencrypt	FALSE 12.1.0.2	enable lob encryption - only on SecureFiles
_kdlxp_mincmp	20 12.1.0.2	minimum comp ratio in pct - only on SecureFiles
_kdlxp_mincmplen	200 12.1.0.2	minimum loblen to compress - only on SecureFiles
_kdlxp_uncmp	FALSE 12.1.0.2	lob data uncompressed - only on SecureFiles
_kdlxp_xfmcache	TRUE 12.1.0.2	enable xfm cache - only on SecureFiles
_securefile_log_num_latches	0 12.1.0.2	Maximum number of open descriptors for securefile log
_securefile_log_shared_pool_size	0 12.1.0.2	Size of securefile log buffer pool from SGA
_securefile_timers	FALSE 12.1.0.2	collect kdlu timers and accumulate per layers
_securefiles_breakreten_retry	5 12.1.0.2	segment retry before dishonoring retention
_securefiles_bulkinsert	FALSE 12.1.0.2	securefiles segment insert only optization
_securefiles_concurrency_estimate	12 12.1.0.2	securefiles concurrency estimate
_securefiles_fg_retry	100 12.1.0.2	segment retry before foreground waits
_securefiles_forceflush	FALSE 12.1.0.2	securefiles force flush before allocation
_securefiles_memory_percentofSGA	8 12.1.0.2	securefiles memory as percent of SGA
_securefiles_spcutl	FALSE 12.1.0.2	securefiles segment utl optimization
_sf_default_enabled	TRUE 12.1.0.2	enable 12g securefile default
db_securefile	ALWAYS 12.1.0.2	permit securefile storage during lob creation

---

_kdli_trace

Seems to be a bitmap of flags.
the only value visible in MOS is

alter session set "_kdli_trace"=257;

Also used together with

alter session set "_securefile_timers"=true;
alter session set "_kdli_timer_dmp"=true;
alter session set events '10046 trace name context forever,level 12';
alter session set events '45050 trace name context forever, level  2056';
-- Event 45050 level 0x808 (Space event)

Bug 17283676 : MOVE LOB EXTREMELY SLOW

---

_securefiles_concurrency_estimate

Securefiles DMLs cause high 'buffer busy waits' & 'enq: TX - contention' wait events leading to whole database performance degradation (Doc ID 1532311.1)

---

2 spfiles - some confusion

At the moment I'm in a big migration project from 9i to 11gR2.
Most of the migrations are done like

• create new DB


• exp/imp


• delete old DB

Even we have templates for all these steps, sometimes something goes wrong.

Today I hit such a something:
As we use Clusters for most of our DBs, cluster_database=TRUE should be set, otherwise it's generating problems. At the Creation of one DB, the 2nd instance did not come up. After some research I found out cluster_database was set to FALSE. No big deal, let's change it:

alter database set cluster_database=TRUE scope=spfile;

But after a srvctl stop instance -d DB_UNIQUE_NAME -i INSTANCE_NAME

srvctl start instance -d DB_UNIQUE_NAME -i INSTANCE_NAME a show parameter cluster_database still gave me FALSE. This confused me!
So I checked $ORACLE_HOME/dbs/initINSTANCE_NAME and there I found spfile=+ASM_DG/DB_UNIQUE_NAME/PARAMETERFILE/spfile.268.745343891 +ASM_DG/DB_UNIQUE_NAME/PARAMETERFILE/spfile.269.745406153 there where 2 spfiles in the pfile-definition!
After a

create pfile='/tmp/pfile1' from spfile='+ASM_DG/DB_UNIQUE_NAME/PARAMETERFILE/spfile.268.745343891';
create pfile='/tmp/pfile2' from spfile='+ASM_DG/DB_UNIQUE_NAME/PARAMETERFILE/spfile.269.745406153';

and a diff /tmp/pfile1 /tmp/pfile2 I decided to use only the 1st spfile in the $ORACLE_HOME/dbs/initINSTANCE_NAME and apply all the differences manually.
Now both instances are up and fine - I just have to delete the correct spfile in ASM.